Relating a Season to Ancestors in Seasonal Exponential Smoothing - dummies

Relating a Season to Ancestors in Seasonal Exponential Smoothing

By Conrad Carlberg

When doing seasonal exponential smoothing in Excel, think back to how exponential smoothing works. It uses a formula like this one to base the next forecast in part on the prior actual and in part on the prior forecast:

New Forecast = (0.3 × Prior Actual) + (0.7 × Prior Forecast)

This amounts to a weighted average of two prior figures — the actual and the forecast. This particular formula gives a good bit more of the weight to the forecast than to the actual. You have to experiment around some with a particular baseline to get the right smoothing constant (that’s the 0.3 in the formula) and the right damping factor (that’s the 0.7 in the formula).

The idea here is that one time period in the baseline is going to be closely related to the following time period. If today’s high temperature were 70°F, you’d have to show an approaching cold front to convince someone that tomorrow’s high will be 50°F. Without additional, contradictory information, they’d bet on 70°F. Yesterday tends to forecast today, and today tends to forecast tomorrow.

But shift to months. A given month’s average temperature is much more closely related to the historical average for that month than it is to the prior month’s average temperature. If May’s average daily high were 70°F, you’d still lean toward 70°F for June, but before you put any money down on it you’d want to know what last June’s average daily high was.

So here’s what you’re going to do: Instead of using just one smoothing constant, you’ll use two. Instead of using only one constant in conjunction with the immediately prior baseline value, you’ll use one for the prior value (smoothing May to help forecast June), and one for the season that’s one year back from this one (smoothing last June to help forecast next June).

The figure shows a seasonal sales baseline, and the associated forecasts, in practice.

1501_seasonal
The seasonal forecasts cannot start until one sequence of baseline seasons has passed.

Notice how the sales invariably head up during the third quarter of each year, and spike during the fourth quarter. Then the bottom falls out during the first and second quarters. The figure also shows the forecasts, which have captured the seasonal pattern in a smoothing equation, making the forecasts that much more accurate.

What if you used simple exponential smoothing? The figure gives some of the bad news.

1502_forecasts
The forecasts smooth through the signal in the baseline.

Here, the smoothing constant is 0.3, and the forecasts are relatively insensitive to fluctuations in the actuals from the baseline. The forecasts do nod in passing to the peaks and valleys in the baseline, but it’s a dismissive sort of nod.

What if you boosted the smoothing constant so that the forecasts track the actuals more than they smooth them? That situation is shown here, where the smoothing constant is 0.7.

1503_baseline-changes
The forecasts are late to reflect the changes in the baseline.

The peaks and valleys are represented more clearly — but they lag one period behind their actual occurrence. Compare the last figure and its tardy forecasts with the first figure and its on-time forecasts. The forecasts in Figure 18-1can show up on time because they pay attention to what happened last year. And showing up is 85 percent of life.

The next figure shows how you can combine the components to get a forecast value. Don’t worry, the source of the components and what they mean become clear as you walk through developing the seasonal forecast.

1504_current-baseline
The seasonal effects are above (positive values) and below (negative values) the current overall level of the baseline.

The formula in cell F5 gives the level of the baseline as of Q4 2012. The formula is:

=AVERAGE(D2:D5)

At the outset of the smoothing process, this is our best estimate of the current level of the baseline. It’s just the average of the four quarterly revenue results for 2012. It is analogous to using the first observation as the first forecast in simple exponential smoothing.

From examining the formula in cell H5:

=F5+G2

you can see that the forecast for Quarter 1 of 2013 is the sum of two quantities:

  • The forecast level of the baseline for Q1 2013 as of Q4 2012 (see cell F5)
  • The effect of being in Quarter 1 as of 2012 (see cell G2)

Every forecast in column E and column H is the sum of the forecast level of the baseline and the effect of the season from the preceding year. A good sanity check compares the seasonal smoothing forecasts in the first figure with the ordinary smoothing forecasts in the next two figures.

Clearly, you’re better off if you can estimate the seasonal effect before it takes place. This is what is happening in the last figure, which combines the level that’s attributable to a season with the general level of the baseline to get the current season’s forecast before the next instance of the season takes place.

That’s the reason to put the forecast for the next period in column H, and for the current period in column E. Doing so helps you remember that you can assemble the forecast for a given period at the end of the preceding period. Notice, for example, that cell H5 has the forecast for the next period, that cell E6 has the forecast for the current period, and that they both equal $548,160.