# 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
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 PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### vikas_newports

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

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)

#### vikas_newports

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

#### Dave Patton

##### Well-known Member

"
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
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
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
@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?​
 ​

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,507
Messages
5,837,751
Members
430,515
Latest member
K_Lynch

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