Slipping & Limiting Contract Revenues Based Off Start / End Dates

[jaminator421]

I am feeling rather lost - I spent from 9am yesterday morning to 5am this morning trying to solve this problem to no avail. I have a report due tomorrow at 12pm and need to get this finalized on a tight timeline. About to lose it haha !

For the sheet below I have 7 cases that I need to develop based off of a management case. Each row after the header row represents a different case. The management case is the "base line case" for which all other numbers will be modified. I was curious if it was possible that based off of the start and end dates for each case to distribute the revenues on a pro rata basis as the date move farther out (in this instance the date increases 6mo more in cases 1-5)

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Case[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Management[/TD]
[TD]9/1/12[/TD]
[TD]4/30/16[/TD]
[TD][/TD]
[TD]$15[/TD]
[TD]$45[/TD]
[TD]$50[/TD]
[TD]$60[/TD]
[TD]$23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case 1[/TD]
[TD]3/1/13[/TD]
[TD]10/30/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case 2[/TD]
[TD]9/1/13[/TD]
[TD]4/30/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case 3[/TD]
[TD]3/1/14[/TD]
[TD]10/30/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case 4[/TD]
[TD]9/1/14[/TD]
[TD]4/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case 5[/TD]
[TD]3/1/15[/TD]
[TD]10/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case 6[/TD]
[TD]9/1/12[/TD]
[TD]1/30/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Case 7[/TD]
[TD]3/1/13[/TD]
[TD]1/30/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Could you help me develop a formula that I can enter beneath each year for each case (aside from the management case) that references the management case and distributes the revenues on a pro rata basis depending on the unique start and end dates? Important to note, for instances where the term - i.e. the end date minus the start date - shortens, like case 6, it would be helpful to just to have the revenue cut off on a pro rata basis. In other words I would imagine I have to annualize the management revenue that occurs in 2016 up to 4/30/16 in the management case then run it out at that annualized rate but only until 1/30/16. This becomes pretty difficult to figure out when you slip the date out but limit the term - as the number you will need to annualize changes. For example, case 7 where the start date slips out 6 months and the end date slips in 3 months.

I am about to go nuts trying to figure this out if you have any wisdom you could provide I would really appreciate it. Thanks in advance for any help you all can provide.

-Jamie

EDIT: The company who's contracts I am looking at is on a calendar year end on 9/30 of each year