Passenger Forecasting

Hi,

I'm doing some basic forecasting based on historical data for the number of passengers for an airport between 2008 through to 2012.
I've used "TREND function" to do a trend projection.

However I'm not sure if using trend is even correct way to proceed.
anyone any other suggestions?

I appreciate any help.

Hi Alex,

Well, if you assume your data is increasing or decreasing in a linear mannor, then the TREND function will work for you. Excel does contain a lot of statisitical functions which probably would help you determine what to use and whether it's linear or not but, alas, that is not my strong point.

I'll be watching this thread to see what comes up.

ChemistB

It depends largely on how your data is "behaving" (which in turn depends on the underlying process that generates the data).

TREND() is essentially linear (ie it assumes all your data is on a straight line).
It can be made to give a polynomial fit with a little bit of fiddling.
FORECAST() might be used instead of TREND(), but this function IS strictly linear.
The best way is usually to get EXCEL to draw you an XY scatter chart of your data, and then try fitting one of the available trendline types through the data.
There are then methods that can be chosen to apply the same technique to your data in the spreadsheet.

Mark

Mark's strategy is a good one. After you try each trend line, Have it display the equation and the correlation on the chart. The closer the correlation is to 1, the better it fits your data. When you find the trending line that best fits your data or when there's a diminishing returns on making your formula more complicated (e.g. in my case going to a fourth degree polynomial raises the correlation coefficient slightly), you can take that formula back and input it into a cell.

I enclosed a random example in which I found a 3 degree polynomial best fit the data entered with a correlation of 0.9928.

ChemistB

One other thing to look for is what is driving your data?
My previous post largely assumed that you are simply forecasting pasenger numbers against date.
However, you might get a better correlation against ticket price, or recent air related accidents, or seat pitch, or...
This might also mean that a particular fit might only work for a particular segment of the data.
You might have a lovely fit upto a given date, then a totally different fit afterward (9/11 would be an obvious example of such a cusp point).
It is possible to build complex formulas, to allow for any number of driving critera, but then you have to start forecasting the driving critera into the future...
Mark.

Get 20 years of prior data.