Question

Please explain the steps that need to be taken in Excel in order to complete the questions.

Wal-Mart Revenue: Case Study One 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 linear regression model to predict Wal-Mart revenue, using CPI as the only (b) Develop a linear regression model to predict Wal-Mart revenue, using Personal (c) Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index (d) Which of these three models is the best? Use R-square value, Significance F values and independent variable Consumption as the only independent variable as the only independent variable other appropriate criteria to explain your answer Identify and remove the four cases corresponding to December revenue (e) Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only (f) Develop a linear regression model to predict Wal-Mart revenue, using Personal (g) Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index (h) Which of these three models is the best? Use R-square values and Significance F values (i) Comparing the results of parts (d) and (h), which of these two models is better? Use R- independent variable Consumption as the only independent variable as the only independent variable. to explain your answer square values, Significance F values and other appropriate criteria to explain your answer Date Wal Mart Revenue 14.764 23.106 CPI Personal Consumption 7868495 7885264 7977730 8005878 Retail Sales Index December 301337 357704 281463 282445 319107 315278 328499 321151 328025 326280 313444 319639 324067 386918 293027 11/28/2003 12/30/2003 552.7 552.1 0 0 0 0 0 13.628 16.722 557.9 8086579 8196516 8161271 8235349 8246121 8313670 8371605 8410820 8462026 8469443 8520687 8568959 8654352 5/28/2004 14.388 7/27/2004 13.764 567.5 4.296 567.6 17.169 13.915571.9 15.739 572.2 26.177570.1 0 0 9/30/2004 10/29/2004 11/29/2004 13.17 571.2 0 18.683 338969 335626 15.697 582.4 8724753 6/28/2005 7/28/2005 351068 351887 355897 333652 336662 8833907 9/30/2005 10/31/2005 11/28/2005 15.709 588.2 595.4 5.397596.7 592 18.618 8882536 8911627 8916377 8955472 9034368 9079246 9123848 9175181 9238576 9270505 9338876 9352650 9348494 9376027 9410758 9478531 9540335 406510 1/27/2006 14.555 18.684 16.639 593.9 595.2 318184 366989 357334 4/28/2006 16.901 21.47 16.542 607.8 7/28/2006 8/29/2006 9/28/2006 10/20/2006 11/24/2006 373279 368611 382600 352686 354740 363468 424946 332797 610.9 20.091 16.583 18.761 28.795 20.473 606.348 603.6

Answer 1

a) linear regression model to predict Wal_Mart revenue using CPI as only independent variable DATA> Regression> select y asWal mart Revenue>x as CPI only SUMMARY OUTPUT Raquare value is 0.113672 Regression Statistics Multiple F 0.337152 R Square 0.113672 Adjusted 0.089717 StandardE 3.689401 Observati F statistics value is 4.745245 p vlaue is 0.035826 39 here p vlaue is less than alpha value hence it is significant model ANOVA MS gnificance F Regressio Residual Total 1 64.59075 64.59075 4.745245 0.035826 37 503.632 13.61168 38 568.2228 P-value Intercept-24.4085 19.248481.26808 0.212692-63.409714.59259-63.4097 14.59259 coefficientandard Err t Stat Lower 95%Upper 95%ower 95.09 p er 95.0% CPI 0.071792 0.032957 2.178358 0.035826 0.005015 0.138568 0.005015 0.138568

here indpendent variable is personal consumption DATA> Regression> select y asWal mart Revenue>x as personal consumption. SUMMARY OUTPUT Raquare value is 0.155255 Regression Statistics Multiple F 0.394024 R Square0.155255 Adjusted I 0.132424 Standard 3.601814 Observati F statistics value is 6.800197 p vlaue is 0.013067 39 model is significant because p value is less than alpha value ANOVA MS gnificance F Regressio Residual Total 1 88.21939 88.21939 6.800197 0.013067 37 480.0034 12.97306 38 568.2228 P-value Intercept -8.89507 10.13901-0.87731 0.38597829.4387 11.64851-29.4387 11.64851 personal 3.03E-06 1.16E-06 2.607719 0.013067 6.75E-07 5.38E-06 6.75E-07 5.38E-06 Coefficientandard Err t Stat Lower 95%Up er 95%ower 95.09 p er 95.0%

