Forecast Function HELP! Spreadsheet attached

Hello, I know similar questions have been posted before but I haven't seen this exact question/scenario. Sorry if this has been posted before but I was not able to find an answer searching through multiple threads.

My spreadsheet is attached.

In this scenario, we are in week 2 of 2016. I want to forecast Sales in Units for week 3 of 2016 using the forecast function.

Historical data points for weeks 45 through 53 (end of 2015) and weeks 1 and 2 of 2016 are available and listed in row 4 (C5:M5).

My formula is set up in cell N:5, where I would like the forecast output as follows: =FORECAST(N4,C5:M5,C4:M4)

Based on this, I get an output of 538.85, which I rounded up to 539.

Questions:

- Is this method "correct?" in the sense that this is how the FORECAST function is meant to be used?
- If I relabeled the in row 4 as 1 through 12 instead of 45-53, I get a completely different answer (644). Why does Excel care what values are in row 4? Can it look at the sales in row 5 and give an output based on what it thinks the next trend will be? Could I be using the wrong function? I am confused that the forecast of week 3 takes into account the numbers (essentially just labels) that I have listed to keep track of the week being forecast.

I hope I am explaining the problem correctly. Please let me know if I left out any information that is needed before the problem can be solved.

Thanks!

Hello, I know similar questions have been posted before but I haven't seen this exact question/scenario. Sorry if this has been posted before but I was not able to find an answer searching through multiple threads.

My spreadsheet is attached.

In this scenario, we are in week 2 of 2016. I want to forecast Sales in Units for week 3 of 2016 using the forecast function.

Historical data points for weeks 45 through 53 (end of 2015) and weeks 1 and 2 of 2016 are available and listed in row 4 (C5:M5).

My formula is set up in cell N:5, where I would like the forecast output as follows: =FORECAST(N4,C5:M5,C4:M4)

Based on this, I get an output of 538.85, which I rounded up to 539.

Questions:

- Is this method "correct?" in the sense that this is how the FORECAST function is meant to be used?
- If I relabeled the in row 4 as 1 through 12 instead of 45-53, I get a completely different answer (644). Why does Excel care what values are in row 4? Can it look at the sales in row 5 and give an output based on what it thinks the next trend will be? Could I be using the wrong function? I am confused that the forecast of week 3 takes into account the numbers (essentially just labels) that I have listed to keep track of the week being forecast.

I hope I am explaining the problem correctly. Please let me know if I left out any information that is needed before the problem can be solved.

Thanks!

Originally Posted by MrShorty

Help file for the =FORECAST() function.
https://support.office.com/en-us/art...4-7ad38bbeda99

As far as mechanics and syntax, yes, this is how the FORECAST() function is intended to be used. However, it appears to me that you need to review and understand the statistics behind the problem and the FORECAST() function to decide if this is really the analysis you want.

As explained in the help file, the FORECAST() function performs a simply "linear" regression to the standard y=mx+b equation. Then uses that result to compute y for the desired x value. The regression is naturally going to be sensitive to what you put in for the "known_x" values, as you have observed. Having the week number "cycle back" to week 1 probably does not make sense in this regression. You need to decide where in your "calendar" is week 0 and number the weeks forward and backward from there to get a meaningful "time" measure. I also note that your y values appear to vary on more of a "cyclical" pattern (high then trending down to a low then trending up to a high and so on). A regression that can account for this kind of cyclical behavior may be more appropriate. You will need to think through these kind of issues to decide how best to proceed.

Hi MrShorty,

Thanks for the explanation. If I'm understanding correctly, Excel doesn't "care" what week it actually is -- for my purposes, it is better to label the weeks from 45 through 53, 1-3, as 1 through 12 and keep track on my own knowing what actual week in the year the "weeks" correspond to.

Based on the Excel help explanation about the FORECAST Formula:

The FORECAST function used linear regression to predict a value.

Is this method "correct?" in the sense that this is how the FORECAST function is meant to be used?

Yes, it is the correct usage

- If I relabeled the in row 4 as 1 through 12 instead of 45-53, I get a completely different answer (644). Why does Excel care what values are in row 4?

If you relabel from 1 to 12 then week 1 2016 is 13 and week 2 2016 is 14 so you would be seeking the value of week 15

Graph the sample and add a 4 degree polynomial and display the equation...

y = -0.3392x4 - 0.0897x3 + 87.612x2 - 604x + 1516.7 Rē = 0.8077

unfortunately the Rē is to low so the predicted value is not so accurate.

Thank you both for the responses. Based on your feedback I made the following changes to the spreadsheet (attached).

I've changed X (weeks) to be a consistent set of weeks so that the calculation is not thrown off by the transition from 2015 to 2016 (I've kept labels in row 3 for my purposes). Thanks for helping me understand the concept of the "relative pattern." I can keep track of the actual week of the year separately.

I have additional Questions:

- When I added the equation, I got an R-squared value of 1.489. Any thought to what this could indicate? I thought that correlation could only range from -1 to +1. How is this possible?
- Do I really care about correlation between weeks and units sold? Even if the R-squared value is 1 what would that prove? I am interested in getting the forecast as close to the actual orders as possible.
- What does the intercept represent in this scenario?
- Does additional data improve the probability that the forecasted number is correct?

Sorry for all the question -- I know this is very basic stuff to most.

As far as GIGO, I will take that to heart. I intend to modify these values based on input from the sales team -- I hope that this is a better starting point than my current method of simply looking at the numbers and firing from the hip.

[QUOTE=joeu2004;4240200]

But in general, I think TREND in harder to use correctly. I think FORECAST is the better function to use, if any.

