Revenue with Percentages that change

[Texan Fan]

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

	Mark's Investment	$1000
		75%	25%	50%
Month	Revenue	Marks Amount
Jan	250	Place formula here
Feb	500
Mar	400
Apr	500

<tbody>
</tbody>

</C$1,B5*$C$2,((SUM(B$4:b5))-c$1)*c$2)[>

 

[Texan Fan]

For some reason my formula will not post properly. Do I need to add HTML code around it?

 

[Robert Mika]

For some reason my formula will not post properly. Do I need to add HTML code around it?

If your formula contains < > sign you need to add spaces beetwen the < or > and the next sign:
like

=IF(AND(A1 < A2,A2 > A3....

Is your formula simialr to this one:

http://www.mrexcel.com/forum/excel-questions/660324-revenue-distribution-formula.html

 

[Robert Mika]

Deleted...

 

[Texan Fan]

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)

 

[Marcelo Branco]

A possible solution

A B C D E F G H

		Mark's Investment	1000
Month	Revenue	Mark	Frank	Mark's Cumultaive	Frank's Cumulative	Mark's %	Frank %
Jan	250,00	187,50	62,50	187,50	62,50	75%	25%
Feb	300,00	225,00	75,00	412,50	137,50	75%	25%
March	300,00	225,00	75,00	637,50	212,50	75%	25%
April	350,00	262,50	87,50	900,00	300,00	75%	25%
May	300,00	100,00	200,00	1000,00	500,00	33%	67%
June	350,00	87,50	262,50	1087,50	762,50	25%	75%
July	400,00	100,00	300,00	1187,50	1062,50	25%	75%
August	350,00	112,50	237,50	1300,00	1300,00	32%	68%
September	400,00	200,00	200,00	1500,00	1500,00	50%	50%
October	350,00	175,00	175,00	1675,00	1675,00	50%	50%
November	400,00	200,00	200,00	1875,00	1875,00	50%	50%
December	350,00	175,00	175,00	2050,00	2050,00	50%	50%
Total	4100,00	2050,00	2050,00

<tbody>
</tbody>

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.

 

[Texan Fan]

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

 

[Marcelo Branco]

You are welcome and thanks for the feedback.

M.