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]

" I am getting error as I am not getting =BIGNUM"

see previous information
- create the named ranges aB, aR, and BigNum as defined previously

or just put a very large number in the cell. Revenue probably will not exceed 10000000 so use that number.

 

[Dave Patton]

T202012a.xlsm
	A	B	C	D	E
14
15		Month_	Rate_a	Upper_a	Rate_l
16		0	0%	0	0%
17		7	25%	2,000	10%
18		13	50%	4,000	20%
19		25	75%	6,000	30%
20
21	Month	Revenue	Share	Earn_A	Earn_L
22	1	10,000.00	2,300.00	0.00	0.00
23	2	10,000.00	2,300.00	0.00	0.00
24	3	10,000.00	2,300.00	0.00	0.00
25	4	10,000.00	2,300.00	0.00	0.00
26	5	10,000.00	2,300.00	0.00	0.00
27	6	10,000.00	2,300.00	0.00	0.00
28	7	14,000.00	4,300.00	1,075.00	430.00
29	8	14,400.00	4,500.00	1,125.00	450.00
30	9	14,800.00	4,700.00	1,175.00	470.00
31	10	15,200.00	4,900.00	1,225.00	490.00
32	11	15,600.00	5,100.00	1,275.00	510.00
33	12	16,000.00	5,300.00	1,325.00	530.00
34	13	16,400.00	5,500.00	2,750.00	1,100.00
35	14	16,800.00	5,700.00	2,850.00	1,140.00
36	15	17,200.00	5,900.00	2,950.00	1,180.00
37	16	17,600.00	6,100.00	3,050.00	1,220.00
38	17	18,000.00	6,300.00	3,150.00	1,260.00
39	18	18,400.00	6,500.00	3,250.00	1,300.00
40	19	18,800.00	6,700.00	3,350.00	1,340.00
41	20	19,200.00	6,900.00	3,450.00	1,380.00
42	21	19,600.00	7,100.00	3,550.00	1,420.00
43	22	20,000.00	7,300.00	3,650.00	1,460.00
44	23	20,400.00	7,500.00	3,750.00	1,500.00
45	24	20,800.00	7,700.00	3,850.00	1,540.00
46	25	21,200.00	7,900.00	5,925.00	2,370.00
47	26	21,600.00	8,100.00	6,000.00	2,430.00
48	27	22,000.00	8,300.00	6,000.00	2,490.00
49	28	22,400.00	8,500.00	6,000.00	2,550.00
50	29	22,800.00	8,700.00	6,000.00	2,610.00
51	30	23,200.00	8,900.00	6,000.00	2,670.00
52	31	23,600.00	9,100.00	6,000.00	2,730.00
53	32	24,000.00	9,300.00	6,000.00	2,790.00
54	33	24,400.00	9,500.00	6,000.00	2,850.00
55	34	24,800.00	9,700.00	6,000.00	2,910.00
56	35	25,200.00	9,900.00	6,000.00	2,970.00
57	36	25,600.00	10,100.00	6,000.00	3,030.00
58				118,725.00	51,120.00
3c

Cell Formulas
Range	Formula
D22:D57	D22	=(A22>0)*MIN(LOOKUP(A22,Month_,Rate_a)*C22,LOOKUP(A22,Month_,Upper_a))
E22:E57	E22	=(A22>0)*MIN(LOOKUP(A22,Month_,Rate_l)*C22,LOOKUP(A22,Month_,Upper_a))
D58:E58	D58	=SUM(D22:D57)
C22:C57	C22	=SUMPRODUCT(--(B22>aB),B22-aB,aR)

Named Ranges
Name	Refers To	Cells
Month_	='3c'!$B$16:$B$19	D22:E57
Rate_a	='3c'!$C$16:$C$19	D22:D57
Rate_l	='3c'!$E$16:$E$19	E22:E57
Upper_a	='3c'!$D$16:$D$19	D22:E57
Yr	='3c'!$B$16:$B$19	D22:E57

 

[Dave Patton]

T202012a.xlsm
	A	B	C	D	E
