Turn in a single MS Excel file containing your individual work.
Sales
Yr 1
Yr 2
Yr 3
Yr 4
Yr 5
Jan
2000
2706
3214
3537
3800
Feb
5000
6323
8105
9184
9978
Mar
7000
9494
10361
12666
13238
Apr
11000
15216
16860
18789
21028
May
14500
18000
21298
25576
30828
Jun
19000
25264
29086
30954
37167
Jul
26000
36449
38057
47982
50933
Aug
15000
18503
23695
28530
28663
Sep
10000
13053
15037
17091
21534
Oct
5000
6478
7711
8635
9654
Nov
4500
5665
6874
8531
8876
Dec
3000
4003
4660
4974
5731
Hint: you’ll probably want to make these demand numbers a single column!
Directions:
Using the table of sales figures listed above, construct the following:
1) All possible moving average forecasts, plus forecasts for year 6 using “periodicity(p) + 1” as the number of periods to
include.
2) All possible four-month weighted moving average forecasts, plus forecasts for year 6, with the weights of 20%, 20%, 30%,
and 30% (most recent month last).
3) All possible simple exponential smoothing forecasts, plus a forecast for year 6. Use solver to find and use the optimal
alpha.
4) The “Static” deseasonalizing method forecast for all possible periods, plus year 6.
5) Use Winter’s method of trend and seasonality corrected exponential smoothing to forecast all possible periods, plus year
6. Use solver to find the optimal alpha, beta, and gamma.
6) Construct the historical MAD, MAPE, and MSE for each of the forecasting methods. Which method works best?
Forecast Errors for Tahoe Salt Forecasting
Forecasting Method MAD MAPE(%) TS Range
Min Max
Four-period moving average 9,719 49 -1.52 2.21
Simple exponential smoothing 10,208 59 -1.38 2.25
Holt’s model 8,836 52 -2.15 2.00
Winter’s model 1,469 8 -2.74 4.00