Note: This method should only be used for Excel 2013 and Prior. There is a new method using the FORECAST.ETS function. Check it out here.
In my opinion, one of the best functions in Excel is FORECAST. We can feed this function values from the past and it will use linear regression to forecast a value for a future point. We have seen this function previously when we considered how to interpolate a value for existing data. As we saw in that post, FORECAST only works where the values have a linear relationship.
Look at the chart below:
The chart shows 2 years of sales revenue. It is clear that there is a seasonal variation, with revenue peaking in November/December each year. It is also clear that there is underlying growth, as each year the revenues increase.
In this circumstance, we cannot use the FORECAST function by itself. If we did we would create the following type of forecast.
Oh dear! That’s never going to be accurate.
To create an accurate seasonal forecast we need to obtain the underlying growth rate through the use of a seasonality index. We can then apply the FORECAST function to the underlying numbers before we re-seasonalize the values. (I’m pretty sure that re-seasonalize is not an actual word, but you know what I mean).
Here is the data table we will be creating.
This table contains is everything we need to create a seasonal forecast. The raw data is in Cells A2-B25. Our aim is to calculate suitable forecast values relating to Apr-17 to Mar-18 (Cells A26-A37). All the values in Columns C through H are calculations which we will look at below.
Calculating a monthly average
The first task is to calculate an average for each calendar month (i.e. an average for April across all years or the average for May across all years etc). The formula in Cell C14 is:
If there were more years of historic data we would include those in the formula also. This formula is copied down into Cells C15-C25.
Calculating a seasonality index
The seasonality index is used to estimate a month’s average value is in comparison to the average of all months. In our example, April values are 81.5% (Cell D14) of an average month and December values are 114.9% (Cell D22) of an average month. The formula in Cell D14 is:
This formula is copied down into Cells D15-D25.
Calculating the underlying values
We now use the seasonality index, calculated above, to calculate the underlying trend. The function in Cell E2 is:
This is copied down into Cells E3-E13. Once we reach Cell E13, we have reached the bottom of the Seasonality Index (Cell D25), so we need to start at April again for the year 2 data. The formula in Cell E14 is:
This is copied down into Cells E15-E25.
Applying the FORECAST Function
Now that we have underlying trend data in cells E2-E25 we can use the FORECAST function to calculate future values. The formula in cell F26 is:
This is copied down into Cells F27-F37.
To prevent the chart having a blank section we set Cell F25 equal to Cell E25.
If we now re-drew the chart on Columns E and F it would appear like this:
Re-applying seasonality to the FORECAST
Cells G2-G25 are set to equal B2-B25. These are historic actual values, so we want to keep these as they are.
Cell H26 is the linear FORECAST calculation multiplied by the seasonality index. The formula in H26 is:
This formula is copied down into Cells H27-H37.
The Cells H26-H37 is our seasonal forecast. Purely for the purposes of drawing the charts, Cell H25 is set equal to Cell G25.
Creating a seasonal forecast chart
You want to see what the chart looks like now, don’t you? Here you go.
That looks more like what we would expect to see, right?
The FORECAST function is great, but sometimes we just need a bit of trickery to force it to work in a non-linear world. For other examples of using the FORECAST function check out: Interpolate values in Excel using the FORECAST
If you’re looking for further information about forecasting techniques in Excel, then check out Engineer Excel (https://engineerexcel.com/blog). While you might not be working in an engineering context the techniques are applicable in many other circumstances.
Discover how you can automate your work with our Excel courses and tools.
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.