Revenue Distribution Formula

[Texan Fan]

Revenues are received monthly and then distributed to Mark and Frank.

At first, 75% of the revenues will go to Mark and 25% to Frank. This arrangement of 75/25 will be changed to 25% to the Mark and 75% to Frank once Mark’s $1000 investment is paid back. Once both parties have been compensated equally the percentage split will convert to 50%/50%. I also need functionality that tracks the money to alert me when the percentages should convert.

What formula should I use?



[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month
[/TD]
[TD]Revenue
[/TD]
[TD]

Mark's 75% / 25% / 50%​

[/TD]
[TD]

Frank's 25% / 75% / 50%​

[/TD]
[/TR]
[TR]
[TD]Jan
[/TD]
[TD]250
[/TD]
[TD]

formula​

[/TD]
[TD]

formula​

[/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD]300
[/TD]
[TD="align: center"]formula
[/TD]
[TD="align: center"]formula
[/TD]
[/TR]
[TR]
[TD]March
[/TD]
[TD]300
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April
[/TD]
[TD]350
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








Thanks in advance for any tips.
Texan Fan
I'm using Excel 2007 and Windows 7

 

[Robert Mika]

Revenues are received monthly and then distributed to Mark and Frank.

At first, 75% of the revenues will go to Mark and 25% to Frank. This arrangement of 75/25 will be changed to 25% to the Mark and 75% to Frank once Mark’s $1000 investment is paid back. Once both parties have been compensated equally the percentage split will convert to 50%/50%. I also need functionality that tracks the money to alert me when the percentages should convert.

What formula should I use?



[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Revenue[/TD]
[TD]

Mark's 75% / 25% / 50%​

[/TD]
[TD]

Frank's 25% / 75% / 50%​

[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]250[/TD]
[TD]

formula​

[/TD]
[TD]

formula​

[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]300[/TD]
[TD="align: center"]formula[/TD]
[TD="align: center"]formula[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD]350[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








Thanks in advance for any tips.
Texan Fan
I'm using Excel 2007 and Windows 7

Hello and welcome:

Excel 2010
	A	B	C	D	E
1	Month	Revenue	Mark's 75% / 25% / 50%	Frank's 25% / 75% / 50%
2
3	Jan	250	187.5	62.5	250
4	Feb	300	337.5	212.5	550
5	March	300	487.5	362.5	850
6	April	350	575	625	1200
Sheet1

Cell Formulas
Range	Formula
C3	=IF(SUM($B$3:B3)<500,C2+B3*0.75,IF(SUM($B$3:B3)<1000,C2+B3*0.5,C2+B3*0.25))
D3	=IF(SUM($B$3:B3)<500,D2+B3*0.25,IF(SUM($B$3:B3)<1000,D2+B3*0.5,D2+B3*0.75))
E3	=SUM($B$3:B3)

 

[Texan Fan]

Thank you for the quick reply Robert. That was my first post on the forum and I appreciate the help. As I continue to work on the spreadsheet I may need to post another question.

Thanks again,
Texan Fan

 

[Robert Mika]

Thank you for the quick reply Robert. That was my first post on the forum and I appreciate the help. As I continue to work on the spreadsheet I may need to post another question.

Thanks again,
Texan Fan

You are welcome.
You can be sure that someone will answer your next question.

 

[jaci50]

Thank you robert it will help in my assignment just because of the solution you provide. I am trying to found out this formula now i got it. revenues.co.uk

 

[Robert Mika]

Thank you robert it will help in my assignment just because of the solution you provide. I am trying to found out this formula now i got it. revenues.co.uk

You are welcome.