Sales Commission Calculation

[C Thornton]

Hi,

I am trying to calculate a sales commission using excel, I can do bits of it
but cant get every break point to calculate.

The main variables are :

Sales Target : e.g. 120000
Actual Sales : 140000

Commission is calculated as follows:

Commission starts at 80% of target (96000)

0% commission is paid if sales fail to reach 80% of target.
1% commission is paid on sales from 80-90% of target
1.5% commission is paid on sales from 90-100% of target
after target is reached commission is paid in bands
Target plus 10000 2% of sales
Target plus 10000 - 20000 2.25% of sales
over 20000 2.5%

I will give a example based on the above

1% of 120000 = 120
1.5% of 120000 = 180
+ 10000 = 200
+ 10000 to 20000= 225

Therefore the total commission is 725.

Thanks in advance

Colin Thornton

 

[Brendan]

It's a long and ugly formula, but here goes:

=SUM((MIN(0.9*D10,D11)-0.8*D10)*0.01*IF(D11<0.8*D10,0,1),
(MIN(D10,D11)-0.9*D10)*0.015*IF(D11<0.9*D10,0,1),
(MIN(D10+10000,D11)-D10)*0.02*IF(D11<D10,0,1),
(MIN(D10+20000,D11)-(D10+10000))*0.0225*IF(D11<D10+10000,0,1),
(D11-(D10+20000))*0.025*IF(D11<D10+20000,0,1))

I have the target in D10 and the actual in D11. You might want to do a test
or two to be sure I got it right.

 

[C Thornton]

Brendan,

Thanks for your help, I cut and pasted the formula in to the cell and it
worked perfectly.

Your a star.

Thanks

Colin