I need to distribute a quarterly bonus based on production.
A bonus pool is created by adding a certain dollar amount for every hour worked. In the case of this example, $12 is added to the pool for every hour worked.
The bonus pool is then distributed based on each worker's relative production in a given quarter.
A bonus pool is created by adding a certain dollar amount for every hour worked. In the case of this example, $12 is added to the pool for every hour worked.
The bonus pool is then distributed based on each worker's relative production in a given quarter.
- We first calculate each worker's total widgets produced by multiplying widgets/hour x hours worked in a given quarter. For the bonus calculation, only widgets/hour over a certain threshold are counted. In this example, the threshold is 2 widgets/hour.
- Each worker then receives a bonus proportional to the number of "bonus" widgets they produced. For example, if Bob produced 2 widgets beyond the threshold and the total pool of "bonus" widgets is 10, Bob receives 20% of the bonus pool.
- The complicating step is there is a cap on the bonus rate. In this case the cap is $15/hour. The primary goal of the cap is to avoid an excessive focus on production and to keep the bonus distribution reasonable between workers. However, applying the cap creates a pool of undistributed bonus dollars remaining. I want to distribute all the dollars in the bonus pool by redistributing the remaining bonus to the uncapped workers. This may take more than one iteration.
| Book3 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Q4 bonus distribution | |||||||||
| 2 | Bonus pool | $ 27,300.00 | Widgets/Hour Threshold | |||||||
| 3 | 2 | |||||||||
| 4 | Name | Production (widgets/hour) | Hours worked | Total Widgets Produced | Adjusted Widgets Produced for bonus | Bonus | Bonus per hour | $15 Capped Bonus per hour | ||
| 5 | Bob | 5 | 500 | 2500 | 1500 | $ 4,636.94 | $ 9.27 | $ 9.27 | ||
| 6 | Mary | 4.75 | 475 | 2256.25 | 1306.25 | $ 4,038.00 | $ 8.50 | $ 8.50 | ||
| 7 | Sally | 6.5 | 450 | 2925 | 2025 | $ 6,259.87 | $ 13.91 | $ 13.91 | ||
| 8 | John | 5.25 | 400 | 2100 | 1300 | $ 4,018.68 | $ 10.05 | $ 10.05 | ||
| 9 | Linda | 8 | 450 | 3600 | 2700 | $ 8,346.50 | $ 18.55 | $ 15.00 | ||
| 10 | ||||||||||
| 11 | Undistributed bonus | $ 1,596.50 | ||||||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =SUM(C5:C9)*12 |
| D5:D9 | D5 | =B5*C5 |
| E5:E9 | E5 | =(B5-$E$3)*C5 |
| F5:F9 | F5 | =(E5/SUM($E$5:$E$9))*$B$2 |
| G5:G9 | G5 | =F5/C5 |
| H5:H9 | H5 | =IF((((E5/SUM($E$5:$E$9))*$B$2)/C5)>15,15,(((E5/SUM($E$5:$E$9))*$B$2)/C5)) |
| H11 | H11 | =B2-SUMPRODUCT(H5:H9,C5:C9) |
