The worksheet “Data1” of the Excel file “MonthlyTravelDistance” shows the monthl

Question

The worksheet “Data1” of the Excel file “MonthlyTravelDistance” shows the monthly distance traveled (in billion miles) by vehicles on urban highways for five consecutive years (Year 1 to Year 5).

Using Excel, plot a line chart or a scatter chart of the time series. What type of patterns can you identify in the data? Provide some details of these patterns.

Define 12 dummy variables corresponding to 12 months (Jan, Feb,…,Dec). Since the categorical variable Month has 12 levels (categories), delete the dummy variable Dec, to assume the linear regression model:

,

in which t = time period. See the worksheet “Data2”.

Run Regression to find the estimated regression equation:

Clearly show this equation.

Using your equation found in Task 2, make forecasts for the twelve months of Year 6. Provide details of your calculations. Are these forecasts consistent with the patterns you observed in Task 1?

To better prepare for the assignment, study pages 409 – 410 in the textbook, and pages 38 – 41, 43 in the slides. The studied examples refer to quarterly (rather than monthly) data, but the approach is very similar as discussed at the bottom of page 410.

Use Microsoft Word to write a managerial report with your name shown on the first page. (I will not accept, for example, pdf documents.) The report should include all your relevant Excel outputs (copy and paste them), so do not attach any separate Excel files

Distance Jan Feb Mar Apr May June July Aug Sep Oct Nov Time 4.22 1 0 0 0 0 0 0 0 0 0 0 1 5.32 0 1 0 0 0 0 0 0 0 0 0 2 5.21 0 0 1 0 0 0 0 0 0 0 0 3 5.12 0 0 0 1 0 0 0 0 0 0 0 4 4.92 0 0 0 0 1 0 0 0 0 0 0 5 4.49 0 0 0 0 0 1 0 0 0 0 0 6 4.55 0 0 0 0 0 0 1 0 0 0 0 7 4.49 0 0 0 0 0 0 0 1 0 0 0 8 4.44 0 0 0 0 0 0 0 0 1 0 0 9 4.39 0 0 0 0 0 0 0 0 0 1 0 10 4.37 0 0 0 0 0 0 0 0 0 0 1 11 4.35 0 0 0 0 0 0 0 0 0 0 0 12 4.31 1 0 0 0 0 0 0 0 0 0 0 13 5.44 0 1 0 0 0 0 0 0 0 0 0 14 5.34 0 0 1 0 0 0 0 0 0 0 0 15 5.24 0 0 0 1 0 0 0 0 0 0 0 16 4.98 0 0 0 0 1 0 0 0 0 0 0 17 4.59 0 0 0 0 0 1 0 0 0 0 0 18 4.68 0 0 0 0 0 0 1 0 0 0 0 19 4.65 0 0 0 0 0 0 0 1 0 0 0 20 4.61 0 0 0 0 0 0 0 0 1 0 0 21 4.68 0 0 0 0 0 0 0 0 0 1 0 22 4.74 0 0 0 0 0 0 0 0 0 0 1 23 4.79 0 0 0 0 0 0 0 0 0 0 0 24 4.38 1 0 0 0 0 0 0 0 0 0 0 25 5.51 0 1 0 0 0 0 0 0 0 0 0 26 5.41 0 0 1 0 0 0 0 0 0 0 0 27 5.36 0 0 0 1 0 0 0 0 0 0 0 28 4.98 0 0 0 0 1 0 0 0 0 0 0 29 4.63 0 0 0 0 0 1 0 0 0 0 0 30 4.71 0 0 0 0 0 0 1 0 0 0 0 31 4.78 0 0 0 0 0 0 0 1 0 0 0 32 4.82 0 0 0 0 0 0 0 0 1 0 0 33 4.88 0 0 0 0 0 0 0 0 0 1 0 34 4.85 0 0 0 0 0 0 0 0 0 0 1 35 4.89 0 0 0 0 0 0 0 0 0 0 0 36 4.45 1 0 0 0 0 0 0 0 0 0 0 37 5.59 0 1 0 0 0 0 0 0 0 0 0 38 5.5 0 0 1 0 0 0 0 0 0 0 0 39 5.41 0 0 0 1 0 0 0 0 0 0 0 40 5.01 0 0 0 0 1 0 0 0 0 0 0 41 4.72 0 0 0 0 0 1 0 0 0 0 0 42 4.78 0 0 0 0 0 0 1 0 0 0 0 43 4.79 0 0 0 0 0 0 0 1 0 0 0 44 4.82 0 0 0 0 0 0 0 0 1 0 0 45 4.92 0 0 0 0 0 0 0 0 0 1 0 46 5.06 0 0 0 0 0 0 0 0 0 0 1 47 5.11 0 0 0 0 0 0 0 0 0 0 0 48 4.51 1 0 0 0 0 0 0 0 0 0 0 49 5.65 0 1 0 0 0 0 0 0 0 0 0 50 5.62 0 0 1 0 0 0 0 0 0 0 0 51 5.49 0 0 0 1 0 0 0 0 0 0 0 52 5.12 0 0 0 0 1 0 0 0 0 0 0 53 4.8 0 0 0 0 0 1 0 0 0 0 0 54 4.88 0 0 0 0 0 0 1 0 0 0 0 55 4.82 0 0 0 0 0 0 0 1 0 0 0 56 4.95 0 0 0 0 0 0 0 0 1 0 0 57 5.12 0 0 0 0 0 0 0 0 0 1 0 58 5.22 0 0 0 0 0 0 0 0 0 0 1 59 5.44 0 0 0 0 0 0 0 0 0 0 0 60

