Question

Wal-Mart is the second-largest retailer in the world. The data file (Wal-Mart Revenue 2004-2009 data.xlsx) is posted below the case study two file, and it holds monthly data on Wal-Mart’s revenue, along with several possibly related economic variables.

1. Develop a multiple linear regression model to predict Wal-Mart's revenue, using CPI, Personal Consumption, and Retail Sales Index as the independent variables. You also need to create residual plots and scatter plots by selecting residual plots box and line fit plots box under “Residuals”. Note: Those plots are for the individual independent variables.
2. To generate the residual plot for the entire model, you need to follow the instruction below. Check the Residuals box under “Residuals” and Excel will generate predicted values and residuals at the bottom of the output for the multiple regression model. Then, highlight these two output values to create the residual plot by Excel’s scatter chart (Insert tab > Charts > Scatter chart). Comment on what you see on the plot. Note: This residual plot is for the entire model.
3. Does it seem that Wal-Mart’s revenue is closely related to the general state of the economy? Use all the plots and statistical criteria on the Regression Analysis output generated by Excel to explain it.

   Identify and delete those five cases (rows) corresponding to December revenue. Then, use the new data set (without the December data) to complete the following problems.

4. Develop a multiple linear regression model to predict Wal-Mart's revenue, using CPI, Personal Consumption, and Retail Sales Index as the independent variables. You also need to create residual plots and scatter plots by selecting residual plots box and line fit plots box under “Residuals”. Note: Those plots are for the individual independent variables.
5. Check the Residuals box under “Residuals” and Excel will generate predicted values and residuals at the bottom of the output for the multiple regression model. Then, highlight these two output values to create the residual plot by Excel’s scatter chart (Insert tab > Charts > Scatter chart). Comment on what you see on the plot. Note: This residual plot is for the entire model.
6. Does it seem that Wal-Mart’s revenue is closely related to the general state of the economy? Use all the plots and statistical criteria on the Regression Analysis output generated by Excel to explain it.
7. Compare these two multiple regression models, and decide which of these two models is better? Use R-square values, adjusted R-square values, Significance F values, p-values, scatter plots and residual plots to explain your answer.

Date Wal Mart Revenue CPI Personal Consumption Retail Sales Index December

1/30/04 12.031 561.9 7983730 279463 0

2/27/04 13.988 547.9 8105878 273645 0

3/31/04 16.322 561.5 8090480 335107 0

4/29/04 13.98 564.2 8086579 315278 0

5/28/04 14.388 566.4 8196516 328499 0

6/30/04 18.111 558.2 8161271 321151 0

7/27/04 13.764 567.5 8235349 322025 0

8/27/04 14.696 567.6 8246121 326280 0

9/30/04 16.569 568.7 8323670 310444 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 8562026 389718 1

1/21/05 13.17 579.2 8469443 293027 0

2/24/05 15.139 574.5 8520687 294892 0

3/30/05 18.683 579 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 19.23 582.6 8924753 351068 0

7/28/05 16.96 580.2 8833907 361887 0

8/26/05 15.709 588.2 8825450 355897 0

9/30/05 19.918 585.4 8882536 333652 0

10/31/05 15.397 596.7 8911627 336662 0

11/28/05 17.384 592 8916377 344441 0

12/30/05 27.92 584.4 8955472 406510 1

1/27/06 14.555 593.9 9034368 320222 0

2/23/06 18.684 595.2 9079246 318184 0

3/31/06 17.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.042 609.6 9338876 368611 0

8/29/06 16.98 610.9 9352650 382600 0

9/28/06 18.091 607.9 9368494 352686 0

10/20/06 16.583 604.6 9376027 334740 0

11/24/06 18.761 603.6 9310758 363468 0

12/29/06 28.795 609.5 9478531 424946 1

1/26/07 20.473 606.27 9540335 332797 0

2/23/07 20.984 611.2 9539246 327686 0

3/30/07 18.939 614.9 9583848 376491 0

4/27/07 21.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 21.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.845 622.5 9946331 434488 1

1/25/08 22.923 623.35 10008141 342422 0

2/29/08 21.512 622.28 10032148 344464 0

3/28/08 22.023 626.9 10030959 339463 0

4/25/08 20.018 631.2 10175561 388158 0

5/30/08 23.509 636.1 10126994 368653 0

6/27/08 21.24 638.7 10090289 431354 0

7/25/08 24.809 640.2 10223995 394488 0

8/29/08 20.981 641.9 10291369 389780 0

9/26/08 20.419 643.2 10305343 403812 0

10/31/08 23.53 641.2 10301087 373978 0

11/28/08 21.022 637.9 10328520 375932 0

12/26/08 23.2 636.9 10362495 384677 1

1/30/09 28.184 641.8 10438041 446195 0

2/27/09 23.962 639.65 10499948 353997 0

3/27/09 22.951 638.93 10523764 356183 0

4/24/09 24.062 643.7 10522721 351032 0

5/29/09 26.592 646.1 10546927 348650 0

6/26/09 23.29 647.5 10591006 461356 0

7/31/09 28.809 657.2 10723995 404488 0

8/28/09 27.981 659.9 10791369 409780 0

Answer 1

	R²	0.716
	Adjusted R²	0.703
	R	0.846
	Std. Error	2.406
	n	68
	k	3
	Dep. Var.	Revenue
ANOVA table
Source	SS	df	MS	F	p-value
Regression	935.1778	3	311.7259	53.85	1.72E-17
Residual	370.5055	64	5.7891
Total	1,305.6833	67
Regression output					confidence interval
variables	coefficients	std. error	t (df=64)	p-value	95% lower	95% upper
Intercept	58.6340
CPI	-0.2915	0.0693	-4.206	.0001	-0.4299	-0.1531
Personal Consumption	0.00001247	0.00000243	5.136	2.84E-06	0.00000762	0.00001732
Retail Sales Index	0.00005849	0.00001080	5.415	9.83E-07	0.00003691	0.00008006

The multiple linear regression model to predict Wal-Mart's revenue, using CPI, Personal Consumption, and Retail Sales Index as the independent variables is:

Revenue = 58.6340 - 0.2915*CPI + 0.00001247*Personal Consumption + 0.00005849*Retail Sales Index

The residual plots are:

Wal-Mart’s revenue is closely related to the general state of the economy.

Please give me a thumbs-up if this helps you out. Thank you!