21	Month	Revenue	Share	Earn_A	Earn_L
22	1	10,000.00	2,300.00	0.00	0.00
23	2	10,000.00	2,300.00	0.00	0.00
24	3	10,000.00	2,300.00	0.00	0.00
25	4	10,000.00	2,300.00	0.00	0.00
26	5	10,000.00	2,300.00	0.00	0.00
27	6	10,000.00	2,300.00	0.00	0.00
28	7	14,000.00	4,300.00	1,075.00	430.00
29	8	14,400.00	4,500.00	1,125.00	450.00
30	9	14,800.00	4,700.00	1,175.00	470.00
31	10	15,200.00	4,900.00	1,225.00	490.00
32	11	15,600.00	5,100.00	1,275.00	510.00
33	12	16,000.00	5,300.00	1,325.00	530.00
34	13	16,400.00	5,500.00	2,750.00	1,100.00
35	14	16,800.00	5,700.00	2,850.00	1,140.00
36	15	17,200.00	5,900.00	2,950.00	1,180.00
37	16	17,600.00	6,100.00	3,050.00	1,220.00
38	17	18,000.00	6,300.00	3,150.00	1,260.00
39	18	18,400.00	6,500.00	3,250.00	1,300.00
40	19	18,800.00	6,700.00	3,350.00	1,340.00
41	20	19,200.00	6,900.00	3,450.00	1,380.00
42	21	19,600.00	7,100.00	3,550.00	1,420.00
43	22	20,000.00	7,300.00	3,650.00	1,460.00
44	23	20,400.00	7,500.00	3,750.00	1,500.00
45	24	20,800.00	7,700.00	3,850.00	1,540.00
46	25	21,200.00	7,900.00	5,925.00	2,370.00
47	26	21,600.00	8,100.00	6,000.00	2,430.00
48	27	22,000.00	8,300.00	6,000.00	2,490.00
49	28	22,400.00	8,500.00	6,000.00	2,550.00
50	29	22,800.00	8,700.00	6,000.00	2,610.00
51	30	23,200.00	8,900.00	6,000.00	2,670.00
52	31	23,600.00	9,100.00	6,000.00	2,730.00
53	32	24,000.00	9,300.00	6,000.00	2,790.00
54	33	24,400.00	9,500.00	6,000.00	2,850.00
55	34	24,800.00	9,700.00	6,000.00	2,910.00
56	35	25,200.00	9,900.00	6,000.00	2,970.00
57	36	25,600.00	10,100.00	6,000.00	3,030.00
58				118,725.00	51,120.00
3c

Cell Formulas
Range	Formula
D22:D57	D22	=(A22>0)*MIN(LOOKUP(A22,{0;7;13;25},{0;0.25;0.5;0.75})*C22,LOOKUP(A22,{0;7;13;25},{0;2000;4000;6000}))
E22:E57	E22	=(A22>0)*MIN(LOOKUP(A22,{0;7;13;25},{0;0.1;0.2;0.3})*C22,LOOKUP(A22,{0;7;13;25},{0;2000;4000;6000}))
D58:E58	D58	=SUM(D22:D57)
C22:C57	C22	=SUMPRODUCT(--(B22>{0;2000;3000;4000;5000;7500;10000}),B22-{0;2000;3000;4000;5000;7500;10000},{0.05;0.05;0.05;0.05;0.1;0.1;0.1})

 

[vikas_newports]

@Dave Patton thanks its aweome

 

[Dave Patton]

Thanks for the feedback.

 

[vikas_newports]

@Dave Patton sorry to disturb you how can we calculate month wise for example If I leave at 16 months then how much I will get

 

[Dave Patton]

What did you try?
If the weeks 17 to 36 are blank, the calculations will show 0 for these weeks.
The total will show the income for weeks 1 -16.

You can calculate the week number.

 

[vikas_newports]

@Dave Patton great idea but how can I make them zero with any formula If I select directly months from a cell

 

[Dave Patton]

" great idea but how can I make them zero with any formula If I select directly months from a cell"

If you are using one of the suggestions, the formulas should still work.

You will have to provide some information; I cannot see your spreadsheet and I cannot guess at what you are trying.

Why didn't you post an extract of your information?

 

[vikas_newports]

@Dave Patton I assume to do it other option is that I try vlookup monthwise and get the value of it at specific month