Forecasting future values based on historical data

[elsenorjose]

I've been reviewing the FORECAST function but it seems a little too complex for what I'm trying to do.

I am trying to forecast projected revenue figures based on historical revenue figures which have some variability between months. In other words, some months revenue goes up, others it declines. I've tried the TREND function and it seems to do what I need but I'm not sure if I'm using it correctly. Given the following monthly revenue figures, what function would best provide me with monthly revenue by month for the next 6 months? Thanks in advance for the assistance.

June-05 July-05 Aug-05 Sep-05 Oct-05 Nov-05 Dec-05 Jan-06
2089 1331 1446 1593 1638 2386 1136 1494

 

[franklin97355]

1000 + RAND()*1000

The answer has always been 42

 

[elsenorjose]

I'm not sure I completely understand your reply. Can you elaborate a bit? Thanks.

 

[bkpchs237]

elsenorjose,
Assuming that your dates are cells A1 thru H1 and your values in A2 thru H2 the following may help. There are a number of formulas (Forecast, Trend, Growth to name a few) in Excel that may help you with what you propose. However, to use them you will have to ask yourself some questions up front.
First, on how many prior months of data does each new value depend? For example, the formula:
=FORECAST(I1,$A2:H2,$A1:H1)
assuming that the new date of Feb '06 is in I1 and copying this formula across the rest of row 2 with new dates in row 1 would use all the data points in its evaluation. Remove the dollar sign and you consider only the previous 8 months.
What exactly do you wish your data to represent? How conservative with the values do you wish to be?
I suggest strongly that you take a good look at the Help files regarding these formulas, which will also steer you to other functions. In this case you will note that the Forecast and Trend functions return almost the same exact results, however, the Growth function does not.

Hope this helps.