Question

Your manager is trying to determine what forecasting method to use. Based upon the following historical data, calculate the following forecast and specify what procedure you would utilize. - PLEASE show all work in Excel including formulas.

a. Calculate the simple three-month moving average forecast for periods 4-12.

b. Calculate the weighted three-month moving average using weights of 0.50, 0.30, and 0.20 for periods 4-12.

c. Calculate the single exponential smoothing forecast for periods 2-12 using an initial forecast (F1) of 61 and an "a" of 0.30.

e. Calculate the mean absolute deviation (MAD) for the forecasts made by each technique in periods 4-12. Which forecasting method do you prefer?

Month 1 - Actual Demand 62

Month 2 - " " " 65

Month 3 - 67

Month 4 - 68

Month 5 - 71

Month 6 - 73

Month 7 - 76

Month 8 - 78

Month 9 - 78

Month 10 - 80

Month 11 - 84

Month 12 - 85

Answer 1

Following formula have been adopted while calculating :

Three month moving average ,

Ft = ( At-1 + At-2 + At-3) /3, Ft = forecast for period t, At-1, At-2 , At-3 = Actual data for period t-1,t-2, t-3 respectively

Weighted three month moving average:

Ft = 0.5 xAt-1 + 0.3xAt-2 + 0.2 x At-3, Ft = forecast for period t, At-1.At-2, At-3 = Actual data for period t-1,t-2 ,t-3 respectively

Formula for exponential smoothing will be :

Ft = alpha x At-1 + ( 1- alpha) x Ft-1

Ft = Forecast for period t

Ft-1 = Forecast for period t-1

At-1 = Actual for period t-1

Alpha = exponential smoothing constant =0.3

Formula for Absolute deviation = Absolute difference between actual value and forecasted value

Refer below table for relevant calculations as per above :

Month	Actual demand	Three month moving average	Absolute deviation	Weighted 3 month moving average	Absolute deviation	Exponential smoothing forecast	Absolute deviation
1	62					61.00	1
2	65					61.30	3.70
3	67					62.41	4.59
4	68	64.67	3.33	65.4	2.6	63.79	4.21
5	71	66.67	4.33	67.1	3.9	65.05	5.95
6	73	68.67	4.33	69.3	3.7	66.84	6.16
7	76	70.67	5.33	71.4	4.6	68.68	7.32
8	78	73.33	4.67	74.1	3.9	70.88	7.12
9	78	75.67	2.33	76.4	1.6	73.02	4.98
10	80	77.33	2.67	77.6	2.4	74.51	5.49
11	84	78.67	5.33	79	5	76.16	7.84
12	85	80.67	4.33	81.6	3.4	78.51	6.49
		Sum =	36.67		31.1		64.86
		MAD	4.07		3.46		5.40

MAD ( Mean absolute deviation) = Sum of absolute deviations / corresponding number of data

Corresponding number of data in first case ( 4th month till 12th month) = 9

Corresponding number of data in 2nd case ( 4th month till 12th month) = 9

Corresponding number of data in 3rd case = ( 1st month till 12th month) = 12

Accorrdingly,

MAD for 3 month moving average = 4.07

MAD for weighted 3 month moving average = 3.46

MAD for exponential smoothing = 5.40

The forecasting method with least MAD indicates least amount of forecasting error between forecast value vs actual value. The least value of MAD in this case is 3.46 which means forecasting with weighted 3 month moving average is the most accurate and therefore preferable one.