Hi All,
I have a challenging formula I am trying to create. It essentially incorporates a threshold on profitability. Below I've laid out a simplified version of the data I am working with to help illustrate my problem.
<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>
</tbody>
So you can see I have a cost line and revenue line. I then have calculated the recovery (profit) for each year and the cumulative recovery starting in year 2017.
On the left, are capital cost of $28,000. What I am looking to do is that this company would retain all profits up until they collect the $28,000, however, once they pass $28,000 in profits all subsequent profits are split by 50%. As you can see, in year 2020 cumulative recovery shows $37,000 which is greater than $28,000. 2020 cumulative recovery should actually show $32,500 because they eclipsed $28,000 threshold and the recovery after that is 50% (i.e. 37k - 28k = 9k *.5 = $4.5k. The recovery during 2021 period would actually only be $32,500 as well.
Year 2021 would be applied against 50% percent as well since we have already recovered the capital costs of $28k. 2021 would actually show $2,500 bringing the cumulative recovery to $35,000 in total.
Does anyone have an equation or suggestions we could apply in this situation? Please shoot me back questions if i need to further clarify.
I have a challenging formula I am trying to create. It essentially incorporates a threshold on profitability. Below I've laid out a simplified version of the data I am working with to help illustrate my problem.
| 2017 | 2018 | 2019 | 2020 | 2021 | Total | ||||
| Capital Cost | $28,000 | Operating cost | $25,000 | $30,000 | $34,000 | $40,000 | $20,000 | $149,000 | |
| Profits retained after Capital Recovery | 50% | Revenue | $5,000 | $38,000 | $48,000 | $75,000 | $25,000 | $191,000 | |
| Recovery during Period | ($20,000) | $8,000 | $14,000 | $35,000 | $5,000 | $42,000 | |||
| Cumulative Recovery | ($20,000) | ($12,000) | $2,000 | $37,000 | $42,000 | $42,000 |
<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>
</tbody>
So you can see I have a cost line and revenue line. I then have calculated the recovery (profit) for each year and the cumulative recovery starting in year 2017.
On the left, are capital cost of $28,000. What I am looking to do is that this company would retain all profits up until they collect the $28,000, however, once they pass $28,000 in profits all subsequent profits are split by 50%. As you can see, in year 2020 cumulative recovery shows $37,000 which is greater than $28,000. 2020 cumulative recovery should actually show $32,500 because they eclipsed $28,000 threshold and the recovery after that is 50% (i.e. 37k - 28k = 9k *.5 = $4.5k. The recovery during 2021 period would actually only be $32,500 as well.
Year 2021 would be applied against 50% percent as well since we have already recovered the capital costs of $28k. 2021 would actually show $2,500 bringing the cumulative recovery to $35,000 in total.
Does anyone have an equation or suggestions we could apply in this situation? Please shoot me back questions if i need to further clarify.
