Below you are given a partial Excel output based on a sample of 12 observations
ID: 452902 • Letter: B
Question
Below you are given a partial Excel output based on a sample of 12 observations relating the number of computers sold by a computer shop per month (y), unit price (x1 in $1,000) and the number of advertising spots (x2) used on a local radio station.
Coefficient
Standard Error
Intercept
17.145
7.865
x1
-0.104
3.282
x2
1.376
0.250
a.
Use the output shown above and write an equation that can be used to predict the monthly sales of computers.
b.
Interpret the coefficients of the estimated regression equation found in Part a.
c.
If the company charges $2,000 for each computer and uses 10 advertising spots, how many computers would you expect them to sell?
d.
At = 0.05, test to determine if the price is a significant variable.
e.
At = 0.05, test to determine if the number of advertising spots is a significant variable.
Coefficient
Standard Error
Intercept
17.145
7.865
x1
-0.104
3.282
x2
1.376
0.250
Explanation / Answer
a.
Use the output shown above and write an equation that can be used to predict the monthly sales of computers.
ANSWER - Equation: y = 17.145 -0.104x1 + 1.376x2
b.
Interpret the coefficients of the estimated regression equation found in Part a.
ANSWER - Interpretation of coefficient of x1 – for every $1000 increase in price, the number of computers sold decreases by 0.104 units.
Interpretation of coefficient of x1 – for per unit increase in advertising spots, the number of computers sold decreases by 1.376 units.
c.
If the company charges $2,000 for each computer and uses 10 advertising spots, how many computers would you expect them to sell?
ANSWER - Computers Sold (y) = 17.145 -0.104*2 + 1.376*10 = 30.697 ~ 31 units
d.
At = 0.05, test to determine if the price is a significant variable.
ANSWER –
t = -0.104 / 3.282 = -0.032 , df = 12-3 = 9, p-value = 0.9754 .
t-critical for ( = 0.05) = 2.262
P-value > 0.2 , therefore null hypothesis is not rejected and therefore price is not a significant variable.
p-value is calculated by excel formula TDIST(abs(t-value),df,2)
t-critical is calculated by excel formula TINV(, df)
df – degree of freedom = number of samples (n) – number of independent variables – 1
e.
At = 0.05, test to determine if the number of advertising spots is a significant variable.
ANSWER –
t = -1.376 / 0.250 = 5.504, df = 12-3 = 9, p-value = 0.0004 .
t-critical for ( = 0.05) = 2.262
P-value < 0.01 , therefore null hypothesis is rejected and therefore advertising spots is a significant variable.
a.
Use the output shown above and write an equation that can be used to predict the monthly sales of computers.
ANSWER - Equation: y = 17.145 -0.104x1 + 1.376x2
b.
Interpret the coefficients of the estimated regression equation found in Part a.
ANSWER - Interpretation of coefficient of x1 – for every $1000 increase in price, the number of computers sold decreases by 0.104 units.
Interpretation of coefficient of x1 – for per unit increase in advertising spots, the number of computers sold decreases by 1.376 units.
c.
If the company charges $2,000 for each computer and uses 10 advertising spots, how many computers would you expect them to sell?
ANSWER - Computers Sold (y) = 17.145 -0.104*2 + 1.376*10 = 30.697 ~ 31 units
d.
At = 0.05, test to determine if the price is a significant variable.
ANSWER –
t = -0.104 / 3.282 = -0.032 , df = 12-3 = 9, p-value = 0.9754 .
t-critical for ( = 0.05) = 2.262
P-value > 0.2 , therefore null hypothesis is not rejected and therefore price is not a significant variable.
p-value is calculated by excel formula TDIST(abs(t-value),df,2)
t-critical is calculated by excel formula TINV(, df)
df – degree of freedom = number of samples (n) – number of independent variables – 1
e.
At = 0.05, test to determine if the number of advertising spots is a significant variable.
ANSWER –
t = -1.376 / 0.250 = 5.504, df = 12-3 = 9, p-value = 0.0004 .
t-critical for ( = 0.05) = 2.262
P-value < 0.01 , therefore null hypothesis is rejected and therefore advertising spots is a significant variable.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.