please see the attached word file for the rubric, and excel file for data you need to use. the assignment has to be done by excel.Develop a multiple linear regression model to predict Wal-Mart revenue, using CPI, Personal Consumption, and Retail Sales Index as the independent variables.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, use 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.Does it seem that Wal-Mart’s revenue is closely related to the general state of the economy?Explain it.Identify and remove the five cases corresponding to December revenue. (a) Develop a multiple linear regression model to predict Wal-Mart revenue, using CPI, Personal Consumption, and Retail Sales Index as the independent variables. (b) 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, use 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. (c) Does it seem that Wal-Mart’s revenue is closely related to the general state of the economy? (d) Compare the results of parts (a) and (d), which of these two models is better? Use R-square values, adjusted R-square values, Significance F values, p-values, scatter plot, residual plot and normal probability plot to explain your answer. (e) Compare the results of parts (a) and (d), what can you conclude about the impact of December data to Wal-Mart Revenue?
20181220200600case_study_two.docx

20181220200555walmart_revenue_2004_2009_1___1_.xlsx

Unformatted Attachment Preview

Wal-Mart Revenue: Case Study Two
Due Date: 12/16, 11:59 pm
*The due date for case two is correct. You have two weeks to work on case two since 12/3.
Please use ONLY one Excel file to complete this case study, and use one spreadsheet for each
problem. Finally, upload the Excel file to the submission link (Week 6 case study) for grading.
No credit will be granted for problems that are not completed using Excel.
Wal-Mart is the second largest retailer in the world. The data file (Wal-Mart Revenue 20042009.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.
(a) Develop a multiple linear regression model to predict Wal-Mart revenue, using CPI,
Personal Consumption, and Retail Sales Index as the independent variables.
(b) 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, use 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.
(c) Does it seem that Wal-Mart’s revenue is closely related to the general state of the
economy? Explain it.
Identify and remove the five cases corresponding to December revenue.
(d) Develop a multiple linear regression model to predict Wal-Mart revenue, using CPI,
Personal Consumption, and Retail Sales Index as the independent variables.
(e) 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, use 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.
(f) Does it seem that Wal-Mart’s revenue is closely related to the general state of the
economy?
(g) Compare the results of parts (a) and (d), which of these two models is better? Use Rsquare values, adjusted R-square values, Significance F values, p-values, scatter plot,
residual plot and normal probability plot to explain your answer.
(h) Compare the results of parts (a) and (d), what can you conclude about the impact of
December data to Wal-Mart Revenue?
Date
1/30/2004
2/27/2004
3/31/2004
4/29/2004
5/28/2004
6/30/2004
7/27/2004
8/27/2004
9/30/2004
10/29/2004
11/29/2004
12/31/2004
1/21/2005
2/24/2005
3/30/2005
4/29/2005
5/25/2005
6/28/2005
7/28/2005
8/26/2005
9/30/2005
10/31/2005
11/28/2005
12/30/2005
1/27/2006
2/23/2006
3/31/2006
4/28/2006
5/25/2006
6/30/2006
7/28/2006
8/29/2006
9/28/2006
10/20/2006
11/24/2006
12/29/2006
1/26/2007
2/23/2007
3/30/2007
4/27/2007
5/25/2007
6/29/2007
7/27/2007
8/31/2007
9/28/2007
10/26/2007
Wal Mart Revenue
12.131
13.628
16.722
13.98
14.388
18.111
13.764
14.296
17.169
13.915
15.739
26.177
13.17
15.139
18.683
14.829
15.697
20.23
15.26
15.709
18.618
15.397
17.384
27.92
14.555
16.87
16.639
17.2
16.901
21.47
16.542
16.98
20.091
16.583
18.761
28.795
16.1
17.984
18.939
22.47
19.201
23.77
18.942
19.38
22.491
18.983
CPI
554.9
557.9
561.5
563.2
566.4
568.2
567.5
567.6
568.7
571.9
572.2
570.1
571.2
574.5
579
582.9
582.4
582.6
585.2
588.2
595.4
596.7
592
609.4
573.9
595.2
598.6
603.5
606.5
607.8
609.6
610.9
607.9
604.6
603.6
604.5
606.3
594.6
599.3
613.3
642.8
623.9
625.6
626.9
623.9
619.9
Personal Consumption
7977730
8005878
8070480
8086579
8196516
8161271
8235349
8246121
8313670
8371605
8410820
8462026
8469443
8520687
8568959
8654352
8644646
8724753
8833907
8825450
8882536
8911627
8916377
8955472
9034368
9079246
9123848
9175181
9238576
9270505
9338876
9352650
9348494
9376027
9410758
9478531
9540335
9500318
9547774
9602393
9669845
9703817
9776564
9791220
9786798
9816093
Retail Sales Index December
281463
0
282445
0
319107
0
315278
0
328499
0
321151
0
328025
0
326280
0
313444
0
319639
0
324067
0
386918
1
293027
0
294892
0
338969
0
335626
0
345400
0
351068
0
351887
0
355897
0
333652
0
336662
0
344441
0
406510
1
322222
0
318184
0
366989
0
357334
0
380085
0
373279
0
368611
0
382600
0
352686
0
354740
0
363468
0
424946
1
332797
0
327686
0
376491
0
366936
0
389687
0
382781
0
378113
0
392125
0
362211
0
364265
0
11/30/2007
12/28/2007
1/25/2008
2/29/2008
3/28/2008
4/25/2008
5/30/2008
6/27/2008
7/25/2008
8/29/2008
9/26/2008
10/31/2008
11/28/2008
12/26/2008
1/30/2009
2/27/2009
3/27/2009
4/24/2009
5/29/2009
6/26/2009
7/31/2009
21.161
31.245
19.923
21.512
19.023
20.178
21.9
21.24
22.1
20.981
20.419
20
21.022
32.85
19.784
20.962
22.951
22.062
20.856
23.700
24.413
620.6
642.5
623.4
622.3
626.9
651.2
636.1
638.7
640.2
641.9
643.2
641.2
637.9
656.9
637.8
639.7
638.9
643.7
648.1
649.4
651.4
9931068
9953178
10018937
10146599
10197093
10255207
10326976
10363123
10440525
10456119
10451414
10482584
10521902
10508628
10578596
10714428
10768153
10829987
10906349
10944809
11027165
372970
434488
342422
344464
339463
388158
378653
397579
394488
389780
403812
373978
381932
443677
350195
353997
356183
351032
354928
395869
389656
0
1
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0

Purchase answer to see full
attachment