Data Analysis with Microsoft Excel: Updated for Office 2007

452 Statistical Methods

This worksheet shows the observed percentage changes in a sample time

series overlaid with the one-parameter exponentially smoothed values. The

smoothing factor w is set at 0.15. In the lower-right corner, the worksheet

contains an area curve indicating the magnitude of the weights assigned to

the observations prior to the last value in the series.

The fi nal forecasted value is 0.028. The most recent observation has the

most weight in calculating this result, with observations decreasing ex-

ponentially in importance. Comparing the curve to the time series tells

you that the large drop in the middle of the time series has little weight

in estimating the fi nal value. In fact, observations prior to that value have

negligible impact.

The mean square error is 0.088 and the standard error is 0.297, showing

that if you had used exponential smoothing on this data your typical error

in forecasting would have been about 0.297 points.

One way of choosing a value for the smoothing constant is to pick the

value that results in the lowest mean square error. Let’s see what happens

to the mean square error when you decrease the value of the smoothing

constant.

Figure 11-12
Exploring
one-parameter

exponential

smoothing

smoothing weight

forecasted values

observed values

magnitude of the

weight assigned to

prior observations