In: Math
The following data represent a company's yearly sales volume and its advertising expenditure over a period of 8 years.
Sales (Millions) |
Advertising ($10,000) |
15 |
32 |
16 |
33 |
18 |
35 |
17 |
34 |
16 |
36 |
19 |
37 |
19 |
39 |
24 |
42 |
To make sure the Excel regression package is installed.
Click “Tools”, à“Add-Ins” àchoose “Analysis Toolpak” and click “OK”.
To use the Excel regression package,
Click “Tools”, à“Data Analysis” and click “Regression”.
a. Use the method of least squares to compute an estimated regression line between sales and advertising.
b. If the company's advertising expenditure is $400,000, what are the predicted sales? Give the answer in dollars.
c. What does the slope of the estimated regression line indicate?
d. What is the coefficient of determination and fully interpret its meaning.
e. What is the standard error of the estimation?
f. Use the F test to determine whether or not the regression model is significant at a= 0.05.
g. Use the t test to determine whether the slope of the regression model is significant at a= 0.05.
h. Develop a 99% confidence interval for the slope of the regression model.
Regression Statistics | ||||||
Multiple R | 0.9197 | |||||
R Square | 0.8459 | |||||
Adjusted R Square | 0.8202 | |||||
Standard Error | 1.1994 | |||||
Observations | 8 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 1 | 47.4 | 47.4 | 32.93 | 0.0012 | |
Residual | 6 | 8.6 | 1.4 | |||
Total | 7 | 56.0 | ||||
Coefficients | Standard Error | t Stat | P-value | Lower 99% | Upper 99% | |
Intercept | -10.42105 | 4.9711 | -2.0963 | 0.0809 | -28.8510 | 8.0089 |
X | 0.78947 | 0.1376 | 5.7382 | 0.0006 | 0.2794 | 1.2996 |
a)
so, regression line is Ŷ =
-10.4211 + 0.7895 *x
b)Predicted Y at X= 40 is
Ŷ = -10.4211 +
0.7895 * 40 =
21.157894736842
answer: $21157895
c)
on average, increasing Advertising expenditure by $1000, predicted sales increase by $789474
d)R Square = 0.8459
about 84.59% of variation in observation of Y is explained by variable X
e)std error ,Se = 1.1994
f)
F=32.93
p value= 0.0012<α=0.05, so, regression model is useful
g)
Ho: ß1= 0
H1: ß1 = 0
T test stat=5.7382
p value= 0.0012<α=0.05, so, slope is significant
h)
99% CI is
lower confidence limit = 0.279
upper confidence limit= 1.300