Trying to create a comp plan for sales reps

[Robert Brown]

I have a spreadsheet that has the following numbers that are entered each
time I create a comp plan for sales reps. Those are:

Salary
Commission
Quota

Using the commission and the quota, I want to calculate the rate. OK,
that's easy, commission divided by quota equals rate.

But here's where I'm having trouble. We use a graduated scale against
quota. That is:

from 0-50% of plan, you'll be paid at 40% your full rate
from 50-75% of plan, you'll be paid at 65% your full rate
from 75-100% of plan, you'll be paid at the full rate
from 100-125% of plan, you'll be paid at 150% of your full rate
above 125% of plan, you'll be paid at 210% of your full rate

What I want to do is to create a spreadsheet that allows me to plug in the
commission and the quota and have it calculate each of these rates. So to
give an example:

Salary = $39k
Commission = $11,500
Quota = $4M

- from 0-50% of plan, the commission rate is .23% (this means you'd be paid
$4,600 commission if you hit exactly 50%)
- from 50-75% of plan, the commission rate is .28% (this means you'd be
paid $7,419 commission if you hit exactly 75%)
- from 75-100% of plan, the commission rate is .41% (this means you'd be
paid $11,500 commission if you hit exactly 100%)
- from 100-125% of plan, the commission rate is .58% (this means you'd be
paid $17,250 commission if you hit exactly 125%)
- above 125% of plan, the commission rate is .69% (this means you'd be paid
$24,150 commission if you hit exactly 150%)

Anyone have suggestions?
TIA, Robert

 

[Maistrye]

I have a spreadsheet that has the following numbers that are entered
each
time I create a comp plan for sales reps. Those are:

Salary
Commission
Quota

Using the commission and the quota, I want to calculate the rate. OK,
that's easy, commission divided by quota equals rate.

But here's where I'm having trouble. We use a graduated scale against
quota. That is:

from 0-50% of plan, you'll be paid at 40% your full rate
from 50-75% of plan, you'll be paid at 65% your full rate
from 75-100% of plan, you'll be paid at the full rate
from 100-125% of plan, you'll be paid at 150% of your full rate
above 125% of plan, you'll be paid at 210% of your full rate

What I want to do is to create a spreadsheet that allows me to plug in
the
commission and the quota and have it calculate each of these rates. So
to
give an example:

Salary = $39k
Commission = $11,500
Quota = $4M

- from 0-50% of plan, the commission rate is .23% (this means you'd be
paid
$4,600 commission if you hit exactly 50%)
- from 50-75% of plan, the commission rate is .28% (this means you'd
be
paid $7,419 commission if you hit exactly 75%)
- from 75-100% of plan, the commission rate is .41% (this means you'd
be
paid $11,500 commission if you hit exactly 100%)
- from 100-125% of plan, the commission rate is .58% (this means you'd
be
paid $17,250 commission if you hit exactly 125%)
- above 125% of plan, the commission rate is .69% (this means you'd be
paid
$24,150 commission if you hit exactly 150%)

Anyone have suggestions?
TIA, Robert

I took a guess as to what you meant. Try the attached spreadsheet.

Scott


+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5061 |
+-------------------------------------------------------------------+

 

[Guest]

Thanks, but the link doesn't seem to be working. Is there somewhere else you
can post it or can you email it directly to me?

 

[Maistrye]

Try this link, you should be able to get to the attachment throug
here.

http://www.excelforum.com/showthread.php?t=563112

Scot