Thursday, August 20, 2015

Excel formula to calculate commission on sales for different slabs


HI,

Although a flat-rate sales commission is fairly straightforward to calculate, most modern commission plans include tiers and bonus plans. In this post I will explain how to calculate a commission payout where slab structure is involved. Calculating commissions for as slab structure is difficult because you are required to calculate commission for different slabs at the different rate.

Below given us slab used for given example:

Sales Amount
Commission %
20000
0%
30000
5%
40000
7%
50000
10%
60000
12%
70000
13%
80000
14%
90000
15%
100000
16%
110000
17%
120000
18%
130000
19%
99999999
19%

so for commission for sales upto 20k is nil, for next 10k it is 5% of amount in excess of 20k and so on..

Refer excel attachment for formula & in 1st sheet & example for understanding in second sheet.

Cheers!!


Click to Download