Sales Commission Calculation
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" wrote:
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
|