Table 7.1
Table 7.1 Quarterly Demand for Tahoe Salt Period
Demand
Year, Qtr
t
D t
00,2 00,3 00,4 01,1 01,2 01,3 01,4 02,1 02,2 02,3 02,4 03,1
1 2 3 4 5 6 7 8 9 10 11 12
8,000 13,000 23,000 34,000 10,000 18,000 23,000 38,000 12,000 13,000 32,000 41,000 Figure 7.1 Quarterly Demand at Tahoe Salt
45,000 40,000 35,000 30,000
d n 25,000 a m20,000 e D 15,000
10,000 5,000 0 00,2
00,3
00,4
01,1
01,2
01,3
01,4
Year, Quarter
02,1
02,2
02,3
02,4
03,1
Figure7.2
Figure 7.2 Deseasonalized Demand for Tahoe Salt Period t
Demand D t
1 2 3 4 5 6 7 8 9 10 11 12
8,000 13,000 23,000 34,000 10,000 18,000 23,000 38,000 12,000 13,000 32,000 41,000
Deseasonalized Demand
19,750 20,625 21,250 21,750 22,500 22,125 22,625 24,125
Equation 7.2 Deseasonalizing Demand
where: D = Demand p = periodicity t = period
Figure 7.3 Deseasonalized Demand for Tahoe Salt 45,000 40,000 35,000 d n a m e D
30,000 25,000 20,000 15,000 10,000 5,000 0 0
2
4
6
8
10
Period, t Demand…
Deseasonalized Demand
12
14
Sheet1
REGRESSION SUMMARY OUTPUT Regression Statistics Multiple R 0.958065237 R Square 0.917888998 Adjusted R Sq 0.90420383 Standard Error 414.5033124 Observations 8 ANOVA df Regression Residual Total
1 6 7 Coefficients
Intercept X Variable 1
18,439 52 4
SS MS F Significance F 11523809.52 11523810 67.07182 0.000178609 1030877.976 171813 12554687.5 Standard Error t Stat P-value 440.8087079 41.82991 1.25E-08 63.95924968 8.189738 0.000179
Initial Level, L Trend, T
Lower 95% Upper 95% Lower 95.0% Upper 95.0% 17360.36726 19517.609 17360.3673 19517.6089 367.3067633 680.31228 367.306763 680.312284
Figure7.4
Historical Data Period
t 1 2 3 4 5 6 7 8 9 10 11 12
ed Demand Demand (Eqn 7.4) D t
8,000 13,000 23,000 34,000 10,000 18,000 23,000 38,000 12,000 13,000 32,000 41,000
18,963 19,487 20,011 20,535 21,059 21,583 22,107 22,631 23,155 23,679 24,203 24,727
Forecasted Data Forecasted Period Year, Qtr
03,2 03,3 03,4 04,1
t 13 14 15 16
Demand F t 1
11,868 17,527 30,770 44,794
Season al Factor Estimate (Eqn 7.6) S i
D t (Eqn 7.5) S t
0.42 0.67 1.15 1.66 0.47 0.83 1.04 1.68 0.52 0.55 1.32 1.66 45,000 40,000 35,000 30,000 25,000 20,000
0.47 0.68 1.17 1.67
Forecast
8,913 13,251 23,413 34,293 9,898 14,676 25,865 37,794 10,883 16,102 28,318 41,294
Figure7.5
Tahoe Salt Forecasts Using Four-Period Moving Average Period Demand t Dt 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
8,000 13,000 23,000 34,000 10,000 18,000 23,000 38,000 12,000 13,000 32,000 41,000
Level Lt
19,500 20,000 21,250 21,250 22,250 22,750 21,500 23,750 24,500
Forecast Ft
Error Et
Absolute Error At
Mean Squared Error MSEt
19,500 20,000 21,250 21,250 22,250 22,750 21,500 23,750 24,500 24,500 24,500 24,500
9,500 2,000 -1,750 -16,750 10,250 9,750 -10,500 -17,250
9,500 2,000 1,750 16,750 10,250 9,750 10,500 17,250
90,250,000 47,125,000 32,437,500 94,468,750 96,587,500 96,333,333 98,321,429 123,226,563
45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000 0
MADt % Error MAPE t
9,500 5,750 4,417 7,500 8,050 8,333 8,643 9,719
Demand Dt Forecast Ft
0
5
10
15
95 11 8 44 85 75 33 42
95 53 38 39 49 53 50 49
TSt
1.00 2.00 2.21 -0.93 0.40 1.56 0.29 -1.52
Figure7.6
Tahoe Salt Forecasts Using Simple Exponential Smoothing Period t
Demand Dt
0 1 2 3 4 5 6 7 8 9 10 11 12
8,000 13,000 23,000 34,000 10,000 18,000 23,000 38,000 12,000 13,000 32,000 41,000
alpha
Level Lt Forecast Ft Error Et 22,083 20,675 19,908 20,217 21,595 20,436 20,192 20,473 22,226 21,203 20,383 21,544 23,490
22,083 20,675 19,908 20,217 21,595 20,436 20,192 20,473 22,226 21,203 20,383 21,544 23,490 23,490 23,490 23,490
14,083 7,675 -3,093 -13,783 11,595 2,436 -2,808 -17,527 10,226 8,203 -11,617 -19,456
Absolute Error At
Mean Squared Error MSEt
14,083 7,675 3,093 13,783 11,595 2,436 2,808 17,527 10,226 8,203 11,617 19,456
0.1
45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000 0
Demand Forecast
1
2
3
4
5
6
7
8
9
10 11 12
198,340,278 128,622,951 88,936,486 114,196,860 118,246,641 99,527,532 86,435,714 114,031,550 112,979,315 108,410,265 110,824,074 133,132,065 11,538
MADt 14,083 10,879 8,284 9,659 10,046 8,777 7,925 9,125 9,247 9,143 9,368 10,208 12761
% Error MAPE t 176 59 13 41 116 14 12 46 85 63 36 47
176 118 83 72 81 70 62 60 62 63 60 59
TSt 1.00 2.00 2.25 0.51 1.64 2.15 2.03 -0.16 0.95 1.86 0.58 -1.38
Figure7.7
Forecasts Using Trend-Corrected Exponential Smoothing (Holt's Model) Period t
Demand Dt
Level Lt
Trend Tt
0 1 2 3 4 5 6 7 8 9 10 11 12
8,000 13,000 23,000 34,000 10,000 18,000 23,000 38,000 12,000 13,000 32,000 41,000
12,015 13,008 14,301 16,439 19,594 20,322 21,570 23,123 26,018 26,262 26,298 27,963 30,443
1,549 1,438 1,409 1,555 1,875 1,645 1,566 1,563 1,830 1,513 1,217 1,307 1,541
alpha Beta
Forecast Ft 13,564 14,445 15,710 17,993 21,469 21,967 23,137 24,686 27,847 27,775 27,515 29,270 31,985 33,526 35,067 36,609
Error Et
Absolute Error At
5,564 1,445 -7,290 -16,007 11,469 3,967 137 -13,314 15,847 14,775 -4,485 -11,730
5,564 1,445 7,290 16,007 11,469 3,967 137 13,314 15,847 14,775 4,485 11,730
0.1 0.2
45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000 0
Demand Forecast
1
2
3
4
5
6
7
8
9
10 11 12
ean Squared Error MSE MADt % Error MAPE t 30,958,096 16,523,523 28,732,318 85,603,146 94,788,701 81,613,705 69,957,267 83,369,836 102,010,079 113,639,348 105,137,395 107,841,864
5,564 3,505 4,767 7,577 8,355 7,624 6,554 7,399 8,338 8,981 8,573 8,836
70 11 32 47 115 22 1 35 132 114 14 29
70 40 37 39.86 54.83 49.36 42.39 41.48 51.54 57.75 53.78 51.68
TSt 1 2 0 -2.15 -0.58 -0.11 -0.11 -1.90 0.22 1.85 1.41 0.04
hlts-regr
HOLT'S MODEL REGRESSION SUMMARY OUTPUT Regression Statistics Multiple R 0.4813272 R Square 0.2316759 Adjusted R 0.1548435 Standard E 10666.883 Observatio 12 ANOVA df Regressio Residual Total
Intercept X Variable
1 10 11
SS 343092657.3 1137824009 1480916667
MS 343092657.3 113782400.9
F ignificance F 3.01534 0.113127
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% ower 95.0 pper 95.0 12,015 6565.012894 1.830179424 0.097147 -2612.611 26642.91 -2612.611 26642.91 1,549 892.0095994 1.73647352 0.113127 -438.5705 3536.473 -438.5705 3536.473
b, estimate of demand and level at t=0
a, estimate of trend at t=0
deseasonalized
Forecasts Using Trend and Seasonality Corrected Exponential Smoothing) Deseasonalizing Demand Period
t
Deseasonalized Deseasonalized Demand Demand D e m a n d (Eqn 7.2) D t (Eqn 7.3) D t D t
1 2 3 4 5 6 7 8 9 10 11 12
8,000 13,000 23,000 34,000 10,000 18,000 23,000 38,000 12,000 13,000 32,000 41,000
18,963 19,487 20,010 20,534 21,058 21,582 22,106 22,629 23,153 23,677 24,201 24,725
19,750 20,625 21,250 21,750 22,500 22,125 22,625 24,125
45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000 0
Season al Factor Estimate (Eqn 7.5) S t
0.42 0.67 1.15 1.66 0.47 0.83 1.04 1.68 0.52 0.55 1.32 1.66
Demand Deseasonlized Demand
1
3
5
7
9
11
(Eqn 7.6) S i
0.47 0.68 1.17 1.66
Sheet2
WINTER'S MODEL REGRESSION SUMMARY OUTPUT Regression Statistics Multiple R 0.9580652 R Square 0.917889 Adjusted R Squar 0.9042038 Standard Error 414.50331 Observations 8 ANOVA df Regression Residual Total
Intercept X Variable 1
SS MS 1 11523809.5 11523809.5 6 1030877.98 171812.996 7 12554687.5
F ignificance F 67.07182 0.000179
Coefficients tandard Erro t Stat P-value Lower 95% Upper 95% ower 95.0 pper 95.0% 18,439 440.808708 41.8299089 1.25E-08 17360.37 19517.61 17360.37 19517.61 524 63.9592497 8.18973841 0.000179 367.3068 680.3123 367.3068 680.3123
initial estimate of
initial estimate of
Figure7.8
Forecasts Using Trend and Seasonality Corrected Exponential Smoothing) Period t
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 alpha Beta Gamma
Demand Trend Dt Level Lt Tt 8,000 13,000 23,000 34,000 10,000 18,000 23,000 38,000 12,000 13,000 32,000 41,000
0.05 0.1 0.1
18 ,439 18,866 19,367 19,869 20,380 20,921 21,689 22,102 22,636 23,291 23,577 24,271 24,791
5 24 514 513 512 512 515 540 527 528 541 515 533 532
Seasonal Factor St 0.47 0.68 1.17 1.67 0.47 0.68 1.17 1.67 0.47 0.69 1.16 1.67 0.47 0.68 1.17 1.67
Forecast Absolute Error Ft Error Et At 8,913 13,179 23,260 34,036 9,723 14,558 25,981 37,787 10,810 16,544 27,849 41,442 11,940 17,579 30,930 44,928
913 179 260 36 -277 -3,442 2,981 -213 -1,190 3,544 -4,151 442
913 179 260 36 277 3,442 2,981 213 1,190 3,544 4,151 442
Mean Squared Error MSEt 832,857 432,367 310,720 233,364 202,036 2,143,255 3,106,508 2,723,856 2,578,653 3,576,894 4,818,258 4,432,987
50,000 40,000 30,000
Demand
20,000
Forecast
10,000 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
MADt % Error MAPE t 913 546 450 347 333 851 1,155 1,037 1,054 1,303 1,562 1,469
11 1 1 0 3 19 13 1 10 27 13 1
11.41 6.39 4.64 3.50 3.36 5.98 6.98 6.18 6.59 8.66 9.05 8.39
TSt 1.00 2.00 3.00 4.00 3.34 -2.74 0.56 0.42 -0.72 2.14 -0.87 -0.63
Forecast Errors for Tahoe Salt Forecasting
Forecasting Method Four-period moving average Simple exponential smoothing Holt's model Winter's model
MAD 9,719 10,208 8,836 1,469
MAPE(%) 49 59 52 8
TS Range Min Max -1.52 2.21 -1.38 2.25 -2.15 2.00 -2.74 4.00
