Budget & Cash Flow Formula

[DVestergaard]

Hi everyone - greetings from Denmark!

With the amount of questions asked I will put a disclaimer up front and say that I have not - to my knowledge - found an answer to the question I am asking here. If anyone is aware of the question already having been answered somewhere else then please let me know. Otherwise... here goes!

Background
I'm working on a 5-year budget impact model focusing on medical devices. Each device is defined by (a) the cost of the item, (b) acquisition year and (c) product lifetime). Example: Device X costs 1.000 EUR and must be paid for in year 1 within the 5-year model, and it has a product lifetime of 3 years after which it must be renewed/a new one purchased. Across a 5-year time horizon this would require the payer to pay 1.000 EUR for Device X in year 1 and year 4.

Problem
So far, I've been able to produce two separate formulas; one that models the correct cost and acquisition year, but fails to include the product lifetime cycle - and vice versa - a formula producing the correct product life cycle and cost, but fails to begin the calculations at the correct acquisition year.

Question
How - if in any way - can I combine the two statements in a way that ensures that Device X is paid for at the correct acquisition year and rebought according to the product lifetime cycle?

Thank you for reading!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[DVestergaard]

Hi there,

Thank you kindly, I managed to do so once the post was submitted. Hope the example helps.

[AliGW]

Try this:

=MAX(SUMIF($B$4:$B$8,G$3,$D$4:$D$8),IF(MOD(COLUMN(G5)-1,$C$4)=0,$D$4,0)+IF(MOD(COLUMN(G5)-1,$C$5)=0,$D$5,0)+IF(MOD(COLUMN(G5)-1,$C$6)=0,$D$6,0)+IF(MOD(COLUMN(G5)-1,$C$7)=0,$D$7,0)+IF(MOD(COLUMN(G5)-1,$C$8)=0,$D$8,0))

You may need to replace commas with semi-colons for your locale.

If this is not what you want, then you need to do as explained at the top of the page and manually mock up the results you want.

[josephteh]

My suggested solution is to calculate the repeat fixed cost for each device and then total up the costs for all the devices.

Formula for cell G4=IF(AND(G$3>=$B4,MOD(G$3-$B4,$C4)=0),$D4,""), copy across the years and down for each device.