a Develop an estimated regression equation with the amount o

a.  Develop an estimated regression equation with the amount of television advertising as the independent variable.

b.  Develop an estimated regression equation with both television advertising and newspaper advertising as the independent variables.

c.  Is the estimated regression equation coefficient for television advertising expenditures the same in part (a) and in part (b)? Interpret the coefficient in each case.

d.  Predict the weekly gross revenue for a week when $3500 is spent on television advertising and $1800 is spent on newspaper advertising (in thousands to 2 decimals).

Please show in Excel form, Thank You!

a. Develop an estimated regression equation with the amount of television advertising as the independent variable. b. Develop an estimated regression equation with both television advertising and newspaper advertising as the independent variables. c. Is the estimated regression equation coefficient for television advertising expenditures the same in part (a) and in part (b)? Interpret the coefficient in each case. d. Predict the weekly gross revenue for a week when $3500 is spent on television advertising and $1800 is spent on newspaper advertising (in thousands to 2 decimals). Please show in Excel form, Thank You!

Solution

Enter the data in excel sheet in cell A1 through C8.

a.

To estimate regression equation with the amount of television advertising as the independent variable, use LINEST function. Select cells E1 through F5. The parameters of the function will be weekly gross revenue as known y\'s and television advertising as known x\'s and 1 and 1 will be the logical values as shown, =LINEST(A1:A8,B1:B8,1,1) and press Ctrl+Shift+Enter.

The value in E1 is the slope and F1 gives the intercept term.

The fitted regression line is Weekly Gross Revenue = 88.64 + 1.60 Television Advertising

The estimated Weekly Gross Revenue is.

b.

To estimate regression equation with both amount of television advertising and newspaper advertising as the independent variable, use LINEST function. Select cells I1 through K5. The parameters of the function will be weekly gross revenue as known y\'s and television advertising as known x\'s and 1 and 1 will be the logical values as shown, =LINEST(A1:A8,B1:C8,1,1) and press Ctrl+Shift+Enter.

The value in I1 is the coefficient of Telivision Advertising, J1 is the coefficient of Newspaper Advertising and K1 gives the intercept term.

The fitted regression line is Weekly Gross Revenue = 83.23 + 1.30 Television Advertising + 2.29 Newspaper Advertising

The estimated Weekly Gross Revenue is.

c. The estimated regression equation coefficient for television advertising expenditures is different in part (a) and in part (b). This is because, in part (a), television advertising is the only independent variable, where as in part (b), television advertising and newspaper advertising both effect Revenue.

d. the weekly gross revenue for a week when $3500 is spent on television advertising and $1800 is spent on newspaper advertising is given by

Weekly Gross Revenue = 83.23 + 1.30 x 3.5 + 2.29 x 1.8 = 91.90 ($1000s)

Weekly Gross Revenue	Television Advertising	Estimated Weekly Gross Revenue
96	5.0	96.66
90	2.0	91.85
95	4.0	95.05
92	2.5	92.65
95	3.0	93.45
94	3.5	94.25
94	2.5	92.65
94	3.0	93.45