Question

In: Math

Problem 8-12 (Algorithmic) Many forecasting models use parameters that are estimated using nonlinear optimization. The basic...

Problem 8-12 (Algorithmic)

Many forecasting models use parameters that are estimated using nonlinear optimization. The basic exponential smoothing model for forecasting sales is

Ft + 1 = αYt + (1 – α)Ft

where

Ft + 1   =   forecast of sales for period t + 1
Yt   =   actual value of sales for period t
Ft   =   forecast of sales for period t
α   =   smoothing constant 0 ≤ α ≤ 1

This model is used recursively; the forecast for time period t + 1 is based on the forecast for period t, Ft; the observed value of sales in period t, Yt and the smoothing parameter α. The use of this model to forecast sales for 12 months is illustrated in the table below with the smoothing constant α = 0.3. The forecast errors, Yt - Ft, are calculated in the fourth column. The value of α is often chosen by minimizing the sum of squared forecast errors, commonly referred to as the mean squared error (MSE). The last column of Table shows the square of the forecast error and the sum of squared forecast errors.

EXPONENTAL SMOOTHING MODEL FOR α=0.3
Week
()
Observed Value
()
Forecast Forecast Error
()
Squared Forecast Error
1 16 16.00 0.00 0.00
2 20 16.00 4.00 16.00
3 18 17.20 0.80 0.64
4 24 17.44 6.56 43.03
5 21 19.41 1.59 2.53
6 16 19.89 -3.89 15.13
7 19 18.72 0.28 0.08
8 21 18.80 2.20 4.84
9 24 19.46 4.54 20.61
10 22 20.82 1.18 1.39
11 12 21.17 -9.17 84.09
12 19 18.42 0.58 0.34
SUM=188.68

In using exponential smoothing models, we try to choose the value of α that provides the best forecasts. Build an Excel Solver or LINGO optimization model that will find the smoothing parameter, α, that minimizes the sum of squared forecast errors. You may find it easiest to put table into an Excel spreadsheet and then use Solver to find the optimal value of α. If required, round your answer for α to three decimal places and the answer for the resulting sum of squared errors to two decimal places.

The optimal value of α is  and the resulting sum of squared errors is .

Solutions

Expert Solution

week observed 0.3 error error^2
1 16 16 0 0
2 20 16 4 16
3 18 17.2 0.8 0.64
4 24 17.44 6.56 43.0336
5 21 19.408 1.592 2.534464
6 16 19.8856 3.8856 15.09789
7 19 18.71992 0.28008 0.078445
8 21 18.80394 2.196056 4.822662
9 24 19.46276 4.537239 20.58654
10 22 20.82393 1.176067 1.383135
11 12 21.17675 9.176753 84.21279
12 19 18.42373 0.576273 0.332091
SS 188.7216

Formulas

week observed 0.3 error error^2
1 16 16 =ABS(C2-B2) =D2*D2
2 20 =(1-$C$1)*C2+$C$1*B2 =ABS(C3-B3) =D3*D3
3 18 =(1-$C$1)*C3+$C$1*B3 =ABS(C4-B4) =D4*D4
4 24 =(1-$C$1)*C4+$C$1*B4 =ABS(C5-B5) =D5*D5
5 21 =(1-$C$1)*C5+$C$1*B5 =ABS(C6-B6) =D6*D6
6 16 =(1-$C$1)*C6+$C$1*B6 =ABS(C7-B7) =D7*D7
7 19 =(1-$C$1)*C7+$C$1*B7 =ABS(C8-B8) =D8*D8
8 21 =(1-$C$1)*C8+$C$1*B8 =ABS(C9-B9) =D9*D9
9 24 =(1-$C$1)*C9+$C$1*B9 =ABS(C10-B10) =D10*D10
10 22 =(1-$C$1)*C10+$C$1*B10 =ABS(C11-B11) =D11*D11
11 12 =(1-$C$1)*C11+$C$1*B11 =ABS(C12-B12) =D12*D12
12 19 =(1-$C$1)*C12+$C$1*B12 =ABS(C13-B13) =D13*D13
SS =SUM(E2:E13)

our objective function is to minimize SS by varying alpha

data -> solver

we get alpha = 0.2305 = 0.231

minimum SS = 187.1287 = 187.13


Related Solutions

Problem 12-88B (Algorithmic) Using Common Size Statements Groff Graphics Company owns and operates a small chain...
Problem 12-88B (Algorithmic) Using Common Size Statements Groff Graphics Company owns and operates a small chain of sportswear stores located near colleges and universities. Groff has experienced significant growth in recent years. The following data are available for Groff: Groff Graphics Company Consolidated Income Statement (In thousands) Year ended December 31, 2019 2018 2017 Sales $54,322 $42,893 $35,526 Cost of goods sold 32,936 25,682 21,721 Gross margin $21,386 $17,211 $13,805 Other income, net 397 439 421 $21,783 $17,650 $14,226 Costs...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT