Spread forecast value based on exact dates but recalculate based on actuals

[waddy]

Hi there, I need to spread contract values over a 12 month period as a forecast, but then when actual billed monthly figures come through, I need these deducting from the total and the remainder re-spreading over the remaining dates. I also need to show any value that rolls over into the next financial year.

Note that values in the months are based on the number of days in that month that the contract period falls over.

[XLent]

If I've understood...

first change your date headings such that they are dates, e.g. enter 1/7/20 into J8, then

Please Login or Register  to view this content.

the row 11 calcs can be copied up/down to other rows.

[waddy]

Thank you, that's great!

The actual version I have has a few columns with other data in between I and J. Is there any way to alter the formula to get over this?

[XLent]

Well, if we assume the first month is now Column L as opposed to J (per your sample) then change references to $I11 in the SUM constructs to anchor off Column K, e.g.:

(SUM($K11:L11)-N($K11))

the main premise of the above is to anchor on the cell immediately to the left of the first month in your results, and subtract the numeric value of said cell from the resulting aggregation.

the use of N is just to handle the possibility of text values being present in that cell -- with your prior sample we didn't require this as $I held a date, and dates are numeric.