Forecast function giving incorrect output

Question

Dear experts,

I have been using FORECAST function to estimate sales for next year(s) and it has been giving the correct answer if numbers of years of data available are odd numbers. e.g. if I have data for 5,7,9 years etc.., it is giving me correct answer. But when number of years are even then it is giving incorrect answer.

I am matching the answer with the book and also solving it manualy. i.e. by the formula Y = a+bX. Below is a sample data for better explanation :

Question 1 :

Year	Sales (Y)	Time Deviation from 2004 (X)
2002	53	-2
2003	64	-1
2004	86	0
2005	54	1
2006	83	2

Its answer is correct i.e. sales for 2007 is 83. It is matching with book's answer.

Question 2 :

Year	Sales (Y)	Time Deviation from 2004 (X)
2002	100	-5
2003	110	-3
2004	115	-1
2005	120	1
2006	135	3
2007	140	3

Its answer is wrong. It is coming to -27.51, whereas it should be Rs. 148 lakhs. You can see that number of years is an even number i.e. 6 years (2002-2007).

Please note that I am feeding time deviation manualy since I could not found any formula to calculate it.

Pls help.

Many Thanks

Answer 1

Ayushman wrote:

Question 1 :

Year	Sales (Y)	Time Deviation from 2004 (X)
2002	53	-2
2003	64	-1
2004	86	0
2005	54	1
2006	83	2

Its answer is correct i.e. sales for 2007 is 83. It is matching with book's answer.

Question 2 :

Year	Sales (Y)	Time Deviation from 2004 (X)
2002	100	-5
2003	110	-3
2004	115	-1
2005	120	1
2006	135	3
2007	140	3

Its answer is wrong. It is coming to -27.51, whereas it should be Rs. 148 lakhs. You can see that number of years is an even number i.e. 6 years (2002-2007).

Please note that I am feeding time deviation manualy since I could not found any formula to calculate it.

When used correctly, FORECAST does indeed return 148 for year 2008 in the second example.

The formula in B7 and B15 should be:

B7:    =FORECAST(A7, B2:B6, A2:A6)

B15:  =FORECAST(A15, B9:B14, A9:A14)

Note that the X and knownX parameters should be Year.

I don't know how you are calculating "time deviation" either.

In the first example, I guessed that it is =A2-2004 in C2, copied down.  Then, it is 3 for 2007.  And the formula in D7 is =FORECAST(C7,B2:B6,C2:C6), which also returns 83.

But obviously that formula does not work for C9:C14.  I derived about -33.1133 (!) in C15 by using the formula =FORECAST(C15,B9:B14,C9:C14) in D15 and using Goal Seek to derive C15.

The result in C15 makes no sense to me.  But it might help you to see your mistake.

Answer 2

Dana wrote:

You are indicating that the time differences are integers, when I believe the years are mid values.  Your integer 2004 is really 0.5 from the mid-point. [....] Just note that your jump from -1 to +1 was not in the same linear proportion.

Or it could simply be a count of half-year increments from mid-2004.  So the beginning of 2004 is -1 half-year, and the beginning of 2005 is +1 half-year.  There is no inconsistency.  The difference between year is 2 (half-years), as demonstrated by my formula.

Arguably, I would use end-year dates.  So I would number 2003 as -1 and 2004 as +1.  But it makes no difference to FORECAST.

Answer 3

 In second question, mid value is between 2004 and 2005. That is why, time deviation for 2004 is -1.

Hi.   Just to add.   You are indicating that the time differences are integers, when I believe the years are mid values.  Your integer 2004 is really 0.5 from the mid-point.  Your  values should be fractions (If I am not mistaken).   The data starts in A1, and I get 148 if you are forecasting 3.5 years into the future to 2008.

Just note that your jump from -1 to +1 was not in the same linear proportion as the first example.

You probably meant for year 2004 to be 0, and 2005 to be 1, in the same linear fashion as below.

> But when number of years are even then it is giving incorrect answer.

Again, If I am not mistaken, there is no need to set the zero reference to 2004.5

2002	100	-2.5
2003	110	-1.5
2004	115	-0.5
2005	120	0.5
2006	135	1.5
2007	140	2.5
2008		3.5
		148
		=FORECAST(C7,B1:B6,C1:C6)

HTH  :>)

Dana DeLouis

Answer 4

Ayushman wrote:

Year	Sales (Y)	Time Deviation from 2004 (X)
2002	100	-5
2003	110	-3
2004	115	-1
2005	120	1
2006	135	3
2007	140	3

Its answer is wrong. It is coming to -27.51, whereas it should be Rs. 148 lakhs.

Ayushman wrote:

You are correct, know X and X should be year. Also, you understand correct that time deviation is 2004-2002 and so on in question 1. In second question, mid value is between 2004 and 2005. That is why, time deviation for 2004 is -1.

In that case, I think time deviation for 2007 should be 5, not 3.  And it should be 7 for 2008.  The formula might be:  =(A9-2004)*2 - 1.

Then, again, FORECAST returns 148 when it is used correctly.

Moreover, I believe I know your mistake in using FORECAST that resulted in -27.51.

Again, the formula in B15 can be =FORECAST(A15,B9:B14,A9:A14), using Year for X and knownX.

Alternatively, you could use the formula in D15, which is =FORECAST(C15,B9:B14,C9:C14), using Time Deviation for X and knownX.

And I believe the incorrect formula in E15 demonstrates how you got -27.51, to wit:  =FORECAST(C15,C9:C14,B9:B14).  Note that knownX (C9:C14) and knownY (B9:B14) are reversed.

Ayushman wrote:

if you want to compute forcast on a paper, then you need time deviation.

It should not matter in this case.  It might matter if "time deviation" had finer granularity than "year", and "time deviation" increments were not exactly proportional to "year" (unlike your example).

In any case, it is difficult to duplicate the FORECAST calculation "on paper".

Note that FORECAST is based on the slope and intercept of a linear regression of the data.  The linear regression line does not necessarily include the endpoints, for example (2002,100) and (2007,140).

It is only by "coincidence" (or by design of the textbook example) that it does in the second example.

In contrast, it does not in the first example in your original posting.

Answer 5

Sir, 

many thanks for the reply. You are correct, know X and X should be year. Also, you understand correct that time deviation is 2004-2002 and so on in question 1. In second question, mid value is between 2004 and 2005. That is why, time deviation for 2004 is -1.

I also do understand that time deviation is not required in MS-excel. But since I was thinking that Known X is time deviation, I mentioned that too.

But if you want to compute forcast on a paper, then you need time deviation.

Many thanks sir. You people here are experts... Hope one day I would be too.