jaminator421
New Member
- Joined
- Jul 26, 2014
- Messages
- 1
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)
<tbody>
</tbody>
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
!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)
| Case | Start | End | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 |
| Management | 9/1/12 | 4/30/16 | $15 | $45 | $50 | $60 | $23 | |||
| Case 1 | 3/1/13 | 10/30/16 | ||||||||
| Case 2 | 9/1/13 | 4/30/17 | ||||||||
| Case 3 | 3/1/14 | 10/30/17 | ||||||||
| Case 4 | 9/1/14 | 4/30/18 | ||||||||
| Case 5 | 3/1/15 | 10/30/18 | ||||||||
| Case 6 | 9/1/12 | 1/30/16 | ||||||||
| Case 7 | 3/1/13 | 1/30/16 |
<tbody>
</tbody>
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
Last edited:
