Moving Averages in Excel Sales Forecasting - dummies

Moving Averages in Excel Sales Forecasting

By Conrad Carlberg

Moving averages is one of the three principal tools that the Excel Data Analysis add-in gives you to make forecasts. You may already be familiar with moving averages. They have two main characteristics, as the name makes clear:

  • They move. More specifically, they move over time. The first moving average may involve Monday, Tuesday, and Wednesday; in that case, the second moving average would involve Tuesday, Wednesday, and Thursday; the third Wednesday, Thursday, and Friday, and so on.
  • They’re averages. The first moving average may be the average of Monday’s, Tuesday’s, and Wednesday’s sales. Then the second moving average would be the average of Tuesday’s, Wednesday’s, and Thursday’s sales, and so on.

The basic idea, as with all forecasting methods, is that something regular and predictable is going on — often called the signal. Sales of ski boots regularly rise during the fall and winter, and predictably fall during the spring and summer. Beer sales regularly rise on NFL Sundays and predictably fall on other days of the week.

But something else is going on, something irregular and unpredictable — often called noise. If a local sporting goods store has a sale on, discounting ski boots from May through July, you and your friends may buy new boots during the spring and summer, even though the regular sales pattern (the signal) says that people buy boots during the fall and winter. As a forecaster, you typically can’t predict this special sale. It’s random and tends to depend on things like overstock. It’s noise.

Say that you run a liquor store, and a Thursday night college football game that looked like it would be the Boring Game of the Week when you were scheduling your purchases in September has suddenly in November turned into one with championship implications. You may be caught short if you scheduled your purchases to arrive at your store the following Saturday, when the signal in the baseline leads you to expect your sales to peak. That’s noise — the difference between what you predict and what actually happens. By definition, noise is unpredictable, and for a forecaster it’s a pain.

If the noise is random, it averages out. Some months, sporting goods stores will be discounting ski boots for less than the cost of an arthroscopy. Some months, a new and really cool model will come out, and the stores will take every possible advantage. The peaks and valleys even out. Some weeks there will be an extra football game or two and you’ll sell (and therefore need) more bottles of beer. Some weeks there’ll be a dry spell from Monday through Friday, you won’t need so much beer, and you won’t want to bear the carrying costs of beer you’re not going to sell for a while.

The idea is that the noise averages out, and that what moving averages show you is the signal. To misquote Johnny Mercer, if you accentuate the signal and eliminate the noise, you latch on to a pretty good forecast.

So with moving averages, you take account of the signal — the fact that you sell more ski boots during certain months and fewer during other months, or that you sell more beer on weekends than on weekdays. At the same time you want to let the random noises — also termed errors — cancel one another out. You do that by averaging what’s already happened in two, three, four, or more previous consecutive time periods. The signal in those time periods is emphasized by the averaging, and that averaging also tends to minimize the noise.

Suppose you decide to base your moving averages on two-month records. That is, you’ll average January and February, and then February and March, and then March and April, and so on. In that case you’re getting a handle on the signal by averaging two consecutive months and reducing the noise at the same time. Then, if you want to forecast what will happen in May, you hope to be able to use the signal — that is, the average of what’s happened in March and April.

The figure shows an example of the monthly sales results and of the two-month moving average.

The moving average shows the general direction of the sales (the signal), and deemphasizes the random variations (the noise).