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:

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


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

23 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



argosdiscountcodeexpert.wordpress.com

argosdiscountcodeexpert.wordpress.com

Calculating A Bonus Or Commission In Excel | Experiments in Finance



تمر هندي

This has been such great help to me, thanks.



oppnarticles.com

oppnarticles.com

Calculating A Bonus Or Commission In Excel | Experiments in Finance



http://sns.ysten.com/space.php?uid=1240&do=blog&id=2286

http://sns.ysten.com/space.php?uid=1240&do=blog&id=2286

Calculating A Bonus Or Commission In Excel | Experiments in Finance



1stwedding.cn

1stwedding.cn

Calculating A Bonus Or Commission In Excel | Experiments in Finance



Mypsychscene.com

Mypsychscene.com

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.