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

 

[jayh13]

I searched everywhere for a solution to a similar problem. I also work with commission bonuses based on how much I earn. For example my target is $39,000, once I reach $39,000 I get 40% of every thing between $39,000-$60,000 and 45% of everything over $60,000. So I needed multiple IF statements for a single cell to punch out the amount I am actually receiving... finally nutted out the formula below, where A1 in the first example is my total above target (total above $39,000)... so A1 assumes I have reached target else it returns 0. Also, 8400 is 40% of $21,000, which is my first comm bracket (between 39K and 60K = 21000)

=IF(A1>21000,((A1-21000)*0.45 +8400), IF(OR(A1<=21000,A1>0),A1*0.4, 0))

You could also write it as below which calculates from my total gross, not my total above target which may be easier to see what's going on based on my above comm figures...


=IF(A1>60000,((A1-60000)*0.45 +8400), IF(OR(A1<=60000,A1>39000),A1*0.4, 0))

Hope that helps someone, it drove me crazy for weeks.