Month Actual Demand 1 62 2 65 3 67 4 68 5 71 6 73 7 76 8 78 9 78 10 80 11 84 12
ID: 415789 • Letter: M
Question
Month Actual Demand 1 62 2 65 3 67 4 68 5 71 6 73 7 76 8 78 9 78 10 80 11 84 12 85 a) Calculate the simple three-moving average forecast for periods 4-12. b) Calculate the weighted three-moving average using the following sets of weights : 1) 0.50, 0.30, and 0.20 for periods 4-12. 2) 0.40, 0.35, and 0.25 for periods 4-12. 3) 0.60, 0.25, and 0.15 for periods 4-12. c) Calculate the single exponential smoothing forecast for periods 2-12 using the following information: 1) Initial forecast (F1) of 61 and an alpha of 0.10. 2) Initial forecast (F1) of 61 and an alpha of 0.20. 3) Initial forecast (F1) of 61 and an alpha of 0.30. d) Calculate the forecast for periods 4-12 using simple linear regression using Excel. e) Calculate the mean absolute deviation (MAD) and Mean square error (MSE) for all the previous forecasts made by each technique for the periods 4-12. Which forecasting method do you recommend to use?
Explanation / Answer
Month
Actual Demand
1
62
2
65
3
67
4
68
5
71
6
73
7
76
8
78
9
78
10
80
11
84
12
85
a.)
Simple three-moving Average
=
(80+84+85) / 3
=
83
b.)
Weighted three-moving average
Part1
Weights (0.5, 0.3, 0.2)
=
(0.5x85) + (0.3x84 + (0.2x80)
=
83.7
Part2
Weights (0.4, 0.35, 0.25)
=
(0.40x85) + (0.35x84) + (0.25x80)
=
83.4
Part3
Weights (0.60, 0.25, 0.15)
=
(0.60x85) + (0.25x84) + (0.15x80)
=
84
c.)
Month
Actual Demand
Forecast for = 0.10
Forecast for = 0.20
Forecast for = 0.30
1
62
61.00
61.00
61.00
2
65
61.10
61.00
61.00
3
67
61.49
61.02
61.00
4
68
62.04
61.11
61.01
5
71
62.64
61.30
61.04
6
73
63.47
61.57
61.12
7
76
64.43
61.95
61.25
8
78
65.58
62.44
61.46
9
78
66.82
63.07
61.76
10
80
67.94
63.82
62.15
11
84
69.15
64.65
62.65
12
85
70.63
65.55
63.25
Ft = Ft-1 + (At-1 + Ft-1), Where
Ft = Forecast Demand for Current Month
Ft-1 = Forecast Demand for Last Month
= Smoothing Constant
At-1 = Actual Demand for Current Month
d.)
Month
Actual
Demand
Forecasted Demand
1
62
62.59
2
65
64.65
3
67
66.71
4
68
68.77
5
71
70.83
6
73
72.89
7
76
74.95
8
78
77.01
9
78
79.06
10
80
81.12
11
84
83.18
12
85
85.24
Regression Equation: Y = 2.0594 X + 60.53
(Obtained from Plotting scatter plot in Excel and adding trend line to the graph in, linear format)
Y =
Forecasted Demand
X =
Month (1,2,3…)
Month
Actual Demand
1
62
2
65
3
67
4
68
5
71
6
73
7
76
8
78
9
78
10
80
11
84
12
85