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).
The data
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:
=AVERAGE(B2,B14)
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:
=C14/AVERAGE($C$14:$C$25)
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:
=B2/D14
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:
=B14/D14
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:
=FORECAST(A26,$E$2:$E$25,$A$2:$A$25)
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:
=F26*D14
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?
Conclusion
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.
Excel Academy
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.
Hi-
Thank you for very through explanation and great example to practice with. However, how do you go about adding up to 5 years of Data to this Forecast trend? I am trying to setup worksheet for future IT spending budget based on past 5 years of spending pattern. Hardware will need to be replaced every few years and I just want to get better forecast output.
Hi Nimesh,
I think you would need to break down the spend into their length of cycle. If hardware is replaced on a 3 year cycle this would be a forecast. If software is on 4 years cycle this would be a forecast. Support and maintenance would be an annual cycle. At the end add all of the individual forecasts together for each year.
You may struggle if you’re only looking at annual data, as it may be difficult to create a pattern on only 5 data points. If you could use monthly data or go back more years it may help establish a pattern which Excel can forecast.
Hope that helps. Let me know how you get on.
Hi
I have been asked to use scientific method to predict/forecast for post implementation review result.
Post implementation review is a survey to seek end user’s feedback on the overall project delivery. The rating scale is a 5 point scale. Our goal is to achieve 50% for rating <=2.
Attached a set of data set. Management would like to predict what would be the result for the rest of months in FY17. I used excel and add the trendline but I don’t understand it. I surfed nets and there are lots of overwhelming information online. The more I read the more I confuse, like alpha, Std Dev, linear, regression, exponential, etc.
I read through and decided to use the simplest model and something I can understand is the Forecast () function to do the prediction. My colleague questioned me amongst so many models what is the reason you use the forecast () function. I don’t know how to answer them and I am not sure whether this is the right technique to use or not.
Please help.
Thanks
Best regards, Yin2
MMM-YY Rating<=2 Trend
Apr-14 57% 63%
May-14 36% 62%
Jun-14 62% 62%
Jul-14 50% 61%
Aug-14 100% 61%
Sep-14 57% 61%
Oct-14 33% 60%
Nov-14 67% 60%
Dec-14 78% 59%
Jan-15 83% 59%
Feb-15 50% 59%
Mar-15 56% 58%
Apr-15 50% 58%
May-15 40% 57%
Jun-15 70% 57%
Jul-15 100% 57%
Aug-15 67% 56%
Sep-15 70% 56%
Oct-15 56% 56%
Nov-15 36% 55%
Dec-15 46% 55%
Jan-16 42% 54%
Feb-16 60% 54%
Mar-16 40% 54%
Apr-16 43% 53%
May-16 50% 53%
Jun-16 43% 52%
Jul-16 43% 52%
Aug-16 75% 52%
Sep-16 45% 51%
Oct-16 57% 51%
Nov-16 60% 51%
Dec-16 33% 50%
Jan-17 29% 50%
Feb-17 50% 49%
Mar-17 58% 49%
Apr-17 57% 49%
May-17 63% 48%
Jun-17 31% 48%
Jul-17 17% 47%
Aug-17 67% 47%
Sep-17 67% 47%
Oct-17 46%
Nov-17 46%
Dec-17 46%
Jan-18 45%
Feb-18 45%
Mar-18 44%
Hi YinYin,
I’m not a statistician, so I’m probably not the best person to answer your question. However, I will share what I know.
The FORECAST function uses linear regression to calculate the future values. Linear regression works where there is a relationship between the X and Y values (i.e. X goes up and Y goes up. Or X goes down and Y goes down). As an example, I’m guessing there is a linear relationship between temperature and ice cream sales.
When using the Linear Trendline on a chart, I believe it should calculate the same result as the FORECAST function (though I have never tested it to check).
Based on your data, there does not appear to be an obvious relationship between the month and the value. So, the linear regression calculated by the FORECAST function may not be the best option. If the past few months serves as a reasonable estimate of the future, then a rolling average may work. Anything more than that, I’d be like you, trying to work out what I can learn from the internet.
Hopefully this helps a bit. Good luck with finding a suitable solution.
Hi Thanks for your great explanations,
Currently i only have 1 year data to use it as database forecast.
so how do i make a re seasonalized or do i no need to re seasonalized it ?
Hi Irsal,
With only 1 year of data, it is impossible to know what is seasonal variation and what is underlying growth. You do need at least 2 complete cycles of data to use this method.
This post is of great help, but can you help me to understand will the generated forecast also cover growth, if not, how can we generate forecast with growth.
Hi Dilshad – if the historic data includes growth, then the FORECAST function will include growth. The function uses linear regression to forecast the values.
Hello. Can this method work on yearly data as well?
Hi Leila,
It can work on any data, provided there is a recognizable cycle of ups and downs. So you could use it to forecast daily sales, as certain days such as Saturdays may have higher sales.
If the data points are more linear in nature, then you can use the FORECAST function without any of the additional columns, as you won’t have the complication of seasonality.
FORECAST is the legacy version of this function. In Excel 2016, Microsoft introduced FORECAST.ETS which takes some of the hard work out of thie activity.
Start with your Month and Revenue data if the first two columns, and delete the rest. In cell C25, enter the value from B25. In cell C26, enter this formula, and fill it down to C37:
=FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,12)
The first three arguments are the same as in the old FORECAST, which has been renamed FORECAST.LINEAR, though FORECAST can still be used. The 4th argument is 12, which means use a seasonality of 12 periods. If you use a seasonality of 1, Excel will detect a seasonality, but it doesn’t work on your data; perhaps it needs more cycles to detect it reliably.
For even more fun, enter these into D26 and E26 and fill them down:
=C26+FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25,0.99,12)
=C26-FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25,0.99,12)
These provide a 99% confidence interval around the forecast.
Thanks Jon. Now that most people have the new forecast functions, this post certainly needs an update. I’ll add it to my list of posts to update (unfortunately that list is getting quite long).
Thanks Jon for pointing out this…
Thanks for the explanation. I have to forecast the demand for currency by denomination, i have data which is long enough only that i have to apply all the above steps to each denominations.
This has been helpful. I can’t wait for my results
Thank you so much, I have learned a lot. I am so excited at how my graph has turned to be.
Thank you so much. I able to get the idea on how to apply forecasting with seasonality in to my javascript. 🙂
It think it’s a logical approach so should work in any language.
Thank you very much for this. I’m using google spreadsheet and it does not provide the excel function FORECAST.ETS and seasonality. So you helped by providing this step-by-step guide.