Question

You work for a supermarket that is considering the best way to promote sales of its store-brand canned vegetables. Store managers believe allocating additional shelf space to the store-brand canned vegetables would create additional sales. Company executives, on the other hand, believe increasing advertising expenditures would be a more effective strategy to expand sales. Complete a regression analysis to help answer this question (use the CANVEG Excel file posted on Canvas). NOTE: consider only a simple linear model, a multiple linear regression model, or an interaction model

1.Write out the equation that represents the hypothesized population regression equation for your model

2.Explain whether a positive or negative relationship is hypothesized

3.Use the “Microsoft Excel Data Analysis Toolpak” to determine the regression coefficients for the relationship and write out the final estimated regression equation. You must include the MS Excel output.

4.Give a practical interpretation of the slope(s) of the least squares line

5.Over what range of x-values is the interpretation meaningful?

6.Does the estimated slope support the relationship between the two variables you initially hypothesized (in parts a and b)? Explain.

7.Evaluate the overall utility of the model including any relevant hypothesis tests (be sure to fully write all 6-steps of any hypothesis test conducted, you can specify the rejection region in words it is not necessary to include a graph though you can if you prefer to).

8.Include any hypothesis tests of individual coefficients that are appropriate given your choice of model specification and results.

9.Given your results, would you recommend the company pursue expanding shelf space or increase advertising expenditures.

Week	Sales	AdExp	ShelfSpc
1	2010	201	75
2	1850	205	50
3	2400	355	75
4	1575	208	30
5	3550	590	75
6	2015	397	50
7	3908	820	75
8	1870	400	30
9	4877	997	75
10	2190	515	30
11	5005	996	75
12	2500	625	50
13	3005	860	50
14	3480	1012	50
15	5500	1135	75
16	1995	635	30
17	2390	837	30
18	4390	1200	50
19	2785	990	30
20	2989	1205	30

Answer 1

You work for a supermarket that is considering the best way to promote sales of its store-brand canned vegetables. Store managers believe allocating additional shelf space to the store-brand canned vegetables would create additional sales. Company executives, on the other hand, believe increasing advertising expenditures would be a more effective strategy to expand sales. Complete a regression analysis to help answer this question

1.Write out the equation that represents the hypothesized population regression equation for your model

y=β0+β1*x1+β2*x2+ε

where y=sales, x1= advertising expenditures, x2= shelf space and ε is error term.

2.Explain whether a positive or negative relationship is hypothesized

Sales are positively related to advertising expenditures and shelf space.

3.Use the “Microsoft Excel Data Analysis Toolpak” to determine the regression coefficients for the relationship and write out the final estimated regression equation. You must include the MS Excel output.

The estimated regression line is

y =-558.546+2.548136*x1+34.1205*x2

4.Give a practical interpretation of the slope(s) of the least squares line

When advertising expenditures increases by 1 unit, sales increases by 2.548136.

When shelf space increases by 1 unit, sales increases by 34.1205.

5.Over what range of x-values is the interpretation meaningful?

It is meaningful when advertising expenditures is within (201, 1205) and shelf space is within (30,75).

6.Does the estimated slope support the relationship between the two variables you initially hypothesized (in parts a and b)? Explain.

Since the two regression coefficients are positive , they are positively related to sales. This supports the previous stated hypothesis.

7.Evaluate the overall utility of the model including any relevant hypothesis tests (be sure to fully write all 6-steps of any hypothesis test conducted, you can specify the rejection region in words it is not necessary to include a graph though you can if you prefer to).

Ho: The model is not useful.

H1: The model is useful.

Reject Ho if calculated F(2,17) > 3.59 the critical value at 0.05 level of significance.

Calculated F= 77.47, P=0.0000 which is < 0.05 level of significance.

Ho is rejected.

The model is useful.

8.Include any hypothesis tests of individual coefficients that are appropriate given your choice of model specification and results.

To test the effect of advertising expenditures, calculated t=9.64, P=0.000 which is < 0.05 level of significance. advertising expenditures is significant.

To test the effect of shelf space, calculated t=7.34, P=0.000 which is < 0.05 level of significance. shelf space is significant.

Since both advertising expenditures and shelf space are significant supports the model specification.

9.Given your results, would you recommend the company pursue expanding shelf space or increase advertising expenditures.

Since both advertising expenditures and shelf space are significant, we recommend the company pursue expanding shelf space or increase advertising expenditures.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.949276
R Square	0.901125
Adjusted R Square	0.889492
Standard Error	391.5064
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	2	23747818	11873909	77.46687	2.87E-09
Residual	17	2605713	153277.3
Total	19	26353531
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-558.546	309.5127	-1.8046	0.088883	-1211.56	94.4684
AdExp	2.548136	0.264233	9.643512	2.63E-08	1.990653	3.105619
ShelfSpc	34.1205	4.648559	7.340016	1.15E-06	24.31289	43.9281