Moving Averages in Excel Sales Forecasting
When you forecast using moving averages, you’re taking the average of several consecutive results — besides sales results, you could just as easily be tracking the number of traffic accidents over time. So, you may get the moving averages like this:
- First moving average: The average of months January, February, and March
- Second moving average: The average of months February, March, and April
- Third moving average: The average of months March, April, and May
Notice that the moving averages each combine an equal number of months (three apiece) and that each consecutive moving average begins with the next consecutive month. The figure has an example.
Columns C through F show the moving averages themselves, as well as where each moving average comes from. For example, the third moving average is 42,745 (in cell E6), and it’s the average of the values in cells B4, B5, and B6.
Suppose you decide that each moving average will be based on three baseline values. The first moving average must be based on the first three, chronologically consecutive values. The baseline values should be in chronological order, as shown.
It’s possible to make the first moving average consist of January, February, and March even if the baseline is in some random order — but doing so is tedious and error prone. And it doesn’t make a lot of sense. But if you sort the baseline in chronological order, you can use a simple copy and paste, or AutoFill, to create the moving averages.
The figure shows how the moving averages should look: The baseline in columns A and B is in order. There’s one and only one record for each time period. The level of the baseline is gradually increasing over time, and the chart of moving averages reflects that increase.
Here’s how easy it is to get the moving averages shown:
- In cell G4, type =AVERAGE(B2:B4) and press Enter.
- If necessary, reselect cell G4. Click the Ribbon’s Home tab and choose Copy from the Edit group.
- Select the range of cells G5:G37 and choose Paste from the Edit group.
That’s all there is to it. (AutoFill is even quicker on the worksheet, but to describe it takes more words on the printed page.)
On the other hand, this figure shows what can happen when the data in your baseline is out of order.
There’s no rhyme or reason to the order in which the baseline data appear — and this is just the sort of thing that can happen if you’ve gotten the baseline data from monthly reports that have been stuffed into a file drawer, or even if you pulled them into your worksheet from a database that stores the monthly results in some other order.
The chart does show the moving averages in chronological order, but when the averages are based on a random sequence of months, that’s not much help. Notice that the moving averages in the chart form a line that shows no trend — but you know from the earlier figure that the trend is gently up.
If you do get data in some sort of random order, as in columns A and B, the problem is easily fixed. Take these steps:
- Click the drop-down arrow in cell A1.
- Click on Sort Oldest to Newest as shown in the following figure.
Your baseline will now be sorted into chronological order, and your moving averages will make sense. (Assuming that your table contains just one record per time period. If not, you’ll probably want to start by moving your data into a pivot table and grouping the records.)