Challenging Formula: Please Help

[hoops3335]

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.

[TABLE="width: 874"]
<colgroup><col><col span="2"><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"]2021[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Capital Cost[/TD]
[TD="align: right"]$28,000[/TD]
[TD][/TD]
[TD]Operating cost[/TD]
[TD="align: right"]$25,000[/TD]
[TD="align: right"]$30,000[/TD]
[TD="align: right"]$34,000[/TD]
[TD="align: right"]$40,000[/TD]
[TD="align: right"]$20,000[/TD]
[TD="align: right"]$149,000[/TD]
[/TR]
[TR]
[TD]Profits retained after Capital Recovery[/TD]
[TD="align: right"]50%[/TD]
[TD][/TD]
[TD]Revenue[/TD]
[TD="align: right"]$5,000[/TD]
[TD="align: right"]$38,000[/TD]
[TD="align: right"]$48,000[/TD]
[TD="align: right"]$75,000[/TD]
[TD="align: right"]$25,000[/TD]
[TD="align: right"]$191,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Recovery during Period[/TD]
[TD="align: right"]($20,000)[/TD]
[TD="align: right"]$8,000[/TD]
[TD="align: right"]$14,000[/TD]
[TD="align: right"]$35,000[/TD]
[TD="align: right"]$5,000[/TD]
[TD="align: right"]$42,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cumulative Recovery[/TD]
[TD="align: right"]($20,000)[/TD]
[TD="align: right"]($12,000)[/TD]
[TD="align: right"]$2,000[/TD]
[TD="align: right"]$37,000[/TD]
[TD="align: right"]$42,000[/TD]
[TD="align: right"]$42,000[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[DRSteele]

I think this might be what you're after. Copy D7 to E7 through H7.

	A	B	C	D	E	F	G	H	I
Total
Capital Cost	Operating cost
Revenue
Recovery during Period
Cumulative Recovery
Profits retained after Capital Recovery

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"]2021[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$28,000 [/TD]

[TD="align: right"]$25,000 [/TD]
[TD="align: right"]$30,000 [/TD]
[TD="align: right"]$34,000 [/TD]
[TD="align: right"]$40,000 [/TD]
[TD="align: right"]$20,000 [/TD]
[TD="align: right"]$149,000 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$5,000 [/TD]
[TD="align: right"]$38,000 [/TD]
[TD="align: right"]$48,000 [/TD]
[TD="align: right"]$75,000 [/TD]
[TD="align: right"]$25,000 [/TD]
[TD="align: right"]$191,000 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]($20,000)[/TD]
[TD="align: right"]$8,000 [/TD]
[TD="align: right"]$14,000 [/TD]
[TD="align: right"]$35,000 [/TD]
[TD="align: right"]$5,000 [/TD]
[TD="align: right"]$42,000 [/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]($20,000)[/TD]
[TD="align: right"]($12,000)[/TD]
[TD="align: right"]$2,000 [/TD]
[TD="align: right"]$37,000 [/TD]
[TD="align: right"]$42,000 [/TD]
[TD="align: right"]$42,000 [/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]50%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]-$20,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]-$12,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]$2,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]$32,500.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]$35,000.00[/TD]
[TD="bgcolor: #E2EFDA, align: right"]$35,000.00[/TD]

</tbody>

Sheet24​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=IF(D6>$B$2,D6-(D6-$B$2)/2,D6)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I7[/TH]
[TD="align: left"]=H7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]