Calculating A Bonus Or Commission In Excel

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel 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:

1

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:

2

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:

3

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))) 4

Scaling Bonus

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

5

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:

6

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)))) 7

As is always the case, you can Bonus_Commission.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

17 Feedbacks on "Calculating A Bonus Or Commission In Excel"

bloodyanal cockcream

winkie winkie



Teenyweenyurl.Com

Teenyweenyurl.Com

Calculating A Bonus Or Commission In Excel | Experiments in Finance



john lewis plc london

john lewis plc london

Calculating A Bonus Or Commission In Excel | Experiments in Finance



where can i find cheap furniture in enterprise al

where can i find cheap furniture in enterprise al

Calculating A Bonus Or Commission In Excel | Experiments in Finance



it-42.ru

it-42.ru

Calculating A Bonus Or Commission In Excel | Experiments in Finance



online store india laptop

online store india laptop

Calculating A Bonus Or Commission In Excel | Experiments in Finance



Cheap sofas n.ireland

Cheap sofas n.ireland

Calculating A Bonus Or Commission In Excel | Experiments in Finance



Http://hbshxs.cn/comment/html/index.php?page=1&id=20866

http://hbshxs.cn/comment/html/index.php?page=1&id=20866

Calculating A Bonus Or Commission In Excel | Experiments in Finance



http://acm.rsu.edu.ru/wiki/index.php/Electricals_Items_That_Can_Be_Automated_In_And_About_The_Residence

http://acm.rsu.edu.ru/wiki/index.php/Electricals_Items_That_Can_Be_Automated_In_And_About_The_Residence

Calculating A Bonus Or Commission In Excel | Experiments in Finance



john lewis Ga congressman

john lewis Ga congressman

Calculating A Bonus Or Commission In Excel | Experiments in Finance



white bedroom furniture for teenage girls

white bedroom furniture for teenage girls

Calculating A Bonus Or Commission In Excel | Experiments in Finance



online shopping games for teenagers

online shopping games for teenagers

Calculating A Bonus Or Commission In Excel | Experiments in Finance



wiki.Lcs.com.vn

wiki.Lcs.com.vn

Calculating A Bonus Or Commission In Excel | Experiments in Finance



namnn.ru

namnn.ru

Calculating A Bonus Or Commission In Excel | Experiments in Finance



hookah online shop uk

hookah online shop uk

Calculating A Bonus Or Commission In Excel | Experiments in Finance



cheap furniture outlets in virginia

cheap furniture outlets in virginia

Calculating A Bonus Or Commission In Excel | Experiments in Finance



how to make garden furniture from logs

how to make garden furniture from logs

Calculating A Bonus Or Commission In Excel | Experiments in Finance



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.