Loading...

Case Study


Open Posted By: taniatemoor Date: 16/08/2020 Academic Level: High School Paper Type: Homework Writing

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

All the answers should be derived using data analysis in excel. 


Due by tomorrow.



Category: Arts & Education Subjects: Art Deadline: 12 Hours Budget: $120 - $240 Pages: 3-6 Pages (Medium Assignment)

Attachment 1

WalMart Revenue Data

Date Wal Mart Revenue CPI Personal Consumption Retail Sales Index December
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
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 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 592 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.348 9540335 332797 0

(a)

(a) Linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable.
Wal Mart Revenue CPI
14.764 552.7 SUMMARY OUTPUT
23.106 552.1
12.131 554.9 Regression Statistics
13.628 557.9 Multiple R 0.3371520645
16.722 561.5 R Square 0.1136715146
13.98 563.2 Adjusted R Square 0.0897166907
14.388 566.4 Standard Error 3.6894006147
18.111 568.2 Observations 39
13.764 567.5
14.296 567.6 ANOVA
17.169 568.7 df SS MS F Significance F
13.915 571.9 Regression 1 64.5907452267 64.5907452267 4.7452452569 0.0358255646
15.739 572.2 Residual 37 503.6320451322 13.6116768955
26.177 570.1 Total 38 568.222790359
13.17 571.2
15.139 574.5 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
18.683 579 Intercept -24.4085437609 19.2484821488 -1.2680762863 0.2126922535 -63.4096732156 14.5925856938 -63.4096732156 14.5925856938
14.829 582.9 X Variable 1 0.0717915502 0.0329567213 2.1783583858 0.0358255646 0.0050148899 0.1385682105 0.0050148899 0.1385682105
15.697 582.4
20.23 582.6
15.26 585.2
15.709 588.2 RESIDUAL OUTPUT
18.618 595.4
15.397 596.7 Observation Predicted Y Residuals
17.384 592 1 15.2706460301 -0.5066460301
27.92 589.4 2 15.2275711 7.8784289
14.555 593.9 3 15.4285874405 -3.2975874405
18.684 595.2 4 15.6439620911 -2.0159620911
16.639 598.6 5 15.9024116718 0.8195883282
20.17 603.5 6 16.0244573071 -2.0444573071
16.901 606.5 7 16.2541902677 -1.8661902677
21.47 607.8 8 16.3834150581 1.7275849419
16.542 609.6 9 16.3331609729 -2.5691609729
16.98 610.9 10 16.3403401279 -2.0443401279
20.091 607.9 11 16.4193108331 0.7496891669
16.583 604.6 12 16.6490437938 -2.7340437938
18.761 603.6 13 16.6705812588 -0.9315812588
28.795 604.5 14 16.5198190034 9.6571809966
20.473 606.348 15 16.5987897086 -3.4287897086
SUMMARY OUTPUT 16 16.8357018243 -1.6967018243
17 17.1587638001 1.5242361999
Regression Statistics 18 17.4387508459 -2.6097508459
Multiple R 0.3371520645 19 17.4028550708 -1.7058550708
R Square 0.1136715146 20 17.4172133808 2.8127866192
Adjusted R Square 0.0897166907 21 17.6038714113 -2.3438714113
Standard Error 3.6894006147 22 17.8192460619 -2.1102460619
Observations 39 23 18.3361452233 0.2818547767
24 18.4294742385 -3.0324742385
ANOVA 25 18.0920539526 -0.7080539526
df SS 26 17.9053959221 10.0146040779
Regression 1 64.5907452267 27 18.228457898 -3.673457898
Residual 37 503.6320451322 28 18.3217869132 0.3622130868
Total 38 568.222790359 29 18.5658781839 -1.9268781839
30 18.9176567798 1.2523432202
Coefficients Standard Error 31 19.1330314304 -2.2320314304
Intercept -24.4085437609 19.2484821488 32 19.2263604456 2.2436395544
X Variable 1 0.0717915502 0.0329567213 33 19.355585236 -2.813585236
34 19.4489142512 -2.4689142512
35 19.2335396007 0.8574603993
the x-variable= 0.071792, the intercept= -24.4085 36 18.996627485 -2.413627485
The regression equation is: 37 18.9248359348 -0.1638359348
38 18.98944833 9.80555167
Revenue= 0.071792*CPI-24.4085 39 19.1221191148 1.3508808852
Regression equation y= 0.071792x-24.4085

X Variable 1 Residual Plot

552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 -0.50664603008153364 7.8784289000335299 -3.2975874405033956 -2.0159620910786664 0.81958832823100281 -2.0444573070949943 -1.8661902677086193 1.7275849419462119 -2.5691609729195548 -2.0443401279387281 0.74968916685033093 -2.7340437937632878 -0.93158125882082032 9.6571809965818751 -3.4287897086290595 -1.696701824261865 1.5242361998752223 -2.6097508458726288 -1.7058550707767566 2.8127866191848909 -2.34387141131368 -2.110246061888958 0.28185477673038406 -3.032474238518903 -0.70805395261763593 10.014604077880936 -3.6734578979819794 0.36221308676873321 -1.9268781838832467 1.2523432201771421 -2.2320314303981377 2.2436395543525798 -2.813585235992587 -2.4689142512418698 0.85746039933340157 -2.4136274850337962 -0.16383593484204084 9.8055516699853804 1.3508808852310104

X Variable 1

Residuals

Y 552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 15.270646030081533 15.227571099966472 15.428587440503396 15.643962091078667 15.902411671768999 16.024457307094995 16.254190267708619 16.383415058053789 16.333160972919554 16.340340127938727 16.41931083314967 16.649043793763287 16.670581258820821 16.519819003418124 16.598789708629059 16.835701824261864 17.158763800124778 17.438750845872629 17.402855070776756 17.41721338081511 17.60387141131368 17.819246061888958 18.336145223269614 18.429474238518903 18.092053952617636 17.905395922119066 18.228457897981979 18.321786913231268 18.565878183883246 18.91765677982286 19.133031430398137 19.226360445647419 19.355585235992589 19.44891425124187 19.2335396006666 18.996627485033795 18.92483593484204 18.989448330014621 19.122119114768989

X Variable 1 Residual Plot

552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 -0.50664603008153364 7.8784289000335299 -3.2975874405033956 -2.0159620910786664 0.81958832823100281 -2.0444573070949943 -1.8661902677086193 1.7275849419462119 -2.5691609729195548 -2.0443401279387281 0.74968916685033093 -2.7340437937632878 -0.93158125882082032 9.6571809965818751 -3.4287897086290595 -1.696701824261865 1.5242361998752223 -2.6097508458726288 -1.7058550707767566 2.8127866191848909 -2.34387141131368 -2.110246061888958 0.28185477673038406 -3.032474238518903 -0.70805395261763593 10.014604077880936 -3.6734578979819794 0.36221308676873321 -1.9268781838832467 1.2523432201771421 -2.2320314303981377 2.2436395543525798 -2.813585235992587 -2.4689142512418698 0.85746039933340157 -2.4136274850337962 -0.16383593484204084 9.8055516699853804 1.3508808852310104

X Variable 1

Residuals

Y 552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 15.270646030081533 15.227571099966472 15.428587440503396 15.643962091078667 15.902411671768999 16.024457307094995 16.254190267708619 16.383415058053789 16.333160972919554 16.340340127938727 16.41931083314967 16.649043793763287 16.670581258820821 16.519819003418124 16.598789708629059 16.835701824261864 17.158763800124778 17.438750845872629 17.402855070776756 17.41721338081511 17.60387141131368 17.819246061888958 18.336145223269614 18.429474238518903 18.092053952617636 17.905395922119066 18.228457897981979 18.321786913231268 18.565878183883246 18.91765677982286 19.133031430398137 19.226360445647419 19.355585235992589 19.44891425124187 19.2335396006666 18.996627485033795 18.92483593484204 18.989448330014621 19.122119114768989

X Variable 1 Residual Plot

