Calculating Total Forecasting

[yuzi]

I have a row of totals in a spreadsheet and I want to calculate a forecasted total based on the previous month's totals.

For example I have two months and I want to know how to calculate the forecasted Jun 09 total:

Apr 09 May 09 Jun 09
4345 5010 ?

Help would be appreciated. Thanks.

[oteixeira]

Hi.
It is necessary to know how do you want to calculate the forecast. What rules to use? Average?

Octavio

[yuzi]

Previously I was basing my forecasting for every month on the below formula:

=IF( AL2 = 0, 0, ROUND( (AM$31 - (SUM(AM$1:AM1)-AM$1) ) * AL2 / (AL$31 - (SUM(AL$1:AL1)-AL$1)), 0) )

However, now I need to do the forecasting for just the total. The total should be based on what the previous total for the previous month was.

[yuzi]

Any help would be appreciated.

[shg]

If A1 and B1 contain values for the two prior periods, then a linear extrapolation to the next period is =2*B1-A1.

There are usually a dozen other factors to consider.

[yuzi]

Actually I just realized that I'm trying to forecast based on previous month's year to current month's year. For example, I'm comparing May 08 to May 09. I tried using the above formula but the result is too high.

[shg]

Pardon ...?

[yuzi]

This is what I want to do:

=2*AQ2 - AD2

But the result that I'm expecting is too high. The numbers should be consistant with a minor change not a drastic change. For example if the result is 15,000 for May, it should be 15,500-17,000 for June, not 120,000.

[yuzi]

To make it easier I've attached a file. I'm trying to forecast the numbers from Nov. 09 to Oct. 10. And the formula that I'm using to do forecasting for July 09 to Oct 09 is based on the totals that were provided.

Now I need a formula for the total to be able to use my other formula.
Hope this makes more sense. Thanks.

[shg]

I don't know what your formula does. If you want a linear trend, in AM2 and copy down and across,

=TREND($G2:$AL2, $G$1:$AL$1, AM$1)

[yuzi]

Thanks, that worked.