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,673
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
@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

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

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
@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
ABCDEF
3RevenueFixedCapBonusFixed RevenueBonus Revenue
420000.05001000
530000.0510000.1150100
640000.0510000.15200150
750000.0510000.2250200
875000.0525000.3375750
9100000.0525000.45001000
10140000.0540000.57002000
11Total4200
Sheet2
Cell Formulas
RangeFormula
C5:C10C5=A5-A4
E4:E10E4=A4*B4
F4:F10F4=C4*D4
F11F11=SUM(F4:F10)
 

Dave Patton

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

ADVERTISEMENT

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

T202012a.xlsm
ABCDEF
1BracketsRate14,000.00
2
3RevenueShare0.005%100.00
41,800.0090.002,000.0010%100.00
52,800.00180.003,000.0015%150.00
63,100.00215.004,000.0020%200.00
74,400.00430.005,000.0030%750.00
86,500.001,000.007,500.0040%1,000.00
97,800.001,420.0010,000.0050%2,000.00
1014,000.004,300.001E+3084,300.00
1120,000.007,300.00
3c
Cell Formulas
RangeFormula
D10D10=BigNum
F3:F9F3=MAX(0,(MIN($F$1,D4)-D3)*E3)
F10F10=SUM(F3:F9)
B4:B11B4=SUMPRODUCT(--(A4>aB),A4-aB,aR)
 

Dave Patton

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

ADVERTISEMENT

"
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,673
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Tiered formula with a table.
The named arrays have the same information.
You can review the formula with Excel's Formula Evaluation.

T202012a.xlsm
HIJKL
1BracketsRate
2Revenue14,000.00
3Share4,300.000.005%
42,000.0010%
53,000.0015%
64,000.0020%
75,000.0030%
87,500.0040%
910,000.0050%
3c
Cell Formulas
RangeFormula
I3I3=SUMPRODUCT(--(I2>K3:K9),I2-K3:K9,L3:L9-L2:L8)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,673
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202012a.xlsm
ABCDEF
1BracketsRate25,600.00
2
3RevenueShare0.005%100.00
41,800.0090.002,000.0010%100.00
52,800.00180.003,000.0015%150.00
63,100.00215.004,000.0020%200.00
74,400.00430.005,000.0030%750.00
86,500.001,000.007,500.0040%1,000.00
97,800.001,420.0010,000.0050%7,800.00
1014,000.004,300.001E+30810,100.00
1125,600.0010,100.00
12
13
14MonthRevenueShareEarn
15110,000.002,300.000.00
16210,000.002,300.000.00
17310,000.002,300.000.00
18410,000.002,300.000.00
19510,000.002,300.000.00
20610,000.002,300.000.00
21714,000.004,300.001,075.00
22814,400.004,500.001,125.00
23914,800.004,700.001,175.00
241015,200.004,900.001,225.00
251115,600.005,100.001,275.00
261216,000.005,300.001,325.00
271316,400.005,500.002,750.00
281416,800.005,700.002,850.00
291517,200.005,900.002,950.00
301617,600.006,100.003,050.00
311718,000.006,300.003,150.00
321818,400.006,500.003,250.00
331918,800.006,700.003,350.00
342019,200.006,900.003,450.00
352119,600.007,100.003,550.00
362220,000.007,300.003,650.00
372320,400.007,500.003,750.00
382420,800.007,700.003,850.00
392521,200.007,900.005,925.00
402621,600.008,100.006,000.00
412722,000.008,300.006,000.00
422822,400.008,500.006,000.00
432922,800.008,700.006,000.00
443023,200.008,900.006,000.00
453123,600.009,100.006,000.00
463224,000.009,300.006,000.00
473324,400.009,500.006,000.00
483424,800.009,700.006,000.00
493525,200.009,900.006,000.00
503625,600.0010,100.006,000.00
3c
Cell Formulas
RangeFormula
D10D10=BigNum
F3:F9F3=MAX(0,(MIN($F$1,D4)-D3)*E3)
F10F10=SUM(F3:F9)
C15:C50,B4:B11B4=SUMPRODUCT(--(A4>aB),A4-aB,aR)
D15:D21D15=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:D50D22=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

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
@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?​
aaa.PNG
 

Watch MrExcel Video

Forum statistics

Threads
1,130,245
Messages
5,641,070
Members
417,192
Latest member
choomkey

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