here indpendent variable is personal consumption DATA> Regression> select y asWal mart Revenue>x retail sale index SUMMARY OUTPUT Regression Statistics Multiple F0.757407 R Square 0.573666 Adjusted 0.562143 Standard 2.558783 Observati Raquare value is 0.573666 F statistics value is 49.78641 p vlaue is approximately zero 39 model is significant because p value is less than alpha value ANOVA MS gnificance F Regressio Residual Total 1 325.9701 325.9701 49.78641 2.39E-08 37 242.2527 6.547371 38 568.2228 P-value Intercept -13.8044.455669-3.09807 0.003708-22.832-4.77592-22.832 -4.77592 Retail sale 9.19E-05 1.3E-05 7.055949 2.39E-08 6.55E-05 0.000118 6.55E-05 0.000118 Coefficientandard Err t Stat Lower 95%Upper 95%ower 95.09 pper 95.0% from three model here R square value for wal mart revenue vs Retail sale index is bigger than other two hence it is good model as compare to others. that is coefficient is large for model then that model is bset model

identify ad removelthe four cases corresponding to december revenue DATA> Regression> select y asWal mart Revenue>x as CPI only here n is 35 SUMMARY OUTPUT Regression Statistics Multiple F 0.644752 R Square 0.415705 Adjusted Standard 1.82676 Observati Raquare value is 0.415705 0.398 F statistics value is 23.47836 p vlaue is approximately zero 35 here p vlaue is less than alpha value hence it is significant model ANOVA MS gnificance F Regressio Residual Total 1 78.3485578.34855 23.47836 2.91E-05 33 110.1228 3.337054 34 188.4713 P-value Intercept33.1342 10.24266-3.23492 0.002765-53.9731-12.2954-53.9731-12.2954 Coefficientandard Err t Stat Lower 95%Upper 95%ower 95.09 pper 95.0% CPI 0.084898 0.017521 4.8454482.91E-05 0.049251 0.120545 0.049251 0.120545

DATA> Regression> select y asWal mart Revenue>x as personal consumption. SUMMARY OUTPUT Raquare value is 0.403582 Regression Statistics Multiple F 0.635281 R Square 0.403582 AdiustedI 0.385509 Standard 1.845615 Observati F statistics value is 22.33031 p vlaue is approximately zero 35 here p vlaue is less than alpha value hence it is significant model ANOVA MS gnificance F Regressio Residual Total 1 76.0636 76.0636 22.33031 4.13E-05 33 112.4077 3.406295 34 188.4713 P-value Intercept-10.0401 5.619427-1.786680.083179 -21.473 1.39267 21.473 1.39267 personal 3.04E-06 6.43E-07 4.725495 4.13E-05 1.73E-06 4.35E-06 1.73E-064.35E-06 coefficientandard Err t Stat Lower 95%Upper 95%ower 95.09 pper 95.0%

DATA> Regression> select y asWal mart Revenue>x Retail sale index SUMMARY OUTPUT Raquare value is 0.324836 Regression Statistics Multiple F 0.569944 R Square 0.324836 Adjusted I 0.304377 Standard 1.963678 Observati F statistics value is 15.87704 p vlaue is approximately 0.000351. 35 here p vlaue is less than alpha value hence it is significant model ANOVA MS gnificance F Regressio Residual Total 1 61.22235 61.22235 15.87704 0.000351 33 127.249 3.856029 34 188.4713 P-value Intercept -0.601344.298038-0.13991 0.889582-9.34576 8.1430899.34576 8.143089 Retail sale 5.1E-05 1.28E-05 3.984601 0.000351 2.5E-05 7.71E-05 2.5E-05 7.71E-05 Coefficientandard Err t stat Lower 95%Up er 95%ower 95.0 p er 95.0%

8) from three model here R square value for wal mart revenue vs CPI is bigger than other two hence it is good model as compare to others. that is coefficient is large for model then that model is bset model from comparison d and h d model is significant because in d one regression model is r square is 0.57 which is greater value than all the model.