Calculate Bonus payouts per quarter - help!

HELENTR

New Member
Joined
Mar 10, 2016
Messages
2
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 ---


Q1Q2Q3
payout pool $ 6,828 $ 7,063 $ 3,922
CountpayoutCountpayoutCountpayout
100%110%1
110%120%121
120%150%1
150%11
233

<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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,119
Office Version
  1. 365
Platform
  1. Windows
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.

ABCDE
1TotalBonus$6,828
2
3%TargetWeightNumberAmount eachTotal
4100-1101.00$2,905.53$0.00
5110-1201.101$3,196.09$3,196.09
6120-1501.251$3,631.91$3,631.91
7150+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

New Member
Joined
Mar 10, 2016
Messages
2
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.

ABCDE
1TotalBonus$6,828
2
3%TargetWeightNumberAmount eachTotal
4100-1101.00$2,905.53$0.00
5110-1201.101$3,196.09$3,196.09
6120-1501.251$3,631.91$3,631.91
7150+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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,556
Messages
5,625,498
Members
416,112
Latest member
somenka

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
Top