552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 -0.50664603008153364 7.8784289000335299 -3.2975874405033956 -2.0159620910786664 0.81958832823100281 -2.0444573070949943 -1.8661902677086193 1.7275849419462119 -2.5691609729195548 -2.0443401279387281 0.74968916685033093 -2.7340437937632878 -0.93158125882082032 9.6571809965818751 -3.4287897086290595 -1.696701824261865 1.5242361998752223 -2.6097508458726288 -1.7058550707767566 2.8127866191848909 -2.34387141131368 -2.110246061888958 0.28185477673038406 -3.032474238518903 -0.70805395261763593 10.014604077880936 -3.6734578979819794 0.36221308676873321 -1.9268781838832467 1.2523432201771421 -2.2320314303981377 2.2436395543525798 -2.813585235992587 -2.4689142512418698 0.85746039933340157 -2.4136274850337962 -0.16383593484204084 9.8055516699853804 1.3508808852310104

X Variable 1

Residuals

Y 552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 552.70000000000005 552.1 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 570.1 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 589.4 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 604.5 606.34799999999996 15.270646030081533 15.227571099966472 15.428587440503396 15.643962091078667 15.902411671768999 16.024457307094995 16.254190267708619 16.383415058053789 16.333160972919554 16.340340127938727 16.41931083314967 16.649043793763287 16.670581258820821 16.519819003418124 16.598789708629059 16.835701824261864 17.158763800124778 17.438750845872629 17.402855070776756 17.41721338081511 17.60387141131368 17.819246061888958 18.336145223269614 18.429474238518903 18.092053952617636 17.905395922119066 18.228457897981979 18.321786913231268 18.565878183883246 18.91765677982286 19.133031430398137 19.226360445647419 19.355585235992589 19.44891425124187 19.2335396006666 18.996627485033795 18.92483593484204 18.989448330014621 19.122119114768989

(b)

(b)  Linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable.
Wal Mart Revenue Personal Consumption
14.764 7868495
23.106 7885264 SUMMARY OUTPUT
12.131 7977730
13.628 8005878 Regression Statistics
16.722 8070480 Multiple R 0.3940240291
13.98 8086579 R Square 0.1552549355
14.388 8196516 Adjusted R Square 0.1324239879
18.111 8161271 Standard Error 3.6018140987
13.764 8235349 Observations 39
14.296 8246121
17.169 8313670 ANOVA
13.915 8371605 df SS MS F Significance F
15.739 8410820 Regression 1 88.219392691 88.219392691 6.8001967182 0.0130666987
26.177 8462026 Residual 37 480.003397668 12.9730648018
13.17 8469443 Total 38 568.222790359
15.139 8520687
18.683 8568959 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
14.829 8654352 Intercept -8.8950748025 10.139008405 -0.8773121046 0.3859782812 -29.4386572154 11.6485076104 -29.4386572154 11.6485076104
15.697 8644646 X Variable 1 0.0000030282 0.0000011612 2.6077186808 0.0130666987 0.0000006753 0.000005381 0.0000006753 0.000005381
20.23 8724753
15.26 8833907
15.709 8825450
18.618 8882536 RESIDUAL OUTPUT
15.397 8911627
17.384 8916377 Observation Predicted Y Residuals
27.92 8955472 1 14.932038192 -0.168038192
14.555 9034368 2 14.9828175161 8.1231824839
18.684 9079246 3 15.2628199601 -3.1318199601
16.639 9123848 4 15.3480567908 -1.7200567908
20.17 9175181 5 15.5436824033 1.1783175967
16.901 9238576 6 15.5924328558 -1.6124328558
21.47 9270505 7 15.9253403968 -1.5373403968
16.542 9338876 8 15.8186126683 2.2923873317
16.98 9352650 9 16.0429331866 -2.2789331866
20.091 9348494 10 16.0755525962 -1.7795525962
16.583 9376027 11 16.2801022153 0.8888977847
18.761 9410758 12 16.4555390417 -2.5405390417
28.795 9478531 13 16.5742885912 -0.8352885912
20.473 9540335 14 16.7293488852 9.4476511148
15 16.7518087961 -3.5818087961
SUMMARY OUTPUT 16 16.9069841605 -1.7679841605
17 17.0531598139 1.6298401861
Regression Statistics 18 17.3117440362 -2.4827440362
Multiple R 0.3940240291 19 17.2823526521 -1.5853526521
R Square 0.1552549355 20 17.5249299862 2.7050700138
Adjusted R Square 0.1324239879 21 17.8554664728 -2.5954664728
Standard Error 3.6018140987 22 17.8298572687 -2.1208572687
Observations 39 23 18.0027231817 0.6152768183
24 18.0908155735 -2.6938155735
ANOVA 25 18.1051993644 -0.7211993644
df SS 26 18.2235855338 9.6964144662
Regression 1 88.219392691 27 18.4624957583 -3.9074957583
Residual 37 480.003397668 28 18.5983938147 0.0856061853
Total 38 568.222790359 29 18.7334560971 -2.0944560971
30 18.8889009682 1.2810990318
Coefficients Standard Error 31 19.0808715837 -2.1798715837
Intercept -8.8950748025 10.139008405 32 19.177557912 2.292442088
X Variable 1 0.0000030282 0.0000011612 33 19.3845966841 -2.8425966841
34 19.4263066495 -2.4463066495
the x-variable is 3.0281E-06 or 0.000003028 35 19.4137215895 0.6772784105
the intercept is -8.895 36 19.4970960978 -2.9140960978
regrssion equation 37 19.6022673487 -0.8412673487
based on personal consumption y=0.000003028x-8.895 38 19.8074952772 8.9875047228
39 19.9946480798 0.4783519202

X Variable 1 Residual Plot

7868495 7885264 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 -0.16803819200906389 8.1231824838883462 -3.1318199600540702 -1.7200567908036568 1.178317596716429 -1.6124328558286045 -1.537340396759296 2.2923873316679142 -2.2789331866022202 -1.7795525961813929 0.88889778465192748 -2.5405390417470066 -0.83528859119439147 9.4476511148108955 -3.5818087961479552 -1.7679841604698154 1.6298401860576845 -2.4827440362031972 -1.585352652117102 2.7050700137612225 -2.5954664727969767 -2.120857268674154 0.6152768182886561 -2.6938155734740317 -0.72119936436718746 9.6964144661658906 -3.9074957583196621 8.5606185321811523E-2 -2.0944560970816646 1.2810990317776358 -2.1798715837321687 2.2924420879672951 -2.8425966840655228 -2.4463066494891699 0.67727841050071547 -2.914096097849022 -0.84126734869326825 8.9875047228431981 0.47835192024097495

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 7868495 7885264 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 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 7868495 7885264 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 14.932038192009063 14.982817516111655 15.26281996005407 15.348056790803657 15.543682403283572 15.592432855828605 15.925340396759296 15.818612668332086 16.04293318660222 16.075552596181392 16.280102215348073 16.455539041747006 16.574288591194392 16.729348885189104 16.751808796147955 16.906984160469815 17.053159813942315 17.311744036203198 17.282352652117101 17.524929986238778 17.855466472796977 17.829857268674154 18.002723181711342 18.090815573474032 18.105199364367188 18.223585533834111 18.462495758319662 18.59839381467819 18.733456097081664 18.888900968222366 19.080871583732169 19.177557912032704 19.384596684065524 19.42630664948917 19.413721589499286 19.49709609784902 19.602267348693267 19.807495277156804 19.994648079759024

X Variable 1

Y

X Variable 1 Residual Plot

