Excel Sales Forecasting For Dummies, 2nd Edition

Chapter 14

Changing Horses: From Moving Averages to Smoothing

IN THIS CHAPTER

Problems with moving averages

Knowing when two items correlate

Making sense of autocorrelation

Dealing with autocorrelation

For all their simplicity and intuitive appeal, moving averages have baggage. One of the problems comes with a short baseline (it’s amazing how many forecasting problems a long baseline solves). Even if you choose to include only two actuals in each moving average, you lose two observations from your forecasts. Choosing a shorter or smaller length for the averages is a balancing act between tracking and smoothing, but it’s also a choice of how much data you’re willing to part with.

In forecasting, the notion of correlation is usually connected to regression forecasts, because correlations are the building blocks of any regression analysis. But moving averages and exponential smoothing also have to do with correlation, a special kind called autocorrelation. Before you can think sensibly about autocorrelation, though, you need to get a basis by looking at garden-variety correlation, and you find that basis in this chapter.

With correlation as background, the final major section in this chapter goes into autocorrelation: how it can get in the way of a good moving-average forecast, how to calculate and diagnose it, and how to make it go away and leave your forecast alone.

Losing Early Averages

One of the problems with forecasting by means of moving averages is that you lose the opportunity ...