In Excel, create forecasts for periods 6-13 using each of the following methods:
ID: 3063431 • Letter: I
Question
In Excel, create forecasts for periods 6-13 using each of the following methods: 5 period simple moving average; 4 period weighted moving average (0.63, 0.26, 0.08, 0.03); exponential smoothing (alpha = 0.23 and the forecast for period 5 = 53); linear regression with the equation based on all 12 periods; and quadratic regression with the equation based on all 12 periods. Round all numerical answers to two decimal places.
The actual values for 12 periods (shown in order) are:
(1) 45 (2) 52 (3) 48 (4) 59 (5) 55 (6) 57 (7) 64 (8) 58 (9) 68 (10) 66 (11) 72 (12) 75
Question 1: Using a 5 period simple moving average, the forecast for period 13 will be:
Question 2: Using the 4 period weighted moving average, the forecast for period 13 will be:
Question 3: With exponential smoothing, the forecast for period 13 will be:
Question 4: With linear regression, the forecast for period 13 will be:
Question 5: With quadratic regression, the forecast for period 13 will be:
Question 6: Considering only the forecasts for period 6-12, what is the lowest MAD value for any of the mods?
Explanation / Answer
Lowest MAD is for Trend
Period Data 5-SMA 4-WMA ES Trend Abs_Error_SMA Abs_Error_WMA Abs_Error_ES Abs_Error_Trend 1 45 46.51282051 2 52 48.94988345 3 48 51.38694639 4 59 53.82400932 5 55 55.16 53 56.26107226 6 57 51.8 55.39 53.46 58.6981352 5.2 1.61 3.54 1.698135198 7 64 54.2 56.37 54.2742 61.13519814 9.8 7.63 9.7258 2.864801865 8 58 56.6 61.31 56.511134 63.57226107 1.4 3.31 1.488866 5.572261072 9 68 58.6 59.39 56.85357318 66.00932401 9.4 8.61 11.14642682 1.990675991 10 66 60.4 64.75 59.41725135 68.44638695 5.6 1.25 6.582748651 2.446386946 11 72 62.6 65.82 60.93128354 70.88344988 9.4 6.18 11.06871646 1.116550117 12 75 65.6 69.7 63.47708832 73.32051282 9.4 5.3 11.52291168 1.679487179 13 67.8 73.29 66.12735801 75.75757576 MAD MAD MAD MAD 7.171428571 4.841428571 7.86792423 2.481185481 SUMMARY OUTPUT Regression Statistics Multiple R 0.94210152 R Square 0.887555273 Adjusted R Square 0.876310801 Standard Error 3.280247446 Observations 12 ANOVA df SS MS F Significance F Regression 1 849.3164336 849.3164336 78.93258305 4.6474E-06 Residual 10 107.6002331 10.76002331 Total 11 956.9166667 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 44.07575758 2.018852745 21.83208146 9.09525E-10 39.57747334 48.57404181 39.57747334 48.57404181 Period 2.437062937 0.274308072 8.884401108 4.6474E-06 1.825866465 3.048259409 1.825866465 3.048259409