Help creating a sales commission formula

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
 
C

Claus Busch

I

isabelle

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sliding Commission Formula 2
Calculate tiered commisions on commulative monthly sales 1
Earnings based on gross commission with split levels 2
Date() help 2
Desperately needing help... 4
Sales Commission Calculation 2
Calculating of Sales Commission 0
Calculation of Sales Commission 1

Top