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.
 

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
Joined
Oct 30, 2014
Messages
4,350
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


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


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
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,350
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 30, 2014
Messages
4,350
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>








 
Master Excel Bundle

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

Threads
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.
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