Using the FORECAST function with seasonality

FORECAST with seasonality

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:

Seasonality FORECAST - historic data

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.

Seasonality FORECAST - linear function

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.

Seasonality FORECAST - data

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:

Seasonality FORECAST - underlying

Formula Magic with Dynamic Arrays

Of all the features available in Excel dynamic arrays provide the most power for the smallest time investment.  Yet most Excel users do not even know what they are.

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:

=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.

Seasonality FORECAST - re-seasonalized

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.



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.

Email Address * First Name *

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.


Don’t forget:

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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

18 thoughts on “Using the FORECAST function with seasonality

  1. Nimesh says:

    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.

    • Excel Off The Grid says:

      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.

  2. Yin Yin says:

    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%

    • Excel Off The Grid says:

      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.

  3. Irsal says:

    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 ?

    • Excel Off The Grid says:

      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.

  4. Dilshad says:

    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.

    • Excel Off The Grid says:

      Hi Dilshad – if the historic data includes growth, then the FORECAST function will include growth. The function uses linear regression to forecast the values.

    • Excel Off The Grid says:

      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.

  5. Jon Peltier says:

    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.

    • Excel Off The Grid says:

      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).

  6. Agnes Akello says:

    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

  7. Richard says:

    Thank you so much. I able to get the idea on how to apply forecasting with seasonality in to my javascript. 🙂

  8. hans says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *