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

	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>

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

Criteria 1	Target Achivement	Payout
Revenue	100	100
Revenue	90-99	60
Revenue	80-89	20
Revenue	Below 80	0
Weight age 70%

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

Criteria 2	Target Achivement	Payout
MC 26% & Above	100	100
MC 24%-25%	90-99	60
MC 21%- 23%	80-89	20
MC 20% & Below	Below 80	0
Weight age 30%

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

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

Criteria 1	Target Achivement	Payout
Revenue	100	100
Revenue	90-99	60
Revenue	80-89	20
Revenue	Below 80	0
Weight age 70%

<tbody>
</tbody>

Criteria 2	Target Achivement	Payout
MC 26% & Above	100	100
MC 24%-25%	90-99	60
MC 21%- 23%	80-89	20
MC 20% & Below	Below 80	0
Weight age 30%

<tbody>
</tbody>

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?