excel

Exponential smoothing methods rely on the assumption that data are stationary with slowly varying mean (gradually evolves over time). Furthermore, more recent observations play a greater role than those in the distant past. Estimates are revised continuously as new information becomes available.

Single Exponential Smoothing

Rearranging we get, t+1 = t + α (Yt - t). In other words, next period’s forecast is current forecast plus the correction factor proportional to the current error in forecasting. Thus, successive forecasts are based on the learning/adjustment process based on the errors made in forecasting. The smoothing factor is arbitrarily selected. If α is large (near 1), then large weight is given to recent values and very low weight to distant past. The smoothing of the series is relatively small (small damping factor). If α is near zero, significant weight is given to a series of distant past values and very low weight to recent values. The damping factor is very large, and the smoothing of the series is extreme (damping all fluctuations).

Holt’s (Two-parameter) Linear Exponential Smoothing Method

Holt’s (two-parameter) smoothing technique is designed to better handle data with well- defined (significant) trend compared to the single (simple) exponential smoothing. Holt’s method allows for evolving local linear trends in a time series. Holt’s technique smooths the level and slope directly by using different smoothing constant for each. These smoothing constants provide estimates of level and slope that adapt over time as new observations become available. Holt’s technique provides a great deal of flexibility (compared to other methods like Brown’s double exponential smoothing) in selecting the rates of tracking the level and trend. Three equations are used in this method:

Lt = the new smoothed value (estimate of current level)

Tt = the trend estimate

Yt = actual observation at period t

t+p = the forecast for p periods into the future

In the special case where α = β, the Holt’s technique is the same as Brown’s double exponential smoothing.

Let us use the same example we used for single smoothing with α = 0.4 and β = 0.3 (Suppose I typed these values in cells J4 and J5). You could simply type the values 0.4 and 0.3 in place of $J$4 and $J$5 in the Excel formulas below. The benefit of using fixed cell values like $J$4 is that you could change those cell value and get a new set of results for different α and β.

Initial values of L and T

To begin the algorithm, the initial L1 is generally set as Y1, and the initial trend value T1 is

[(Y2 – Y1)/2 + (Y4 -Y3)/2] = 0. The Excel formula I used is =(C3-C2)/2+(C5-C4)/2

The first row has labels and data starts from second row. Therefore, L2 appears in the third row and L1 and T1 appear in second row.

The Algorithm Now the algorithm can start according to the equations 1-3 above.

L2 = 0.4(Y2) + (1-0.4)(L2-1 + T2-1) = 0.4(35) + 0.6(50 + 0) = 44→The Excel formula is =$J$4*C3+(1-$J$4)*(D2+E2)

T2 = β(Lt – Lt-1) + (1- β) Tt-1 = 0.3(L2 – L1) + 0.7T1 = 0.3(44 - 50) + 0.7(0) = -1.8→ The Excel formula is =($J$5*(D3-D2)+(1-$J$5)*E2)

t+p = Lt + p Tt for one period ahead forecast, p = 1 and t+p = Lt + Tt

2 = L1 + T1 = 50+ 0 = 50 → the Excel formula is =D2+E2

e2 = Y2 - 2 = 35 – 50 = -15 → the Excel formula is =C3-F3

Successive values are calculated in a similar fashion by copying the formula in successive rows. You can copy all four formulas together by selecting the four cells in the third row and dragging down the corner. The result is the table below:

Actual values are available only for 25 periods (up to 2006.Q1). So, errors are calculated only for 25 periods. The forecast for 2006.q2 would be = 69.92 + 2*4.48 = 78.88

Exponential Smoothing (0.1)

Data Point

Value

error plot (0.1)

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -15 -23.5 -6.1499999999999986 -0.53499999999999659 -10.481499999999997 -24.433349999999997 -11.990015 -5.7910134999999983 -20.211912149999996 -23.190720934999995 4.1283511585000028 18.715516042650002 -3.1560355616149991 -12.840432005453501 18.443611195091847 16.59925007558266 -6.0674931975604807E-2 -5.0546074387780422 20.450853305099763 33.405767974589786 5.0651911771308065 -5.4413279405822763 20.102804853475952 38.092524368128359

Exponential Smoothing (0.7)

Data Point

Value

et(.7) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -15 -14.5 10.649999999999999 8.1950000000000003 -7.5414999999999992 -17.262450000000001 4.8212650000000004 6.446379499999999 -13.066086150000004 -8.9198258449999983 22.324052246500003 21.697215673949998 -13.490835297814996 -14.0472505893445 25.785824823196648 7.735747446958996 -12.679275765912301 -8.803 7827297736868 22.358865181067898 21.707659554320372 -18.487702133703891 -15.546310640111166 20.336106807966651 26.100832042389996

2