New Sales Commission calculations

Please Help!!!

I am terrible when it comes to creating forumlas in Excell. My company is switching to a different, tiered based commission for our sales people.

We are a cell phone company, and our commission will be a sliding scale. The more gross profit the sales rep brings, the more they get paid for each phone sold.

Here are the ranges:

$0-500 GP $8 per phone
$501-$1000 $10 per phone
$1001-1500 $12 per phone
$1501-2000 $15 per phone
$2001-2500 $18 per phone
$2501+ $20 per phone

example:

John brings in $1700gp, and sells 17 phones, he would earn $255.
John brings in $900 gp, and sells 5 phones, he would earn $50.

We have 70 sales reps.

I really don't expect anyone to do the work for me, but if anyone can at least advise me where to start, I would really appreciate it.

Hi

Post a dummy worksheet for us if you can Im sure if I cant help you someone else will dont give real details but have the same layout as the company.

Chris

Ideally, it would be nice to change the scale per sales rep. Some reps are in higher trafficking areas, so expectations are different. Cheers to anybody that can help me out!!!

=lookup(c5,b$22:c$27)*d5

Hi

Ive updated your file with nested if statements it looks a lot more complex than the other solutions but once you have read through it a few times it is easy to understand, copy this formula for all sales reps you can then change the figures for the reps in high traffic areas etc.


if you need further help let me know


Chris

Hi Shg

This is a very helpful solution - i may be trying however to use this functionality in the wrong case ! i am trying to adapt it to a tiered cost basis where I ahve the following

- Minimum cost pa $1,000, regardless of account size

- When account size increases then the costs is a function of the account size (in aggregate), but subject to tiers as follows :

0 - 100,000 = 1%
100,001 - 200,000 = 0.5%
200,001 - 300,000 = 0.4%
300,001 - 400,000 = 0.3%
400,001 - 500,000 = 0.2%
500,001 + = 0.1%

The problem (i think) is when you bring in the tiering into play !!

Any comemnts welcome !

thank you