Redistribute remaining bonus after a reaching a cap

[Mtnfan]

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.

1. 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.
2. 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.
3. 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.

I could manually recalculate the distribution for as many iterations as needed until the bonus pool is completely distributed. However, what I would like to do is to automatically redistribute the remaining bonus pool to the uncapped workers. Ideally, the formula would consider the relative contributions of each remaining worker (since each worker's relative contribution changes once a "capped" worker is removed from the pool).

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)

 

[Dave Patton]

T202110b.xlsm
	A	B	C	D	E	F	G	H	I
1	Q4 bonus distribution
2	Bonus pool	27300			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	Increase
5	Bob	5	500	2,500.00	1,500.00	4,636.94	9.27	9.27	390.58
6	Mary	4.75	475	2,256.25	1,306.25	4,038.00	8.50	8.50	340.13
7	Sally	6.5	450	2,925.00	2,025.00	6,259.87	13.91	13.91	527.28
8	John	5.25	400	2,100.00	1,300.00	4,018.68	10.05	10.05	338.50
9	Linda	8	450	3,600.00	2,700.00	8,346.50	18.55	15.00
10
11						Undistributed bonus		1596.50	1,596.50
12						Uncapped		18,953.50
3a

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))
I5:I8	I5	=(C5*G5)/$H$12*$H$11
I11	I11	=SUM(I5:I9)
H11	H11	=B2-SUMPRODUCT(H5:H9,C5:C9)
H12	H12	=SUMPRODUCT(H5:H8,C5:C8)

 

[Mtnfan]

T202110b.xlsm
	A	B	C	D	E	F	G	H	I
1	Q4 bonus distribution
2	Bonus pool	27300			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	Increase
5	Bob	5	500	2,500.00	1,500.00	4,636.94	9.27	9.27	390.58
6	Mary	4.75	475	2,256.25	1,306.25	4,038.00	8.50	8.50	340.13
7	Sally	6.5	450	2,925.00	2,025.00	6,259.87	13.91	13.91	527.28
8	John	5.25	400	2,100.00	1,300.00	4,018.68	10.05	10.05	338.50
9	Linda	8	450	3,600.00	2,700.00	8,346.50	18.55	15.00
10
11						Undistributed bonus		1596.50	1,596.50
12						Uncapped		18,953.50
3a

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))
I5:I8	I5	=(C5*G5)/$H$12*$H$11
I11	I11	=SUM(I5:I9)
H11	H11	=B2-SUMPRODUCT(H5:H9,C5:C9)
H12	H12	=SUMPRODUCT(H5:H8,C5:C8)

Thank you for your thoughtful reply! I may not be understanding your solution but here are two thoughts:

1. The cap still applies when you incorporate the increase, so even after your iteration there is still a small pool of undistributed dollars.
2. Is there a way to automate all subsequent iterations in a single formula? So for example, I would have a single "capped bonus" pool column.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Q4 bonus distribution
2	Bonus pool	27300			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	Increase	$15 Capped Bonus per hour v2
5	Bob	5	500	2500	1500	4636.943	9.273885	9.273885	390.58026	$ 10.06
6	Mary	4.75	475	2256.25	1306.25	4038.004	8.501062	8.501062	340.13031	$ 9.22
7	Sally	6.5	450	2925	2025	6259.873	13.91083	13.91083	527.28335	$ 15.00
8	John	5.25	400	2100	1300	4018.684	10.04671	10.04671	338.50289	$ 10.89
9	Linda	8	450	3600	2700	8346.497	18.54777	15		$ 15.00
10
11						Undistributed bonus		1596.497	1596.4968	$ 37.16
12						Uncapped		18953.503
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))
I5:I8	I5	=(C5*G5)/$H$12*$H$11
J5:J9	J5	=IF(H5=15,15,IF((SUM(F5,I5)/C5)>15,15,SUM(F5,I5)/C5))
I11	I11	=SUM(I5:I9)
J11	J11	=B2-SUMPRODUCT(J5:J9,C5:C9)
H11	H11	=B2-SUMPRODUCT(H5:H9,C5:C9)
H12	H12	=SUMPRODUCT(H5:H8,C5:C8)

 

[Dave Patton]

H12 can be =SUMPRODUCT(H5:H9,C5:C9,--(H5:H9<15))

