Data Analysis with Microsoft Excel: Updated for Office 2007

474 Statistical Methods

index values of the previous period. The recursive equation for Tn is identi-

cal to the recursive equation for the two-parameter model.

As you would expect, three-parameter smoothing also smoothes out the

values of the seasonal indexes, because these might also change over time.

We use a different smoothing constant for these indices. The recursive equa-

tion for a seasonal index In is

In 5 c

yn

Sn

1112 c 2 In 2 q

The value of the smoothed seasonal index is a weighted average of the

current seasonal index based on the values of yn and Sn, and the index value

from the previous period. Calculating initial estimates of S 0 , T 0 , and each

initial seasonal index is beyond the scope of this book.

Forecasting Liquor Sales

Let’s use exponential smoothing to predict future liquor sales. For the pur-

poses of demonstration, we’ll assume a multiplicative model. You will have

to decide on values for each of the three smoothing constants. The values

need not be the same. For example, seasonal adjustments often change more

slowly than the trend and location factors, so you might want to choose a

low value for the seasonal smoothing constant, say about 0.05. However,

if you feel that the trend factor or location factor will change more rapidly

over the course of time, you will want a higher value for the smoothing con-

stant, such as 0.15. As you have seen in this chapter, the values you choose

for these smoothing constants depend in part on your experience with the

data. Excel does not provide a feature to do smoothing with the Winters’

method. One has been provided for you with the Exponential Smoothing

command found in StatPlus.

To forecast future liquor sales:

1 Return to the Liquor Sales worksheet.

2 Click Time Series from the StatPlus menu and then click Exponential

Smoothing.

3 Select Sales for your Data Values variable.

4 Enter 0.15 in the General Options Weight box. This is your value for w.

5 Click the Linear Trend option button, and enter 0.15 in the Linear

Weight box. This is your value for t.

6 Click the Multiplicative option button, and enter 0.05 in the Seasonal

Weight box. This is the value of c. Verify that the length of the period

is set to 12.