Archive for April, 2016

# Calculating A Bonus Or Commission In Excel

### Excel function tutorials

One of the recurring type of requests that I receive through managing this blog is trying to calculate a commission or bonus structure, especially by managers that have a sales force. I did touch on the subject a bit but I thought I’d do a quick review of the different type of commissions and how I’d calculate them in Excel. I am also providing an Excel file that you can view here if you can’t wait:) Here a few types of commissions and how I’d do them:

Flat Commission

In this structure, a sales person would get a % of sales no matter what. This is the most simple case:

Flat Commission With A Floor

In this structure, a sales person would get a % of sales but only under the condition that they reach a minimum of sales:

Flat Commission With A Ceiling

As much as companies like to reward star employees, some are worried about the idea that a sales guy would end up making more than the president so they end up adding a maximum/ceiling that someone can earn:

Brackets

Here is a common system that is used. If you generate 10K in sales, you will earn X%. If you earn 25%, you will earn X% of your sales, etc. Here I will use the Nested If excel function:

=IF(A10<\$A\$2,\$B\$2*A10,IF(A10<\$A\$3,\$B\$3*A10,IF(A10<\$A\$4,A10*\$B\$4,A10*\$B\$5)))

Scaling Bonus

The scaling bonus looks similar but is different in the sense that if you look at the same chart:

This time, if you make \$25K in sales, you will get 3% of the first 10,000 and 8% on the next 15,000. This is slightly more complicated. Here is how I will do it, step by step:

First I will determine the amounts you would make at each number of the scale:

Then, I will determine the appropriate “level” and add anything incremental by using nested if conditions:

=IF(A11<\$A\$3,A11*\$B\$2,IF(A11<\$A\$4,\$C\$3+(A11-\$A\$3)*\$B\$3,IF(A11<\$A\$5,\$C\$4+(A11-\$A\$4)*\$B\$4,IF(A11<\$A\$6,\$C\$5+(A11-\$A\$5)*\$B\$5,(A11-\$A\$6)*B6+\$C\$6))))

As is always the case, you can Bonus_Commission.