Need Help on Incentive Scheme Calculation

Icecube

New Member
Joined
Feb 19, 2016
Messages
7
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 Profit100% 100%
Gross Profit90%-99% 60%
Gross Profit80%-89% 20%

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

Please help. thanks in advance. need this urgent.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
based on your example I assume it's 2% incentive rather than .02%.
have a look to see if it meets your requirement

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">10500000</td><td style="text-align: right;;">26%</td><td style="text-align: right;;">2%</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10000000</td><td style="text-align: right;;">24%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">32760</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2/A1>=1,B2>=0.26</font>),C1*B1*A1,IF(<font color="Red">A2/A1>=0.9,0.6*C1*B1*A1,IF(<font color="Green">A2/A1>=0.8,0.2*B1*A1,0</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
this should also work

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">10000000</td><td style="text-align: right;;">24%</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">52000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">B1>=0.26,1,IFERROR(<font color="Red">LOOKUP(<font color="Green">A1/1050000,{0.8,0.9,1},{0.2,0.6,1}</font>),0</font>)</font>)*A1*0.26*0.02</td></tr></tbody></table></td></tr></table><br />
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
Target Achievement
Payoutmytable
Revenue100%100%achievement%incentive%
Revenue90%-99%60%10
Revenue80%-89%20%800.01
900.015
990.02
1000.025

Target AchivementPayout1010.026
Gross Profit100%100%1200.027
Gross Profit90%-99%60%1500.028
Gross Profit80%-89%20%2000.029
3000.03
5000.031
200000.031
nameREV Target %GP Target %REVENUEincentiveBONUS
andy11212317500000.026546375
bill9311020000000.020541000
colin879522500000.012528125you can change the bands
dave7574250000000and the %'s as you wish
ed949627500000.01541250
fred1259630000000.02163000
geoge1008432500000.017556875
harry919775500000.015113250
ian828532500000.0132500
james837736500000.00518250
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
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

forget that, i've misread your post.
apology

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

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">10500000</td><td style="text-align: right;;">26%</td><td style="text-align: right;;">2%</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10000000</td><td style="text-align: right;;">24%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">32760</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2/A1>=1,B2>=0.26</font>),C1*B1*A1,IF(<font color="Red">A2/A1>=0.9,0.6*C1*B1*A1,IF(<font color="Green">A2/A1>=0.8,0.2*B1*A1,0</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

this should also work

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">10000000</td><td style="text-align: right;;">24%</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">52000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">B1>=0.26,1,IFERROR(<font color="Red">LOOKUP(<font color="Green">A1/1050000,{0.8,0.9,1},{0.2,0.6,1}</font>),0</font>)</font>)*A1*0.26*0.02</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Icecube

New Member
Joined
Feb 19, 2016
Messages
7
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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)))

<thead>
</thead><tbody>
</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
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

but 2% of 2730000 = 54600.

3rd try

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">10000000</td><td style="text-align: right;;">25%</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">19656</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">A1/10500000,{0.8,0.9,1},{0.2,0.6,1}</font>)*LOOKUP(<font color="Red">B1/0.26,{0,0.8,0.9,1},{0,0.2,0.6,1}</font>)*10500000*0.26*0.02,0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
sorry, having a bad day.
formula should read

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
Joined
Feb 19, 2016
Messages
7
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

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>








 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top