You can apply the same logic again.

You could also review Goal Seek, Solver or advanced Add-ins.

 

[Mtnfan]

Thanks again for your thoughtful solution!

I will look into those add-ins. I remember feeling somewhat flummoxed when I first looked at them some time ago but it's always worth trying again!

 

[Dave Patton]

I used Goal Seek.

I put a guess rate in H4 0.05

Select H10
Goal Seek
to Value 0
By changing H4

T202110b.xlsm
	A	B	C	D	E	F	G	H	I
1	Q4 bonus distribution
2	Bonus pool	$27,300.00			Widgets/Hr Threshold
3					2
4	Name	Production (widgets/hour)	Hours worked	Total Widgets Produced	Adjusted Widgets Produced for bonus	Bonus	$15 Capped Bonus per hour	0.087159
5	Bob	5	500	2,500.00	1,500.00	4,636.94	9.27	10.08219	5,041.10
6	Mary	4.75	475	2,256.25	1,306.25	4,038.00	8.50	9.242009	4,389.95
7	Sally	6.5	450	2,925.00	2,025.00	6,259.87	13.91	15	6,750.00
8	John	5.25	400	2,100.00	1,300.00	4,018.68	10.05	10.92237	4,368.95
9	Linda	8	450	3,600.00	2,700.00	6,750.00	15.00	15	6,750.00
10					8,831.25	25,703.50		$0.00
11								27,300.00	27,300.00
12
3a

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	=MIN(E5/$E$10*$B$2,C5*15)
G5:G9	G5	=MIN(F5/C5,15)
I5:I9	I5	=C5*H5
E10:F10	E10	=SUM(E5:E9)
H5:H9	H5	=MIN(G5*(1+$H$4),15)
H10	H10	=B2-SUMPRODUCT(C5:C9,H5:H9)
H11	H11	=SUMPRODUCT(C5:C9,H5:H9)
I11	I11	=SUM(I5:I9)

 

[Dave Patton]

Put a value in D3 say 0.8
Goal Seek
Select D10
Change value to 27300
By Changing D3

T202110b.xlsm
	A	B	C	D	E
1	Q4 bonus distribution
2	Bonus pool	$27,300.00	Widgets/Hr Threshold
3			2	2.36073059
4	Name	Production (widgets/hour)	Hours worked	Bonus	$15 Capped Bonus per hour
5	Bob	5	500	5,041.10	10.08
6	Mary	4.75	475	4,389.95	9.24
7	Sally	6.5	450	6,750.00	15.00
8	John	5.25	400	4,368.95	10.92
9	Linda	8	450	6,750.00	15.00
10				27,300.00
11
3aa

Cell Formulas
Range	Formula
E5:E9	E5	=D5/C5
D5:D9	D5	=MIN((B5-$C$3)*C5*(1+$D$3),C5*15)
D10	D10	=SUM(D5:D9)

 

[Mtnfan]

Interesting.

I understand that the MIN function returns the lowest value of the series and you included a bonus of $15/hour (hours x 15) in the series so it would return no more than that number.

In your example, is D3 is the number that Goal Seek finds to determine an even distribution among the remainder? Does that hold up over multiple iterations? Suppose the group of workers was five times as large and 5 people capped out on the first iteration, and 3 people capped out on the second. I'm imagining I'll need three or four iterations most quarters.

 

[Dave Patton]

Did you try post #7?
N.B. Copy the post to a clean sheet. Click on the icon below the f(x) in the heading and paste the data into your spreadsheet.
Put a value say say 0 or .001 in D3 sand then try the Goal Seek.


The Goal Seek should yield the results per your specifications.

"Does that hold up over multiple iterations? Suppose the group of workers was five times as large and 5 people capped out on the first iteration, and 3 people capped out on the second. "
Why is there a second iteration?

Goal Seek should work with larger data.

"I'm imagining I'll need three or four iterations most quarters."

I do not know why you would imagine that. Goal Seek iterates to determine the solution.
What do you mean by multiple iterations. Does post #7 not answer your question?

 

[Mtnfan]

Dave --

Thanks for your input. I finally worked this out. I don't know why I found it confusing. Goal Seek is actually very easy to use and worked well using your examples. Thank you!