forecast price with intercept & slope

[scottlarock]

Hey all Excel geniuses,
This is actually my FIRST forum post ever ! yep yep gety yep...
I'm officially a web geek ! yeee haaww...

let's imagine I have :
1) average price per year:2006=$200; 2007=$210; 2008=$215;2009=$210
2) # of units sold per year: 2006=1000;2007=1200;2008=1400;2009=1100

I would like to find out how to forecast a market price for 2009
Up to now, I do the following :
1) intercept(av price 2006 to 2009;# of units sold per yr 2006 to 2009)
2) slope(av price 2006 to 2009;# of units sold per yr 2006 to 2009)
3) forecast price = # of units sold in 2009=1100*slope+intercept

Am I right or do I have it upside down...maybe you also have an example.
And how would it work with the normal Forecast function ?
Thank you very much in advance for elucidating this dilemna.

 

[ciavala]

I don't know if this is exactly what you are looking for, but it is a very good explantion of the function sumproduct.

http://www.datapigtechnologies.com/flashfiles/sumproduct.html


Hope that is some help
ciavala

 

[scottlarock]

Unfortunately not.
It is indeed a very good explanation of the sumProduct function which gives you the average price per Unit.
But what I need to know is the future average price according to the past trend.
Attributes are:

1) average price per year:2006=$200; 2007=$210; 2008=$215;2009=$210
2) # of units sold per year: 2006=1000;2007=1200;2008=1400;2009=1100

What I did up to now and don' know if it is right is :
1) intercept(av price 2006 to 2009;# of units sold per yr 2006 to 2009)
2) slope(av price 2006 to 2009;# of units sold per yr 2006 to 2009)
3) forecast price = # of units sold in 2009=1100*slope+intercept

Am I right or do I have it upside down...maybe you also have an example.
And how would it work with the normal Forecast function ?
Thank you very much in advance for elucidating this dilemna.

 

[scottlarock]

Does anyone have any help on forecast price methods.
I could use help on both simple 'forecast' formula but most important would be the multiple step method with 'slope' & 'intercept' ?
Thanks a ton :°) and happy easter !

 

[Edleets]

Scoot
You need to forecast future based on past performance. If you need to forecast 2009, your slope and intercept need to be up to 2008 (do not include 2009 data) and forecsst for 2009 applying the formula:

Y = Slope X + Intercept

Where:
Y = unknown variable (in this case the price for 2009)
X = the knom variable (in this case the units sold for 2009)

The only thing I am not sure is why you are trying to forecast cost based on units sold (which are uncertaint). Should you be forecasting 2009 units sold based on 2009 costs?

Also, you may use the statistical (regresion analyis) formula = LINEST()

I hope this help you.

Ed.

 

[scottlarock]

Hi ed,
Thanks for your reply.
Actually, the number of Units is number of MW sold in a given year.
The idea is to predict price of a target bid according to the past correlation between the total MW sold and average price per MW for a series of years.

i.e.:

Year: 2006 (A1)
total MW sold: 10000 (A2)
average $/MW: 200 (A3)

Year:2007 (B1)
total MW sold: 12000 (B2)
average $/MW: 220 (B3)

Year: 2008 (C1)
total MW sold: 12500 (C2)
average $/MW: 230 (C3)

year: 1st half 2009 (D1)
total MW sold: 5000 (D2)
average $/MW: 225 (D3)

What I understand from my research on price prediction is the following :

medium forecasted price =D2*(slope(A3:D3;A2:D2)+intercept(A3:D3;A2:D2))

I need to use the total MW sold in 2009 one way or another, whether I take 5000 as it is and also cut the previous years into 2, that way I would have the total MW sold per semester, thus a Business cycle per semester which makes more sense when predicting future prices.
Or I could use the forecasted total MW sold in the whole current or upcoming year and apply the formula as specified above.

Does this method make sense ?
I don't know how to use your =Linest() function ?

Many thanks in advance.

 

[Edleets]

Scott
You can use your total MW sold in 2009 or just by semester. Your formula is OK except that you cannot include in the formula the forecsted period's data to get the slope and the intercept. In your case, if you are forecasting the MW for 2009 based on 5000 units sold, then the formula will look like:

medium forecasted price =D2*(slope(A3:C3;A2:C2)+intercept(A3:C3;A2:C2))

In summary, whatever you want to use, year or semester, for your next forecasted period you only know the units sold. And from this data you will forecast the price, so they cannot be part of the slope or intercept.

You can use the Linest() formula to get more statistical data as part of your output (std errors of slope and intercept, the R-squared, the degree of freedom, etc). You can get help from the Help menu in Excel.

Thanks
Ed.