Need Help on Incentive Scheme Calculation

I

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

[TABLE="width: 344"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Target Achievement[/TD]
[TD] Payout[/TD]
[/TR]
[TR]
[TD]Revenue [/TD]
[TD]100%[/TD]
[TD] 100%[/TD]
[/TR]
[TR]
[TD]Revenue [/TD]
[TD]90%-99%[/TD]
[TD] 60%[/TD]
[/TR]
[TR]
[TD]Revenue [/TD]
[TD]80%-89%[/TD]
[TD] 20%[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 344"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Target Achivement[/TD]
[TD] Payout[/TD]
[/TR]
[TR]
[TD]Gross Profit[/TD]
[TD]100%[/TD]
[TD] 100%[/TD]
[/TR]
[TR]
[TD]Gross Profit[/TD]
[TD]90%-99%[/TD]
[TD] 60%[/TD]
[/TR]
[TR]
[TD]Gross Profit[/TD]
[TD]80%-89%[/TD]
[TD] 20%[/TD]
[/TR]
</tbody>[/TABLE]

Please help. thanks in advance. need this urgent.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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)))
 
Upvote 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
 
Upvote 0
[TABLE="width: 1026"]
<colgroup><col span="2"><col><col><col><col><col span="4"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Target Achievement
[/TD]
[TD]Payout[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mytable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Revenue[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]achievement%[/TD]
[TD="colspan: 2"]incentive%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Revenue[/TD]
[TD]90%-99%[/TD]
[TD="align: right"]60%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Revenue[/TD]
[TD]80%-89%[/TD]
[TD="align: right"]20%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0.015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0.025[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]Target Achivement[/TD]
[TD]Payout[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]0.026[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Gross Profit[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]0.027[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Gross Profit[/TD]
[TD]90%-99%[/TD]
[TD="align: right"]60%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0.028[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Gross Profit[/TD]
[TD]80%-89%[/TD]
[TD="align: right"]20%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0.029[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0.031[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]0.031[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]name[/TD]
[TD]REV Target %[/TD]
[TD]GP Target %[/TD]
[TD]REVENUE[/TD]
[TD]incentive[/TD]
[TD]BONUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]andy[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]1750000[/TD]
[TD="align: right"]0.0265[/TD]
[TD="align: right"]46375[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]bill[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]0.0205[/TD]
[TD="align: right"]41000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]colin[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]2250000[/TD]
[TD="align: right"]0.0125[/TD]
[TD="align: right"]28125[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]you can change the bands[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]dave[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]2500000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]and the %'s as you wish[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ed[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]2750000[/TD]
[TD="align: right"]0.015[/TD]
[TD="align: right"]41250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]fred[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]3000000[/TD]
[TD="align: right"]0.021[/TD]
[TD="align: right"]63000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]geoge[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]3250000[/TD]
[TD="align: right"]0.0175[/TD]
[TD="align: right"]56875[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]harry[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]7550000[/TD]
[TD="align: right"]0.015[/TD]
[TD="align: right"]113250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ian[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]3250000[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]32500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]james[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]3650000[/TD]
[TD="align: right"]0.005[/TD]
[TD="align: right"]18250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]assumption - the 2 incentive%'s are averaged[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]note if you do NOT give extra incentive for beyond 100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]your salesmen will not strive for more business as it will not be worth anything to them[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
forget that, i've misread your post.
apology

AlanY said:
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)))
Click to expand...

AlanY said:
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
Click to expand...
 
Last edited:
Upvote 0
AlanY said:
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10500000[/TD]
[TD="align: right"]26%[/TD]
[TD="align: right"]2%[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10000000[/TD]
[TD="align: right"]24%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]32760[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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%
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10000000[/TD]
[TD="align: right"]24%[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]52000[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD]=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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]






​


 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VLOOKUP Help for Report Card Grades Incentive Program
Replies
8
Views
442
mcgonma
M
T
  • Question Question
Commissions Calculation Excel
Replies
3
Views
609
etaf
etaf
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
L
  • Solved
Need A Help With My formula
Replies
3
Views
207
Cubist
Cubist
A
  • Question Question
create report for 40000 rows to calculation stock across sheet based on sheet name in cell value
Replies
1
Views
339
Amer Omar
A

Forum statistics

Threads
1,222,248
Messages
6,164,814
Members
451,918
Latest member
Pinnacleinfotechcad

Share this page

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
Back
Top