By Conrad Carlberg

When you’re asked to make a sales forecast using Excel data, one of the first things you need to consider is how far into the future you want to peer. Some forecasting techniques put you in a position to forecast farther out than do others. This figure shows two techniques that let you forecast just one time period ahead.

3901_One-time-period
Moving averages are usually limited to one-step-ahead forecasts.

Notice what happens when you stretch them too far: Like rubber bands, they break and snap back at you.

Look first at cell D5. It’s the average of cells B2, B3, and B4, and it’s what the moving-average approach forecasts for September 2004. That is, the way this forecast is set up, the forecast for September is the average of June, July, and August. You can see the forecast of $40,867 in cell D5, and the formula itself for illustration in cell E5.

The formula in D5 is copied and pasted down through cell D28, where it provides the “real” forecast for August 2016. I’m using “real” in the sense that I haven’t yet seen an actual value for that month — my most recent actual value is for July 2016 — so August 2016 is past the end of the baseline and the forecast for that month is a real forecast. The formula itself appears in cell E28.

But if youcopy and paste the formula one more row down, to try for a forecast for September 2016, you’ve stretched it too far. Now it’s trying to average the actual results for June through August 2016, and you have no actual for August. Because of the way that Excel’s AVERAGE works, it ignores cell B28 and the formula returns the average of B26 and B27.

The District Attorney will decline to prosecute if you’re found shifting suddenly from a three-month moving average to a two-month moving average, but you really shouldn’t. If you do, you’re inviting an apple to mix with the oranges.

And if you take your forecast much farther down, it’ll start returning the really nasty error value #DIV/0!. (That exclamation point iis meant to get your attention. Excel is yelling at you, “You’re trying to divide by zero!”)

A similar situation occurs with exponential smoothing, and it’s shown in this figure. The formula for smoothing is different from the formula for moving averages, but something similar happens when you get past the one-step-ahead forecast in cell D28.

3902_regression
If you want to forecast farther ahead, consider a regression forecast.

Notice that the formula in cell D29 (the formula is shown in E29; the value that the formula returns appears in D29) uses the values in cells B28 and D28. But because you don’t yet have an actual for August, the “forecast” for September 2016 is faulty: In fact, it’s nothing more than the forecast for August multiplied by 0.7. Again, in this sort of exponential smoothing, you’re limited to a one-step-ahead forecast.

The figure shows a different situation, where the forecast is built using regression rather than moving averages or exponential smoothing.

Using regression, you’re in a different position than with moving averages and exponential smoothing. As this next figure shows, you can create your forecasts using date itself as a predictor: Each forecast value there is based on the relationship in the baseline between date and revenue.

3903_trendline
The trendline in the chart is taken from the worksheet. You can also get one from the Chart menu.

Because you know the value of the next two dates, August and September 2016, you can use the relationship between date and revenue in the baseline on the next two dates to get a forecast. The forecast values appear in cells C28 and C29 and show up in the chart as the final two points in the Forecast series.

Now, the farther out into the future you forecast using regression, the thinner the ice gets (or, if you prefer the earlier metaphor, the more strain you’re putting on the rubber band). The farther you get from the end of your baseline, the more opportunities there are for the actuals to change direction — for example, to turn down or to level off.

If you have a real need to forecast, say, 12 months into the future on a monthly basis, and if you think there’s a dependable relationship between date and revenue amount, then regression may be your best choice. But keep in mind that things get flaky out there in the future.

Another method to push your forecast out beyond a one-step-ahead approach is seasonal smoothing. This approach, which depends on a seasonal component in your baseline, can support a forecast that goes that year into the future. It ain’t necessarily so, but it’s possible.