Using Periodic Relationships in Excel Sales Forecasting - dummies

Using Periodic Relationships in Excel Sales Forecasting

By Conrad Carlberg

Over time, a baseline tends to display consistent behavior: Its level is increasing, decreasing, or remaining stationary (or it may be seasonal or cyclic). The relationships between time periods help measure this behavior: the relationship between one month and the next, or between one quarter and the next, or between one quarter and the same quarter in the prior year.

Your baseline might mix up the relationships between its time periods for various reasons, some good and some bad. A couple of examples:

  • Whoever assembled the baseline data (not you, certainly) overlooked the sales revenues for June 15 through June 30. This is a real problem, and it’s really indefensible. “The dog ate my homework” doesn’t cut it here.
  • The warehouse burned to the ground and nobody could sell anything until the factory could catch up with the loss of inventory. Again, a real problem, but it doesn’t help your forecast even if the police do catch the arsonist.

The reason is this: If almost all your baseline consists of monthly revenues, and one time period represents just half a month, any forecast that depends on the entire baseline will be thrown off. The figure shows an example of what can happen.

Bad data from a recent time period can lead to a bad forecast.

Cells A1:B27 contain a baseline with accurate revenues throughout. Exponential smoothing gives the forecast for August 2016 in cell C28.

Cells H1:I27 have the same baseline, except for cell I25. For some reason (careless accounting, that warehouse fire, or something else), the revenue for May 2016 has been underreported. The result is that the forecast for August 2016 is more than $6,000 less than it is when the May 2016 revenues are the result of neither an error nor a one-time incident. Six thousand dollars may not sound like a lot, but in this context it’s an 8 percent difference. And it’s even worse right after the problem occurs: The difference in the two forecasts is 17 percent in June 2016.

If the missing data can’t be located, due perhaps to an accounting error, or if no error was made but some really unusual incident interrupted the sales process during May 2016, you’d probably estimate the actuals for May. A couple of reasonable ways to do that:

  • Take the average of April and June and assign that average to May.
  • Use June 2014 through April 2016 as a baseline, and forecast May 2016. Then use that May 2016 forecast in your full baseline, January 2014 through July 2016.

This situation is a good reason for charting your baseline. Just looking at the baseline, you might not notice that May 2016 is an oddball. But it jumps right out at you if you chart the baseline — see the following figure, particularly June through August 2016 in each chart.

Oddball data jumps out at you when you chart the baseline.

Don’t worry about small differences in the length of the baseline’s time periods. March has one more day in it than April does, but it’s not worth worrying about. Two missing weeks is another matter.