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

			Q1		Q2		Q3
	payout pool			$ 6,828			$ 7,063			$ 3,922
			Count	payout		Count	payout		Count	payout
100%	110%								1
110%	120%		1			2			1
120%	150%		1
150%						1			1
			2			3			3

<tbody>
</tbody>

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
1	TotalBonus	$6,828
2
3	%Target	Weight	Number	Amount each	Total
4	100-110	1.00		$2,905.53	$0.00
5	110-120	1.10	1	$3,196.09	$3,196.09
6	120-150	1.25	1	$3,631.91	$3,631.91
7	150+	1.75		$5,084.68	$0.00
8					$6,828.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
1	TotalBonus	$6,828
2
3	%Target	Weight	Number	Amount each	Total
4	100-110	1.00		$2,905.53	$0.00
5	110-120	1.10	1	$3,196.09	$3,196.09
6	120-150	1.25	1	$3,631.91	$3,631.91
7	150+	1.75		$5,084.68	$0.00
8					$6,828.00

<tbody>
</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!!