excel seasonal forecasting technique

Excel Seasonal Forecasting Technique

Dealing with Awkward Actuals

About a year ago I was using a creative excel forecasting technique to send a forecast to the finance team based on a couple of years’ worth of data. The historical sales data were difficult to base a sales forecast on for various reasons. I used a formula (taken from CIMA) to produce the following forecast:

dynamic sales forecast using ts=t*sv*r

Factors that made my historical data hard to extrapolate into a forecast trend were:

  • Rapidly increasing volumes generally
  • Seasonal swings in sales (e.g. winter peaks)
  • Evidence of promotional events causing random spikes in the past trend

The formula for getting around this is actually pretty basic:

TS=T*SV*R

TS=the time series sales forecast you’re aiming for
T=a linear trend (or curvilinear if you wish)
SV=seasonal variance
R=random component

4 Steps for a Better Excel Forecasting Technique

Get my 4-step Excel solution download here.

1. Take random known events out of your actuals to get an adjusted actuals series: A

When deriving a trend line as we will do in step 2 you don’t want to include random events such as an operational decision to layoff half the sales force, or ‘the stock market crash’, etc. So you need to apply historic knowledge of known factors to cut out these impacts.

Remove random component from historical sales data
Remove random component from historical data to get adjusted actuals

2. Get a trend line: T

You can use an Excel chart, the LINEST() function or another method to derive an equation that relates time to sales (e.g. Sales=4*year+2000). To do this use your adjusted trend from step one as your baseline data. That is an important part of this Excel forecasting technique.

3. Find the seasonal variance multiplier: SV

If your trend has any seasonality in it–i.e. movies in autumn, retail energy in winter, etc. then you need to account for that by using a multiplier for each month in your forecast. To do this find the average variance to trend for historical periods–in my case I did it by month using a well placed pivot table, getting the average of A/T (Adjusted Actuals/Trend). Ensure your A/T’s sum up to the number of periods in all ‘seasons’, so in my case 12 (see solution download).

4. Create the Time Series, adding the random component (R) if necessary

Add any known random component factors (e.g. upcoming promotions, economic factors, etc.) then combine it all together by applying the formula TS=T*SV*R.

Resulting sales forecast adjusted for seasonality
Resulting excel forecast adjusted for seasonality
Sales Forecast Chart (incl. seasonality and random component)
Excel Forecast Chart (incl. seasonality and random component) plotted vs. original unadjusted actuals.

This technique applies to any time series forecast, provided there is some correlation between actuals and your trend line. As noted by  Entrepreneur you don’t need to use advanced mathematical techniques for useful forecasting, so hopefully this should be sufficient for most commercial applications. To see my 4-step application of the technique download my workbook here.

Ideas to improve this even more? Did you try this Excel forecasting technique out? Please share your comments below.

4 thoughts on “Excel Seasonal Forecasting Technique”

Share your thoughts below :)