Revenue with Percentages that change

Texan Fan

New Member
Joined
Sep 19, 2012
Messages
6
The Problem:
Revenues are received monthly and then distributed to two different parties at percentages that change.

1. At first, 75% of the revenues will go to Mark and 25% to Frank.
2. This arrangement of 75/25 will be changed to 25% to the Mark and 75% to Frank once Mark’s initial investment of $1000 is paid back.
3. Once both parties have been compensated equally the percentage will change one last time to 50% Mark and 50% Frank.
I also need to be able to see when the percentages convert.

My solution so far:
So far this is the formula I have created. This formula does two main things. It checks to see if the total amount of the revenue column up to the current month is less then or greater the $1000. If it is less then it performs the calculation of multiplying the revenue for that month by 75%. If it is greater than the $1000 then it calculates the difference and only multiplies the difference by 75%.


=IF(SUM(B$4:B5)<C$1,B5*$C$2,((SUM(B$4:B5))-C$1)*C$2)

The problem with the formula is that it keeps calculating at 75% even after the $1000. I’m not sure how to get it to convert to 25% in the middle of the month.
I have thought about creating separate columns for calculating each percentage. I may be going about this completely wrong so I am completely open to a new spreadsheet layout and/or formulas for solving the problem.

Thanks
Texan Fan
using Window 7, Excel 2007


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mark's Investment
[/TD]
[TD]$1000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]75%
[/TD]
[TD]25%
[/TD]
[TD]50%
[/TD]
[/TR]
[TR]
[TD]Month
[/TD]
[TD]Revenue
[/TD]
[TD]Marks Amount
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan
[/TD]
[TD]250
[/TD]
[TD]Place formula here
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]400
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</C$1,B5*$C$2,((SUM(B$4:b5))-c$1)*c$2)[>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Thank you Robert. Let me try this again. This my formula for the spreadsheet above =IF(SUM(B$4:B5) < C$1,B5*$C$2,((SUM(B$4:B5))-C$1)*C$2)
 
Upvote 0
A possible solution

A B C D E F G H[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 123, bgcolor: transparent"]Mark's Investment
[/TD]
[TD="width: 104, bgcolor: transparent, align: right"]1000
[/TD]
[TD="width: 123, bgcolor: transparent"][/TD]
[TD="width: 126, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Month
[/TD]
[TD="bgcolor: transparent"]Revenue
[/TD]
[TD="bgcolor: transparent"]Mark
[/TD]
[TD="bgcolor: transparent"]Frank
[/TD]
[TD="bgcolor: transparent"]Mark's Cumultaive
[/TD]
[TD="bgcolor: transparent"]Frank's Cumulative
[/TD]
[TD="bgcolor: transparent"]Mark's %
[/TD]
[TD="bgcolor: transparent"]Frank %
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jan
[/TD]
[TD="bgcolor: transparent, align: right"]250,00
[/TD]
[TD="bgcolor: transparent, align: right"]187,50
[/TD]
[TD="bgcolor: transparent, align: right"]62,50
[/TD]
[TD="bgcolor: transparent, align: right"]187,50
[/TD]
[TD="bgcolor: transparent, align: right"]62,50
[/TD]
[TD="bgcolor: transparent, align: right"]75%
[/TD]
[TD="bgcolor: transparent, align: right"]25%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Feb
[/TD]
[TD="bgcolor: transparent, align: right"]300,00
[/TD]
[TD="bgcolor: transparent, align: right"]225,00
[/TD]
[TD="bgcolor: transparent, align: right"]75,00
[/TD]
[TD="bgcolor: transparent, align: right"]412,50
[/TD]
[TD="bgcolor: transparent, align: right"]137,50
[/TD]
[TD="bgcolor: transparent, align: right"]75%
[/TD]
[TD="bgcolor: transparent, align: right"]25%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]March
[/TD]
[TD="bgcolor: transparent, align: right"]300,00
[/TD]
[TD="bgcolor: transparent, align: right"]225,00
[/TD]
[TD="bgcolor: transparent, align: right"]75,00
[/TD]
[TD="bgcolor: transparent, align: right"]637,50
[/TD]
[TD="bgcolor: transparent, align: right"]212,50
[/TD]
[TD="bgcolor: transparent, align: right"]75%
[/TD]
[TD="bgcolor: transparent, align: right"]25%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]April
[/TD]
[TD="bgcolor: transparent, align: right"]350,00
[/TD]
[TD="bgcolor: transparent, align: right"]262,50
[/TD]
[TD="bgcolor: transparent, align: right"]87,50
[/TD]
[TD="bgcolor: transparent, align: right"]900,00
[/TD]
[TD="bgcolor: transparent, align: right"]300,00
[/TD]
[TD="bgcolor: transparent, align: right"]75%
[/TD]
[TD="bgcolor: transparent, align: right"]25%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May
[/TD]
[TD="bgcolor: transparent, align: right"]300,00
[/TD]
[TD="bgcolor: transparent, align: right"]100,00
[/TD]
[TD="bgcolor: transparent, align: right"]200,00
[/TD]
[TD="bgcolor: transparent, align: right"]1000,00
[/TD]
[TD="bgcolor: transparent, align: right"]500,00
[/TD]
[TD="bgcolor: #92D050, align: right"]33%
[/TD]
[TD="bgcolor: #92D050, align: right"]67%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]June
[/TD]
[TD="bgcolor: transparent, align: right"]350,00
[/TD]
[TD="bgcolor: transparent, align: right"]87,50
[/TD]
[TD="bgcolor: transparent, align: right"]262,50
[/TD]
[TD="bgcolor: transparent, align: right"]1087,50
[/TD]
[TD="bgcolor: transparent, align: right"]762,50
[/TD]
[TD="bgcolor: transparent, align: right"]25%
[/TD]
[TD="bgcolor: transparent, align: right"]75%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]July
[/TD]
[TD="bgcolor: transparent, align: right"]400,00
[/TD]
[TD="bgcolor: transparent, align: right"]100,00
[/TD]
[TD="bgcolor: transparent, align: right"]300,00
[/TD]
[TD="bgcolor: transparent, align: right"]1187,50
[/TD]
[TD="bgcolor: transparent, align: right"]1062,50
[/TD]
[TD="bgcolor: transparent, align: right"]25%
[/TD]
[TD="bgcolor: transparent, align: right"]75%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]August
[/TD]
[TD="bgcolor: transparent, align: right"]350,00
[/TD]
[TD="bgcolor: transparent, align: right"]112,50
[/TD]
[TD="bgcolor: transparent, align: right"]237,50
[/TD]
[TD="bgcolor: transparent, align: right"]1300,00
[/TD]
[TD="bgcolor: transparent, align: right"]1300,00
[/TD]
[TD="bgcolor: #00B0F0, align: right"]32%
[/TD]
[TD="bgcolor: #00B0F0, align: right"]68%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]September
[/TD]
[TD="bgcolor: transparent, align: right"]400,00
[/TD]
[TD="bgcolor: transparent, align: right"]200,00
[/TD]
[TD="bgcolor: transparent, align: right"]200,00
[/TD]
[TD="bgcolor: transparent, align: right"]1500,00
[/TD]
[TD="bgcolor: transparent, align: right"]1500,00
[/TD]
[TD="bgcolor: transparent, align: right"]50%
[/TD]
[TD="bgcolor: transparent, align: right"]50%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]October
[/TD]
[TD="bgcolor: transparent, align: right"]350,00
[/TD]
[TD="bgcolor: transparent, align: right"]175,00
[/TD]
[TD="bgcolor: transparent, align: right"]175,00
[/TD]
[TD="bgcolor: transparent, align: right"]1675,00
[/TD]
[TD="bgcolor: transparent, align: right"]1675,00
[/TD]
[TD="bgcolor: transparent, align: right"]50%
[/TD]
[TD="bgcolor: transparent, align: right"]50%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]November
[/TD]
[TD="bgcolor: transparent, align: right"]400,00
[/TD]
[TD="bgcolor: transparent, align: right"]200,00
[/TD]
[TD="bgcolor: transparent, align: right"]200,00
[/TD]
[TD="bgcolor: transparent, align: right"]1875,00
[/TD]
[TD="bgcolor: transparent, align: right"]1875,00
[/TD]
[TD="bgcolor: transparent, align: right"]50%
[/TD]
[TD="bgcolor: transparent, align: right"]50%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]December
[/TD]
[TD="bgcolor: transparent, align: right"]350,00
[/TD]
[TD="bgcolor: transparent, align: right"]175,00
[/TD]
[TD="bgcolor: transparent, align: right"]175,00
[/TD]
[TD="bgcolor: transparent, align: right"]2050,00
[/TD]
[TD="bgcolor: transparent, align: right"]2050,00
[/TD]
[TD="bgcolor: transparent, align: right"]50%
[/TD]
[TD="bgcolor: transparent, align: right"]50%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total
[/TD]
[TD="bgcolor: transparent, align: right"]4100,00
[/TD]
[TD="bgcolor: transparent, align: right"]2050,00
[/TD]
[TD="bgcolor: transparent, align: right"]2050,00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

D1 = 1000 (Mark's Investment)

Formula in E4 (under Mark's cumulative)
=IF(N(E3)>=$D$1,IF(N(E3)=N(F3),N(E3)+0.5*B4,IF((N(F3)+0.75*B4)>(N(E3)+0.25*B4),SUM($B$4:B4)/2,N(E3)+0.25*B4)),MIN($D$1,SUMPRODUCT($B$4:B4*0.75)))
copy down

Formula in F4
=SUM($B$4:B4)-E4
copy down

Formula in C4
=E4-N(E3)
copy down

Formula in D4
=B4-C4
copy down

Formula in G4
=C4/B4
copy down format as %

Formula in H4
=D4/B4
copy down format as %

Remark: there are two special months with different percents:
1. When Mark recovers his investment. In Green (May in the example above)
2. When both parties have been compensated equally. In Blue (August in the example above)

Hope this is what you need.

M.
 
Upvote 0
Marcelo,

Your solution is brilliant. I’m really amazed at the spreadsheet design and formula you suggested. Thank you very much for the time and effort you put into it. You have helped me out greatly.

Texan Fan
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top