Choosing Time Periods in Excel Sales Forecasting

By Conrad Carlberg

Suppose that you need to forecast sales for a time period that’s out a ways from the present — a year, for instance. Here’s where judgment enters the picture, along with the nature of your requirements.

If your baseline consists of several years, with actuals broken out by months, one thing you may consider is to change the baseline’s time period from months to years. Then you can forecast the entire next year — although your forecasts would not be month-by-month. You would get the one-step-ahead forecast, and that one step would be the entire year.

Pivot tables are useful for summarizing baseline data into longer time periods.

Here’s what’s going on in the figure:

  • Column A contains the month during which revenue was recognized. It extends down past the bottom of the visible worksheet area to December 2016.
  • Column B contains the revenue for each month.
  • The range D3:E8 contains a pivot table. (Excel’s pivot tables are huge assets for forecasting, and you can find out how to use them in Chapter 8.) This pivot table converts the monthly data in columns A and B to annual data — the sum of the revenue for each year.
  • The forecast for each year, using moving averages, is in the range G6:H8. Your one-step-ahead forecast for 2017 is in cell H9. In this case, the forecasts are based on two-year moving averages, rather than the three-period averages that appear.

The approach has a couple drawbacks:

  • Your baseline goes from 60 observations (monthly revenue over 5 years, a good long baseline) to 5 observations (yearly revenue over 5 years, a short baseline indeed). Reducing the length of your baseline so drastically often causes misleading results. But because the monthly revenues show the same gradual growth as the annual totals, you can have some confidence in the annuals.
  • Whoever requested the forecast — an accountant, a bank, a sales manager, a sales VP — could want to see the forecast for 2017 on a monthly basis. If so, you’re probably going to have to back up to the monthly baseline and use regression (probably, because you may be able to find the seasonality in the baseline that would support seasonal smoothing).