The gure on the following page shows an Excel spreadsheet that calculates a two- period weighted moving

The figure on the following page shows an Excel spreadsheet that calculates a two- period weighted moving average and exponential smoothing model for a set of demand numbers, as well as the resulting MFE and MAD values. Re-create this spreadsheet in Excel. While your formatting does not have to be exactly the same, your answers should be.
Your spreadsheet should recalculate results whenever any changes are made to the shaded cells. If your logic is correct, changing the initial forecast for the exponential smoothing model to 50 will result in new MFE and MAD values of 0.76 and 5.62, respectively. Similarly, changing both weights for the two-period model to 0.5 should result in new MFE and MAD values of -0.077 and 7.692, respectively.

Transcribed Image Text:

D. F G H. A 1 Comparing a Two-Period Moving Average and an Exponential Smoothing Model 2 Two-period moving average model Weight on Period t-2: Weight on Period t-1: Exponential smoothing model Initial forecast: Alpha (a): 3 4 0.35 65 0.65 0.3 6. Absolute Absolute Deviation 5.00 Forecast Forecast 7 Period Demand Forecast Forecast 65.00 Error Deviation Error 8 1 60 -5.00 9 2 53 63,50 -10.50 10.50 10 3 65 55.45 9.55 9.55 60.35 4.65 4.65 11 72 60.8 11.2 11.2 61.75 10.26 10.26 12 5. 72 66.55 2.45 2.45 64.82 7.18 7.18 13 74 72 2 66.98 7.02 7.02 14 50 73.3 -23.3 23.3 69.08 -19.08 19.08 15 8 60 58.4 1.6 1.6 63.36 -3.36 3.36 16 9 72 56.5 15.5 15.5 62.35 9.65 9.65 17 10 53 67.8 -14.8 14.8 65.25 -12.25 12.25 18 11 56 59.65 -3.65 3.65 61.57 -5.57 5.57 19 12 51 54.95 -3.95 3.95 59.90 --8.90 8.90 20 13 51 52.75 -1.75 1.75 57.23 -6.23 6.23 21 14 54 51 3 55.36 -1.36 1.36 22 15 55 52.95 2.05 2.05 54.95 0.05 0.05 23 MFE = --0.008 MFE = -1.380 24 MAD = 7.292 MAD = 7.350