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:
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
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.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: