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]

Try the suggestions. Check your calculation for 14000.

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

What did you try?
What part is new to you?

 

[vikas_newports]

@Dave Patton I get the point how you are doing name of range aB and aR .. it was doing it wrong earlier
yes the above method of calculation is new to mee too
I am trying to break it into part then take the same of it but failing

 

[Dave Patton]

"I get the point how you are doing name of range aB and aR .. it was doing it wrong earlier
yes the above method of calculation is new to mee too
I am trying to break it into part then take the same of it but failing"

It would help if you install the XL2BB and post your information.
I can provide more information but I do not know what you are trying.

 

[vikas_newports]

@Dave Patton
I am trying to construct a workbook but not properly managing it but still here is the raw working
I think we need to automate it as per CAP and cap will be decied upon inputted amount

asdf.xlsx
	A	B	C	D	E	F
3	Revenue	Fixed	Cap	Bonus	Fixed Revenue	Bonus Revenue
4	2000	0.05	0	0	100	0
5	3000	0.05	1000	0.1	150	100
6	4000	0.05	1000	0.15	200	150
7	5000	0.05	1000	0.2	250	200
8	7500	0.05	2500	0.3	375	750
9	10000	0.05	2500	0.4	500	1000
10	14000	0.05	4000	0.5	700	2000
11					Total	4200
Sheet2

Cell Formulas
Range	Formula
C5:C10	C5	=A5-A4
E4:E10	E4	=A4*B4
F4:F10	F4	=C4*D4
F11	F11	=SUM(F4:F10)

 

[Dave Patton]

Try either of the suggestions with 14000 and you will get 4300.
You formula also yields 4300 but you show 4160

T202012a.xlsm
	A	B	C	D	E	F
1				Brackets	Rate	14,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%	2,000.00
10	14,000.00	4,300.00		1E+308		4,300.00
11	20,000.00	7,300.00
3c

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

 

[vikas_newports]

What is big number @Dave Patton
Yes it is 4200 my manual calculation mistake

 

[Dave Patton]

"
What is big number @Dave Patton
Yes it is 4200 my manual calculation mistake"

BigNum is the largest number Excel can use in a cell. =9.99999999999999E+307

Does the 4200 related to your question?

If you want to use either of the suggestions that I made, try the following.

- open or create a clean (unused) sheet
- with post #15 click on the icon below the F(x)
- paste the information into cell A1 of your sheet
- create the named ranges aB, aR, and BigNum as defined previously
- try the formula

The SumProduct formula calculates the amount for tiered commissions, taxes, etc. It can be structured with Tables etc.
I can expand or show you alternative structures.

 

[Dave Patton]

Tiered formula with a table.
The named arrays have the same information.
You can review the formula with Excel's Formula Evaluation.

T202012a.xlsm
	H	I	J	K	L
1				Brackets	Rate
2	Revenue	14,000.00
3	Share	4,300.00		0.00	5%
4				2,000.00	10%
5				3,000.00	15%
6				4,000.00	20%
7				5,000.00	30%
8				7,500.00	40%
9				10,000.00	50%
3c

Cell Formulas
Range	Formula
I3	I3	=SUMPRODUCT(--(I2>K3:K9),I2-K3:K9,L3:L9-L2:L8)

 

[Dave Patton]

T202012a.xlsm
	A	B	C	D	E	F
1				Brackets	Rate	25,600.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%	7,800.00
10	14,000.00	4,300.00		1E+308		10,100.00
11	25,600.00	10,100.00
12
13
14	Month	Revenue	Share	Earn
15	1	10,000.00	2,300.00	0.00
16	2	10,000.00	2,300.00	0.00
17	3	10,000.00	2,300.00	0.00
18	4	10,000.00	2,300.00	0.00
19	5	10,000.00	2,300.00	0.00
20	6	10,000.00	2,300.00	0.00
21	7	14,000.00	4,300.00	1,075.00
22	8	14,400.00	4,500.00	1,125.00
23	9	14,800.00	4,700.00	1,175.00
24	10	15,200.00	4,900.00	1,225.00
25	11	15,600.00	5,100.00	1,275.00
26	12	16,000.00	5,300.00	1,325.00
27	13	16,400.00	5,500.00	2,750.00
28	14	16,800.00	5,700.00	2,850.00
29	15	17,200.00	5,900.00	2,950.00
30	16	17,600.00	6,100.00	3,050.00
31	17	18,000.00	6,300.00	3,150.00
32	18	18,400.00	6,500.00	3,250.00
33	19	18,800.00	6,700.00	3,350.00
34	20	19,200.00	6,900.00	3,450.00
35	21	19,600.00	7,100.00	3,550.00
36	22	20,000.00	7,300.00	3,650.00
37	23	20,400.00	7,500.00	3,750.00
38	24	20,800.00	7,700.00	3,850.00
39	25	21,200.00	7,900.00	5,925.00
40	26	21,600.00	8,100.00	6,000.00
41	27	22,000.00	8,300.00	6,000.00
42	28	22,400.00	8,500.00	6,000.00
43	29	22,800.00	8,700.00	6,000.00
44	30	23,200.00	8,900.00	6,000.00
45	31	23,600.00	9,100.00	6,000.00
46	32	24,000.00	9,300.00	6,000.00
47	33	24,400.00	9,500.00	6,000.00
48	34	24,800.00	9,700.00	6,000.00
49	35	25,200.00	9,900.00	6,000.00
50	36	25,600.00	10,100.00	6,000.00
3c

Cell Formulas
Range	Formula
D10	D10	=BigNum
F3:F9	F3	=MAX(0,(MIN($F$1,D4)-D3)*E3)
F10	F10	=SUM(F3:F9)
C15:C50,B4:B11	B4	=SUMPRODUCT(--(A4>aB),A4-aB,aR)
D15:D21	D15	=IF(A15<=6,0,IF(A15<=12,MIN(0.25*C15,2000),IF(A15<=24,MIN(0.5*C15,4000),IF(A15<=36,MIN(0.75*C15,6000),0))))
D22:D50	D22	=IF(A22<6,0,IF(A22<=12,MIN(0.25*C22,2000),IF(A22<=24,MIN(0.5*C22,4000),IF(A22<=36,MIN(0.75*C22,6000),0))))

 

[vikas_newports]

@Dave Patton I am getting error as I am not getting =BIGNUM
is it function or something else how can i get it. currently it is

#NAME?​

​