Data Analysis with Microsoft Excel: Updated for Office 2007

458 Statistical Methods

average yield would be the weather, because a region of the country might

go through several years of drought or good weather.

Now suppose the values in the time series follow a linear trend so that

the series is better represented by this equation.

yt 5 b 01 b 1 t1et

where b 1 is the trend parameter, whose value can also change over time.

If b 0 and b 1 were constant throughout time, you could estimate their values

using simple linear regression. However, when the values of these para-

meters change, you can try to estimate their values using the same smooth-

ing techniques you used with one-parameter exponential smoothing (this

approach is known as Holt’s method). This type of smoothing estimates a

line fi tting the time series, with more weight given to recent data and less

weight given to distant data. A county planner might use this method to

forecast the growth of a suburb. The planner would not expect the rate of

growth to be constant over time. When the suburb was new, it could have

had a very high growth rate, which might change as the area becomes satu-

rated with people, as property taxes change, or as new community services

are added. In forecasting the probable growth of the community, the planner

tends to weight recent growth rates much more heavily than older ones.

Calculating the Smoothed Values

The formulas for two-parameter smoothing are very similar in form to the

simple one-parameter equations. Defi ne Sn to be the value of the location

parameter for the nth observation and Tn to be the trend parameter. Because

we have two parameters, we also need two smoothing constants. We’ll use

the familiar w constant for smoothing the estimates of Sn, and we’ll call t

the smoothing constant for Tn. Using the same recursive form as was dis-

cussed with one-parameter exponential smoothing, we calculate Sn and Tn

as follows:

Sn 5 wyn 1112 w^21 Sn 211 Tn 212

Tn 5 t^1 Sn 2 Sn 2121112 t^2 Tn 21

and the formula for the forecasted value of yn 11 is

yn 115 Sn 1 Tn

The values of the parameters need not be equal. Although the equations

may seem complicated, the idea is fairly straightforward. The value of Sn is

a weighted average of the current observation and the previous forecasted

value. The value of Tn is a weighted average of the change in Sn and the previ-

ous estimate of the trend parameter. As with simple exponential smoothing,

you must determine the initial values S 0 and T 0. One method is to fi t a lin-

ear regression line to the entire series and use the intercept and slope of the

regression equation as initial estimates for the location and trend parameters.