Explanation / Answer

Answer:

Regression Analysis

R²

0.939

Adjusted R²

0.924

n

60

R

0.969

k

12

Std. Error

0.104

Dep. Var.

Distance

ANOVA table

Source

SS

df

MS

F

p-value

Regression

7.8952

12  

0.6579

60.38

2.21E-24

Residual

0.5121

47  

0.0109

Total

8.4073

59  

Regression output

confidence interval

variables

coefficients

std. error

   t (df=47)

p-value

95% lower

95% upper

Intercept

4.5758

0.0547

83.591

8.90E-53

4.4656

4.6859

Jan

-0.4380

0.0666

-6.578

3.58E-08

-0.5720

-0.3041

Feb

0.6805

0.0665

10.234

1.51E-13

0.5467

0.8143

Mar

0.5851

0.0664

8.811

1.62E-11

0.4515

0.7187

Apr

0.4836

0.0663

7.292

2.96E-09

0.3502

0.6170

May

0.1522

0.0663

2.297

.0261

0.0189

0.2854

June

-0.2133

0.0662

-3.222

.0023

-0.3464

-0.0801

July

-0.1487

0.0661

-2.249

.0292

-0.2818

-0.0157

Aug

-0.1722

0.0661

-2.605

.0123

-0.3052

-0.0392

Sep

-0.1596

0.0661

-2.417

.0196

-0.2925

-0.0267

Oct

-0.0991

0.0660

-1.501

.1401

-0.2319

0.0338

Nov

-0.0585

0.0660

-0.887

.3797

-0.1914

0.0743

Time

0.0095

0.0008

11.902

8.68E-16

0.0079

0.0110

Regression Equation

Distance = 4.5758 - 0.4380 Jan + 0.6805 Feb + 0.5851 Mar + 0.4836 Apr + 0.1522 May

           - 0.2133 June - 0.1487 July - 0.1722 Aug - 0.1596 Sep - 0.0991 Oct - 0.0585 Nov

           + 0.009451 Time

Prediction for Jan, year 6,

Predicted Distance = 4.5758 - 0.4380 *1 + 0.6805 *0 + 0.5851 *0 + 0.4836 *0 + 0.1522 *0

           - 0.2133 *0 - 0.1487 *0 - 0.1722 *0 - 0.1596 *0 - 0.0991 *0 - 0.0585 *0

           + 0.009451 *61

=4.714

Similarly prediction for other months are calculated.

95% Confidence Intervals

95% Prediction Intervals

Year 6

Time

Predicted

lower

upper

lower

upper

Jan

61

4.714

4.60413

4.82437

4.47713

4.95137

Feb

62

5.842

5.73213

5.95237

5.60513

6.07937

Mar

63

5.756

5.64613

5.86637

5.51913

5.99337

Apr

64

5.664

5.55413

5.77437

5.42713

5.90137

May

65

5.342

5.23213

5.45237

5.10513

5.57937

June

66

4.986

4.87613

5.09637

4.74913

5.22337

July

67

5.060

4.95013

5.17037

4.82313

5.29737

Aug

68

5.046

4.93613

5.15637

4.80913

5.28337

Sep

69

5.068

4.95813

5.17837

4.83113

5.30537

Oct

70

5.138

5.02813

5.24837

4.90113

5.37537

Nov

71

5.188

5.07813

5.29837

4.95113

5.42537

Dec

72

5.256

5.14613

5.36637

5.01913

5.49337

Regression Analysis

R²

0.939

Adjusted R²

0.924

n

60

R

0.969

k

12

Std. Error

0.104

Dep. Var.

Distance

ANOVA table

Source

SS

df

MS

F

p-value

Regression

7.8952

12  

0.6579

60.38

2.21E-24

Residual

0.5121

47  

0.0109

Total

8.4073

59  

Regression output

confidence interval

variables

coefficients

std. error

   t (df=47)

p-value

95% lower

95% upper

Intercept

4.5758

0.0547

83.591

8.90E-53

4.4656

4.6859

Jan

-0.4380

0.0666

-6.578

3.58E-08

-0.5720

-0.3041

Feb

0.6805

0.0665

10.234

1.51E-13

0.5467

0.8143

Mar

0.5851

0.0664

8.811

1.62E-11

0.4515

0.7187

Apr

0.4836

0.0663

7.292

2.96E-09

0.3502

0.6170

May

0.1522

0.0663

2.297

.0261

0.0189

0.2854

June

-0.2133

0.0662

-3.222

.0023

-0.3464

-0.0801

July

-0.1487

0.0661

-2.249

.0292

-0.2818

-0.0157

Aug

-0.1722

0.0661

-2.605

.0123

-0.3052

-0.0392

Sep

-0.1596

0.0661

-2.417

.0196

-0.2925

-0.0267

Oct

-0.0991

0.0660

-1.501

.1401

-0.2319

0.0338

Nov

-0.0585

0.0660

-0.887

.3797

-0.1914

0.0743

Time

0.0095

0.0008

11.902

8.68E-16

0.0079

0.0110

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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