Allocation Across Periods - Front / Back Loading

[redhots4]

Hi everyone - This may be more of a mathematical question vs. Excel but here goes. We're hiring 250 people over 18 months starting in July and I want to show three hiring plan alternatives:

1. Hire EVENLY across 18 months (5.6% per month)
2. FRONT LOAD - hire a variable % ABOVE 5.6% in Month #1, tapering in a decreasing way to the final hires in month 18
3. BACK LOAD - hire a variable % ABOVE 5.6% in Month #18, ramping up in an increasing each month starting in month 1

The trick here is the terms "decreasing" and "increasing". Take my example worksheet for the FRONT LOAD column. I have the Variable set = 50%, so Month 1 is 5.6% + 50% = 8.3%. I could take the remaining 91.7% percent (100% - 8.3% = 91.7%) and divide it evenly across the remaining 17 periods (5.4% each), but I want to front load across all months, not just the first one. So month #2 should be something around 8.1%, and so forth across the remaining months. Month 18 would probably see something in the 2-3% range, maybe less. (It would be a bonus to be able to set the % to hire in the final month and have Excel create a down ramp ending at that percentage).

My goal with this post is a formula for the blank cells in columns C & D.

I hope the objective is clear. Has anyone done something like this or have ideas? Thanks in advance! --Shawn

SF Practice Operating Model.xlsx
	A	B	C	D
1		EVEN	FRONT LOAD	BACK LOAD
2	Jul 22	5.6%	8.3%
3	Aug 22	5.6%
4	Sep 22	5.6%
5	Oct 22	5.6%
6	Nov 22	5.6%
7	Dec 22	5.6%
8	Jan 23	5.6%
9	Feb 23	5.6%
10	Mar 23	5.6%
11	Apr 23	5.6%
12	May 23	5.6%
13	Jun 23	5.6%
14	Jul 23	5.6%
15	Aug 23	5.6%
16	Sep 23	5.6%
17	Oct 23	5.6%
18	Nov 23	5.6%
19	Dec 23	5.6%		8.33%
20
21	Variable	50%
Sheet1

Cell Formulas
Range	Formula
B2:B19	B2	=1/(COUNTA($A$2:$A$19))
C2	C2	=B2*(1+B21)
A3:A19	A3	=A2+365/12
D19	D19	=B19*(1+B21)

 

[KRice]

Here is one approach. This assumes a linear trend, where the linear slope is determined by specifying how much above or below you want the starting and ending points to be relative to the start/end of the "Even" profile.

Book1
	A	B	C	D
1	Slope		-0.003	0.003
2	Total	100.0%	100.0%	100.0%
3		EVEN	FRONT LOAD	BACK LOAD
4
5	Jul-22	5.6%	8.0%	3.1%
6	Aug-22	5.6%	7.7%	3.4%
7	Sep-22	5.6%	7.5%	3.7%
8	Oct-22	5.6%	7.2%	3.9%
9	Nov-22	5.6%	6.9%	4.2%
10	Dec-22	5.6%	6.6%	4.5%
11	Jan-23	5.6%	6.3%	4.8%
12	Feb-23	5.6%	6.0%	5.1%
13	Mar-23	5.6%	5.7%	5.4%
14	Apr-23	5.6%	5.4%	5.7%
15	May-23	5.6%	5.1%	6.0%
16	Jun-23	5.6%	4.8%	6.3%
17	Jul-23	5.6%	4.5%	6.6%
18	Aug-23	5.6%	4.2%	6.9%
19	Sep-23	5.6%	3.9%	7.2%
20	Oct-23	5.6%	3.7%	7.5%
21	Nov-23	5.6%	3.4%	7.7%
22	Dec-23	5.6%	3.1%	8.0%
23
24	Variable	0.5
redhots4

Cell Formulas
Range	Formula
C1	C1	=($B$22*(1-$B$24)-$B$5*(1+$B$24))/(COUNTA($A$5:$A$22)+1)
D1	D1	=($B$22*(1+$B$24)-$B$5*(1-$B$24))/(COUNTA($A$5:$A$22)+1)
B2:D2	B2	=SUM(B5:B22)
B5:B22	B5	=1/(COUNTA($A$5:$A$22))
C5:C22	C5	=C$1*COUNT($A$5:$A5)+($B$5*(1+$B$24))
D5:D22	D5	=D$1*COUNT($A$5:$A5)+($B$5*(1-$B$24))
A6:A22	A6	=A5+365/12

 

[KRice]

Shawn,
I wasn't entirely happy with the math in my last post. The even distribution case is straightforward, but where you'd like to weight the hiring rate as a function of time, we need some strategy for applying a hiring rate trend on a month-by-month basis. In this revision, I chose a linear trend, which is convenient because even though this is a continuous function, we can set the target value for the first data point (percentage to be hired in Jul-22) to the midpoint of Jul-22, and since the trend is linear, this value represents the average hiring percentage for that month. Then we need to ensure that the total percentage hired after all months is 100 %. Those are the two constraints used to determine the slope and intercept of the linear trend line. Then the hiring percentage value for each month is reported as the point value for the mid-point of that month. The math is messy and there is probably a way to do this using trend/forecast functions, but this is an old-school method. Note that the Back Load case requires you to enter a negative starting factor. In the depicted example, I'm starting 40 % lower than the Even case. Since these are linear trends, if you set C1=-D1, you'll obtain the same "percent to be hired" at opposite ends of the lists. A sum confirms that 100 % is obtained for the entire list. Check the list to determine if any of the values become negative--that would indicate violation of some constraint. For example, if front loading is excessive, it may not be possible to linearly drop the hiring rate over the entire 18 month period without obtaining a negative hiring rate. You can change C1 and D1 to see the effect of more or less frontloading/backloading.

