Wal-Mart is the second largest retailer in the world. The data is included. It h

Question

Wal-Mart is the second largest retailer in the world. The data is included. It holds monthly data on Wal-Mart’s revenue, along with several possibly related economic variables.

1. Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable.

2. Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable.

3. Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable.

4. Which of these three models is the best? Use R-square value, Significance F values and other appropriate criteria to explain your answer.

5. Identify and remove the four cases corresponding to December revenue.

6. Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable.

7. Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable.

8. Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable.

9. Which of these three models is the best? Use R-square values and Significance F values to explain your answer.

10. Comparing the results of parts (d) and (h), which of these two models is better? Use R-square values, Significance F values and other appropriate criteria to explain your answer.

Please use one Excel file to complete this problem, and use one sheet for one sub-problem. Please be clear so i can really understand the calculations and input into excel

Date Wal Mart Revenue CPI Personal Consumption Retail Sales Index December 11/28/2003 14.764 552.7 7868495 301337 0 12/30/2003 23.106 552.1 7885264 357704 1 1/30/2004 12.131 554.9 7977730 281463 0 2/27/2004 13.628 557.9 8005878 282445 0 3/31/2004 16.722 561.5 8070480 319107 0 4/29/2004 13.98 563.2 8086579 315278 0 5/28/2004 14.388 566.4 8196516 328499 0 6/30/2004 18.111 568.2 8161271 321151 0 7/27/2004 13.764 567.5 8235349 328025 0 8/27/2004 14.296 567.6 8246121 326280 0 9/30/2004 17.169 568.7 8313670 313444 0 10/29/2004 13.915 571.9 8371605 319639 0 11/29/2004 15.739 572.2 8410820 324067 0 12/31/2004 26.177 570.1 8462026 386918 1 1/21/2005 13.17 571.2 8469443 293027 0 2/24/2005 15.139 574.5 8520687 294892 0 3/30/2005 18.683 579 8568959 338969 0 4/29/2005 14.829 582.9 8654352 335626 0 5/25/2005 15.697 582.4 8644646 345400 0 6/28/2005 20.23 582.6 8724753 351068 0 7/28/2005 15.26 585.2 8833907 351887 0 8/26/2005 15.709 588.2 8825450 355897 0 9/30/2005 18.618 595.4 8882536 333652 0 10/31/2005 15.397 596.7 8911627 336662 0 11/28/2005 17.384 592 8916377 344441 0 12/30/2005 27.92 589.4 8955472 406510 1 1/27/2006 14.555 593.9 9034368 322222 0 2/23/2006 18.684 595.2 9079246 318184 0 3/31/2006 16.639 598.6 9123848 366989 0 4/28/2006 20.17 603.5 9175181 357334 0 5/25/2006 16.901 606.5 9238576 380085 0 6/30/2006 21.47 607.8 9270505 373279 0 7/28/2006 16.542 609.6 9338876 368611 0 8/29/2006 16.98 610.9 9352650 382600 0 9/28/2006 20.091 607.9 9348494 352686 0 10/20/2006 16.583 604.6 9376027 354740 0 11/24/2006 18.761 603.6 9410758 363468 0 12/29/2006 28.795 604.5 9478531 424946 1 1/26/2007 20.473 606.348 9540335 332797 0

Explanation / Answer

1)

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.34

R Square

0.11

Adjusted R Square

0.09

Standard Error

3.69

Observations

39

ANOVA

df

SS

MS

F

Significance F

Regression

1.00

64.59

64.59

4.75

0.04

Residual

37.00

503.63

13.61

Total

38.00

568.22

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

-24.41

19.25

-1.27

0.21

-63.41

14.59

CPI

0.07

0.03

2.18

0.04

0.01

0.14

2)

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.39

R Square

0.16

Adjusted R Square

0.13

Standard Error

3.60

Observations

39.00

ANOVA

df

SS

MS

F

Significance F

Regression

1

88.22

88.22

6.80

0.01

Residual

37

480.00

12.97

Total

38

568.22

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

-8.895075

10.139008

-0.877312

0.385978

-29.438657

11.648508

Personal Consumption

0.000003

0.000001

2.607719

0.013067

0.000001

0.000005

3)

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.757

R Square

0.574

Adjusted R Square

0.562

Standard Error

2.559

Observations

39

ANOVA

df

SS

MS

F

Significance F

Regression

1

325.970

325.970

49.786

0.000

Residual

37

242.253

6.547

Total

38

568.223

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

-13.80397

4.45567

-3.09807

0.00371

-22.83201

-4.77592

Retail Sales Index

0.00009

0.00001

7.05595

0.00000

0.00007

0.00012

4)

The best model is the one with the Retail Sales Index being the independent variable because that model has an r^2 of 0.57 whereas the ones with CPI and Personal Consumption have an r^2 of 0.11 and 0.155 respectively. This tells that higher amount of variation in dependent variable is explained for by the independent variable using the retail sales index as the independent variable.

6)

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.6448

R Square

0.4157

Adjusted R Square

0.3980

Standard Error

1.8268

Observations

35.0000

ANOVA

df

SS

MS

F

Significance F

Regression

1

78.348554

78.348554

23.478363

0.000029

Residual

33

110.122766

3.337054

Total

34

188.471321

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

-33.134219

10.242658

-3.234924

0.002765

-53.973062

-12.29537496

CPI

0.084898

0.017521

4.845448

0.000029

0.049251

0.120545097

7)

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.635280913

R Square

0.403581839

Adjusted R Square

0.385508561

Standard Error

1.845614939

Observations

35

ANOVA

df

SS

MS

F

Significance F

Regression

1

76.06360218

76.06360218

22.33030707

4.13258E-05

Residual

33

112.4077186

3.406294502

Total

34

188.4713207

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

-10.040140

5.619427

-1.786684

0.083179

-21.472951

1.392670

Personal Consumption

0.000003

0.000001

4.725495

0.000041

0.000002

0.000004

8)

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.5699

R Square

0.3248

Adjusted R Square

0.3044

Standard Error

1.9637

Observations

35.0000

ANOVA

df

SS

MS

F

Significance F

Regression

1

61.22235

61.22235

15.87704

0.00035

Residual

33

127.24897

3.85603

Total

34

188.47132

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

-0.601336

4.298038

-0.139909

0.889582

-9.345761

8.143089

Retail Sales Index

0.000051

0.000013

3.984601

0.000351

0.000025

0.000077

9)

After removing December revenue, model with CPI is the best because the r^2 is the highest in that case and it is 0.41 whereas for Personal Consumption it is 0.4035 and for Retail Sales Index it is 0.32.

10)

Considering all of the above models, the model with the highest r^2 is the one with Retail Sales Index as independent variable before removing the December revenue. However, after removing the December revenue, the r^2 for CPI and Personal Consumption has increased as compared to when the December revenue was included.

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.34

R Square

0.11

Adjusted R Square

0.09

Standard Error

3.69

Observations

39

ANOVA

df

SS

MS

F

Significance F

Regression

1.00

64.59

64.59

4.75

0.04

Residual

37.00

503.63

13.61

Total

38.00

568.22

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Intercept

-24.41

19.25

-1.27

0.21

-63.41

14.59

CPI

0.07

0.03

2.18

0.04

0.01

0.14

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.