Question

The following scenario is to be used to complete Case Study 2 – Regression Analysis. Please create your output in Excel, Copy it to Microsoft Word and answer the questions below. Everything should be in one word file. Please copy and paste the excel output created as the last page of the assignment, after the answers to the questions.

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 follows.

Weekly Gross Revenue ($1000s)	Television Advertising ($1000s)	Newspaper Advertising ($1000)
96	5.0	1.5
90	2.0	2.0
95	4.0	1.5
92	2.5	2.5
95	3.0	3.3
94	3.5	2.3
94	2.5	4.2
94	3.0	2.5

How many independent variables are there?

3 independent variables

List and label each independent variable (x?, x?, etc.)

X1 = Weekly gross revenue

X2= Television Advertising

X3= Newspaper advertising

Develop a simple linear regression equation using ONLY the amount of television as the independent variable. (Include this output)

Develop a simple linear regression equation using ONLY the newspaper advertising as the independent variable. (Include this output)

Develop a multiple regression equation using the amount of television and newspaper advertising as the independent variables. (Include this output)

Answer the following questions based on the multiple regression output ONLY!!

What is the proportion of variation in Weekly Gross Revenue due to television advertising and newspaper advertising?

What is the strength of the linear relationship between the amount of television, newspaper advertising and weekly gross revenue?

List the SSR, SSE, SST, MSR, MSE.

Give the value of F.

What is the p value for this regression model? P = (two decimal places)

Is this model useful? If so, why and if not, why not. If the model is useful, proceed to question 12.

If the model is useful, estimate the weekly gross revenue for a week when $3500 is spent on television advertising and $1800 is spent on newspaper advertising?

Are each of the variables good for the model? List their p values and explain your answer.

Need assistance on answering please

Answer 1

How many independent variables are there?

There are 2 independent variables namely Television Advertising and Newspaper Advertising.

Steps for doing regression analysis in excel.

Below you can find our data. The big question is: is there a relation between Weekly gross revenue (Output) and expenditure on Television and Newspaper advertising (Input). In other words: can we predict Weekly gross revenue if we know Television and Newspaper advertising expenditures?

1. On the Data tab, in the Analysis group, click Data Analysis.

Note: If you can't find the Data Analysis button, You will have to load the Analysis ToolPak add-in.

2. Select Regression and click OK.

3. Select the Y Range (A1:A9). This is called dependent variable.

4. Select the X Range (B1:B9) for Television advertising expenditure only, (C1:C9) for Newspaper advertising expenditure only and (B1:C9) for Television and Newspaper advertising expenditures. These are the explanatory variables (also called independent variables). These columns must be adjacent to each other.

5. Check Labels.

6. Click New Worksheet Ply in Output option.

8. Click OK.

Once you click Ok, you will get summary output table.

Excel Summary Output (rounded to 4 decimal places) using the television advertising as the independent variable:

Estimated regression equation that can be used to predict the weekly gross using ONLY the television advertising as the independent variable is as follows:

Weekly Gross Revenue = 88.6377 + 1.6039*Television Advertising

Excel Summary Output (rounded to 4 decimal places) using ONLY the newspaper advertising as the independent variable

Estimated regression equation that can be used to predict the weekly gross revenue using ONLY the newspaper advertising as the independent variable is as follows:

Weekly Gross Revenue = 93.8564 - 0.0430*Newspaper Advertising

Excel summary output using the amount of television and newspaper advertising as the independent variables:

Estimated regression equation that can be used to predict the weekly gross revenue using the amount of television and newspaper advertising as the independent variables is as follows:

Weekly Gross Revenue = 83.2301 + 2.2902*Television Advertising + 1.3010*Newspaper Advertising

R-squared is a statistical measure of how close the data are to the fitted regression line. It is also known as the coefficient of determination, or the coefficient of multiple determination for multiple regression. In case of multiple regression, we can see that R square value is 0.9190. This means that 91.90% variation in Weekly Gross Revenue is expalined by Television and Newspaper advertising expenditures.

From the ANOVA table of multiple regression output, we can see that

Sum of Squares of Error (SSE) = 2.0646

Sum of Squares Regression (SSR) = 23.4354

Total Sum of Squares (SST) = 25.5

Mean square due to regression (MSR) = 11.7177

Mean square due to error (MSE) = 0.4129

F value = MSR/MSE = 28.3778

p value for this regression model = 0.0019 = 0.00 (Rounded to 2 decimal places)

The F-ratio in the ANOVA table tests whether the overall regression model is a good fit for the data. The table shows that the independent variables statistically significantly predict the dependent variable, F(2, 5) = 28.3778, p < .05 (i.e., the regression model is a good fit of the data). Thus, it can be said that the model is useful.

Now, it is given that $3500 is spent on television advertising and $1800 is spent on newspaper advertising. For predicting weekly gross revenue, we need to substitute television advertising as 3.5 and newspaper advertising 1.8 in the multiple regresison equation.

Weekly Gross Revenue = 83.2301 + 2.2902*Television Advertising + 1.3010*Newspaper Advertising

Weekly Gross Revenue = 83.2301 + 2.2902*3.5 + 1.3010*1.8

Weekly Gross Revenue = 93.5876

Weekly Gross Revenue = $ 9358.76

We have solved 9 subparts of the question