MrExcel_20220314.xlsx
	A	B	C	D
1	Start higher/lower factor		0.4	-0.4
2	Slope		-0.00261	0.00261
3	Intercept		0.07908	0.03203
4	Total	100.0%	100.0%	100.0%
5		EVEN	FRONT LOAD	BACK LOAD
6	Jul-22	5.6%	7.8%	3.3%
7	Aug-22	5.6%	7.5%	3.6%
8	Sep-22	5.6%	7.3%	3.9%
9	Oct-22	5.6%	7.0%	4.1%
10	Nov-22	5.6%	6.7%	4.4%
11	Dec-22	5.6%	6.5%	4.6%
12	Jan-23	5.6%	6.2%	4.9%
13	Feb-23	5.6%	5.9%	5.2%
14	Mar-23	5.6%	5.7%	5.4%
15	Apr-23	5.6%	5.4%	5.7%
16	May-23	5.6%	5.2%	5.9%
17	Jun-23	5.6%	4.9%	6.2%
18	Jul-23	5.6%	4.6%	6.5%
19	Aug-23	5.6%	4.4%	6.7%
20	Sep-23	5.6%	4.1%	7.0%
21	Oct-23	5.6%	3.9%	7.3%
22	Nov-23	5.6%	3.6%	7.5%
23	Dec-23	5.6%	3.3%	7.8%
redhots4 (2)

Cell Formulas
Range	Formula
C2:D2	C2	=2*(1-C$3*COUNTA($A$6:$A$23))/(COUNTA($A$6:$A$23)^2)
C3:D3	C3	=((COUNTA($A$6:$A$23))^2*($B$6*(1+C$1))-1)/(COUNTA($A$6:$A$23)*(COUNTA($A$6:$A$23)-1))
B4:D4	B4	=SUM(B6:B23)
B6:B23	B6	=1/(COUNTA($A$6:$A$23))
C6:D23	C6	=C$2*(COUNT($A$6:$A6)-0.5)+C$3
A7:A23	A7	=A6+365/12

 

[redhots4]

Shawn,
I wasn't entirely happy with the math in my last post. The even distribution case is straightforward, but where you'd like to weight the hiring rate as a function of time, we need some strategy for applying a hiring rate trend on a month-by-month basis. In this revision, I chose a linear trend, which is convenient because even though this is a continuous function, we can set the target value for the first data point (percentage to be hired in Jul-22) to the midpoint of Jul-22, and since the trend is linear, this value represents the average hiring percentage for that month. Then we need to ensure that the total percentage hired after all months is 100 %. Those are the two constraints used to determine the slope and intercept of the linear trend line. Then the hiring percentage value for each month is reported as the point value for the mid-point of that month. The math is messy and there is probably a way to do this using trend/forecast functions, but this is an old-school method. Note that the Back Load case requires you to enter a negative starting factor. In the depicted example, I'm starting 40 % lower than the Even case. Since these are linear trends, if you set C1=-D1, you'll obtain the same "percent to be hired" at opposite ends of the lists. A sum confirms that 100 % is obtained for the entire list. Check the list to determine if any of the values become negative--that would indicate violation of some constraint. For example, if front loading is excessive, it may not be possible to linearly drop the hiring rate over the entire 18 month period without obtaining a negative hiring rate. You can change C1 and D1 to see the effect of more or less frontloading/backloading.

MrExcel_20220314.xlsx
	A	B	C	D
1	Start higher/lower factor		0.4	-0.4
2	Slope		-0.00261	0.00261
3	Intercept		0.07908	0.03203
4	Total	100.0%	100.0%	100.0%
5		EVEN	FRONT LOAD	BACK LOAD
6	Jul-22	5.6%	7.8%	3.3%
7	Aug-22	5.6%	7.5%	3.6%
8	Sep-22	5.6%	7.3%	3.9%
9	Oct-22	5.6%	7.0%	4.1%
10	Nov-22	5.6%	6.7%	4.4%
11	Dec-22	5.6%	6.5%	4.6%
12	Jan-23	5.6%	6.2%	4.9%
13	Feb-23	5.6%	5.9%	5.2%
14	Mar-23	5.6%	5.7%	5.4%
15	Apr-23	5.6%	5.4%	5.7%
16	May-23	5.6%	5.2%	5.9%
17	Jun-23	5.6%	4.9%	6.2%
18	Jul-23	5.6%	4.6%	6.5%
19	Aug-23	5.6%	4.4%	6.7%
20	Sep-23	5.6%	4.1%	7.0%
21	Oct-23	5.6%	3.9%	7.3%
22	Nov-23	5.6%	3.6%	7.5%
23	Dec-23	5.6%	3.3%	7.8%
redhots4 (2)

Cell Formulas
Range	Formula
C2:D2	C2	=2*(1-C$3*COUNTA($A$6:$A$23))/(COUNTA($A$6:$A$23)^2)
C3:D3	C3	=((COUNTA($A$6:$A$23))^2*($B$6*(1+C$1))-1)/(COUNTA($A$6:$A$23)*(COUNTA($A$6:$A$23)-1))
B4:D4	B4	=SUM(B6:B23)
B6:B23	B6	=1/(COUNTA($A$6:$A$23))
C6:D23	C6	=C$2*(COUNT($A$6:$A6)-0.5)+C$3
A7:A23	A7	=A6+365/12

Thanks for your help on this! I think I have a working model. Cheers!