Future Revenue Earning Based on Scenarios

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,678
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
" 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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,678
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,678
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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})
 
Solution

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,678
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Thanks for the feedback.
 

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
Feb 15, 2002
Messages
4,678
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,678
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
" 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,323
Messages
5,641,530
Members
417,215
Latest member
Diaryman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top