Calculate Bonus payouts per quarter - help!

[HELENTR]

Hello Excel guru's

Please help! I'm going mad trying to figure this out.....!

I'm trying to work out how to allocate bonuses per quarter out of a pool of money:

1) In Q1 x 2 individuals achieved their targets - although at different percentages.
So I need to pay out the pool money of $6828 in Q1 to the 2 individuals.
BUT the tricky part is that the person who achieved between 125-150% needs to get more than the person who achieved 110-120%

2) In Q2 x 3 individuals achieved their targets - at different percentages
So I need to pay out the pool money of $7063 in Q2 to the 3 individuals.
BUT the tricky part is that the person who achieved 150%+ needs to get more than the 2 people who achieved 110-120%

3) In Q3 x 3 individuals achieved their targets - at different percentages
So I need to pay out the pool money of $3922 in Q3 to the 3 individuals.
BUT the tricky part is that the person who achieved 150%+ needs to get more than the person who achieved 110-120% and who needs to get more than the person who achieved 100-110%


-- removed inline image ---


[TABLE="width: 610"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Q1[/TD]
[TD][/TD]
[TD="colspan: 2"]Q2[/TD]
[TD][/TD]
[TD="colspan: 2"]Q3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]payout pool[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 6,828[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 7,063[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 3,922[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count[/TD]
[TD]payout[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD]payout[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD]payout[/TD]
[/TR]
[TR]
[TD="align: right"]100%[/TD]
[TD="align: right"]110%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110%[/TD]
[TD="align: right"]120%[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]120%[/TD]
[TD="align: right"]150%[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



So I think I have to work out the weightings for each category that gets at least a "1" (so Q1 is 50% and 50%) but don't know how to weight it further to account for the tiered weighting....

Hope someone can help?
Many thanks!
Helen

 

[StephenCrump]

Welcome to the Forum!

Try this:

D4: =B4*TotalBonus/SUMPRODUCT(B$4:B$7,C$4:C$7)
E4: =C4*D4

You can vary the relative weights (highlighted) depending on how much you want to tilt the bonus pool to the higher performers.

	A	B	C	D	E
TotalBonus

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

[TD="align: right"]$6,828[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="align: center"]%Target[/TD]
[TD="align: center"]Weight[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Amount each[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]100-110[/TD]
[TD="bgcolor: #FFFF00, align: center"]1.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$2,905.53[/TD]
[TD="align: right"]$0.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]110-120[/TD]
[TD="bgcolor: #FFFF00, align: center"]1.10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$3,196.09[/TD]
[TD="align: right"]$3,196.09[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]120-150[/TD]
[TD="bgcolor: #FFFF00, align: center"]1.25[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$3,631.91[/TD]
[TD="align: right"]$3,631.91[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]150+[/TD]
[TD="bgcolor: #FFFF00, align: center"]1.75[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$5,084.68[/TD]
[TD="align: right"]$0.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,828.00[/TD]

</tbody>

​

 

[HELENTR]

Welcome to the Forum!

Try this:

D4: =B4*TotalBonus/SUMPRODUCT(B$4:B$7,C$4:C$7)
E4: =C4*D4

You can vary the relative weights (highlighted) depending on how much you want to tilt the bonus pool to the higher performers.

	A	B	C	D	E
TotalBonus

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]$6,828[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="align: center"]%Target[/TD]
[TD="align: center"]Weight[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Amount each[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]100-110[/TD]
[TD="bgcolor: #FFFF00, align: center"]1.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$2,905.53[/TD]
[TD="align: right"]$0.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]110-120[/TD]
[TD="bgcolor: #FFFF00, align: center"]1.10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$3,196.09[/TD]
[TD="align: right"]$3,196.09[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]120-150[/TD]
[TD="bgcolor: #FFFF00, align: center"]1.25[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$3,631.91[/TD]
[TD="align: right"]$3,631.91[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]150+[/TD]
[TD="bgcolor: #FFFF00, align: center"]1.75[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$5,084.68[/TD]
[TD="align: right"]$0.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,828.00[/TD]

</tbody>

​

Oh my goodness - I knew it was something simple enough but just couldn't get it.

Thank you so much Stephen this works perfectly!!