7868495 7885264 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 -0.16803819200906389 8.1231824838883462 -3.1318199600540702 -1.7200567908036568 1.178317596716429 -1.6124328558286045 -1.537340396759296 2.2923873316679142 -2.2789331866022202 -1.7795525961813929 0.88889778465192748 -2.5405390417470066 -0.83528859119439147 9.4476511148108955 -3.5818087961479552 -1.7679841604698154 1.6298401860576845 -2.4827440362031972 -1.585352652117102 2.7050700137612225 -2.5954664727969767 -2.120857268674154 0.6152768182886561 -2.6938155734740317 -0.72119936436718746 9.6964144661658906 -3.9074957583196621 8.5606185321811523E-2 -2.0944560970816646 1.2810990317776358 -2.1798715837321687 2.2924420879672951 -2.8425966840655228 -2.4463066494891699 0.67727841050071547 -2.914096097849022 -0.84126734869326825 8.9875047228431981 0.47835192024097495

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 7868495 7885264 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 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 7868495 7885264 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 14.932038192009063 14.982817516111655 15.26281996005407 15.348056790803657 15.543682403283572 15.592432855828605 15.925340396759296 15.818612668332086 16.04293318660222 16.075552596181392 16.280102215348073 16.455539041747006 16.574288591194392 16.729348885189104 16.751808796147955 16.906984160469815 17.053159813942315 17.311744036203198 17.282352652117101 17.524929986238778 17.855466472796977 17.829857268674154 18.002723181711342 18.090815573474032 18.105199364367188 18.223585533834111 18.462495758319662 18.59839381467819 18.733456097081664 18.888900968222366 19.080871583732169 19.177557912032704 19.384596684065524 19.42630664948917 19.413721589499286 19.49709609784902 19.602267348693267 19.807495277156804 19.994648079759024

X Variable 1

Y

(c)

c) Linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable.
Wal Mart Revenue Retail Sales Index
14.764 301337
23.106 357704
12.131 281463 SUMMARY OUTPUT
13.628 282445
16.722 319107 Regression Statistics
13.98 315278 Multiple R 0.7574074001
14.388 328499 R Square 0.5736659697
18.111 321151 Adjusted R Square 0.5621434283
13.764 328025 Standard Error 2.5587830317
14.296 326280 Observations 39
17.169 313444
13.915 319639 ANOVA
15.739 324067 df SS MS F Significance F
26.177 386918 Regression 1 325.9700780358 325.9700780358 49.7864101155 0.0000000239
13.17 293027 Residual 37 242.2527123232 6.5473706033
15.139 294892 Total 38 568.222790359
18.683 338969
14.829 335626 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
15.697 345400 Intercept -13.803967588 4.455669125 -3.098068371 0.0037083535 -22.8320107869 -4.7759243891 -22.8320107869 -4.7759243891
20.23 351068 X Variable 1 0.0000918587 0.0000130186 7.0559485624 0.0000000239 0.0000654805 0.000118237 0.0000654805 0.000118237
15.26 351887
15.709 355897
18.618 333652
15.397 336662 RESIDUAL OUTPUT
17.384 344441
27.92 406510 Observation Predicted Y Residuals
14.555 322222 1 13.8764695716 0.8875304284
18.684 318184 2 19.0542711737 4.0517288263
16.639 366989 3 12.0508689712 0.0801310288
20.17 357334 4 12.141074254 1.486925746
16.901 380085 5 15.5087993828 1.2132006172
21.47 373279 6 15.157072267 -1.177072267
16.542 368611 7 16.3715366696 -1.9835366696
16.98 382600 8 15.6965586475 2.4144413525
20.091 352686 9 16.3279956268 -2.5639956268
16.583 354740 10 16.1677021254 -1.8717021254
18.761 363468 11 14.9886033377 2.1803966623
28.795 424946 12 15.5576682325 -1.6426682325
20.473 332797 13 15.9644187336 -0.2254187336
14 21.7378324064 4.4391675936
15 13.1131234415 0.0568765585
SUMMARY OUTPUT 16 13.2844399917 1.8545600083
17 17.3332976783 1.3497023217
Regression Statistics 18 17.0262139102 -2.1972139102
Multiple R 0.7574074001 19 17.9240412357 -2.2270412357
R Square 0.5736659697 20 18.4446965745 1.7853034255
Adjusted R Square 0.5621434283 21 18.5199288826 -3.2599288826
Standard Error 2.5587830317 22 18.8882824303 -3.1792824303
Observations 39 23 16.8448847573 1.7731152427
24 17.1213795649 -1.7243795649
ANOVA 25 17.835948704 -0.451948704
df SS 26 23.537528842 4.382471158
Regression 1 325.9700780358 27 15.7949393581 -1.2399393581
Residual 37 242.2527123232 28 15.4240137657 3.2599862343
Total 38 568.222790359 29 19.9071795753 -3.2681795753
30 19.0202834398 1.1497165602
Coefficients Standard Error 31 21.1101616354 -4.2091616354
Intercept -13.803967588 4.455669125 32 20.4849710504 0.9850289496
X Variable 1 0.0000918587 0.0000130186 33 20.0561744517 -3.5141744517
34 21.3411863667 -4.3611863667
the x-variable is 9.19E-05 or 0.0000919 35 18.5933240159 1.4976759841
the intercept is -13.804 36 18.7820018681 -2.1990018681
37 19.5837449515 -0.8227449515
38 25.2310365741 3.5639634259
Regression equation y=0.0000919x-13.804 39 16.7663455345 3.7066544655

X Variable 1 Residual Plot

301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 0.8875304284375396 4.0517288263375875 8.0131028791196712E-2 1.4869257460324512 1.2132006172125998 -1.1770722670203604 -1.9835366696205252 2.4144413524886978 -2.5639956268225177 -1.871702125382587 2.1803966622867037 -1.6426682325100614 -0.22541873358512987 4.439167593634977 5.6876558503899943E-2 1.8545600082543494 1.3497023217399082 -2.1972139101720245 -2.2270412357157809 1.7853034255097135 -3.2599288826159576 -3.179282430337512 1.7731152427462646 -1.7243795648951004 -0.45194870397888209 4.3824711579839359 -1.2399393581371854 3.2599862343066164 -3.2681795753069629 1.1497165601672563 -4.2091616353970984 0.98502894958865994 -3.5141744517170252 -4.3611863666987709 1.4976759840599669 -2.199001868064741 -0.82274495148461924 3.5639634258655875 3.706654465514827

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 13.87646957156246 19.054271173662414 12.050868971208804 12.141074253967549 15.508799382787402 15.157072267020361 16.371536669620525 15.696558647511303 16.327995626822517 16.167702125382586 14.988603337713297 15.557668232510061 15.964418733585131 21.737832406365023 13.1131234414961 13.28443999174565 17.333297678260092 17.026213910172025 17.92404123571578 18.444696574490287 18.519928882615957 18.888282430337512 16.844884757253734 17.121379564895101 17.835948703978882 23.537528842016066 15.794939358137185 15.424013765693385 19.907179575306962 19.020283439832745 21.110161635397098 20.484971050411339 20.056174451717027 21.341186366698771 18.593324015940034 18.782001868064739 19.583744951484618 25.231036574134414 16.766345534485172

X Variable 1

Y

X Variable 1 Residual Plot

301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 0.8875304284375396 4.0517288263375875 8.0131028791196712E-2 1.4869257460324512 1.2132006172125998 -1.1770722670203604 -1.9835366696205252 2.4144413524886978 -2.5639956268225177 -1.871702125382587 2.1803966622867037 -1.6426682325100614 -0.22541873358512987 4.439167593634977 5.6876558503899943E-2 1.8545600082543494 1.3497023217399082 -2.1972139101720245 -2.2270412357157809 1.7853034255097135 -3.2599288826159576 -3.179282430337512 1.7731152427462646 -1.7243795648951004 -0.45194870397888209 4.3824711579839359 -1.2399393581371854 3.2599862343066164 -3.2681795753069629 1.1497165601672563 -4.2091616353970984 0.98502894958865994 -3.5141744517170252 -4.3611863666987709 1.4976759840599669 -2.199001868064741 -0.82274495148461924 3.5639634258655875 3.706654465514827

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 13.87646957156246 19.054271173662414 12.050868971208804 12.141074253967549 15.508799382787402 15.157072267020361 16.371536669620525 15.696558647511303 16.327995626822517 16.167702125382586 14.988603337713297 15.557668232510061 15.964418733585131 21.737832406365023 13.1131234414961 13.28443999174565 17.333297678260092 17.026213910172025 17.92404123571578 18.444696574490287 18.519928882615957 18.888282430337512 16.844884757253734 17.121379564895101 17.835948703978882 23.537528842016066 15.794939358137185 15.424013765693385 19.907179575306962 19.020283439832745 21.110161635397098 20.484971050411339 20.056174451717027 21.341186366698771 18.593324015940034 18.782001868064739 19.583744951484618 25.231036574134414 16.766345534485172

