ToddMBaker
New Member
- Joined
- Dec 17, 2021
- Messages
- 4
- Office Version
- 365
- Platform
- MacOS
Hi! I am creating a spreadsheet to calculate the correct split of a bonus pool amongst three employees. Each employee has a current pay rate and a target pay rate. We want to split the bonus pool so that all employees end up with the same percentage of their target pay rate. I have the sheet working so long as the bonus pool is large enough to get all employees to an equal percent of target, but when the bonus pool is smaller, the employee with the lowest percent of target ends up getting a smaller amount. I would be happy to add more commentary, but I think it might be easier to just look at the sheet which I have tried to include using XL2BB. Please let me know if I need to provide more information.
In the example sheet, any bonus pool greater than or equal to 12,333.33 works correctly (i.e., each employee ends up with the same percent of their target/goal salary). Any pool size less than 12,333.33 result in Employee 1 getting less than they should.
In the example sheet, any bonus pool greater than or equal to 12,333.33 works correctly (i.e., each employee ends up with the same percent of their target/goal salary). Any pool size less than 12,333.33 result in Employee 1 getting less than they should.
Bonus Pool Calculation - example.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Bonus Pool | $10,000.00 | |||||
2 | |||||||
3 | Employee | Employee 1 | Employee 2 | Employee 3 | Q1 Totals | ||
4 | Amount paid | $65,000.00 | $44,000.00 | $56,000.00 | $165,000.00 | ||
5 | Quarterly Target Pay | $80,000.00 | $50,000.00 | $60,000.00 | $190,000.00 | ||
6 | % of Target | 81.25% | 88.00% | 93.33% | |||
7 | Equilization amount | $9,666.67 | $2,666.67 | $0.00 | $12,333.33 | ||
8 | Split Remainder | $0.00 | $0.00 | $0.00 | $0.00 | ||
9 | Total bonus | $7,837.84 | $2,162.16 | $0.00 | $10,000.00 | ||
10 | New Quarterly comp | $72,837.84 | $46,162.16 | $56,000.00 | |||
11 | New % of goal | 91.05% | 92.32% | 93.33% | |||
2021 Compensation |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E4:E5,E8:E9 | E4 | =SUM(B4:D4) |
B6:D6 | B6 | =B4/B5 |
B7:D7 | B7 | =(MAX($B$6:$D$6)-B6)*B5 |
E7 | E7 | =SUM(B7:C7) |
B8:D8 | B8 | =IF((MIN(B5-B4-B7,(B4+B7)/($E$4+$E$7)*($B$1-$E$7)))>0,MIN(B5-B4-B7,(B4+B7)/($E$4+$E$7)*($B$1-$E$7)),0) |
B9:D9 | B9 | =IF($E$7<$B$1,(B7+B8),((B7/SUM($B$7:$D$7))*$B$1)) |
B10:D10 | B10 | =B4+B9 |
B11:D11 | B11 | =B10/B5 |