Future Revenue Earning Based on Scenarios

[vikas_newports]

Hello! I am looking for someone to design a simple tool to simulate a revenue share agreement.
The excel ( or google sheet) should make it easy for me to estimate how much I will get paid based on 3 factors:
1. how much revenue the business is generating
2. if and when I leave the company
3. if and when I am fired from the company

the first calculation is simple. I get paid 5% of all revenue up to $2,000 plus 10% of all revenue between $2,000 & $3,000 plus 15% of all revenue between $3,000 & $4,000 plus 20% of all revenue between $4,000 and $5,000 plus 30% of all revenue between $5,000 and $7,500 plus 40% of all revenue between $7,500 and $10,000 plus 50% of all revenue above 10,000

The revenue-sharing agreement is for 3 years

If I chose to leave the role and worked 6 months or less, I earn no more money. If I worked between 6 months and 1 year, I will continue to earn 25% of my predetermined revenue share capped at $2,000 / month. If I worked between 1 year and 2 years I will continue to earn 50% of my predetermined revenue share capped at $4,000. If I worked between 2 and 3 years with the company I will continue to earn 75% of my predetermined revenue share capped at $6,000.

If Laura (business owner) fires me and I've for less than 6 months, I earn no more money. If I worked between 6 months and 1 year, I will continue to earn 10% of my predetermined revenue share capped at $2,000 / month. If I worked between 1 year and 2 years I will continue to earn 20% of my predetermined revenue share capped at $4,000. If I worked between 2 and 3 years with the company I will continue to earn 30% of my predetermined revenue share capped at $6,000.

Is this something you can help me with?

 

[Dave Patton]

Is this a class assignment?

Please post with XL2BB the layout of your data and the calculations that you have completed.

There are gaps in the information that you provided.

If the revenue is 50,000 what would your share be?
Are the calculations based on cumulative or monthly revenue?

 

[vikas_newports]

if revenue was 50K my payment would be $22,300 every month
my payment changes every month based on the revenue
if revenue is 1,000 I get paid 50

I am trying to construct a workbook but dont know how to do

 

[Fluff]

Has your question solved? If not why have you marked it as such?

 

[Dave Patton]

T202012a.xlsm
	A	B
1
2	Revenue	Share
3	$50,000.00	$22,300.00
4
3c

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT(--(A3>aB),A3-aB,aR)

Is this a class assignment? So you want us to do your assignment!

Please post with XL2BB the layout of your data and the calculations that you have completed. Not done.
Are the calculations based on cumulative or monthly revenue??

There are gaps in the information that you provided. ??

T202012a.xlsm
	A	B	C	D	E	F	G
10
11	Named arrays	Brackets	aB	={0;2000;3000;4000;5000;7500;10000}
12		Rate Diff	aR	={0.05;0.05;0.05;0.05;0.1;0.1;0.1}
3c

 

[vikas_newports]

Fluff , it was by mistaken clicked as solved
I tried to construct this formula for First Part and My date of joining is 01-Dec-2020

=IF(A3<=2000,A3*0%,IF(A3<=3000,(A3-3000)*10%,IF(A3<=4000,(4000-A3)*15%,IF(A3<=5000,(5000-A3)*20%,IF(A3<=7500,(7500-A3)*30%,IF(A3<=10000,(10000-A3)*40%,IF(A3>10000,(10000-A3)*50%,"Failed")))))))

 

[vikas_newports]

There are gaps in the information that you provided. ??

Dave, it is monthly and I am trying the below function for scenario 1

=IF(A3<=2000,A3*0%,IF(A3<=3000,(A3-3000)*10%,IF(A3<=4000,(4000-A3)*15%,IF(A3<=5000,(5000-A3)*20%,IF(A3<=7500,(7500-A3)*30%,IF(A3<=10000,(10000-A3)*40%,IF(A3>10000,(10000-A3)*50%,"Failed")))))))

 

[vikas_newports]

asdf.xlsx
	A	B	C	D	E	F	G
3	Revenue	Fixed	Extra	Bonus	Fixed Revenue	Bonus Revenue	Final
4	1800	5%	0	0	90	0	90
5	2800	5%	200	10%	140	20	160
6	3100	5%	900	15%	155	135	290
7	4400	5%	600	20%	220	120	340
8	6500	5%	1000	30%	325	300	625
9	7800	5%	2200	40%	390	880	1270
10	11000	5%	1000	50%	550	500	1050
Sheet1

Cell Formulas
Range	Formula
E4:E10	E4	=A4*B4
F4:F10	F4	=C4*D4
G4:G10	G4	=E4+F4
C5	C5	=3000-A5
C6	C6	=4000-A6
C7	C7	=5000-A7
C8	C8	=7500-A8
C9	C9	=10000-A9
C10	C10	=A10-10000

 

[Dave Patton]

I calculate different amounts.
Compare the formula in column F with your formulas.

I also showed the SumProduct results.

T202012a.xlsm
	A	B	C	D	E	F
1				Brackets	Rate	11,000.00
2
3	Revenue	Share		0.00	5%	100.00
4	1,800.00	90.00		2,000.00	10%	100.00
5	2,800.00	180.00		3,000.00	15%	150.00
6	3,100.00	215.00		4,000.00	20%	200.00
7	4,400.00	430.00		5,000.00	30%	750.00
8	6,500.00	1,000.00		7,500.00	40%	1,000.00
9	7,800.00	1,420.00		10,000.00	50%	500.00
10	11,000.00	2,800.00				2,800.00
11
3c

Cell Formulas
Range	Formula
B4:B10	B4	=SUMPRODUCT(--(A4>aB),A4-aB,aR)
F3:F9	F3	=MAX(0,(MIN($F$1,D4)-D3)*E3)
F10	F10	=SUM(F3:F9)

 

[vikas_newports]

@Dave Patton I need to revised my calculations as I got the right click (right ans)
for example
If revenue is 14,000 the calculation for my pay is (2000x5%)+ (1000x10%)+(1000x15%)+(1000x20%)+(2500x30%)+(2500x40%)+(4000x50%) = $4,160

if revenue is 2,400 the calculation for my pay is (2000x5%)+(400x10%) = $140

Please note above method of array is new to me I am not getting properly it