X Variable 1

Y

(d)

(d) 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.
The regression equation with the highest R-square value is the best regression equation because it means the data is closest to the regression equation
For Revenue versus CPI
R-squared value=0.1137
For Revenue and personal consumption:
R-squared value=0.115
For Revenue and Retails sales index
R-squared value=0.5737
From R-squared value, the Revenue and the Retails Sales index gives the best regression equation because it has the highest value of the R-squared value
It has also the lowest p-value, meaning it is the best regression equation
It also has the highest value of the F-statistic
Regresion equation of the Revenue and the Retails Sales Index has the best regression equation

(e)

e) Residual plot of the revenue vs retails sales index
Wal Mart Revenue Retail Sales Index
14.764 301337
23.106 357704
12.131 281463
13.628 282445
16.722 319107
13.98 315278
14.388 328499
18.111 321151
13.764 328025
14.296 326280
17.169 313444
13.915 319639
15.739 324067
26.177 386918
13.17 293027
15.139 294892
18.683 338969
14.829 335626
15.697 345400
20.23 351068
15.26 351887
15.709 355897
18.618 333652
15.397 336662
17.384 344441
27.92 406510
14.555 322222
18.684 318184
16.639 366989
20.17 357334
16.901 380085
21.47 373279
16.542 368611
16.98 382600
20.091 352686
16.583 354740
18.761 363468
28.795 424946
20.473 332797
Regression Statistics COMMENTS
Multiple R 0.7574074001 They line plot indicates a strong correlation between Y and the predicted Y, meaning the regression equation is strong
R Square 0.5736659697 The residual plot indicates the residuals are almost evenly distributed above and below 0, indicating the regression equation is strong
Adjusted R Square 0.5621434283
Standard Error 2.5587830317
Observations 39
ANOVA
df SS MS F Significance F
Regression 1 325.9700780358 325.9700780358 49.7864101155 0.0000000239
Residual 37 242.2527123232 6.5473706033
Total 38 568.222790359
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -13.803967588 4.455669125 -3.098068371 0.0037083535 -22.8320107869 -4.7759243891 -22.8320107869 -4.7759243891
X Variable 1 0.0000918587 0.0000130186 7.0559485624 0.0000000239 0.0000654805 0.000118237 0.0000654805 0.000118237
RESIDUAL OUTPUT
Observation Predicted Y Residuals
1 13.8764695716 0.8875304284
2 19.0542711737 4.0517288263
3 12.0508689712 0.0801310288
4 12.141074254 1.486925746
5 15.5087993828 1.2132006172
6 15.157072267 -1.177072267
7 16.3715366696 -1.9835366696
8 15.6965586475 2.4144413525
9 16.3279956268 -2.5639956268
10 16.1677021254 -1.8717021254
11 14.9886033377 2.1803966623
12 15.5576682325 -1.6426682325
13 15.9644187336 -0.2254187336
14 21.7378324064 4.4391675936
15 13.1131234415 0.0568765585
16 13.2844399917 1.8545600083
17 17.3332976783 1.3497023217
18 17.0262139102 -2.1972139102
19 17.9240412357 -2.2270412357
20 18.4446965745 1.7853034255
21 18.5199288826 -3.2599288826
22 18.8882824303 -3.1792824303
23 16.8448847573 1.7731152427
24 17.1213795649 -1.7243795649
25 17.835948704 -0.451948704
26 23.537528842 4.382471158
27 15.7949393581 -1.2399393581
28 15.4240137657 3.2599862343
29 19.9071795753 -3.2681795753
30 19.0202834398 1.1497165602
31 21.1101616354 -4.2091616354
32 20.4849710504 0.9850289496
33 20.0561744517 -3.5141744517
34 21.3411863667 -4.3611863667
35 18.5933240159 1.4976759841
36 18.7820018681 -2.1990018681
37 19.5837449515 -0.8227449515
38 25.2310365741 3.5639634259
39 16.7663455345 3.7066544655

X Variable 1 Residual Plot

301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 0.8875304284375396 4.0517288263375875 8.0131028791196712E-2 1.4869257460324512 1.2132006172125998 -1.1770722670203604 -1.9835366696205252 2.4144413524886978 -2.5639956268225177 -1.871702125382587 2.1803966622867037 -1.6426682325100614 -0.22541873358512987 4.439167593634977 5.6876558503899943E-2 1.8545600082543494 1.3497023217399082 -2.1972139101720245 -2.2270412357157809 1.7853034255097135 -3.2599288826159576 -3.179282430337512 1.7731152427462646 -1.7243795648951004 -0.45194870397888209 4.3824711579839359 -1.2399393581371854 3.2599862343066164 -3.2681795753069629 1.1497165601672563 -4.2091616353970984 0.98502894958865994 -3.5141744517170252 -4.3611863666987709 1.4976759840599669 -2.199001868064741 -0.82274495148461924 3.5639634258655875 3.706654465514827

X Variable 1

Residuals

Y 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 13.87646957156246 19.054271173662414 12.050868971208804 12.141074253967549 15.508799382787402 15.157072267020361 16.371536669620525 15.696558647511303 16.327995626822517 16.167702125382586 14.988603337713297 15.557668232510061 15.964418733585131 21.737832406365023 13.1131234414961 13.28443999174565 17.333297678260092 17.026213910172025 17.92404123571578 18.444696574490287 18.519928882615957 18.888282430337512 16.844884757253734 17.121379564895101 17.835948703978882 23.537528842016066 15.794939358137185 15.424013765693385 19.907179575306962 19.020283439832745 21.110161635397098 20.484971050411339 20.056174451717027 21.341186366698771 18.593324015940034 18.782001868064739 19.583744951484618 25.231036574134414 16.766345534485172

X Variable 1 Residual Plot

301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 0.8875304284375396 4.0517288263375875 8.0131028791196712E-2 1.4869257460324512 1.2132006172125998 -1.1770722670203604 -1.9835366696205252 2.4144413524886978 -2.5639956268225177 -1.871702125382587 2.1803966622867037 -1.6426682325100614 -0.22541873358512987 4.439167593634977 5.6876558503899943E-2 1.8545600082543494 1.3497023217399082 -2.1972139101720245 -2.2270412357157809 1.7853034255097135 -3.2599288826159576 -3.179282430337512 1.7731152427462646 -1.7243795648951004 -0.45194870397888209 4.3824711579839359 -1.2399393581371854 3.2599862343066164 -3.2681795753069629 1.1497165601672563 -4.2091616353970984 0.98502894958865994 -3.5141744517170252 -4.3611863666987709 1.4976759840599669 -2.199001868064741 -0.82274495148461924 3.5639634258655875 3.706654465514827

X Variable 1

Residuals

Y 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 14.763999999999999 23.106000000000002 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 26.177 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 27.92 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 28.795000000000002 20.472999999999999 Predicted Y 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 406510 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 424946 332797 13.87646957156246 19.054271173662414 12.050868971208804 12.141074253967549 15.508799382787402 15.157072267020361 16.371536669620525 15.696558647511303 16.327995626822517 16.167702125382586 14.988603337713297 15.557668232510061 15.964418733585131 21.737832406365023 13.1131234414961 13.28443999174565 17.333297678260092 17.026213910172025 17.92404123571578 18.444696574490287 18.519928882615957 18.888282430337512 16.844884757253734 17.121379564895101 17.835948703978882 23.537528842016066 15.794939358137185 15.424013765693385 19.907179575306962 19.020283439832745 21.110161635397098 20.484971050411339 20.056174451717027 21.341186366698771 18.593324015940034 18.782001868064739 19.583744951484618 25.231036574134414 16.766345534485172

