Forecasting ⚫ CHAPTER 8 ⚫
8-21
17. Utility company
Quarter
Year 1
Year 2
Year 3
Year 4
1
103.5
94.7
118.6
109.3
2
126.1
116.0
141.2
131.6
3
144.5
137.1
159.0
149.5
4
166.1
152.5
178.2
169.0
Totals
540.2
500.3
597.0
559.4
Averages
135.05
125.075
149.25
139.85
Quarter
Year 1
Year 2
Year 3
Year 4
Average
Seasonal Index
1
0.7664
0.7571
0.7946
0.7816
0.7749
2
0.9337
0.9274
0.9410
0.9410
0.9371
3
1.0700
1.0961
1.0653
1.0690
1.0751
4
1.2299
1.2193
1.1940
1.2084
1.2129
Totals
4.0
4.0
4.0
4.0
4.0
Forecast for Year 5
Quarter
Average Demand
Adjusted
per Quarter
Demand
1
150
116.235
=
116
2
150
140.565
=
141
3
150
161.265
=
161
4
150
181.935
=
182
600
600
Turning to the Seasonal Forecasting Solver of OM Explorer, we get the same results:
PART 2 Managing Customer Demand
8-22
18. Franklin Tooling
a. Naïve (1-Period Moving Average) Forecasting results
Demand for
Component
135.AG
1-Period Moving Average (Naïve) Forecast
Forecast
Error
ABS
(Error)
Square
(Error)
Percent
(Error)
137
136
137.00
143
136.00
136
143.00
141
136.00
5.00
5.00
25.00
3.55
128
141.00
-13.00
13.00
169.00
10.16
149
128.00
21.00
21.00
441.00
14.09
136
149.00
-13.00
13.00
169.00
9.56
134
136.00
-2.00
2.00
4.00
1.49
142
134.00
8.00
8.00
64.00
5.63
125
142.00
-17.00
17.00
289.00
13.60
134
125.00
9.00
9.00
81.00
6.72
118
134.00
-16.00
16.00
256.00
13.56
131
118.00
13.00
13.00
169.00
9.92
132
131.00
1.00
1.00
1.00
0.76
124
132.00
-8.00
8.00
64.00
6.45
121
124.00
-3.00
3.00
9.00
2.48
127
121.00
6.00
6.00
36.00
4.72
118
127.00
-9.00
9.00
81.00
7.63
120
118.00
2.00
2.00
4.00
1.67
115
120.00
-5.00
5.00
25.00
4.35
106
115.00
-9.00
9.00
81.00
8.49
120
106.00
14.00
14.00
196.00
11.67
113
120.00
-7.00
7.00
49.00
6.19
121
113.00
8.00
8.00
64.00
6.61
119
121.00
-2.00
2.00
4.00
1.68
Forecast
119.00
CFE
-17.00
MAD
8.68
MSE
103.68
MAPE
6.86
Forecasting ⚫ CHAPTER 8 ⚫
8-23
b. 3-Period Moving Average
Demand for
Component
135.AG
3-Period Moving Average Forecast
Forecast
Error
ABS
(Error)
Square
(Error)
Percent
(Error)
137
136
143
136
138.67
141
138.33
2.67
2.67
7.11
1.89
128
140.00
-12.00
12.00
144.00
9.38
149
135.00
14.00
14.00
196.00
9.40
136
139.33
-3.33
3.33
11.11
2.45
134
137.67
-3.67
3.67
13.44
2.74
142
139.67
2.33
2.33
5.44
1.64
125
137.33
-12.33
12.33
152.11
9.87
134
133.67
0.33
0.33
0.11
0.25
118
133.67
-15.67
15.67
245.44
13.28
131
125.67
5.33
5.33
28.44
4.07
132
127.67
4.33
4.33
18.78
3.28
124
127.00
-3.00
3.00
9.00
2.42
121
129.00
-8.00
8.00
64.00
6.61
127
125.67
1.33
1.33
1.78
1.05
118
124.00
-6.00
6.00
36.00
5.08
120
122.00
-2.00
2.00
4.00
1.67
115
121.67
-6.67
6.67
44.44
5.80
106
117.67
-11.67
11.67
136.11
11.01
120
113.67
6.33
6.33
40.11
5.28
113
113.67
-0.67
0.67
0.44
0.59
121
113.00
8.00
8.00
64.00
6.61
119
118.00
1.00
1.00
1.00
0.84
Forecast
117.67
CFE
-39.33
MAD
5.94
MSE
55.59
MAPE
4.78
PART 2 Managing Customer Demand
8-24
c. Exponential Smoothing, with =.28
Demand for
Component
135.AG
Exponential Smoothing Forecast
Forecast
Error
ABS
(Error)
Square
(Error)
Percent
(Error)
137
137.00
136
137.00
143
136.72
136
138.48
141
137.78
3.22
3.22
10.34
2.28
128
138.68
-10.68
10.68
114.17
8.35
149
135.69
13.31
13.31
177.07
8.93
136
139.42
-3.42
3.42
11.69
2.51
134
138.46
-4.46
4.46
19.91
3.33
142
137.21
4.79
4.79
22.92
3.37
125
138.55
-13.55
13.55
183.68
10.84
134
134.76
-0.76
0.76
0.57
0.57
118
134.55
-16.55
16.55
273.76
14.02
131
129.91
1.09
1.09
1.18
0.83
132
130.22
1.78
1.78
3.18
1.35
124
130.72
-6.72
6.72
45.11
5.42
121
128.84
-7.84
7.84
61.40
6.48
127
126.64
0.36
0.36
0.13
0.28
118
126.74
-8.74
8.74
76.42
7.41
120
124.29
-4.29
4.29
18.44
3.58
115
123.09
-8.09
8.09
65.48
7.04
106
120.83
-14.83
14.83
219.82
13.99
120
116.67
3.33
3.33
11.06
2.77
113
117.61
-4.61
4.61
21.21
4.08
121
116.32
4.68
4.68
21.94
3.87
119
117.63
1.37
1.37
1.88
1.15
Forecast
118.01
CFE
-70.62
MAD
6.29
MSE
61.88
MAPE
5.11
Forecasting ⚫ CHAPTER 8 ⚫
8-25
d. Trend Projection with Regression: model Y=143.1613-1.1289X obtained from the Time Series Forecasting
Solver of OM Explorer for Trend Projection with Regression
Demand for
Component
135.AG
Trend Projection with Regression
Forecast
Error
ABS
(Error)
Square
(Error)
Percent
(Error)
137
142.03
136
140.90
143
139.77
136
138.64
141
137.52
3.48
3.48
12.12
2.47
128
136.39
-8.39
8.39
70.39
6.55
149
135.26
13.74
13.74
188.76
9.22
136
134.13
1.87
1.87
3.49
1.37
134
133.00
1.00
0.99
1.00
0.74
142
131.87
10.13
10.13
102.53
7.13
125
130.74
-5.74
5.74
32.97
4.59
134
129.62
4.38
4.38
19.22
3.27
118
128.49
–10.49
10.49
109.92
8.89
131
127.36
3.64
3.64
13.26
2.78
132
126.23
5.77
5.77
33.30
4.37
124
125.10
-1.10
1.10
1.21
0.89
121
123.97
-2.97
2.97
8.83
2.45
127
122.84
4.16
4.16
17.28
3.27
118
121.71
-3.71
3.71
13.77
3.14
120
120.59
–0.59
0.59
0.34
0.49
115
119.46
–4.46
4.46
19.86
3.88
106
118.33
–12.33
12.33
151.97
11.63
120
117.20
2.80
2.80
7.85
2.33
113
116.07
-3.07
3.07
9.40
2.72
121
114.94
6.06
6.06
36.71
5.01
119
113.81
5.19
5.19
26.91
4.36
Forecast
112.68
CFE
9.36
MAD
5.23
MSE
40.06
MAPE
4.16
PART 2 Managing Customer Demand
8-26
These results are confirmed by the output from the Time Series Forecasting Solver of OM
Explorer:
Forecasting ⚫ CHAPTER 8 ⚫
8-27
(e). As seen in the summary table, the Trend Projection with Regression method provides superior
results across all performance criteria.The reason that it does not have a CFE of 0.0 is that the
regression begins with period 1, but the error analysis begins in period 5.
CFE
MAD
MSE
MAPE
1-Period Moving Average (Naïve) Forecast
-17.00
8.68
103.68
6.86%
3-Period Moving Average Forecast
-39.33
5.94
55.59
4.78%
Exponential Smoothing Forecast
-70.62
6.29
61.88
5.11%
Trend Projection with Regression
9.36
5.23
40.06
4.16%
PART 2 Managing Customer Demand
8-28
19. Combination Forecast for Problem 18
In ranked order, the best methods for Franklin Tool, based on MAD, are Trend Projection
with Regression (MAD=5.23), 3-period Moving Average (MAD=5.94), Exponential
Smoothing (MAD=6.29), and Naïve (MAD=8.68).
Combination Forecast giving equal weight to all four methods:
Forecasts
Demand
Trend
MA
EA
Naive
Comb
of all 4
Error
Absolute
Error
Demand
Absolute
Percent
Error
137
142.03
136
140.91
137.00
143
139.78
136.00
136
138.65
138.67
143.00
141
137.52
138.33
137.78
136.00
137.41
3.59
3.59
12.89
2.55
128
136.39
140.00
138.68
141.00
139.02
-11.02
11.02
121.41
8.61
149
135.26
135.00
135.69
128.00
133.49
15.51
15.51
240.61
10.41
136
134.13
139.33
139.42
149.00
140.47
-4.47
4.47
19.99
3.29
134
133.00
137.67
138.46
136.00
136.28
-2.28
2.28
5.21
1.70
142
131.87
139.67
137.21
134.00
135.69
6.31
6.31
39.84
4.44
125
130.75
137.33
138.55
142.00
137.16
-12.16
12.16
147.81
9.73
134
129.62
133.67
134.76
125.00
130.76
3.24
3.24
10.50
2.42
118
128.49
133.67
134.55
134.00
132.67
-14.67
14.67
215.35
12.44
131
127.36
125.67
129.91
118.00
125.23
5.77
5.77
33.24
4.40
132
126.23
127.67
130.22
131.00
128.78
3.22
3.22
10.38
2.44
124
125.10
127.00
130.72
132.00
128.70
-4.70
4.70
22.13
3.79
121
123.97
129.00
128.84
124.00
126.45
-5.45
5.45
29.72
4.51
127
122.84
125.67
126.64
121.00
124.04
2.96
2.96
8.77
2.33
118
121.71
124.00
126.74
127.00
124.86
-6.86
6.86
47.11
5.82
120
120.59
122.00
124.29
118.00
121.22
-1.22
1.22
1.49
1.02
115
119.46
121.67
123.09
120.00
121.05
-6.05
6.05
36.65
5.26
106
118.33
117.67
120.83
115.00
117.96
-11.96
11.96
142.92
11.28
120
117.20
113.67
116.67
106.00
113.38
6.62
6.62
43.76
5.51
113
116.07
113.67
117.61
120.00
116.84
-3.84
3.84
14.71
3.39
121
114.94
113.00
116.32
113.00
114.31
6.69
6.69
44.70
5.53
119
113.81
118.00
117.63
121.00
117.61
1.39
1.39
1.93
1.17
Forecast
115.35
CFE
–
29.40
MAD
6.36
MSE
56.87
MAPE
5.09
Forecasting ⚫ CHAPTER 8 ⚫
8-29
These results are confirmed by the output from the Time Series Forecasting Solver of OM
Explorer:
PART 2 Managing Customer Demand
8-30
Combination Forecast giving equal weight to the best three methods (Trend Projection with
Regression, 3-period Moving Average, and Exponential Smoothing):
Forecasts
Demand
Trend
MA
EA
Comb of
best 3
Error
Absolute
Error
Squared
Error
Absolute
Percent
Error
137
142.03
137.00
136
140.91
137.00
143
139.78
136.72
136
138.65
138.67
138.48
138.60
141
137.52
138.33
137.78
137.88
3.12
3.12
9.74
2.21
128
136.39
140.00
138.68
138.36
-10.36
10.36
107.29
8.09
149
135.26
135.00
135.69
135.32
13.68
13.68
187.20
9.18
136
134.13
139.33
139.42
137.63
-1.63
1.63
2.65
1.20
134
133.00
137.67
138.46
136.38
-2.38
2.38
5.65
1.77
142
131.87
139.67
137.21
136.25
5.75
5.75
33.05
4.05
125
130.75
137.33
138.55
135.54
-10.54
10.54
111.17
8.44
134
129.62
133.67
134.76
132.68
1.32
1.32
1.74
0.98
118
128.49
133.67
134.55
132.23
-14.23
14.23
202.59
12.06
131
127.36
125.67
129.91
127.65
3.35
3.35
11.25
2.56
132
126.23
127.67
130.22
128.04
3.96
3.96
15.70
3.00
124
125.10
127.00
130.72
127.61
-3.61
3.61
13.00
2.91
121
123.97
129.00
128.84
127.27
-6.27
6.27
39.30
5.18
127
122.84
125.67
126.64
125.05
1.95
1.95
3.80
1.54
118
121.71
124.00
126.74
124.15
-6.15
6.15
37.85
5.21
120
120.59
122.00
124.29
122.29
-2.29
2.29
5.26
1.91
115
119.46
121.67
123.09
121.40
-6.40
6.40
41.02
5.57
106
118.33
117.67
120.83
118.94
-12.94
12.94
167.44
12.21
120
117.20
113.67
116.67
115.85
4.15
4.15
17.25
3.46
113
116.07
113.67
117.61
115.78
-2.78
2.78
7.73
2.46
121
114.94
113.00
116.32
114.75
6.25
6.25
39.03
5.16
119
113.81
118.00
117.63
116.48
2.52
2.52
6.35
2.12
Forecast
116.12
CFE
-33.53
MAD
5.71
MSE
48.46
MAPE
4.60
Forecasting ⚫ CHAPTER 8 ⚫
8-31
These results are confirmed by the output from the Time Series Forecasting Solver of OM
Explorer:
PART 2 Managing Customer Demand
8-32
Combination Forecast giving equal weight to the best two methods (Trend Projection with
Regression and 3-period Moving Average):
Forecasts
Demand
Trend
MA
Comb of
best 2
Error
Absolute
Error
Squared
Error
Absolute
Percent
Error
137
142.03
136
140.91
143
139.78
136
138.65
138.67
138.66
141
137.52
138.33
137.93
3.07
3.07
9.45
2.18
128
136.39
140.00
138.19
-10.19
10.19
103.93
7.96
149
135.26
135.00
135.13
13.87
13.87
192.37
9.31
136
134.13
139.33
136.73
-0.73
0.73
0.54
0.54
134
133.00
137.67
135.33
-1.33
1.33
1.78
1.00
142
131.87
139.67
135.77
6.23
6.23
38.81
4.39
125
130.75
137.33
134.04
-9.04
9.04
81.71
7.23
134
129.62
133.67
131.64
2.36
2.36
5.56
1.76
118
128.49
133.67
131.08
-13.08
13.08
171.01
11.08
131
127.36
125.67
126.51
4.49
4.49
20.14
3.43
132
126.23
127.67
126.95
5.05
5.05
25.52
3.83
124
125.10
127.00
126.05
-2.05
2.05
4.20
1.65
121
123.97
129.00
126.49
-5.49
5.49
30.10
4.53
127
122.84
125.67
124.25
2.75
2.75
7.54
2.16
118
121.71
124.00
122.86
-4.86
4.86
23.59
4.12
120
120.59
122.00
121.29
-1.29
1.29
1.67
1.08
115
119.46
121.67
120.56
-5.56
5.56
30.93
4.84
106
118.33
117.67
118.00
-12.00
12.00
143.93
11.32
120
117.20
113.67
115.43
4.57
4.57
20.86
3.81
113
116.07
113.67
114.87
-1.87
1.87
3.49
1.65
121
114.94
113.00
113.97
7.03
7.03
49.42
5.81
119
113.81
118.00
115.91
3.09
3.09
9.57
2.60
Forecast
115.17
CFE
-14.98
MAD
5.45
MSE
44.37
MAPE
4.38
Forecasting ⚫ CHAPTER 8 ⚫
8-33
These results are confirmed by the output from the Time Series Forecasting Solver of OM
Explorer:
Of the three Combination Methods, the “best-two” forecast provides superior results. These
forecasts are better than the Naïve, Moving Average and Exponential Smoothing methods.
PART 2 Managing Customer Demand
8-34
20. Large Public Library
Using the Time Series Forecasting Solver of OM Explorer, we get the following results by
varying the number of periods (n) in the Simple Moving Average method:
n
Forecast for
January, Year 4
MAD
CFE
1
2,451
282
604
2
2,299
267
–68
3
2,221
257
–291
4
2,127
242
–524
5
2,037
242
–846
10
2,189
216
–445
In general, as n increases, MAD decreases and CFE (bias) increases. The 10-month average
seems to be a good combination of relatively low MAD and low CFE.
21. Large Public Library (continued, using 1,847 as initial average)
Using the Time Series Forecasting Solver, we get the following results by varying in the
exponential smoothing model:
Forecast for
January, Year 4
MAD
CFE
0.10
2,193
265
3,458
0.20
2,178
256
1,654
0.30
2,186
256
1,131
0.50
2,259
258
823
0.65
2,325
255
736
0.70
2,346
256
713
0.80
2,385
262
673
1.00
2,451
282
604
Forecasting ⚫ CHAPTER 8 ⚫
8-35
22. Large Public Library (continued)
Using the Time Series Forecasting Solver of OM Explorer, we get the following results
PART 2 Managing Customer Demand
8-36
23. Cannister Inc.
a. Multiplicative Seasonal Method
Year
1
2
3
4
5
Jan
742
741
896
951
1,030
Feb
697
700
793
861
1,032
Mar
776
774
885
938
1,126
Apr
898
932
1,055
1,109
1,285
May
1,030
1,099
1,204
1,274
1,468
Jun
1,107
1,223
1,326
1,422
1,637
Jul
1,165
1,290
1,303
1,486
1,611
Aug
1,216
1,349
1,436
1,555
1,608
Sep
1,208
1,341
1,473
1,604
1,528
Oct
1,131
1,296
1,453
1,600
1,420
Nov
971
1,066
1,170
1,403
1,119
Dec
783
901
1,023
1,209
1,013
Total
11,724
12,712
14,017
15,412
15,877
Averages
977
1,059.333
1,168.083
1,284.333
1,323.083
Year
1
2
3
4
5
Average
Seasonal Index
Jan
0.759
0.699
0.767
0.740
0.778
0.749
Feb
0.713
0.661
0.679
0.670
0.780
0.701
Mar
0.794
0.731
0.758
0.730
0.851
0.773
Apr
0.919
0.880
0.903
0.863
0.971
0.907
May
1.054
1.037
1.031
0.992
1.110
1.045
Jun
1.133
1.154
1.135
1.107
1.237
1.153
Jul
1.192
1.218
1.116
1.157
1.218
1.180
Aug
1.245
1.273
1.229
1.211
1.215
1.235
Sep
1.236
1.266
1.261
1.249
1.155
1.233
Oct
1.158
1.223
1.244
1.246
1.073
1.189
Nov
0.994
1.006
1.002
1.092
0.846
0.988
Dec
0.801
0.851
0.876
0.941
0.766
0.847
b. Simple Linear Regression Model to forecast annual sales
10,646.6 1,100.6YX=+
obtained from the Time Series Forecasting Solver of OM Explorer for Trend
Projection with Regression
d. Monthly Seasonal Forecast for Year 6
(17,250/12)*Average Seasonal Index
Jan
1,076.7
Jul
1,696.4
Feb
1,007.3
Aug
1,774.9
Mar
1,110.9
Sep
1,773.1
Apr
1,304.4
Oct
1,708.9
May
1,501.9
Nov
1,420.2
Jun
1,658.1
Dec
1,217.5
Forecasting ⚫ CHAPTER 8 ⚫
8-37
24. Midwest Computer Company
a. The results of the Trend Projection with Regression Solver of OM Explorer are
PART 2 Managing Customer Demand
8-38
b. The linear regression model is
The statistical results and graph are as follows:
c. While both methods provide extremely high coefficients of determination, the Trend
Forecasting ⚫ CHAPTER 8 ⚫
8-39
25. P&Q Supermarkets
Numerous methods could be used.
Moving Average
Moving Average
Number of Periods
MAD
CFE
Forecast
2
7.09
5.00
39.50
3
6.87
13.67
41.33
4
6.21
12.75
41.50
5
4.52
1.00
44.80
6
4.80
7.00
44.17
7
4.94
7.43
43.43
12
5.15
3.08
43.08
Exponential smoothing
Use
=020.
for the lowest MAD.
MAD
CFE
Forecast
=020.
5.34
47.62
42.53
=100.
7.52
5.00
38
Use
= 1.00 (equivalent of naïve model) for the lowest bias.
Trend Projction with Regression
PART 2 Managing Customer Demand
8-40
Combination
The Combination method gives slightly better forecasts. However, the graphic plot of the
data reveals a spike every fifth period in a cycle. None of the methods tried so far reasonably
accounts for the fifth-period spike. One way to deal with this cyclical data is to use the
Seasonal
Seasonal
Seasonal
Seasonal
Average
Period
Cycle 1
Index
Cycle 2
Index
Cycle 3
Index
Cycle 4
Index
Index
1
33
0.8508
38
0.8837
43
0.9862
41
0.9535
0.9186
2
37
0.9536
42
0.9767
39
0.8945
36
0.8372
0.9155
3
31
0.7990
40
0.9302
37
0.8486
39
0..9070
0.8712
4
39
1.0052
41
0.9535
43
0.9862
41
0.9535
0.9746
5
54
1.3918
54
1.2558
56
1.2844
58
1.3488
1.3202
194
215
218
215