Starting with 10 page, 10 year timeline: Want to add scheduled costs and maint. in year 3

Rick,

I have made a "simplified" dataset so the concept is clear, rather than having to scroll up and down as required on your original.

C10 shows you start with 12 units generating $1oo each (C11)

Enter the number of extra units you want into C14.

Col P is a range named ""Months" that populates a DropList in C15.

Select the "Start Date" in C15

You said you started earning after five months, so C16 finds the row in the range that matches the Start Date, and adds five to it

="Month "&MATCH(C15,Month,0)+5

In A2 you then set the formula as:

=IF(A1>=$C16,($C10+$C14)*$C11,$C10*$C11)

(If the date in A2 is more than, or equal to, the Revenue Date in C16, then add the original and new units together and multiply that by the revenue in C11.
If the date is less than the revenue date for the new build, just multiply the original number in C10 by the C11 revenue.)

Copy that across to the Col L.

If you now change the New Start date in C15, the month the additional revenue kicks in changes automatically

If you then want to add a third set later on, you repeat the steps a few rows down, and extend the formula in row 2 so it says "if the date is more than the third set, add the three lots, it if is more than the second, just add the two, otherwise just use the original number"

And for the other years, you add whichever Sheet Number has your Year 1 variables (Sheet 1 in the attachment, but change as you need it):

=IF(A1>=Sheet1!$C16,(Sheet1!$C10+Sheet1!$C14)*Sheet1!$C11,Sheet1!$C10*Sheet1!$C11)


Hope it is clear?

Ochimus

Rick,

Attachment just sent has the formulae you want in every sheet. Works as follows for each January column,and dragged across the other eleven months:

=IF(AND('Year 1'!$D89<>"",B4>='Year 1'!$D89),((365/12)*('Year 1'!$D60+'Year 1'!$D77+'Year 1'!$D82+'Year 1'!$D87)*('Year 1'!$D69*4)),IF(AND('Year 1'!$D84<>"",B4>='Year 1'!$D84),((365/12)*('Year 1'!$D60+'Year 1'!$D77+'Year 1'!$D82)*('Year 1'!$D69*4)),IF(AND('Year 1'!$D79<>"",B4>='Year 1'!$D79),((365/12)*('Year 1'!$D60+'Year 1'!$D77)*('Year 1'!$D69*4)),((365/12)*('Year 1'!$D60)*('Year 1'!$D69*4)))))


Is there a Fourth Set (Start of Use in Sheet1 D89)
If yes, it checks whether the Month number is more than or equal to that date.
If yes, it adds D60+ D72+D82+D87 on the Year 1 sheet and calculates on that total

IF there is no Fourth Set, or the Month number is before that Start of Use, , it looks for a Third Set (Start of Use in Sheet1 D84)
If yes, it checks whether the Month number is more than or equal to that date.
If yes, it adds D60+ D72+D82 on the Year 1 sheet and calculates on that total

IF there is no Third Set, or the Month number is before that Start of Use, , it looks for a Second Set (Start of Use in Sheet1 D79)
If yes, it checks whether the Month number is more than or equal to that date.
If yes, it adds D60+ D72 on the Year 1 sheet and calculates on that total

If there is no Second Set, or the Month number is before that Start of Use, , it uses the original number in Sheet1 D60

I set the attachment for months 30, 40 and 50, but if you change or clear them, you can see the lease revenues change accordingly.

Two quick points. I would "lock" those cells to ensure nobody deleted or changed the formulae. Secondly, you have not allowed for inflation? The costs and revenues in Year 10 are still those you set in Year 1?

Ochimus