(f)

(f) Identify and remove the four cases corresponding to December revenue.
Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable.
We remove the values with 1
After removing, the new data is as follows:
Wal Mart Revenue CPI
14.764 552.7
12.131 554.9
13.628 557.9
16.722 561.5
13.98 563.2
14.388 566.4
18.111 568.2
13.764 567.5
14.296 567.6
17.169 568.7
13.915 571.9
15.739 572.2
13.17 571.2
15.139 574.5
18.683 579
14.829 582.9
15.697 582.4
20.23 582.6
15.26 585.2
15.709 588.2
18.618 595.4
15.397 596.7
17.384 592
14.555 593.9
18.684 595.2
16.639 598.6
20.17 603.5
16.901 606.5
21.47 607.8
16.542 609.6
16.98 610.9
20.091 607.9
16.583 604.6
18.761 603.6
20.473 606.348
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.6447522354
R Square 0.4157054451
Adjusted R Square 0.3979995495
Standard Error 1.8267603919
Observations 35
ANOVA
df SS MS F Significance F
Regression 1 78.3485542745 78.3485542745 23.4783630486 0.0000290663
Residual 33 110.1227664683 3.3370535293
Total 34 188.4713207429
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -33.1342186173 10.2426576417 -3.2349239598 0.0027654609 -53.9730622759 -12.2953749587 -53.9730622759 -12.2953749587
X Variable 1 0.0848979804 0.0175211841 4.8454476624 0.0000290663 0.0492508634 0.1205450974 0.0492508634 0.1205450974
Regression equation y=0.0849x-33.1342
RESIDUAL OUTPUT
Observation Predicted Y Residuals
1 13.7888951429 0.9751048571
2 13.9756706998 -1.8446706998
3 14.2303646409 -0.6023646409
4 14.5359973703 2.1860026297
5 14.680323937 -0.700323937
6 14.9519974742 -0.5639974742
7 15.1048138389 3.0061861611
8 15.0453852527 -1.2813852527
9 15.0538750507 -0.7578750507
10 15.1472628291 2.0217371709
11 15.4189363664 -1.5039363664
12 15.4444057605 0.2945942395
13 15.3595077801 -2.1895077801
14 15.6396711154 -0.5006711154
15 16.0217120271 2.6612879729
16 16.3528141506 -1.5238141506
17 16.3103651604 -0.6133651604
18 16.3273447565 3.9026552435
19 16.5480795055 -1.2880795055
20 16.8027734467 -1.0937734467
21 17.4140389055 1.2039610945
22 17.52440628 -2.12740628
23 17.1253857721 0.2586142279
24 17.2866919349 -2.7316919349
25 17.3970593094 1.2869406906
26 17.6857124427 -1.0467124427
27 18.1017125466 2.0682874534
28 18.3564064878 -1.4554064878
29 18.4667738623 3.0032261377
30 18.619590227 -2.077590227
31 18.7299576015 -1.7499576015
32 18.4752636603 1.6157363397
33 18.195100325 -1.612100325
34 18.1102023446 0.6507976554
35 18.3435019947 2.1294980053

X Variable 1 Residual Plot

552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 0.97510485708523831 -1.8446706997674127 -0.60236464093012643 2.1860026296746184 -0.70032393698425821 -0.56399747422448421 3.0061861610778884 -1.2813852526508107 -0.75787505068957017 2.0217371708840979 -1.5039363663561218 0.29459423952760133 -2.1895077800848259 -0.50067111536381148 2.6612879728921186 -1.5238141506194012 -0.61336516042561939 3.9026552434968629 -1.2880795055108205 -1.0937734466735343 1.2039610945359591 -2.1274062799678912 0.25861422785369825 -2.7316919348826829 1.2869406906134664 -1.0467124427042727 2.0682874533966285 -1.4554064877660799 3.0032261377300742 -2.0775902269675512 -1.7499576014713902 1.6157363396913169 -1.6121003250297008 0.65079765535787359 2.1294980052528274

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 14.763999999999999 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 20.472999999999999 Predicted Y 552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 13.788895142914761 13.975670699767413 14.230364640930127 14.535997370325383 14.680323936984259 14.951997474224484 15.104813838922112 15.04538525265081 15.05387505068957 15.147262829115903 15.418936366356121 15.444405760472399 15.359507780084826 15.639671115363811 16.021712027107881 16.352814150619402 16.310365160425619 16.327344756503138 16.54807950551082 16.802773446673534 17.414038905464039 17.524406279967891 17.125385772146302 17.286691934882683 17.397059309386535 17.685712442704272 18.101712546603373 18.35640648776608 18.466773862269925 18.619590226967553 18.729957601471391 18.475263660308684 18.195100325029699 18.110202344642126 18.343501994747172

X Variable 1

Y

X Variable 1 Residual Plot

552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 0.97510485708523831 -1.8446706997674127 -0.60236464093012643 2.1860026296746184 -0.70032393698425821 -0.56399747422448421 3.0061861610778884 -1.2813852526508107 -0.75787505068957017 2.0217371708840979 -1.5039363663561218 0.29459423952760133 -2.1895077800848259 -0.50067111536381148 2.6612879728921186 -1.5238141506194012 -0.61336516042561939 3.9026552434968629 -1.2880795055108205 -1.0937734466735343 1.2039610945359591 -2.1274062799678912 0.25861422785369825 -2.7316919348826829 1.2869406906134664 -1.0467124427042727 2.0682874533966285 -1.4554064877660799 3.0032261377300742 -2.0775902269675512 -1.7499576014713902 1.6157363396913169 -1.6121003250297008 0.65079765535787359 2.1294980052528274

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 14.763999999999999 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 20.472999999999999 Predicted Y 552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 13.788895142914761 13.975670699767413 14.230364640930127 14.535997370325383 14.680323936984259 14.951997474224484 15.104813838922112 15.04538525265081 15.05387505068957 15.147262829115903 15.418936366356121 15.444405760472399 15.359507780084826 15.639671115363811 16.021712027107881 16.352814150619402 16.310365160425619 16.327344756503138 16.54807950551082 16.802773446673534 17.414038905464039 17.524406279967891 17.125385772146302 17.286691934882683 17.397059309386535 17.685712442704272 18.101712546603373 18.35640648776608 18.466773862269925 18.619590226967553 18.729957601471391 18.475263660308684 18.195100325029699 18.110202344642126 18.343501994747172

X Variable 1

Y

(g)

