Need Help on Incentive Scheme Calculation

[Icecube]

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.

 

[Icecube]

Thanks oldbrewer!

 

[AlanY]

you'ew welcome.
glad that finally got it sorted

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1		AED	26%	Incentive
2	Target	10,500,000.00	2,730,000.00	0.02%
3	Actual	10,500,000.00	2,730,000.00			0%	0%
4		100%	100%	546.00		80%	20%
5						90%	60%
6	Actual	11,000,000.00	3,300,000.00	660.00		100%	100%
7	Actual	8,400,010.00	1,965,602.34	78.62
4c

Cell Formulas
Range	Formula
C2	=B2*$C$1
C3	=B3*$C$1
C4	=C3/C2
C6	=0.3*B6
C7	=B7*0.26*0.9
B4	=B3/B2
B7	=B3*0.8+10
D4	=LOOKUP(MIN(B4,C4),F3:G6)*C3*D2
D6	=LOOKUP(MIN(B6/$B$2,C6/B6/$C$1),$F$3:$G$6)*C6*$D$2
D7	=LOOKUP(MIN(B7/$B$2,C7/B7/$C$1),$F$3:$G$6)*C7*$D$2

The above uses .02% per your commentary.

 

[Icecube]

Dear Alan....need your help once again....I keyed in the formula you gave me which is what i wanted but there is now a small change that i require.

=LOOKUP(A9/10500000,{0,0.8,0.9,1},{0,0.2,0.6,1})*LOOKUP(B9/0.26,{0,0.8,0.9,1},{0,0.2,0.6,1})*10500000*0.26*0.02

But now what I want is 70% weightage to be given to Revenue and 30% weigthage to MC

[TABLE="width: 223"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Criteria 1[/TD]
[TD] Target Achivement[/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]
[TR]
[TD]Revenue [/TD]
[TD] Below 80[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Weight age 70%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 237"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Criteria 2[/TD]
[TD] Target Achivement[/TD]
[TD]Payout[/TD]
[/TR]
[TR]
[TD]MC 26% & Above[/TD]
[TD] 100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]MC 24%-25%[/TD]
[TD] 90-99[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]MC 21%- 23%[/TD]
[TD] 80-89[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]MC 20% & Below [/TD]
[TD] Below 80[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Weight age 30%[/TD]
[/TR]
</tbody>[/TABLE]

Can you help on this?

 

[Icecube]

Dear Alan....need your help once again....I keyed in the formula you gave me which is what i wanted but there is now a small change that i require.

=LOOKUP(A9/10500000,{0,0.8,0.9,1},{0,0.2,0.6,1})*LOOKUP(B9/0.26,{0,0.8,0.9,1},{0,0.2,0.6,1})*10500000*0.26*0.02

But now what I want is 70% weightage to be given to Revenue and 30% weigthage to MC

[TABLE="class: cms_table, width: 223"]
<tbody>[TR]
[TD]Criteria 1[/TD]
[TD]Target Achivement[/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]
[TR]
[TD]Revenue[/TD]
[TD]Below 80[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Weight age 70%[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 237"]
<tbody>[TR]
[TD]Criteria 2[/TD]
[TD]Target Achivement[/TD]
[TD]Payout[/TD]
[/TR]
[TR]
[TD]MC 26% & Above[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]MC 24%-25%[/TD]
[TD]90-99[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]MC 21%- 23%[/TD]
[TD]80-89[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]MC 20% & Below[/TD]
[TD]Below 80[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Weight age 30%[/TD]
[/TR]
</tbody>[/TABLE]


Can you help on this?

 

[AlanY]

got it wrong again
working on it

 

[AlanY]

need some clarification.

originally, we work out that with 10,500,000 sale (100%) and 26% profit margin (100%) the bonus will be
=10,500,500*.26*.02=54,600.

what is the expected bonus of the 70%/30% split?