please see the attached word file for the rubric, and excel file for data you need to use. the assignment has to be done by excel.Develop a multiple linear regression model to predict Wal-Mart revenue, using CPI, Personal Consumption, and Retail Sales Index as the independent variables.Check the residuals box under residuals and Excel will generate predicted values and residuals at the bottom of the output for the multiple regression model.Then, use these two output values to create the residual plot by Excel’s scatter chart (Insert tab > Charts > Scatter chart).Comment on what you see on the plot.Does it seem that Wal-Mart’s revenue is closely related to the general state of the economy?Explain it.Identify and remove the five cases corresponding to December revenue. (a) Develop a multiple linear regression model to predict Wal-Mart revenue, using CPI, Personal Consumption, and Retail Sales Index as the independent variables. (b) Check the residuals box under residuals and Excel will generate predicted values and residuals at the bottom of the output for the multiple regression model. Then, use these two output values to create the residual plot by Excel’s scatter chart (Insert tab > Charts > Scatter chart). Comment on what you see on the plot. (c) Does it seem that Wal-Mart’s revenue is closely related to the general state of the economy? (d) Compare the results of parts (a) and (d), which of these two models is better? Use R-square values, adjusted R-square values, Significance F values, p-values, scatter plot, residual plot and normal probability plot to explain your answer. (e) Compare the results of parts (a) and (d), what can you conclude about the impact of December data to Wal-Mart Revenue?

20181220200600case_study_two.docx

20181220200555walmart_revenue_2004_2009_1___1_.xlsx

Unformatted Attachment Preview

Wal-Mart Revenue: Case Study Two

Due Date: 12/16, 11:59 pm

*The due date for case two is correct. You have two weeks to work on case two since 12/3.

Please use ONLY one Excel file to complete this case study, and use one spreadsheet for each

problem. Finally, upload the Excel file to the submission link (Week 6 case study) for grading.

No credit will be granted for problems that are not completed using Excel.

Wal-Mart is the second largest retailer in the world. The data file (Wal-Mart Revenue 20042009.xlsx) is posted below the case study two file, and it holds monthly data on Wal-Mart’s

revenue, along with several possibly related economic variables.

(a) Develop a multiple linear regression model to predict Wal-Mart revenue, using CPI,

Personal Consumption, and Retail Sales Index as the independent variables.

(b) Check the residuals box under residuals and Excel will generate predicted values and

residuals at the bottom of the output for the multiple regression model. Then, use these

two output values to create the residual plot by Excel’s scatter chart (Insert tab > Charts >

Scatter chart). Comment on what you see on the plot.

(c) Does it seem that Wal-Mart’s revenue is closely related to the general state of the

economy? Explain it.

Identify and remove the five cases corresponding to December revenue.

(d) Develop a multiple linear regression model to predict Wal-Mart revenue, using CPI,

Personal Consumption, and Retail Sales Index as the independent variables.

(e) Check the residuals box under residuals and Excel will generate predicted values and

residuals at the bottom of the output for the multiple regression model. Then, use these

two output values to create the residual plot by Excel’s scatter chart (Insert tab > Charts >

Scatter chart). Comment on what you see on the plot.

(f) Does it seem that Wal-Mart’s revenue is closely related to the general state of the

economy?

(g) Compare the results of parts (a) and (d), which of these two models is better? Use Rsquare values, adjusted R-square values, Significance F values, p-values, scatter plot,

residual plot and normal probability plot to explain your answer.

(h) Compare the results of parts (a) and (d), what can you conclude about the impact of

December data to Wal-Mart Revenue?

Date

1/30/2004

2/27/2004

3/31/2004

4/29/2004

5/28/2004

6/30/2004

7/27/2004

8/27/2004

9/30/2004

10/29/2004

11/29/2004

12/31/2004

1/21/2005

2/24/2005

3/30/2005

4/29/2005

5/25/2005

6/28/2005

7/28/2005

8/26/2005

9/30/2005

10/31/2005

11/28/2005

12/30/2005

1/27/2006

2/23/2006

3/31/2006

4/28/2006

5/25/2006

6/30/2006

7/28/2006

8/29/2006

9/28/2006

10/20/2006

11/24/2006

12/29/2006

1/26/2007

2/23/2007

3/30/2007

4/27/2007

5/25/2007

6/29/2007

7/27/2007

8/31/2007

9/28/2007

10/26/2007

Wal Mart Revenue

12.131

13.628

16.722

13.98

14.388

18.111

13.764

14.296

17.169

13.915

15.739

26.177

13.17

15.139

18.683

14.829

15.697

20.23

15.26

15.709

18.618

15.397

17.384

27.92

14.555

16.87

16.639

17.2

16.901

21.47

16.542

16.98

20.091

16.583

18.761

28.795

16.1

17.984

18.939

22.47

19.201

23.77

18.942

19.38

22.491

18.983

CPI

554.9

557.9

561.5

563.2

566.4

568.2

567.5

567.6

568.7

571.9

572.2

570.1

571.2

574.5

579

582.9

582.4

582.6

585.2

588.2

595.4

596.7

592

609.4

573.9

595.2

598.6

603.5

606.5

607.8

609.6

610.9

607.9

604.6

603.6

604.5

606.3

594.6

599.3

613.3

642.8

623.9

625.6

626.9

623.9

619.9

Personal Consumption

7977730

8005878

8070480

8086579

8196516

8161271

8235349

8246121

8313670

8371605

8410820

8462026

8469443

8520687

8568959

8654352

8644646

8724753

8833907

8825450

8882536

8911627

8916377

8955472

9034368

9079246

9123848

9175181

9238576

9270505

9338876

9352650

9348494

9376027

9410758

9478531

9540335

9500318

9547774

9602393

9669845

9703817

9776564

9791220

9786798

9816093

Retail Sales Index December

281463

0

282445

0

319107

0

315278

0

328499

0

321151

0

328025

0

326280

0

313444

0

319639

0

324067

0

386918

1

293027

0

294892

0

338969

0

335626

0

345400

0

351068

0

351887

0

355897

0

333652

0

336662

0

344441

0

406510

1

322222

0

318184

0

366989

0

357334

0

380085

0

373279

0

368611

0

382600

0

352686

0

354740

0

363468

0

424946

1

332797

0

327686

0

376491

0

366936

0

389687

0

382781

0

378113

0

392125

0

362211

0

364265

0

11/30/2007

12/28/2007

1/25/2008

2/29/2008

3/28/2008

4/25/2008

5/30/2008

6/27/2008

7/25/2008

8/29/2008

9/26/2008

10/31/2008

11/28/2008

12/26/2008

1/30/2009

2/27/2009

3/27/2009

4/24/2009

5/29/2009

6/26/2009

7/31/2009

21.161

31.245

19.923

21.512

19.023

20.178

21.9

21.24

22.1

20.981

20.419

20

21.022

32.85

19.784

20.962

22.951

22.062

20.856

23.700

24.413

620.6

642.5

623.4

622.3

626.9

651.2

636.1

638.7

640.2

641.9

643.2

641.2

637.9

656.9

637.8

639.7

638.9

643.7

648.1

649.4

651.4

9931068

9953178

10018937

10146599

10197093

10255207

10326976

10363123

10440525

10456119

10451414

10482584

10521902

10508628

10578596

10714428

10768153

10829987

10906349

10944809

11027165

372970

434488

342422

344464

339463

388158

378653

397579

394488

389780

403812

373978

381932

443677

350195

353997

356183

351032

354928

395869

389656

0

1

0

0

0

0

0

0

0

0

0

0

0

1

0

0

0

0

0

0

0

…

Purchase answer to see full

attachment