(g) Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable.
Wal Mart Revenue Personal Consumption
14.764 7868495
12.131 7977730
13.628 8005878 SUMMARY OUTPUT
16.722 8070480
13.98 8086579 Regression Statistics
14.388 8196516 Multiple R 0.6352809133
18.111 8161271 R Square 0.4035818388
13.764 8235349 Adjusted R Square 0.3855085612
14.296 8246121 Standard Error 1.8456149387
17.169 8313670 Observations 35
13.915 8371605
15.739 8410820 ANOVA
13.17 8469443 df SS MS F Significance F
15.139 8520687 Regression 1 76.0636021829 76.0636021829 22.330307066 0.0000413258
18.683 8568959 Residual 33 112.40771856 3.4062945018
14.829 8654352 Total 34 188.4713207429
15.697 8644646
20.23 8724753 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
15.26 8833907 Intercept -10.0401404897 5.6194273236 -1.7866839291 0.083178865 -21.4729513425 1.3926703632 -21.4729513425 1.3926703632
15.709 8825450 X Variable 1 0.0000030407 0.0000006435 4.7254954307 0.0000413258 0.0000017316 0.0000043498 0.0000017316 0.0000043498
18.618 8882536
15.397 8911627
17.384 8916377
14.555 9034368 RESIDUAL OUTPUT
18.684 9079246
16.639 9123848 Observation Predicted Y Residuals
20.17 9175181 1 13.8855277296 0.8784722704
16.901 9238576 2 14.2176776983 -2.0866776983
21.47 9270505 3 14.3032670911 -0.6752670911
16.542 9338876 4 14.4997018627 2.2222981373
16.98 9352650 5 14.54865396 -0.56865396
20.091 9348494 6 14.8829384943 -0.4949384943
16.583 9376027 7 14.7757693118 3.3352306882
18.761 9410758 8 15.0010176789 -1.2370176789
20.473 9540335 9 15.033772011 -0.737772011
10 15.2391677014 1.9298322986
11 15.4153301807 -1.5003301807
12 15.5345709097 0.2044290903
13 15.712825385 -2.542825385
14 15.8686425956 -0.7296425956
15 16.0154228701 2.6675771299
SUMMARY OUTPUT 16 16.2750766649 -1.4460766649
17 16.2455637103 -0.5485637103
Regression Statistics 18 16.4891444083 3.7408555917
Multiple R 0.6352809133 19 16.8210480809 -1.5610480809
R Square 0.4035818388 20 16.7953329504 -1.0863329504
Adjusted R Square 0.3855085612 21 16.9689138825 1.6490861175
Standard Error 1.8456149387 22 17.0573706476 -1.6603706476
Observations 35 23 17.0718139336 0.3121860664
24 17.4305881997 -2.8755881997
ANOVA 25 17.5670483663 1.1169516337
df SS 26 17.7026693019 -1.0636693019
Regression 1 76.0636021829 27 17.8587571341 2.3112428659
Residual 33 112.40771856 28 18.0515217907 -1.1505217907
Total 34 188.4713207429 29 18.1486080391 3.3213919609
30 18.3565031781 -1.8145031781
Coefficients Standard Error 31 18.398385667 -1.418385667
Intercept -10.0401404897 5.6194273236 32 18.3857485519 1.7052514481
X Variable 1 0.0000030407 0.0000006435 33 18.4694679192 -1.8864679192
34 18.5750741861 0.1859258139
x-variable is 3.04E-06 or 0.00000304 35 18.9690779073 1.5039220927
intercept is -10.04
Regression equation y=0.00000304x-10.04

X Variable 1 Residual Plot

7868495 7977730 8005878 8070480 8086579 8196516 8161271 8235349 8246121 8313670 8371605 8410820 8469443 8520687 8568959 8654352 8644646 8724753 8833907 8825450 8882536 8911627 8916377 9034368 9079246 9123848 9175181 9238576 9270505 9338876 9352650 9348494 9376027 9410758 9540335 0.87847227039468123 -2.0866776983009174 -0.6752670910673686 2.2222981373148087 -0.56865395996198487 -0.49493849430068515 3.3352306881653391 -1.2370176789425713 -0.73777201100453738 1.9298322986451275 -1.5003301807468219 0.20442909034396806 -2.5428253850058926 -0.72964259556878375 2.6675771298958217 -1.446076664921069 -0.54856371031714701 3.7408555917170183 -1.5610480809428378 -1.0863329503962298 1.6490861175494196 -1.6603706475837665 0.31218606636960189 -2.8755881997205925 1.1169516337108085 -1.0636693019211876 2.3112428659457862 -1.1505217906702931 3.3213919608701303 -1.8145031781392191 -1.4183856669826618 1.7052514481351935 -1.8864679191746987 0.18592581394411312 1.5039220926673309

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 7868495 7977730 8005878 8070480 8086579 8196516 8161271 8235349 8246121 8313670 8371605 8410820 8469443 8520687 8568959 8654352 8644646 8724753 8833907 8825450 8882536 8911627 8916377 9034368 9079246 9123848 9175181 9238576 9270505 9338876 9352650 9348494 9376027 9410758 9540335 14.763999999999999 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 20.472999999999999 Predicted Y 7868495 7977730 8005878 8070480 8086579 8196516 8161271 8235349 8246121 8313670 8371605 8410820 8469443 8520687 8568959 8654352 8644646 8724753 8833907 8825450 8882536 8911627 8916377 9034368 9079246 9123848 9175181 9238576 9270505 9338876 9352650 9348494 9376027 9410758 9540335 13.885527729605318 14.217677698300918 14.303267091067369 14.499701862685193 14.548653959961985 14.882938494300685 14.775769311834662 15.001017678942571 15.033772011004537 15.239167701354873 15.415330180746821 15.534570909656033 15.712825385005893 15.868642595568783 16.015422870104178 16.27507666492107 16.245563710317146 16.489144408282982 16.821048080942838 16.795332950396229 16.968913882450579 17.057370647583767 17.071813933630398 17.430588199720592 17.567048366289193 17.702669301921187 17.858757134054215 18.051521790670293 18.148608039129869 18.356503178139221 18.398385666982662 18.385748551864808 18.469467919174697 18.575074186055886 18.969077907332668

X Variable 1

Y

X Variable 1 Residual Plot

7868495 7977730 8005878 8070480 8086579 8196516 8161271 8235349 8246121 8313670 8371605 8410820 8469443 8520687 8568959 8654352 8644646 8724753 8833907 8825450 8882536 8911627 8916377 9034368 9079246 9123848 9175181 9238576 9270505 9338876 9352650 9348494 9376027 9410758 9540335 0.87847227039468123 -2.0866776983009174 -0.6752670910673686 2.2222981373148087 -0.56865395996198487 -0.49493849430068515 3.3352306881653391 -1.2370176789425713 -0.73777201100453738 1.9298322986451275 -1.5003301807468219 0.20442909034396806 -2.5428253850058926 -0.72964259556878375 2.6675771298958217 -1.446076664921069 -0.54856371031714701 3.7408555917170183 -1.5610480809428378 -1.0863329503962298 1.6490861175494196 -1.6603706475837665 0.31218606636960189 -2.8755881997205925 1.1169516337108085 -1.0636693019211876 2.3112428659457862 -1.1505217906702931 3.3213919608701303 -1.8145031781392191 -1.4183856669826618 1.7052514481351935 -1.8864679191746987 0.18592581394411312 1.5039220926673309

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 7868495 7977730 8005878 8070480 8086579 8196516 8161271 8235349 8246121 8313670 8371605 8410820 8469443 8520687 8568959 8654352 8644646 8724753 8833907 8825450 8882536 8911627 8916377 9034368 9079246 9123848 9175181 9238576 9270505 9338876 9352650 9348494 9376027 9410758 9540335 14.763999999999999 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 20.472999999999999 Predicted Y 7868495 7977730 8005878 8070480 8086579 8196516 8161271 8235349 8246121 8313670 8371605 8410820 8469443 8520687 8568959 8654352 8644646 8724753 8833907 8825450 8882536 8911627 8916377 9034368 9079246 9123848 9175181 9238576 9270505 9338876 9352650 9348494 9376027 9410758 9540335 13.885527729605318 14.217677698300918 14.303267091067369 14.499701862685193 14.548653959961985 14.882938494300685 14.775769311834662 15.001017678942571 15.033772011004537 15.239167701354873 15.415330180746821 15.534570909656033 15.712825385005893 15.868642595568783 16.015422870104178 16.27507666492107 16.245563710317146 16.489144408282982 16.821048080942838 16.795332950396229 16.968913882450579 17.057370647583767 17.071813933630398 17.430588199720592 17.567048366289193 17.702669301921187 17.858757134054215 18.051521790670293 18.148608039129869 18.356503178139221 18.398385666982662 18.385748551864808 18.469467919174697 18.575074186055886 18.969077907332668

X Variable 1

Y

(h)

