How to Use Smoothing Constants in Forecasting Seasonal Sales

By Conrad Carlberg

Seasonal smoothing in Excel uses not one but two smoothing constants: one for the current level of the baseline (alpha) and one for the current seasonal effect (delta).

Actually, there are sometimes three smoothing constants: one for the current level, one for the current season, and one for the slope in the baseline, and a smoothing model that uses all three is called a Holt-Winters model. To keep from getting things tangled up, assume that either there’s no slope in the baseline, or that you’re working with a baseline that you’ve already differenced and, thus, made stationary.

If you read books on forecasting, you may see the level constant referred to as alpha and the seasonal constant referred to as delta. (There’s not a lot of standardization in the Greek names for the constants, but it appears that the literature on smoothing tends to prefer alpha for the level constant and delta for the seasonal constant.)

The figure shows an example of the smoothing equation for the forecast’s level component.

The first estimate of the baseline’s level is the average of the revenues in the first year, in cell F5.

Bear in mind that, to forecast revenue in Q2 2013, you want to do your data gathering and apply your formulas during Q1 2013. So, you’re working with information that’s available to you by the end of Q1 2013. More generally, you can forecast revenue for your next period as soon as the data for the current period is available. And with seasonal smoothing you can legitimately forecast out as far as one full turn of the seasons beyond the most recent actual result in your baseline.