The fact is: I don't believe that FORECAST (or TREND) is the right method for you to use to forecast future sales, given the historical pattern.

It might help if you created an XY Scatter chart (markers only) of your historical data first. IMHO, you should always start forecasting this way. See the attached images.

In all cases, I added a linear trendline. Note that the equation for the linear trendline is of the form y = a*x + b. That is what FORECAST (or TREND) uses. Note the dependency on x, the week number in your design.

The important take-away is to note that the linear trendline does not fit your data very well. Therefore, linear regression (FORECAST or TREND) is a poor predictor of future weeks.

Instead, your historical data is cyclical.

Unfortunately, there is no good predictor for so little data. For example, try fitting a polynomial trendline with order 4, 5 and 6 for very different results. Will week 3 of 2016 continue to trend down or trend up? No one can say.

QUOTE]

Re-reading your post, I am disappointed that the FORECAST function doesn't help me out much but thanks for helping me understand why!

I added a polynomial trend-line. It's much closer to my "gut" feeling regarding the trend. Is there a way to display the values along the polynomial trend-line at the weekly data points? I am hoping I can use the polynomial value as a "starting point" before working with sales.

This is essentially a duplicate of the thread posted here:
http://www.excelforum.com/excel-form...questions.html

Most of the follow-up comments are in the other thread.

Originally Posted by joebmcc

As far as GIGO, I will take that to heart. I intend to modify these values based on input from the sales team

I was not referring to the data. The "GI" is the use of FORECAST for data that does not exhibit a linear trend. The "GO" is the bogus predicted value(s) based on FORECAST.

Originally Posted by joebmcc

I am disappointed that the FORECAST function doesn't help me out much

That's like being "disappointed" that PRODUCT does not correctly sum the data. We must always choose the right Excel function (formula) for the problem.

Arguably, it is unfortunate that the linear regression function has such a generic name, FORECAST. I suspect that Microsoft is not responsible for that. In any case, it is what it is. Try not to read too much into function names.

Originally Posted by joebmcc

When I added the equation, I got an R-squared value of 1.489. Any thought to what this could indicate? I thought that correlation could only range from -1 to +1. How is this possible?

R is between -1 and +1. So R-squared should be between 0 and +1.

The incorrect R-squared is due to a defect that arises when we select a specific Intercept for the trendline. See
https://support.microsoft.com/en-us/kb/829249 (click here).

I have not tried the "work-around" described in the KB. I simply never set the Intercept.

Originally Posted by joebmcc

Do I really care about correlation between weeks and units sold? Even if the R-squared value is 1 what would that prove?

The trendline R-squared is a measure of fitness between the actual y-data (units sold) and the y-values estimated by the trendline, not the x-data (week numbers).

A larger R-squared indicates a better fit between the actual and estimated y-values.

However, even if the trendline fits the actual y-data exactly, that does not mean the trendline formula is a good predictor of future or past y-values.

That is especially true of polynomial trendlines. A polynomial of order n can fit n+1 data points exactly. But such trendlines tend to "blow up" very quickly, greatly exaggerating future and past y-values. See further discussion below.

Originally Posted by joebmcc

Does additional data improve the probability that the forecasted number is correct?

It is difficult to say. But we cannot know one way or the other without analyzing the additional data. So in that sense, it improves forecasting by helping us determine what patterns are and are not relevant.

As I said before, forecasting is as much art as it is science. A big part of that art is interpreting the data to decide how it should or should not be used.

Originally Posted by joebmcc

I added a polynomial trend-line. It's much closer to my "gut" feeling regarding the trend.

Yes, an order-5 polynomial trendline does fit the last 12-week data very well. See attachment "image 1" below. And if the predicted 13th week fits your expectations, perhaps that trendline is suitable for that purpose.

Originally Posted by joebmcc

Is there a way to display the values along the polynomial trend-line at the weekly data points?

We would need to create the future y-values in Excel cells, along with corresponding x-values (week numbers). Then we can add a series to the chart, referencing the range of future y-values.

To accomplish that, we might put the values of the 6 coefficients of the trendline formula into AF2:AK2, and put the following formula into AD5, which corresponds to units sold in the 13th week:

=SERIESSUM(AD4, 5, -1, $AF$2:$AK$2)

To enter the coefficients into AF2:AK2, we might manually them from the trendline label and paste them into the appropriate cell, where AF2 corresponds to x^5.

If you choose to do that, it is important to display the coefficients with sufficient precision. Right-click on the trendline label, then click on Format Trendline Label, then Number on the left side of the dialog box. For this purpose, I like to select Scientific format with 14 decimal places.

Alternatively, we might select AF2:AK2 and array-enter the following formula (press ctrl+shift+Enter instead just Enter):

=LINEST(S5:AC5, S4:AC4^{1;2;3;4;5})

Usually, it is better to use LINEST. Sometimes LINEST returns different coefficients; but the value of SERIESSUM is about the same as the trendline value.

However, sometimes LINEST returns bogus coefficents or Excel errors. In that case, it is better to manually copy the coefficients from the trendline label.

-----

But see attachment "image 2" to see what happens when we use that order-5 polynomial trendline to predict units sold in weeks 14 and 15. Does that fit your expectations as well?! I presume it does not.

Sometimes, none of the standard trendline models and forecast functions is appropriate to use.

Looking at your 28-week data, it appears to have a cyclical pattern. So ostensibly, it might be better to use a sinusoidal regression formula. But honestly, I don't know how to do that.

More accurately, when I tried a formula that I found online, it did not fit your actual data well. Perhaps I simply did not use the sinusoidal regression formula correctly.

In that case, it might be best to use an ad hoc approach. See attachments "image 3" and "image 4". Let me know if you want those methods explained.