Data Analysis with Microsoft Excel: Updated for Office 2007

Chapter 11 Times Series 449

an approach that gives equal weight to all observations within the period

is not always reasonable. For example, if the belief that increased indus-

trialization is accelerating the effects of human-made global warming and

we want to predict future temperature values, we may want to give greater

weight to the most recent observations and lesser weight to observations

further in the past.

Many analysts advocate a moving average that gives greater weight to more

recent values and one in which the value of the weights drops off exponen-

tially. This kind of moving average is not limited to a set period of values

but gives some weight to all observations in the data set. The most recent

observation gets weight w, where the value of w ranges from 0 to 1. The

next most recent observation gets weight w 112 w 2 , the one before that gets

weight w 112 w 22 , and so on. In general, the weight assigned to an observa-

tion k units prior to the current observation is equal to w 112 w 2 k^21. The

exponentially weighted moving average is therefore

Exponentially weighted average

5 wyn 211 w 112 w 2 yn 221 w 112 w 22 yn 231 c

Here w is called a smoothing factor or smoothing constant. This tech-

nique is called exponential smoothing or, specifically, one-parameter

exponential smoothing. Table 11-3 gives the weights for prior observations

under different values of w.

Table 11-3 Exponential Weights

yn 21 yn 22 yn 23 yn 24 yn 25 yn 26

ww(1 2 w) w(1 2 w)^2 w(1 2 w)^3 w(1 2 w)^4 w(1 2 w)^5

0.01 0.0099 0.0098 0.0097 0.0096 0.0095

0.15 0.1275 0.1084 0.0921 0.0783 0.0666

0.45 0.2475 0.1361 0.0749 0.0412 0.0226

0.75 0.1875 0.0469 0.0117 0.0029 0.0007

As the table indicates, different values of w cause the weights assigned to

previous observations to change. For example, when w equals 0.01, approxi-

mately equal weight is given to a value from the most recent observation and

to values observed six units earlier. However, when w has the value of 0.75,

the weight assigned to previous observations quickly drops, so that values

collected six units prior to the current time receive essentially no weight. In

a sense, you could say that as the value of w approaches zero, the smoothed

average has a longer memory, whereas as w approaches 1, the memory of

prior values becomes shorter and shorter.