Question

Please use ONLY one Excel file to complete this case study, and use one spreadsheet for each problem.

1. Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable.
2. Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable.
3. Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable.
4. Which of these three models is the best?  Use R-square values, Significance F values, p-values and other appropriate criteria to explain your answer.
5. Generate a scatter plot, residual plot and normal probability plot for the best model in part (d) and comment on what you see.

Identify and remove the five cases corresponding to December revenue.

6. Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable.
7. Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable.
8. Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable.
1. Which of these three models is the best?  Use R-square values and Significance F values to explain your answer.
10. Generate a scatter plot, residual plot and normal probability plot for the best model in part (i) and comment on what you see.
11. Comparing the results of parts (d) and (i), which of these two models is better? Use R-square values, Significance F values, p-values, scatter plot, residual plot and normal probability plot to explain your answer.

Date	Wal Mart Revenue	CPI	Personal Consumption	Retail Sales Index	December
10/31/03	12.452	562.4	7941071	302065	0
11/28/03	14.764	552.7	7868495	301337	0
12/30/03	23.106	552.1	7885264	357704	1
1/30/04	12.131	554.9	7977730	281463	0
2/27/04	13.628	557.9	8005878	282445	0
3/31/04	16.722	561.5	8070480	319107	0
4/29/04	13.98	563.2	8086579	315278	0
5/28/04	14.388	566.4	8196516	328499	0
6/30/04	18.111	568.2	8161271	321151	0
7/27/04	13.764	567.5	8235349	328025	0
8/27/04	14.296	567.6	8246121	326280	0
9/30/04	17.169	568.7	8313670	313444	0
10/29/04	13.915	571.9	8371605	319639	0
11/29/04	15.739	572.2	8410820	324067	0
12/31/04	26.177	570.1	8462026	386918	1
1/21/05	13.17	571.2	8469443	293027	0
2/24/05	15.139	574.5	8520687	294892	0
3/30/05	18.683	579.6	8568959	338969	0
4/29/05	14.829	582.9	8654352	335626	0
5/25/05	15.697	582.4	8644646	345400	0
6/28/05	20.23	582.6	8724753	351068	0
7/28/05	15.26	585.2	8833907	351887	0
8/26/05	15.709	588.2	8825450	355897	0
9/30/05	18.618	595.4	8882536	333652	0
10/31/05	15.397	596.7	8911627	336662	0
11/28/05	17.384	593.2	8916377	344441	0
12/30/05	27.92	589.4	8955472	406510	1
1/27/06	14.555	593.9	9034368	322222	0
2/23/06	18.684	595.2	9079246	318184	0
3/31/06	16.639	598.6	9123848	366989	0
4/28/06	20.17	603.5	9175181	357334	0
5/25/06	16.901	606.5	9238576	380085	0
6/30/06	21.47	607.8	9270505	373279	0
7/28/06	16.542	609.6	9338876	368611	0
8/29/06	16.98	610.9	9352650	382600	0
9/28/06	20.091	607.9	9348494	352686	0
10/20/06	16.583	604.6	9376027	354740	0
11/24/06	18.761	603.6	9410758	363468	0
12/29/06	28.795	604.5	9478531	424946	1
1/26/07	20.473	606.3	9540335	332797	0
2/23/07	21.922	619.4	9479239	335014	0
3/30/07	18.939	614.9	9583848	376491	0
4/27/07	22.47	619.8	9635181	366936	0
5/25/07	19.201	622.8	9698576	389687	0
6/29/07	23.77	623.9	9731285	382781	0
7/27/07	18.942	625.6	9799656	378113	0
8/31/07	19.38	626.9	9813630	392125	0
9/28/07	22.491	623.9	9809274	362211	0
10/26/07	18.983	621.6	9836807	364265	0
11/30/07	21.161	620.6	9870758	372970	0
12/28/07	31.245	622.5	9966331	434488	1

Answer 1

SUMMARY OUTPUT	Wal-Mart revenue = -37.38+0.094*CPI
Regression Statistics
Multiple R	0.510643234
R Square	0.260756512
Adjusted R Square	0.24566991
Standard Error	3.652515065
Observations	51
ANOVA
	df	SS	MS	F	Significance F
Regression	1	230.5832559	230.5832559	17.28397923	0.000128901
Residual	49	653.7024486	13.3408663
Total	50	884.2857045
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-37.38405868	13.40478715	-2.788858806	0.007511067	-64.32198699	-10.44613036	-64.32198699	-10.44613036
CPI	0.094126383	0.022640682	4.157400537	0.000128901	0.048628229	0.139624536	0.048628229	0.139624536

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.556811013
R Square	0.310038504
Adjusted R Square	0.295957658
Standard Error	3.52866711
Observations	51
ANOVA
	df	SS	MS	F	Significance F
Regression	1	274.1626174	274.1626	22.01846	2.2E-05
Residual	49	610.1230871	12.45149
Total	50	884.2857045
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-14.80717277	7.073736886	-2.09326	0.041525	-29.0224	-0.59197	-29.0224	-0.59197
Personal Consumption	3.71033E-06	7.90712E-07	4.692383	2.2E-05	2.12E-06	5.3E-06	2.12E-06	5.3E-06

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.785448624
R Square	0.616929541
Adjusted R Square	0.609111777
Standard Error	2.629284571
Observations	51
ANOVA
	df	SS	MS	F	Significance F
Regression	1	545.5419742	545.5419742	78.91380516	8.80428E-12
Residual	49	338.7437303	6.913137354
Total	50	884.2857045
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-15.31056105	3.801915782	-4.027064755	0.000195852	-22.95079686	-7.670325237	-22.95079686	-7.670325237
Retail Sales Index	9.65922E-05	1.08734E-05	8.883344255	8.80428E-12	7.47413E-05	0.000118443	7.47413E-05	0.000118443

Wal-Mart revenue, using Retail Sales Index as the only independent variable is the best model.

lower P value and higher .

(e)