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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Dave Patton

##### Well-known Member
Is this a class assignment?

Please post with XL2BB the layout of your data and the calculations that you have completed.

There are gaps in the information that you provided.

If the revenue is 50,000 what would your share be?
Are the calculations based on cumulative or monthly revenue?

#### vikas_newports

##### Board Regular
if revenue was 50K my payment would be \$22,300 every month
my payment changes every month based on the revenue
if revenue is 1,000 I get paid 50

I am trying to construct a workbook but dont know how to do

Last edited by a moderator:

#### Fluff

##### MrExcel MVP, Moderator
Has your question solved? If not why have you marked it as such?

#### Dave Patton

##### Well-known Member

T202012a.xlsm
AB
1
2RevenueShare
3\$50,000.00\$22,300.00
4
3c
Cell Formulas
RangeFormula
B3B3=SUMPRODUCT(--(A3>aB),A3-aB,aR)

Is this a class assignment? So you want us to do your assignment!

Please post with XL2BB the layout of your data and the calculations that you have completed. Not done.
Are the calculations based on cumulative or monthly revenue??

There are gaps in the information that you provided. ??

T202012a.xlsm
ABCDEFG
10
11Named arraysBracketsaB={0;2000;3000;4000;5000;7500;10000}
12Rate DiffaR={0.05;0.05;0.05;0.05;0.1;0.1;0.1}
3c

#### vikas_newports

##### Board Regular
Fluff , it was by mistaken clicked as solved
I tried to construct this formula for First Part and My date of joining is 01-Dec-2020
Excel Formula:
``=IF(A3<=2000,A3*0%,IF(A3<=3000,(A3-3000)*10%,IF(A3<=4000,(4000-A3)*15%,IF(A3<=5000,(5000-A3)*20%,IF(A3<=7500,(7500-A3)*30%,IF(A3<=10000,(10000-A3)*40%,IF(A3>10000,(10000-A3)*50%,"Failed")))))))``

#### vikas_newports

##### Board Regular

There are gaps in the information that you provided. ??

Dave, it is monthly and I am trying the below function for scenario 1

Excel Formula:
``=IF(A3<=2000,A3*0%,IF(A3<=3000,(A3-3000)*10%,IF(A3<=4000,(4000-A3)*15%,IF(A3<=5000,(5000-A3)*20%,IF(A3<=7500,(7500-A3)*30%,IF(A3<=10000,(10000-A3)*40%,IF(A3>10000,(10000-A3)*50%,"Failed")))))))``

#### Dave Patton

##### Well-known Member
I calculate different amounts.
Compare the formula in column F with your formulas.

I also showed the SumProduct results.

T202012a.xlsm
ABCDEF
1BracketsRate11,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%500.00
1011,000.002,800.002,800.00
11
3c
Cell Formulas
RangeFormula
B4:B10B4=SUMPRODUCT(--(A4>aB),A4-aB,aR)
F3:F9F3=MAX(0,(MIN(\$F\$1,D4)-D3)*E3)
F10F10=SUM(F3:F9)

#### vikas_newports

##### Board Regular
@Dave Patton I need to revised my calculations as I got the right click (right ans)
for example
If revenue is 14,000 the calculation for my pay is (2000x5%)+ (1000x10%)+(1000x15%)+(1000x20%)+(2500x30%)+(2500x40%)+(4000x50%) = \$4,160

if revenue is 2,400 the calculation for my pay is (2000x5%)+(400x10%) = \$140

Please note above method of array is new to me I am not getting properly it

Replies
11
Views
728
Replies
5
Views
160
Replies
2
Views
222
Replies
0
Views
308
Replies
2
Views
202

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,163,943
Messages
5,834,512
Members
430,291
Latest member
sunilbalan

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