Question

#1) Use computer software packages, such as Excel, to solve this problem. The owner of Showtime Movie Theaters, Inc., would like to predict weekly gross revenue as a function of advertising expenditures.

Weekly Gross Revenue ($1,000s)	Television Advertising ($1,000s)	Newspaper Advertising ($1,000s)
105	5.0	1.5
90	2.0	2.0
95	4.0	1.5
92	2.5	2.5
93	3.0	3.3
94	3.5	2.3
94	2.5	4.2

a) Develop an estimated regression equation with the amount of television advertising as the independent variable (to 2 decimals).

b) Develop an estimated regression equation with both television advertising and newspaper advertising as the independent variables (to 2 decimals).

c) Using the multiple regression model in part b, predict weekly gross revenue for a week when $3,300 is spent on television advertising and $1,700 thousand is spent on newspaper advertising? (Give your answer in dollars, rounded to the nearest dollar). Find a 95% confidence interval around your prediction.

Answer 1

a) Develop an estimated regression equation with the amount of television advertising as the independent variable (to 2 decimals).

From the information, the dependent variable is,Weekly Gross Revenue (Y)  and the independent variables are Television Advertising (X1) and Newspaper Advertising (X2).

Instructions to build a regression model to predict the weekly gross revenue, with the amount of television advertising.

1.Import the data into Excel worksheet.

2.Go to Data Data Analysis Regression OK.

3.Select the variable weekly gross revenue into Input Y Range and variable television advertising into input X range.

4.Select the option Labels.

5.Click OK.

Follow the above instructions to get the regression output as shown below:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.835509978
R Square	0.698076923
Adjusted R Square	0.622596154
Standard Error	1.098950548
Observations	6
ANOVA
	df	SS	MS	F	Significance F
Regression	1	11.16923077	11.16923	9.248407643	0.038360151
Residual	4	4.830769231	1.207692
Total	5	16
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	87.0769	1.9986	43.5674	1.6595E-06	81.5277	92.6261
Television Avt	2.0307	0.6677	3.0411	0.038360151	0.1767	3.8847

The estimated regression equation is,

b) Develop an estimated regression equation with both television advertising and newspaper advertising as the independent variables (to 2 decimals).

1) The regression model to predict the weekly gross revenue with the amount of television advertising is,

  

2) The regression model to predict the weekly gross revenue with the amount of newspaper advertising is,

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.115153179
R Square	0.013260255
Adjusted R Square	-0.233424682
Standard Error	1.986695493
Observations	6
ANOVA
	df	SS	MS	F	Significance F
Regression	1	0.212164074	0.212164074	0.053753808	0.8280337
Residual	4	15.78783593	3.946958982
Total	5	16
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	92.4413	2.5425	36.3571	3.41667E-06	85.3819	99.5006
Newspaper Advt	0.2121	0.9150	0.2318	0.8280	-2.3285	2.7528

The regression model to predict the weekly gross revenue with the amount of newspaper advertising is,

  

c) Using the multiple regression model in part b, predict weekly gross revenue for a week when $3,300 is spent on television advertising.

Using the multiple regression model in part b, predict weekly gross revenue for a week when $1,700 thousand is spent on newspaper advertising.