Please Help - forecast function in excel?? unexpected values

Hi all,

I hope someone would be able to help me.

I am trying to forecast for a product (with seasonality) in excel by using the forecast function and then using a graph to map out the figures. I believe I have followed the steps correctly, but some of the answers don't make sense

For example:
1) in Sept 2016 (AH:6) we expect a peak
2) In April 2017(A0:6) we would expect a peak
3) in Sept 2017 (AT:6) we would expect a peak.

Formula used- I am rolling the formula to only include the 12 months data, to add more weight to the fact that the product is growing
Formula in cell AF:5 FORECAST($AF$4,$H$5:$AE$5,$H$4:$AE$4)
Formula in cell AG:5 FORECAST(AG$5,I$6:AF$6,I$5:AF$5) etc

I thought I understood the formula, but maybe I don't, as the numbers are not what I expected. Please could somebody perhaps point out where I am going wrong or if there is a better way to do this?

Extra Info
Grey columns are actual sales
Yellow columns are forecasted sales
I added comments underneath- as I would have some extra information

Thank you!

Attachment 468969

The FORECAST() takes the input data specified by the 2nd and 3rd arguments, performs a linear regression, and estimates a new y value at new x based on that linear regression (see here for more detailed description of the FORECAST() function https://support.office.com/en-us/art...4-7ad38bbeda99 ). The values you are getting appear to me to be correct for a linear regression extrapolated to the specified month number.

I would suggest that you probably need to abandon the built in FORECAST() function and build your own sequence of calculations that will perform the forecast, since I don't think that a linear trend based on month number is really going to be the correct forecasting algorithm.

This is not the kind of stuff I do, so I don't think I can suggest a suitable algorithm. However, your comments indicate that you have some idea of how you would like these forecasts computed. If you can explain how you came up with the 1066 for Sep 2016 and 2500 for Apr 2017 and 1600 for Sep 2017, we should be able to help you program that algorithm into Excel.

Is it perhaps that the growth is calculated overall and then distributed over the 12 months (with little weight to which respective month)?

That's probably pretty close to the reason. Excel's FORECAST() function knows nothing about seasonality -- it only knows straight lines. If you want to better see what the FORECAST() function is seeing, change your chart type from line to XY scatter.

As I indicated, I would not use the FORECAST() function for this. If you want a seasonal forecast, you are going to need to program the algorithm in that you want. Assuming you want a "linear" forecast based on the previous two Januaries, I would start with reviewing some algebra. The three pages in this lesson http://www.purplemath.com/modules/strtlneq.htm will remind you how to use points to find slopes and intercepts, which you can then use to find any other point on the line. Then, in Excel, this will become forecast=(2015to2016slope)*2017+(2015to2016intercept). I will assume for now that you will review the algebra lesson and will then know how to get slope and interecept.