Linear Extrapolation across multiple periods

[Student1990]

Hi all,

Attached is a worksheet that illustrates my problem. Every month, I get a month end balance for a store's bank account. I'd like to be able to forecast what the balance might be for the next month, next 3 months, next 6 months, etc. all the way out to 5 years from the present, all based upon the data I already have. Basically, I want to extrapolate the balance n periods into the future. E.g. If I type "12000" as my new balance in C16 for 10/31, the projections in column H will update.

On my worksheet, columns B and C have my historical data, containing actual balances from each month. I'd like for this solution, if possible, to be a rolling process, meaning as I enter the month-end balance for each month, it will recalculate the 1 month, etc. projections in column H.

I know I could highlight column C and drag down the appropriate number of periods to get a linear extrapolation, but is there a quicker way using formulas or a macro?

Thanks for reading.

[ChemistB]

Without seeing your worksheet (Go Advanced>Manage Attachments), I can only give general advice. It sounds like you want dynamic named ranges that expand as you enter data. You can use those named ranges to perform linear regression or forecast.

This explains Dynamic named ranges...
http://www.contextures.com/xlNames01.html