Question

Use excel, show excel commands. #8

For problerms 7 througl use the following time serles data:l Th fol PERIOD DEMAND 221 247 228 233 240 152 163 155 167 158 4 5 7 10 7. () Develop a last period forecast for periods 2 through 11. Calculate the MFE, MAD, and MAPE values for periods 2 through 10. Is this a good model? Why? 8. () Develop a three-period weighted moving average forecast for periods 4 through 11. Use weights of 0.4, 0.35, and 0.3, with the most recent observation weighted the highest. Calculate the MFE, MAD, and MAPE values for periods 4 through 10. How do your results compare with those for problem 7?

Answer 1

• Next period Actual = Last period's actual

  Period 2 Forecast = Period 1 Actual

  $MFE = \sum_{i=1}^{n}(Actual_{i}-Forecast_{i})/n$

  $MAD=(1/n)\sum \left | Actual -Forecast \right |$

  $MAPE=\frac{\sum(( \left | Actual-Forecast \right |/Actual)*100)}{n}$

• 
  absolute forecast error absolute forecast error/demand Demand 3-period wt moving avg forecast Forecast error 0.4 0.35 0.25 1 Period 221 247 - 228 233 240 152 163 155 167 158 LV 232.9 234.75 234.55 203.05 178.4 157.05 161.8 160.4 0.1 5.25 82.55 40.05 23.4 9.95 3.8 0.1 5.25 82.55 40.05 23.4 9.95 3.8 0.000429185 0.021875 0.543092105 0.245705521 0.150967742 0.059580838 0.024050633 10 10 14 MFE 15 MAD 16 МАРЕ 19.214 23.586 0.1494
  
  1 Period Demand 3-period wt moving avg forecast Forecast error absolute forecast error absolute forecast error/demand 221 247 228 233 240 152 163 155 167 158 0.4 0.35 0.25 ABS(DS) #ABS(D6) ABS(07 ABS(D8) #ABS(09) SH$2*B4+$H$3 B3+SHS4 B2 B5-CS E5/B5 -E6/B6 -E7/B7 -E8/B8 -E9/B9 #10/B10 -E11/B11 6 5 10 9 11 10 12 11 13 14 MFEAVERAGE(D5:D11) 15 MAD FAVERAGE(ES:E11) 16 MAPE AVERAGE(FS:F11) -$H$2 B10+SHS3 B9+$H$4 B8 B11-C11 ABS(D11) -SH$2 B11+SH$3 *B10+$H$4 B9
  

• 

  Price % Error= | Error I /A ctual * 100% Month lchi Naive forecast Error Error (IErrorl)42 221 247 228 233 240 152 163 155 167 158 221 247 228 233 240 152 163 155 167 26.00 19.00 5.00 7.00 88.00 11.00 8.00 12.00 9.00 26.00 19.00 5.00 7.00 88.00 11.00 8.00 12.00 9.00 676.000 361.000 25.000 49.000 7744.000 121.000 64.000 144.000 81.000 10.5% 8.3% 2.1% 2.9% 57.9% 6.7% 5.2% 7.2% 5.7% MFE 1029.44 AVERAGE(F2:F13 AVERAGE(G2:G13) AVERAGE(H5:H13) MAD 20.56 MAPE 11.8% with MAD at 20.56 and MAPE at 11.8%, the forecast deviations are too huge, especially because of the sudden dip in demand in Period 6. Hence it doesn't behave as a good model