# Future Revenue Earning Based on Scenarios

#### vikas_newports

##### Board Regular
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

##### Well-known Member
" 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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Dave Patton

##### Well-known Member
T202012a.xlsm
ABCDE
14
15Month_Rate_aUpper_aRate_l
1600%00%
17725%2,00010%
181350%4,00020%
192575%6,00030%
20
21MonthRevenueShareEarn_AEarn_L
22110,000.002,300.000.000.00
23210,000.002,300.000.000.00
24310,000.002,300.000.000.00
25410,000.002,300.000.000.00
26510,000.002,300.000.000.00
27610,000.002,300.000.000.00
28714,000.004,300.001,075.00430.00
29814,400.004,500.001,125.00450.00
30914,800.004,700.001,175.00470.00
311015,200.004,900.001,225.00490.00
321115,600.005,100.001,275.00510.00
331216,000.005,300.001,325.00530.00
341316,400.005,500.002,750.001,100.00
351416,800.005,700.002,850.001,140.00
361517,200.005,900.002,950.001,180.00
371617,600.006,100.003,050.001,220.00
381718,000.006,300.003,150.001,260.00
391818,400.006,500.003,250.001,300.00
401918,800.006,700.003,350.001,340.00
412019,200.006,900.003,450.001,380.00
422119,600.007,100.003,550.001,420.00
432220,000.007,300.003,650.001,460.00
442320,400.007,500.003,750.001,500.00
452420,800.007,700.003,850.001,540.00
462521,200.007,900.005,925.002,370.00
472621,600.008,100.006,000.002,430.00
482722,000.008,300.006,000.002,490.00
492822,400.008,500.006,000.002,550.00
502922,800.008,700.006,000.002,610.00
513023,200.008,900.006,000.002,670.00
523123,600.009,100.006,000.002,730.00
533224,000.009,300.006,000.002,790.00
543324,400.009,500.006,000.002,850.00
553424,800.009,700.006,000.002,910.00
563525,200.009,900.006,000.002,970.00
573625,600.0010,100.006,000.003,030.00
58118,725.0051,120.00
3c
Cell Formulas
RangeFormula
D22:D57D22=(A22>0)*MIN(LOOKUP(A22,Month_,Rate_a)*C22,LOOKUP(A22,Month_,Upper_a))
E22:E57E22=(A22>0)*MIN(LOOKUP(A22,Month_,Rate_l)*C22,LOOKUP(A22,Month_,Upper_a))
D58:E58D58=SUM(D22:D57)
C22:C57C22=SUMPRODUCT(--(B22>aB),B22-aB,aR)
Named Ranges
NameRefers ToCells
Month_='3c'!\$B\$16:\$B\$19D22:E57
Rate_a='3c'!\$C\$16:\$C\$19D22:D57
Rate_l='3c'!\$E\$16:\$E\$19E22:E57
Upper_a='3c'!\$D\$16:\$D\$19D22:E57
Yr='3c'!\$B\$16:\$B\$19D22:E57

#### Dave Patton

##### Well-known Member
Cell Formulas
RangeFormula
D22:D57D22=(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:E57E22=(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:E58D58=SUM(D22:D57)
C22:C57C22=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})

#### Dave Patton

##### Well-known Member

Thanks for the feedback.

#### vikas_newports

##### Board Regular
@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

##### Well-known Member

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

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

#### Dave Patton

##### Well-known Member
" 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

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

Replies
0
Views
159
Replies
2
Views
98
Replies
6
Views
163
Replies
1
Views
330
Replies
6
Views
200

1,130,129
Messages
5,640,297
Members
417,135
Latest member
zeusmining

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back