Distance Jan Feb Mar Apr May June July Aug Sep Oct Nov Time 4.22 1 0 0 0 0 0 0 0
ID: 3263271 • Letter: D
Question
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 The worksheet “Data 1" 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) 1. 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 2. 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-time period. See the worksheet "Data2". Run Regression in Data Analysis of Excel to find the estimated regression equation: Clearly show this equation. 3. 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? Note. To better prepare for the assignment, study pages 409 - 410 in the textbook, and pages 38 -41,42 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.Explanation / Answer
2)
y =
3)12 month of year 6 .
t = 72 ,all dummy variable will be 0
hence
y =4.57575+ 0.009451389*72 =5.25625
SUMMARY OUTPUT Regression Statistics Multiple R 0.969064688 R Square 0.939086369 Adjusted R Square 0.923533953 Standard Error 0.104384637 Observations 60 ANOVA df SS MS F Significance F Regression 12 7.895180833 0.657931736 60.38202358 2.21E-24 Residual 47 0.512119167 0.010896152 Total 59 8.4073 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 4.57575 0.054739766 83.59096817 8.89956E-53 4.465628 4.685872 4.465628 4.685872204 Jan -0.43803472 0.066593988 -6.577691668 3.57753E-08 -0.572 -0.30406 -0.572 -0.304064898 Feb 0.680513889 0.066494492 10.23413927 1.50936E-13 0.546744 0.814284 0.546744 0.814283552 Mar 0.5850625 0.066404343 8.810606018 1.61658E-11 0.451474 0.718651 0.451474 0.718650806 Apr 0.483611111 0.066323579 7.291692026 2.95587E-09 0.350185 0.617037 0.350185 0.617036942 May 0.152159722 0.066252235 2.296673043 0.026140045 0.018877 0.285442 0.018877 0.285442028 June -0.21329167 0.066190342 -3.222398643 0.00231125 -0.34645 -0.08013 -0.34645 -0.080133875 July -0.14874306 0.066137925 -2.248982802 0.029239658 -0.2818 -0.01569 -0.2818 -0.015690713 Aug -0.17219444 0.066095008 -2.605256446 0.012255352 -0.30516 -0.03923 -0.30516 -0.03922844 Sep -0.15964583 0.066061608 -2.416620452 0.019601345 -0.29254 -0.02675 -0.29254 -0.02674702 Oct -0.09909722 0.066037741 -1.50061496 0.140143636 -0.23195 0.033754 -0.23195 0.033753577 Nov -0.05854861 0.066023417 -0.886785538 0.379709655 -0.19137 0.074273 -0.19137 0.074273371 Time 0.009451389 0.000794081 11.90230064 8.67895E-16 0.007854 0.011049 0.007854 0.011048873