Moving Averages in the Excel Data Analysis Add-in

By Conrad Carlberg

After you have the Excel Data Analysis add-in installed and you’ve made it available to Excel, you can select any one of its analysis tools and run that analysis on the input data that you supply. In the world of forecasting, that means the baseline that you’ve gathered and structured properly on a worksheet.

The first tool you might consider — if only because it’s the easiest to use and understand — is the Moving Average tool. As always with the add-in, begin by going to the Ribbon’s Data tab and choosing Data Analysis. In the Analysis Tools list box, select Moving Average and click OK.

The Moving Average dialog box, shown here, appears.

The Interval is the number of actuals from your baseline to use in each moving average.

Moving day: Getting from here to there

As easy as moving averages are to set up and understand, you take on an additional responsibility when you decide to forecast with them. The issue is how many time periods from your baseline you should include in each moving average.

Use the same number of actual observations in calculating each moving average. If the first moving average that you have Excel calculate uses three periods from the baseline, then all the moving averages in your forecast use three periods.

You want to select the right number of periods:

  • If you use too few, the forecasts will respond to random shocks in the baseline, when what you’re after is to smooth out the random errors and focus on the real drivers of your sales results.
  • If you use too many, the forecasts lag behind real, persistent changes in the level of the baseline — maybe too far for you to react effectively.

When you decide to use the Moving Average tool — or, more generally, to use moving averages regardless of whether you use the tool or enter the formulas yourself — you’re taking a position on the effect of recent baseline values versus the effect of more distant baseline values.

Suppose you have a baseline that extends from January 2016 to December 2016, and you use a three-month moving average of sales results for your forecasts. The forecast for January 2017 would be the average of the results from October, November, and December 2016. That forecast is dependent entirely on the final quarter of 2016 and is mathematically independent of the first three quarters of 2016.

What if instead you had chosen a six-month moving average? Then the forecast for January 2017 would be based on the average of July through December 2016. It would be entirely dependent on the second half of 2016, and the first half of 2016 would have no direct influence on the January 2017 forecast.

It could well be that either of these situations — or another one, such as a two-month moving average — is exactly what you want. For example, you may need your forecast to emphasize recent results. That emphasis can be especially important if you suspect that a recent event, such as a significant change in your product line, will have an effect on sales.

On the other hand, you may not want to emphasize recent sales results too much. Emphasizing recent sales results can obscure what’s going on with your baseline in the long term. If you’re not sure how much to emphasize recent results, you have a couple of good options:

  • Experiment with different numbers of time periods to make up your moving averages. This approach is often best.
  • Use exponential smoothing, which uses the entire baseline to get a forecast but gives greater weight to the more recent baseline values. Exponential smoothing gives a little less weight to the next-to-last baseline value, a little less weight to the one before that, and so on all the way back to the first baseline value, which has the least amount of influence on the next forecast.

Moving averages and stationary baselines

Moving averages are well suited to stationary baselines (baselines whose levels do not generally increase or decrease over a long period of time). You can use moving averages with baselines that trend up or down, but you should usually detrend them first or else use one of the more complicated moving-average models.

How do you tell a stationary baseline from one that is trending up or down? One way is to look at it. The following figure has an example. The baseline certainly looks stationary. It has spikes and peaks and valleys, but overall the baseline doesn’t appear to trend up or down.

Over a longer period of time (say, six years rather than two), this baseline may turn out to be part of a cycle. But for shorter-term purposes, this is a stationary baseline.

The problem with just looking at the baseline is that sometimes it’s not entirely clear whether it’s stationary or trended. What do you think about the baseline shown in the following figure? Looking at the chart, it’s hard to say whether the baseline is stationary. It might be, but then again it might really be drifting gradually down. You can make a quick test by checking the correlation between date and revenue.

This baseline looks as though it may be gently heading down. Adding a trendline to it can help you interpret what’s going on.