22. (***) (Microsoft Excel problem). The following figure 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. (Hint: Format the cells for the exponential smoothing model to show only two decimal places. Otherwise, the number of decimal places that shows will increase with each new forecast.)

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:

A B E F G H 1 Comparing a Two-Period Moving Average and an Exponential Smoothing Model Two-period moving average model 2 3 4 Weight on Period t-2: 0.35. 5 Weight on Period t-1: 0.65 6 Exponential smoothing model Initial forecast: Alpha (a): 65 0.3 Forecast Absolute Forecast Absolute 7 Period Demand Forecast Error Deviation Forecast Error Deviation 8 1 60 65.00 -5.00 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 4 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 6 74 72 2 2 66.98 7.02 7.02 14 7 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 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