In this tutorial, you will learn how to calculate the incentive amounts for sales team in Excel.
What is Sales Incentive?
Sales incentive is the extra amount of money payable to sales representatives for achieving the given target. There can be other incentives type like % of the sales revenue too, which is commonly known as commission.
Here, we are going to look into how to calculate the sales incentive for team based on the Given Target and Achievement of sales revenue.
Calculation of Sales Incentive
Look into below example, where there is a data of Target, Achievement and structure or criteria for eligibility of incentive.
Here in this example, the criteria for incentive is set based on Achievement %. Calculate the Ach % by simply dividing the Achievement by Target. Incentive eligibility criteria has 4 parameters ranging from 95% to 110% with different amounts.
To calculate the incentive amounts in F column, use the IFS Function of Excel. This function checks for multiple logics and returns value if the logic is true. You have to check the % Ach. in E column and compare this with the criteria, if it matches a specific criterion, it displays the corresponding amount.
In the F5 cell, write this formula to calculate the applicable incentive amount.
=IFS(E5>=$I$8,$J$8,E5>=$I$7,$J$7,E5>=$I$6,$J$6,E5>=$I$5,$J$5,E5<$I$5,0)
IFS function compares the % Ach with every condition here. The first logic in function is E5>=$I$8, it means IF 105% is greater than or equal to 110%, then display 2500 in result. But this logic fails here, so the formula will test for second logic, whenever the result of logic is true, it displays the “value if true” value.
What If You Have Old Excel Version
If you have the old excel version, you may not have the IFS function available. In this case you need to use the nested IF function. Nested IF is the combination of multiple IF statements inside IF. Write below formula in F5 cell in case you do not have the IF function available.
=IF(E5>=$I$8,$J$8,IF(E5>=$I$7,$J$7,IF(E5>=$I$6,$J$6,IF(E5>=$I$5,$J$5,IF(E5<95%,0)))))
Download the practice workbook file used here in this video from the link below. If you want to watch the step-by-step video tutorial, click on play button below to watch it in YouTube.
