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

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.