Combine Budget and Actual Months to create "Forecast"

[davidkirkup]

Hello,

I have financial data that is arranged as follows:

Account Code, Actual, Jan, Feb, Mar, ....Nov, Dec, YTD, Full Year
Account Code, Budget, Jan, Feb, Mar, ....Nov, Dec, YTD, Full Year

There are several hundred rows for each account code. I realize this is not the best table organization for dates but that is what I have. I would like to create a forecast each month which would the sum of actual to date plus remaining budget. Ideally I would like another set of several hundred rows that were individual forecast lines. Since this would change every month, is there any clever way to compute this? All I can think is duplicating the Actual rows, then cut and paste the remaining budget columns and pasting it - and hoping that my account codes match exactly (not necessarily likely). Or maybe some kind of vlookup on account code - which seems tedious.

OR Would it make sense to create a new separate table and somehow blend the two (actual and budget) based on the current month?

 

[davidkirkup]

Clarification: What I actually need is a single value for each Account Code which is Full Year forecast. This could be a new column that has a value just on the Actual Rows.