(h)  Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable.
Wal Mart Revenue Retail Sales Index
14.764 301337
12.131 281463
13.628 282445 SUMMARY OUTPUT
16.722 319107
13.98 315278 Regression Statistics
14.388 328499 Multiple R 0.5699442193
18.111 321151 R Square 0.3248364131
13.764 328025 Adjusted R Square 0.3043769105
14.296 326280 Standard Error 1.9636775405
17.169 313444 Observations 35
13.915 319639
15.739 324067 ANOVA
13.17 293027 df SS MS F Significance F
15.139 294892 Regression 1 61.2223478005 61.2223478005 15.8770434896 0.0003514832
18.683 338969 Residual 33 127.2489729423 3.8560294831
14.829 335626 Total 34 188.4713207429
15.697 345400
20.23 351068 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
15.26 351887 Intercept -0.6013362599 4.2980384153 -0.1399094661 0.8895819742 -9.3457611648 8.143088645 -9.3457611648 8.143088645
15.709 355897 X Variable 1 0.000051012 0.0000128023 3.9846007943 0.0003514832 0.0000249656 0.0000770585 0.0000249656 0.0000770585
18.618 333652
15.397 336662
17.384 344441
14.555 322222 RESIDUAL OUTPUT
18.684 318184
16.639 366989 Observation Predicted Y Residuals
20.17 357334 1 14.7704766892 -0.0064766892
16.901 380085 2 13.7566635479 -1.6256635479
21.47 373279 3 13.8067573642 -0.1787573642
16.542 368611 4 15.6769605133 1.0450394867
16.98 382600 5 15.4816354394 -1.5016354394
20.091 352686 6 16.156065526 -1.768065526
16.583 354740 7 15.7812291084 2.3297708916
18.761 363468 8 16.1318858224 -2.3678858224
20.473 332797 9 16.042869825 -1.746869825
10 15.3880793711 1.7809206289
11 15.7040989147 -1.7890989147
12 15.9299801963 -0.1909801963
13 14.346566696 -1.176566696
14 14.4417041373 0.6972958627
15 16.6901615101 1.9928384899
SUMMARY OUTPUT 16 16.5196282842 -1.6906282842
17 17.0182198935 -1.3212198935
Regression Statistics 18 17.3073560958 2.9226439042
Multiple R 0.5699442193 19 17.3491349508 -2.0891349508
R Square 0.3248364131 20 17.5536932026 -1.8446932026
Adjusted R Square 0.3043769105 21 16.4189305314 2.1990694686
Standard Error 1.9636775405 22 16.5724767503 -1.1754767503
Observations 35 23 16.969299354 0.414700646
24 15.8358629957 -1.2808629957
ANOVA 25 15.6298764069 3.0541235931
df SS 26 18.1195186712 -1.4805186712
Regression 1 61.2223478005 27 17.6269974938 2.5430025062
Residual 33 127.2489729423 28 18.7875722536 -1.8865722536
Total 34 188.4713207429 29 18.4403843579 3.0296156421
30 18.2022601885 -1.6602601885
Coefficients Standard Error 31 18.9158675163 -1.9358675163
Intercept -0.6013362599 4.2980384153 32 17.389893565 2.701106435
X Variable 1 0.000051012 0.0000128023 33 17.4946722805 -0.9116722805
34 17.9399053034 0.8210946966
x-variable is 0.000051 35 16.3753152433 4.0976847567
Intercept is -0.60134
Regression equation is y=0.000051x-0.60134

X Variable 1 Residual Plot

301337 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 332797 -6.4766891549918881E-3 -1.6256635478893529 -0.17875736416805132 1.0450394867386201 -1.5016354394007507 -1.7680655259798481 2.3297708915515933 -2.3678858223992982 -1.746869825040509 1.7809206288834982 -1.7890989147484149 -0.19098019629839058 -1.1765666960022561 0.69729586269451183 1.9928384898674274 -1.6906282842469409 -1.3212198935218922 2.9226439041688792 -2.0891349507519497 -1.8446932025621159 2.1990694686391592 -1.1754767503006409 0.41470064600078871 -1.2808629956525657 3.0541235930779678 -1.4805186711602047 2.5430025062031731 -1.8865722536305078 3.0296156420851972 -1.6602601884759345 -1.9358675162994743 2.7011064349846308 -0.9116722805311035 0.82109469657634904 4.0976847567433197

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 301337 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 332797 14.763999999999999 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 20.472999999999999 Predicted Y 301337 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 332797 14.770476689154991 13.756663547889353 13.806757364168051 15.676960513261381 15.481635439400751 16.156065525979848 15.781229108448407 16.131885822399298 16.042869825040508 15.388079371116502 15.704098914748414 15.929980196298391 14.346566696002256 14.441704137305488 16.690161510132572 16.519628284246942 17.018219893521891 17.307356095831121 17.349134950751949 17.553693202562116 16.418930531360839 16.572476750300641 16.969299353999212 15.835862995652565 15.629876406922033 18.119518671160204 17.626997493796829 18.787572253630508 18.440384357914802 18.202260188475936 18.915867516299475 17.38989356501537 17.494672280531102 17.93990530342365 16.375315243256679

X Variable 1

Y

X Variable 1 Residual Plot

301337 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 332797 -6.4766891549918881E-3 -1.6256635478893529 -0.17875736416805132 1.0450394867386201 -1.5016354394007507 -1.7680655259798481 2.3297708915515933 -2.3678858223992982 -1.746869825040509 1.7809206288834982 -1.7890989147484149 -0.19098019629839058 -1.1765666960022561 0.69729586269451183 1.9928384898674274 -1.6906282842469409 -1.3212198935218922 2.9226439041688792 -2.0891349507519497 -1.8446932025621159 2.1990694686391592 -1.1754767503006409 0.41470064600078871 -1.2808629956525657 3.0541235930779678 -1.4805186711602047 2.5430025062031731 -1.8865722536305078 3.0296156420851972 -1.6602601884759345 -1.9358675162994743 2.7011064349846308 -0.9116722805311035 0.82109469657634904 4.0976847567433197

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 301337 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 332797 14.763999999999999 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 20.472999999999999 Predicted Y 301337 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 293027 294892 338969 335626 345400 351068 351887 355897 333652 336662 344441 322222 318184 366989 357334 380085 373279 368611 382600 352686 354740 363468 332797 14.770476689154991 13.756663547889353 13.806757364168051 15.676960513261381 15.481635439400751 16.156065525979848 15.781229108448407 16.131885822399298 16.042869825040508 15.388079371116502 15.704098914748414 15.929980196298391 14.346566696002256 14.441704137305488 16.690161510132572 16.519628284246942 17.018219893521891 17.307356095831121 17.349134950751949 17.553693202562116 16.418930531360839 16.572476750300641 16.969299353999212 15.835862995652565 15.629876406922033 18.119518671160204 17.626997493796829 18.787572253630508 18.440384357914802 18.202260188475936 18.915867516299475 17.38989356501537 17.494672280531102 17.93990530342365 16.375315243256679

X Variable 1

Y

(i)

(i) 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.
We indentify the regression equation with the highest R-squared value, the highest F value and the lowest p-value
The regression equation with the highest R-square value is the best regression equation because it means the data is closest to the regression equation
For Revenue versus CPI
R-squared value = 0.416
For Revenue and personal consumption:
R-squared value= 0.4036
For Revenue and Retails sales index
R-squared value= 0.3248
The regression with the highest R-squared value, the highest F value, and the lowest p-value is the regression equation of Revenue on CPI
Best Regression The regression of revenue on CPI

(j)

