| 0 |
Subject: Forecasting Numbers in Excel
Posted by: ukula
- [406252212] Thu, Jul 22, 2004, 16:42
I want to forecast years 2005-2007 in Excel based on the following numbers:
2000 36,597
2001 23,240
2002 20,947
2003 19,570
2004 18,723
2005
2006
2007
I see a ton of forecasting methods available, linear, exponential, growth, etc....
What would be the best method?
|
| 1 | Slackjawed Yokel
Leader
ID: 52347519
Thu, Jul 22, 2004, 22:56
|
For one, I would change your x-data to be 1-7 as you really don't want your fit to be a function of the actual year. Then try a power function.
|
| 2 | Sludge
ID: 475323018
Thu, Jul 22, 2004, 23:48
|
(y/10000)^-6.15 = -10.415034+0.005208*x
y is the second column, x is the year.
Divide y by 10,000 just to bring the numbers down to earth a bit.
The exponent -6.15 isn't taken to as many decimal places as possible, but I don't really want to either (a) write the function to minimize the SSE or (b) minimize it by hand.
Predictions:
2000 30920.6
2001 22869.5
2002 20704.4
2003 19474.0
2004 18628.4
2005 17990.7
2006 17482.1
2007 17061.3
First point is way off, but that probably can't be avoided with the kind of power transformation being done here.
Usual warnings apply. There's not a lot of data. Forecasting even one step into the future is risky at best, much less three years in the future. Caveat emptor!
Yokel - Why?
|
| 3 | Slackjawed Yokel
Leader
ID: 52347519
Fri, Jul 23, 2004, 00:48
|
If you're asking why I suggested to change the values of the x-data. I guess my first reaction was that the actual value of the year shouldn't be taken into consideration - I think we're more interested in the deltas. (I mean should we be using the Aztec calendar, the Roman calendar?). Normally if I have some time data, I would start it at 0, but seeing as we were trying to fit a curve to it, and that the data seems to be decaying, I suggested it start at 1.
|
| 4 | ukula
ID: 12630237
Fri, Jul 23, 2004, 08:42
|
Tnaks guys - but I guess I don't see why the year should even be included in the equation. To me it is just a label. Instead of 2001,2002,2003,etc... it could easily be January, February, March, etc...
I'm just looking for a trend here. It is obviously a downward trend that is decreasing in decreasing increments. It's not a linear trend but rather an exponential (wrong term?) trend.
Excel offers many different types of trend functions and I was just trying to figure out which one to use to project the next three years.
|
| 5 | Sludge
ID: 54692111
Fri, Jul 23, 2004, 11:11
|
Of course the values of the labels of the x's shouldn't matter. Shouldn't matter one bit. Using a linear combination of the x's as I did above, it doesn't. They could be 0, 12, 24, 36, 48 and it wouldn't matter. The linear portion involving the x's is invariant to scale and location changes. For this reason, one desirable property of a model is that it be invariant to location and scale changes, but it's not absolutely necessary. Some models involving the x's aren't, and the choice of values of the x's to use becomes important and, unavoidably, arbitrary.
It's for that reason that I would disagree with your statement that "you really don't want your fit to be a function of the actual year."
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|
|