Missing Data Causes Unequal Time Periods in Sales Forecasting

By Conrad Carlberg

When you’re working with sales forecasts that are based on moving averages and on exponential smoothing, you’re working with forecasts that depend on a baseline with consecutive time periods. But if some of those time periods are missing, you could be in trouble. This figure shows how a full baseline works.

Excel can calculate the trendline and the equation for you.

Notice the straight line in the chart. It’s called a trendline. The trendline indicates how well the gradual growth in revenues tracks against the dates when the revenues were recognized. Just what you like to see. Generally, the greater the incline in the trendline, the stronger the relationship between the time period and the revenue, and this is a pretty decent result.

The trendline is created by right-clicking the charted data series and choosing Add Trendline from the shortcut menu. Charting the trendline as a separate series gives you more control; using Add Trendline is faster.

Contrast the chart shown previously with the one in the following figure.

The smaller the R-squared value, the less dependable the forecast. An R-squared of 0.07 is small.

The dates and revenues are the same as in the previous figure, except that seven months are missing, some from the start of the baseline and a few from well into the baseline. Now, the relationship between the time period and its revenue has been disrupted by the missing periods, and the trendline in the chart has become nearly horizontal, indicating a much weaker relationship.

You wouldn’t be able to put much trust in a forecast constructed from the baseline in the second figure, even though the data that’s in it is a subset of the same as the data in the figure figure. The lesson: Arrange for a baseline that’s chock-full of consecutive time periods — or don’t bother with a forecast.