Choosing the tranche, depending on cumulative spend

[enigma359]

Hi guys, sorry for the poorly worded title. I have a unique problem which I am stuck with... Let me try to describe it here and in addition, attach a worked example.

I am working with a quarterly model, however have a a criteria/tranche which is based on yearly/cumulative spend.

Criteria

• Tranche A: cumulative spend < 10,
• Tranche B: cumulative spend between 10 and 15
• Tranche C: Cumulative spend between 15 and 20
• Tranche D: cumulative spend above 20

If the model was on a yearly basis, i would have no problem modeling it, however I am having problems with it being a quarterly model.

For each quarter, given the expenditure (or cumulative expenditure for the year), I would need to know which tranche would be applicable to me. Please let me attach an example of this to better illustrate my problem - unfortunately i'm unable put attachments.

Any help is greatly appreciated.

 

[enigma359]

Here's a picture to illustrate this