Question

A manager has been using a certain technique to forecast demand for project management software at her store. Actual demand and her corresponding predictions are shown below:

Month	Actual Demand	Manager's Forecast
March	45	45
April	42	50
May	34	45
June	48	40
July	38	45

 

a. What was the manager's forecast error for each month?

b. What is the mean error (ME), the mean squared error (MSE), the mean absolute deviation (MAD), and the tracking signal for these five months of forecasting?

c. If the manager had used a 3-month moving average instead of her technique, what would have been her forecast for June and July?

d. If the manager had used simple exponential smoothing with a = 0.2 instead of her technique, what would the forecast for August be, assuming that simple exponential smoothing had produced a perfectly accurate forecast in March?

Answer 1

Extract the data as shown below: Manager's forecast Actual demand 45 Month March April May June July 48 Formulate MS excel spreadsheet as shown below: wN A Month March April May June July B Actual demand Manager's forecast Error 45 45 I=B2-C2 42 50 =B3-C3 34 45 =B4-C4 48 40 =B5-C5 38 45 B6-C6 4 5 6 The output is shown below: Actual demand Manager's forecast 1 2 Error 0 45 А Month March April May June July 42 -8 4 5 6 34 48 38 43 40 45 -11 8 -7 Hence, the manager's forecast error for each month is calculated above. Formulate MS excel spreadsheet as shown below:

A D B C Actual Manager demands forecast Tracking Signal 1 Month Error Absolute Error Squared error 2 March 45 45 =B2-C2 =ABS(B2-C2) =E242 =D2/SES7 3 April 42 50 B3-C3 ABS(B3-C3) E3^2 -D3/SE$7 4 May 34 45 B4-C4 =ABS(B4-C4) =E42 =D4/SES7 5 6 June July 48 38 40 45 =D5/SES7 =D6/SES7 =B5-C5 =ABS(B5-C5) =E542 =B6-C6 =ABS(B6-C6) =E642 =AVERAGE(D2-D6) =AVERAGE(E2:E6) =AVERAGE(F2:F6) MAD MSE The output is shown below: D A B C Actual Manager 1 Month demands forecast Absolute Squared Trackin Error error signal Error 2 March 45 450 . 00 3 April 42 4 May 8 18 64 5 6 June July 48 38 40 45 -3.6 ME 6.8 MAD 49 59.6 MSE Hence, ME = -3.6 MAD= 6.8 MSE = 59.6 Formulate MS excel spreadsheet as shown below: 10 Manager's forecast 11 12 Month March April May June July Actual demand 45 142 34 48 38 13 AVERAGE(B11:313) =AVERAGE(B12:B14) 15

The output is shown below: 10 Month 11 March April 13 May 14 June 15 July Actual demand Manager's forecast 45 42 34 1 48 40.3 38 Hence, the forecast for June and July using 3-month moving average is 40.3 and 41.3 respectively.

Formulate MS excel spreadsheet as shown below: Month March April May June July August Actual demand Manager's forecast 45 45 142 I=C20-(SC$27*(B20-C20) 34 =C21+($C$27*(B21-C21)) 48 =C22+(SCS27*(B22-C22) 38 =C23+($C$270(B23-C23)) =C24+($C$27" (B24-C24)) 24 25 a 0.2 The output is provided below: А Month March April May June July August Actual demand Manager's forecast 45 45.0 42 45.0 34 44.4 48 1 42.3 38 43.5 42.4 Da 0.2 Hence, the forecast for August using exponential smoothing is 42.4