(j) Generate a residual plot and a line fit plot for the best model in part (i) and comment on what you see.
Wal Mart Revenue CPI
14.764 552.7
12.131 554.9
13.628 557.9
16.722 561.5
13.98 563.2
14.388 566.4
18.111 568.2
13.764 567.5
14.296 567.6
17.169 568.7
13.915 571.9
15.739 572.2
13.17 571.2
15.139 574.5
18.683 579
14.829 582.9
15.697 582.4
20.23 582.6
15.26 585.2
15.709 588.2
18.618 595.4
15.397 596.7
17.384 592
14.555 593.9
18.684 595.2
16.639 598.6
20.17 603.5
16.901 606.5
21.47 607.8
16.542 609.6
16.98 610.9
20.091 607.9
16.583 604.6
18.761 603.6 Comment on the line plot and the residual plot
20.473 606.348
The line fit plot indicates that the points are close to the regression equation
Therefore, the regression equation is a good model for the data
The residual plots indicate the residuals are almost evenly distributed around 0, meaning the regression equation is a good model
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.6447522354
R Square 0.4157054451
Adjusted R Square 0.3979995495
Standard Error 1.8267603919
Observations 35
ANOVA
df SS MS F Significance F
Regression 1 78.3485542745 78.3485542745 23.4783630486 0.0000290663
Residual 33 110.1227664683 3.3370535293
Total 34 188.4713207429
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -33.1342186173 10.2426576417 -3.2349239598 0.0027654609 -53.9730622759 -12.2953749587 -53.9730622759 -12.2953749587
X Variable 1 0.0848979804 0.0175211841 4.8454476624 0.0000290663 0.0492508634 0.1205450974 0.0492508634 0.1205450974
RESIDUAL OUTPUT
Observation Predicted Y Residuals
1 13.7888951429 0.9751048571
2 13.9756706998 -1.8446706998
3 14.2303646409 -0.6023646409
4 14.5359973703 2.1860026297
5 14.680323937 -0.700323937
6 14.9519974742 -0.5639974742
7 15.1048138389 3.0061861611
8 15.0453852527 -1.2813852527
9 15.0538750507 -0.7578750507
10 15.1472628291 2.0217371709
11 15.4189363664 -1.5039363664
12 15.4444057605 0.2945942395
13 15.3595077801 -2.1895077801
14 15.6396711154 -0.5006711154
15 16.0217120271 2.6612879729
16 16.3528141506 -1.5238141506
17 16.3103651604 -0.6133651604
18 16.3273447565 3.9026552435
19 16.5480795055 -1.2880795055
20 16.8027734467 -1.0937734467
21 17.4140389055 1.2039610945
22 17.52440628 -2.12740628
23 17.1253857721 0.2586142279
24 17.2866919349 -2.7316919349
25 17.3970593094 1.2869406906
26 17.6857124427 -1.0467124427
27 18.1017125466 2.0682874534
28 18.3564064878 -1.4554064878
29 18.4667738623 3.0032261377
30 18.619590227 -2.077590227
31 18.7299576015 -1.7499576015
32 18.4752636603 1.6157363397
33 18.195100325 -1.612100325
34 18.1102023446 0.6507976554
35 18.3435019947 2.1294980053

X Variable 1 Residual Plot

552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 0.97510485708523831 -1.8446706997674127 -0.60236464093012643 2.1860026296746184 -0.70032393698425821 -0.56399747422448421 3.0061861610778884 -1.2813852526508107 -0.75787505068957017 2.0217371708840979 -1.5039363663561218 0.29459423952760133 -2.1895077800848259 -0.50067111536381148 2.6612879728921186 -1.5238141506194012 -0.61336516042561939 3.9026552434968629 -1.2880795055108205 -1.0937734466735343 1.2039610945359591 -2.1274062799678912 0.25861422785369825 -2.7316919348826829 1.2869406906134664 -1.0467124427042727 2.0682874533966285 -1.4554064877660799 3.0032261377300742 -2.0775902269675512 -1.7499576014713902 1.6157363396913169 -1.6121003250297008 0.65079765535787359 2.1294980052528274

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 14.763999999999999 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 20.472999999999999 Predicted Y 552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 13.788895142914761 13.975670699767413 14.230364640930127 14.535997370325383 14.680323936984259 14.951997474224484 15.104813838922112 15.04538525265081 15.05387505068957 15.147262829115903 15.418936366356121 15.444405760472399 15.359507780084826 15.639671115363811 16.021712027107881 16.352814150619402 16.310365160425619 16.327344756503138 16.54807950551082 16.802773446673534 17.414038905464039 17.524406279967891 17.125385772146302 17.286691934882683 17.397059309386535 17.685712442704272 18.101712546603373 18.35640648776608 18.466773862269925 18.619590226967553 18.729957601471391 18.475263660308684 18.195100325029699 18.110202344642126 18.343501994747172

X Variable 1

Y

X Variable 1 Residual Plot

552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 0.97510485708523831 -1.8446706997674127 -0.60236464093012643 2.1860026296746184 -0.70032393698425821 -0.56399747422448421 3.0061861610778884 -1.2813852526508107 -0.75787505068957017 2.0217371708840979 -1.5039363663561218 0.29459423952760133 -2.1895077800848259 -0.50067111536381148 2.6612879728921186 -1.5238141506194012 -0.61336516042561939 3.9026552434968629 -1.2880795055108205 -1.0937734466735343 1.2039610945359591 -2.1274062799678912 0.25861422785369825 -2.7316919348826829 1.2869406906134664 -1.0467124427042727 2.0682874533966285 -1.4554064877660799 3.0032261377300742 -2.0775902269675512 -1.7499576014713902 1.6157363396913169 -1.6121003250297008 0.65079765535787359 2.1294980052528274

X Variable 1

Residuals

X Variable 1 Line Fit Plot

Y 552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 14.763999999999999 12.131 13.628 16.722000000000001 13.98 14.388 18.111000000000001 13.763999999999999 14.295999999999999 17.169 13.914999999999999 15.739000000000001 13.17 15.138999999999999 18.683 14.829000000000001 15.696999999999999 20.23 15.26 15.709 18.617999999999999 15.397 17.384 14.555 18.684000000000001 16.638999999999999 20.170000000000002 16.901 21.47 16.542000000000002 16.98 20.091000000000001 16.582999999999998 18.760999999999999 20.472999999999999 Predicted Y 552.70000000000005 554.9 557.9 561.5 563.20000000000005 566.4 568.20000000000005 567.5 567.6 568.70000000000005 571.9 572.20000000000005 571.20000000000005 574.5 579 582.9 582.4 582.6 585.20000000000005 588.20000000000005 595.4 596.70000000000005 592 593.9 595.20000000000005 598.6 603.5 606.5 607.79999999999995 609.6 610.9 607.9 604.6 603.6 606.34799999999996 13.788895142914761 13.975670699767413 14.230364640930127 14.535997370325383 14.680323936984259 14.951997474224484 15.104813838922112 15.04538525265081 15.05387505068957 15.147262829115903 15.418936366356121 15.444405760472399 15.359507780084826 15.639671115363811 16.021712027107881 16.352814150619402 16.310365160425619 16.327344756503138 16.54807950551082 16.802773446673534 17.414038905464039 17.524406279967891 17.125385772146302 17.286691934882683 17.397059309386535 17.685712442704272 18.101712546603373 18.35640648776608 18.466773862269925 18.619590226967553 18.729957601471391 18.475263660308684 18.195100325029699 18.110202344642126 18.343501994747172

X Variable 1

Y

(k)

(k) Comparing the results of parts (d) and (i), which of these two models is better? Use R-square values, Significance F values, p-values and other appropriate criteria to explain your answer
We choose the regression equation with the highest R-squared value because it will be the best regression model for the data
Comparing the two regression equations, the
R-squared value for (d) is 0.5737
R-squared value for (I) is 0.4157
Therefore, (d) is the better regression model

Attachment 2

Due Date: 12/11

Please use one Excel file to complete this case study, and use one spreadsheet for one problem. Finally, upload the Excel file to the submission link 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 (WalMart_revenue.xlsx) is included in the Excel data zip file in week one, 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) Find the residuals and the predicted values for the multiple regression model, and then plot the residuals against the predicted values 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?

Identify and remove the four 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) Find the residuals and the predicted values for the multiple regression model, and then plot the residuals against the predicted values 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 R-square values, adjusted R-square values, Significance F values, and p-values to explain your answer.

Attachment 3

Data

Date Wal Mart Revenue CPI Personal Consumption Retail Sales Index December
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 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 592 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.348 9540335 332797 0