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.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
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
ترخیص کالا
ترخیص کالا
Calculating A Bonus Or Commission In Excel | Experiments in Finance
Top 5 Free Online Courses With Certificates In 2019
Top 5 Free Online Courses With Certificates In 2019
Calculating A Bonus Or Commission In Excel | Experiments in Finance
galvanized metal roofing
galvanized metal roofing
Experiments in Finance
download
download
Experiments in Finance