Help creating a sales commission formula

  • Thread starter Thread starter rbrown999
  • Start date Start date
R

rbrown999

I am creating a tiered commission structure that pays reps thus:

0% - 80% of quota, will get them x% of a deal they bring in
80% - 110% of quota, will get them y% of a deal they bring in
110% - 999% of quota, will get them z% of a deal they bring in

So for example:

A quota is $2,000,000
In month 1, they sell $500,000
In month 2, they sell $100,000
In month 3, they sell $300,000
In month 4, they sell $100,000
In month 5, they sell $200,000
.... and so on ...

Here's what the data looks like in the spreadsheet:

Bookings level Quota Low Range Quota High Range Commission Rate
Tier 1 bookings 0% 80% 5.5%
Tier 2 bookings 80% 110% 8.5%
Tier 3 bookings 110% 999% 12.2%

I want to create a spreadsheet that the rep can use to plug their sales into a given month and have a formula calculate their commission against their plan on a monthly basis.

Can someone help me understand what that formula will look like?

TIA,
Rob
 
hi,

month sales 2 000 000.00 $
1 100 000.00 $ 5.5%
2 200 000.00 $ 5.5%
3 300 000.00 $ 5.5%
4 400 000.00 $ 5.5%
5 500 000.00 $ 5.5%
6 100 000.00 $ 8.5%

in range C2:
=INDEX({0.122,0.085,0.055},MATCH(SUM($B$1:B2)/$C$1,{9.9,1.1,0.75},-1))
and then fill down.

isabelle


Le 2014-03-28 11:13, (e-mail address removed) a écrit :
I am creating a tiered commission structure that pays reps thus:

0% - 80% of quota, will get them x% of a deal they bring in
80% - 110% of quota, will get them y% of a deal they bring in
110% - 999% of quota, will get them z% of a deal they bring in

So for example:

A quota is $2,000,000
In month 1, they sell $500,000
In month 2, they sell $100,000
In month 3, they sell $300,000
In month 4, they sell $100,000
In month 5, they sell $200,000
... and so on ...

Here's what the data looks like in the spreadsheet:

Bookings level Quota Low Range Quota High Range Commission Rate
Tier 1 bookings 0% 80% 5.5%
Tier 2 bookings 80% 110% 8.5%
Tier 3 bookings 110% 999% 12.2%

I want to create a spreadsheet that the rep can use to plug their sales into a given month
Click to expand...
and have a formula calculate their commission against their plan on a monthly basis.
 
Back
Top