In: Statistics and Probability
1. The owner of Showtime Movie Theaters, Inc. would like to estimate weekly gross revenue as a function of advertising expenditures. Historical data for a sample of eight weeks follow.
Weekly Gross |
Television |
Newspaper |
Radio |
Revenue |
Advertising |
Advertising |
Advertising |
($1000s) |
($1000s) |
($1000s) |
($1000s) |
96 |
5 |
1.5 |
0.3 |
90 |
2 |
2 |
0.2 |
95 |
4 |
1.5 |
0.3 |
92 |
2.5 |
2.5 |
0.1 |
95 |
3 |
3.3 |
0.4 |
94 |
3.5 |
2.3 |
0.4 |
94 |
2.5 |
4.2 |
0.3 |
94 |
3 |
2.5 |
0.3 |
PLEASE SHOW IN EXCEL AND HOW TO DO IT. Thank you!
Ans a )
SUMMARY OUTPUT | ||||||
Regression Statistics | ||||||
Multiple R | 0.807807 | |||||
R Square | 0.652553 | |||||
Adjusted R Square | 0.594645 | |||||
Standard Error | 1.215175 | |||||
Observations | 8 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 1 | 16.6401 | 16.6401 | 11.26881 | 0.015288 | |
Residual | 6 | 8.859903 | 1.476651 | |||
Total | 7 | 25.5 | ||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
Intercept | 88.63768 | 1.582367 | 56.01588 | 2.17E-09 | 84.76577 | 92.50959 |
Tv Ads | 1.603865 | 0.477781 | 3.356905 | 0.015288 | 0.434777 | 2.772952 |
Ans b )
SUMMARY OUTPUT | ||||||
Regression Statistics | ||||||
Multiple R | 0.958663 | |||||
R Square | 0.919036 | |||||
Adjusted R Square | 0.88665 | |||||
Standard Error | 0.642587 | |||||
Observations | 8 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 2 | 23.43541 | 11.7177 | 28.37777 | 0.001865 | |
Residual | 5 | 2.064592 | 0.412918 | |||
Total | 7 | 25.5 | ||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
Intercept | 83.23009 | 1.573869 | 52.88248 | 4.57E-08 | 79.18433 | 87.27585 |
Tv Ads | 2.290184 | 0.304065 | 7.531899 | 0.000653 | 1.508561 | 3.071806 |
News ads | 1.300989 | 0.320702 | 4.056697 | 0.009761 | 0.476599 | 2.125379 |
Ans c )
SUMMARY OUTPUT | ||||||
Regression Statistics | ||||||
Multiple R | 0.965593 | |||||
R Square | 0.93237 | |||||
Adjusted R Square | 0.881648 | |||||
Standard Error | 0.656613 | |||||
Observations | 8 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 3 | 23.77544 | 7.925146 | 18.38183 | 0.00838 | |
Residual | 4 | 1.724561 | 0.43114 | |||
Total | 7 | 25.5 | ||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
Intercept | 83.5228 | 1.641648 | 50.87741 | 8.93E-07 | 78.96485 | 88.08074 |
Tv Ads | 2.079043 | 0.39123 | 5.314125 | 0.006029 | 0.992815 | 3.165271 |
News ads | 1.124548 | 0.383224 | 2.934439 | 0.042625 | 0.060547 | 2.18855 |
Radio ad | 2.84172 | 3.199861 | 0.888076 | 0.424677 | -6.04252 | 11.72596 |
Ans d ) the estimated regression equation coefficient for television advertising expenditures is not same in par a), in part b) and in part c)
in a ) for every one $ increase in tv ad there is 1.60$ increase in revenue
in b ) for every one $ increase in tv ad there is 2.29$ increase in revenue
in c ) for every one $ increase in tv ad there is 2.07$ increase in revenue
Ans e) for a )
R Square | 0.652553 |
Adjusted R Square | 0.594645 |
for b
R Square | 0.919036 |
Adjusted R Square | 0.88665 |
for c
R Square | 0.93237 |
Adjusted R Square | 0.881648 |
the coefficient of determination increases as we add more independent variables in the equation.
ANs e ) revenue = 83.5228+ 2.079 Tv ads + 1.125 news ad + 2.842 radio ad
revenue= 83.5228+ 2.079*3500+1.125*1800+2.842*350 = 10378.96