# 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

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
and have a formula calculate their commission against their plan on a monthly basis.