Chapter 11 Times Series 479
Optimizing the Exponential Smoothing
Constant (optional)
As you’ve seen in this chapter, the choice for the value of the exponential
smoothing constant depends partly on the analyst’s experience and intu-
ition. Many analysts advocate using the value that minimizes the mean
square error. You can use an Excel add-in called the Solver to calculate this
value. To demonstrate how this technique works, open the Exponential
workbook, which contains a set of time series data.
To open the Exponential workbook:
1 Open Exponential workbook from the Chapter11 data folder.
2 Save the fi le as Exponential Smoothing.
The workbook displays the column of sample time series data. Let’s create a
column of exponentially smoothed forecasts. First we must decide on an initial
estimate for the smoothing constant w; we can start with any value we want, so
let’s start with 0.15. From this value, we’ll calculate the mean square error.
To calculate the mean square error:
1 Click cell F1, type 0.15, and then press Enter.
Next determine a value for S 0 to be put in cell C2. We’ll use the fi rst
value in the time series.
2 Click cell C2, type =B2, and then press Enter.
Now create a column of smoothed forecasts Sn, using the recursive
smoothing equation.
3 Select the range C3:C120.
4 Type =$F$1*B2+(1-$F$1)*C2; then press Enter.
5 Click the Fill button from the Editing group on the Home tab and
click Down to fi ll the formula down the rest of the column.
Now create a column of squared errors [(forecast – observed)^2 ].
6 Select the range D2:D120.
7 Type =(C2-B2)^2, and then press Enter.
8 Fill the formula down the rest of the column.
Finally, calculate the mean square error for this particular value of w.
