# Need Help on Incentive Scheme Calculation

#### Icecube

##### New Member
Hi Folks, I badly need help to calculate quarterly incentives for my sales team in outdoor as well as in retail. I am really bad in excel scenario calculations, hence i am requesting for urgent help.

I have to allocate 2 criteria's for them to be able to qualify for incentives.
1st Criteria is Revenue & the 2nd Criteria is Gross Profit

Considering a sample case where a salesman needs to achieve a Revenue target of AED 10,500,000 & needs to make minimum 26% Gross Profit i.e AED 2,730,000. So if he does 100% of revenue target and get 26% GP...then he will be eligible for 0.02% incentive on GP value of 2,730,000 i.e AED 54,600

But now the conditions where i need help is if he does not fulfill one of the 2 criteria's what would be the incentive amount on the below conditions set for both the criteria's

 Target Achievement Payout Revenue 100% 100% Revenue 90%-99% 60% Revenue 80%-89% 20%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 Target Achivement Payout Gross Profit 100% 100% Gross Profit 90%-99% 60% Gross Profit 80%-89% 20%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### AlanY

##### Well-known Member
based on your example I assume it's 2% incentive rather than .02%.
have a look to see if it meets your requirement

Excel 2012
ABC
11050000026%2%
21000000024%
332760
Sheet1
Cell Formulas
RangeFormula
B3=IF(AND(A2/A1>=1,B2>=0.26),C1*B1*A1,IF(A2/A1>=0.9,0.6*C1*B1*A1,IF(A2/A1>=0.8,0.2*B1*A1,0)))

#### AlanY

##### Well-known Member
this should also work

Excel 2012
AB
11000000024%
2
352000
Sheet1
Cell Formulas
RangeFormula
B3=IF(B1>=0.26,1,IFERROR(LOOKUP(A1/1050000,{0.8,0.9,1},{0.2,0.6,1}),0))*A1*0.26*0.02

#### oldbrewer

##### Well-known Member
 Target Achievement Payout mytable Revenue 100% 100% achievement% incentive% Revenue 90%-99% 60% 1 0 Revenue 80%-89% 20% 80 0.01 90 0.015 99 0.02 100 0.025 Target Achivement Payout 101 0.026 Gross Profit 100% 100% 120 0.027 Gross Profit 90%-99% 60% 150 0.028 Gross Profit 80%-89% 20% 200 0.029 300 0.03 500 0.031 20000 0.031 name REV Target % GP Target % REVENUE incentive BONUS andy 112 123 1750000 0.0265 46375 bill 93 110 2000000 0.0205 41000 colin 87 95 2250000 0.0125 28125 you can change the bands dave 75 74 2500000 0 0 and the %'s as you wish ed 94 96 2750000 0.015 41250 fred 125 96 3000000 0.021 63000 geoge 100 84 3250000 0.0175 56875 harry 91 97 7550000 0.015 113250 ian 82 85 3250000 0.01 32500 james 83 77 3650000 0.005 18250 assumption - the 2 incentive%'s are averaged note if you do NOT give extra incentive for beyond 100% your salesmen will not strive for more business as it will not be worth anything to them

<colgroup><col span="2"><col><col><col><col><col span="4"><col><col span="2"></colgroup><tbody>
</tbody>

#### AlanY

##### Well-known Member

apology

based on your example I assume it's 2% incentive rather than .02%.
have a look to see if it meets your requirement

Excel 2012
ABC
11050000026%2%
21000000024%
332760
Sheet1
Cell Formulas
RangeFormula
B3=IF(AND(A2/A1>=1,B2>=0.26),C1*B1*A1,IF(A2/A1>=0.9,0.6*C1*B1*A1,IF(A2/A1>=0.8,0.2*B1*A1,0)))

this should also work

Excel 2012
AB
11000000024%
2
352000
Sheet1
Cell Formulas
RangeFormula
B3=IF(B1>=0.26,1,IFERROR(LOOKUP(A1/1050000,{0.8,0.9,1},{0.2,0.6,1}),0))*A1*0.26*0.02

Last edited:

#### Icecube

##### New Member
based on your example I assume it's 2% incentive rather than .02%.
have a look to see if it meets your requirement

Excel 2012
ABC
11050000026%2%
21000000024%
332760

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B3=IF(AND(A2/A1>=1,B2>=0.26),C1*B1*A1,IF(A2/A1>=0.9,0.6*C1*B1*A1,IF(A2/A1>=0.8,0.2*B1*A1,0)))

</tbody>

<tbody>
</tbody>
Thanks a ton alan but the formula is giving an error however just to clarify that the incentive is actually 0.02% of the GP%

#### AlanY

##### Well-known Member

but 2% of 2730000 = 54600.

3rd try

Excel 2012
AB
11000000025%
219656
Sheet1
Cell Formulas
RangeFormula
B2=IFERROR(LOOKUP(A1/10500000,{0.8,0.9,1},{0.2,0.6,1})*LOOKUP(B1/0.26,{0,0.8,0.9,1},{0,0.2,0.6,1})*10500000*0.26*0.02,0)

Last edited:

#### AlanY

##### Well-known Member

Code:
``=LOOKUP(A1/10500000,{0,0.8,0.9,1},{0,0.2,0.6,1})*LOOKUP(B1/0.26,{0,0.8,0.9,1},{0,0.2,0.6,1})*10500000*0.26*0.02``

Last edited:

#### Icecube

##### New Member
Oh yeah youre right mate...m totally going nutters on my sheet btw on your second solution below,

If i am changing the percentage of GP, the total is still remaining the same. I mean right now you have it set as 24%, if i lower it down to say 20%, the total figure should change right? and the same should be functional if i change the revenue which is working fine but the total doesn't change when i change the GP% age. Help me out here mate. m stranded!
Excel 2012
AB
11000000024%
2
352000

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B3=IF(B1>=0.26,1,IFERROR(LOOKUP(A1/1050000,{0.8,0.9,1},{0.2,0.6,1}),0))*A1*0.26*0.02

</tbody>

<tbody>
</tbody>

#### Icecube

##### New Member
Cheers Alan...You're a Star!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,943
Messages
5,856,438
Members
431,814
Latest member
qualitypavingstone

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back