Exponential Smoothing in Excel Sales Forecasting

By Conrad Carlberg

Exponential smoothing is one of the three principal forecasting methods used in Excel sales forecasting and helps form the basis for the more-advanced techniques and models.

The term exponential smoothing sounds intimidating and pretentious. Don’t worry about what it’s called — it’s just a kind of self-correcting moving average.

Suppose that in June, you forecast $100,000 in sales for July. When the July sales results are in, you find that your July forecast of $100,000 was $25,000 too low — you actually made $125,000 in sales. Now you need to forecast your sales for August. The idea behind this approach to forecasting is to adjust your August forecast in a way that would have made the July forecast more accurate. That is, because your July forecast was too low, you increase your August forecast above what it would have been otherwise.

More generally:

  • If your most recent forecast turned out to be an underestimate, you adjust your next forecast upward.
  • If your most recent forecast turned out to be an overestimate, you adjust your next forecast downward.

You don’t make these adjustments just by guessing. There are formulas that help out, and the Data Analysis add-in’s Exponential Smoothing tool can enter the formulas for you. Or you can roll your own formulas if you want.

This figure shows what you would forecast if your prior forecast (for July) was too low — then you boost your forecast for August.

2001_underestimate
Here’s what happens if your forecast for July was an underestimate. Notice that the August forecast is kicked up.

And if your prior, July forecast was too high, you cool your jets a little bit in your August forecast, as shown here.

2002_overestimate
Your forecast for March 2015 was too high, so exponential smoothing makes you back off your forecast for April 2015.