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?
 

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
Joined
Feb 15, 2002
Messages
4,666
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Has your question solved? If not why have you marked it as such?
 

Dave Patton

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

ADVERTISEMENT

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

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,288
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.
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