Excel formula for a dynamic ramp plan based on job and recruitment

[Hemar5]

Hi All,

I have attached spreadsheet with 3 tabs
Details: Please refer employees recruited number for each job per week (Column O to BV)
Summary : This is where the formula should go
Refer: This is reference of a value used in formula

There are 10 Jobs for which employees would be recruited within 60 weeks randomly. (Details tab: Column O to BV)
Summary Tab:

1. You can select each job in B1
2. Onboarded employees will be reflected in Row 2 from Column V: Column BL
3. Ramp% will get reflected from Refer tab
4. Production of one employee= Target(always 96)*Ramp%
5. First week employee will do 5%
6. Next week 10%
...likewise
7. Formula should be written in Row#8 of Summary.
8. This should reflect the total production by all employees for the week
9. if that week has 2 employees in Ramp2 week and 3 employees from Ramp3 week, then it should say 10%*96*2 + 15%*96*3
10. I have written the formula which works if all job starts in Week1. But, each job gets its employee in different weeks and ramp% changes for each change


Please help on the formula

Thank you so much

 

[Hemar5]

 

[CA_Punit]

It is very difficult to estimate the column reference from the picture.
Please care to post data using xl2bb addin which is available for download in the right hand side of reply box

 

[Hemar5]

Ramp Up Plan.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Process	Job1		Week --->	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16
2	Total Monthly	1,000		Onboarded Employees	3	3	3	3	3	3	3	3	2	10	10	10	10	10	10	10
3	Per day	53		Ramp	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
4	Target per day	96		Ramp%	5%	10%	15%	20%	30%	40%	50%	60%	70%	80%	90%	100%	100%	100%	100%	100%
5	Required FTE	1		Ramp Target per Employee	5	10	14.4	19.2	28.8	38.40	48.00	57.60	67.20	76.80	86.40	96	96	96	96	96
6				Number of Business Days	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
7				Weekly Ramp per Employee	5	10	14	19	29	38	48	58	67	77	86	96	96	96	96	96
8				Volume per Week	14.4	43.2	86.4	144.0	230.4	345.6	489.6	662.4	859.2	1,118.4	1,440.0	1,824.0	2,236.8	2,716.8	3,264.0	3,878.4
9
Summary

Cell Formulas
Range	Formula
E2:T2	E2	=VLOOKUP($B$1,Table1,COLUMN()+13,0)
E3	E3	=IF(E2>0,1,0)
F3:T3	F3	=IF(E3<1,IF(F2>0,E3+1,0),E3+1)
E4,N4:T4	E4	=IF(E3>0,VLOOKUP(E3,Refer!$A:$B,2,0),0)
F4:M4	F4	=IF(E3>1,VLOOKUP(F3,Refer!$A:$B,2,0),IF(F3>0,VLOOKUP(F3,Refer!$A:$B,2,0),0))
E5:T5	E5	=E$4*$B$4
B2	B2	=VLOOKUP($B$1,Details!$A:$N,5,0)
B3	B3	=B2/19
B5	B5	=B3/B4
E7:T7	E7	=E5*E6
E8	E8	=($E$2*E5*E6)
F8	F8	=($E$2*F5*F6)+($F$2*E5*E6)
G8	G8	=($E$2*G5*G6)+($F$2*F5*F6)+($G$2*E5*E6)
H8	H8	=($E$2*H5*H6)+($F$2*G5*G6)+($G$2*F5*F6)+($H$2*E5*E6)
I8	I8	=($E$2*I5*I6)+($F$2*H5*H6)+($G$2*G5*G6)+($H$2*F5*F6)+($I$2*E5*E6)
J8	J8	=($E$2*J5*J6)+($F$2*I5*I6)+($G$2*H5*H6)+($H$2*G5*G6)+($I$2*F5*F6)+($J$2*E5*E6)
K8	K8	=($E$2*K5*K6)+($F$2*J5*J6)+($G$2*I5*I6)+($H$2*H5*H6)+($I$2*G5*G6)+($J$2*F5*F6)+($K$2*E5*E6)
L8	L8	=($E$2*L5*L6)+($F$2*K5*K6)+($G$2*J5*J6)+($H$2*I5*I6)+($I$2*H5*H6)+($J$2*G5*G6)+($K$2*F5*F6)+($L$2*E5*E6)
M8	M8	=($E$2*M5*M6)+($F$2*L5*L6)+($G$2*K5*K6)+($H$2*J5*J6)+($I$2*I5*I6)+($J$2*H5*H6)+($K$2*G5*G6)+($L$2*F5*F6)+($M$2*E5*E6)
N8	N8	=($E$2*N5*N6)+($F$2*M5*M6)+($G$2*L5*L6)+($H$2*K5*K6)+($I$2*J5*J6)+($J$2*I5*I6)+($K$2*H5*H6)+($L$2*G5*G6)+($M$2*F5*F6)+($N$2*E5*E6)
O8	O8	=($E$2*O5*O6)+($F$2*N5*N6)+($G$2*M5*M6)+($H$2*L5*L6)+($I$2*K5*K6)+($J$2*J5*J6)+($K$2*I5*I6)+($L$2*H5*H6)+($M$2*G5*G6)+($N$2*F5*F6)+($O$2*E5*E6)
P8	P8	=($E$2*P5*P6)+($F$2*O5*O6)+($G$2*N5*N6)+($H$2*M5*M6)+($I$2*L5*L6)+($J$2*K5*K6)+($K$2*J5*J6)+($L$2*I5*I6)+($M$2*H5*H6)+($N$2*G5*G6)+($O$2*F5*F6)+($P$2*E5*E6)
Q8	Q8	=($E$2*Q5*Q6)+($F$2*P5*P6)+($G$2*O5*O6)+($H$2*N5*N6)+($I$2*M5*M6)+($J$2*L5*L6)+($K$2*K5*K6)+($L$2*J5*J6)+($M$2*I5*I6)+($N$2*H5*H6)+($O$2*G5*G6)+($P$2*F5*F6)+($Q$2*E5*E6)
R8	R8	=($E$2*R5*R6)+($F$2*Q5*Q6)+($G$2*P5*P6)+($H$2*O5*O6)+($I$2*N5*N6)+($J$2*M5*M6)+($K$2*L5*L6)+($L$2*K5*K6)+($M$2*J5*J6)+($N$2*I5*I6)+($O$2*H5*H6)+($P$2*G5*G6)+($Q$2*F5*F6)+($R$2*E5*E6)
S8	S8	=($E$2*S5*S6)+($F$2*R5*R6)+($G$2*Q5*Q6)+($H$2*P5*P6)+($I$2*O5*O6)+($J$2*N5*N6)+($K$2*M5*M6)+($L$2*L5*L6)+($M$2*K5*K6)+($N$2*J5*J6)+($O$2*I5*I6)+($P$2*H5*H6)+($Q$2*G5*G6)+($R$2*F5*F6)+($S$2*E5*E6)
T8	T8	=($E$2*T5*T6)+($F$2*S5*S6)+($G$2*R5*R6)+($H$2*Q5*Q6)+($I$2*P5*P6)+($J$2*O5*O6)+($K$2*N5*N6)+($L$2*M5*M6)+($M$2*L5*L6)+($N$2*K5*K6)+($O$2*J5*J6)+($P$2*I5*I6)+($Q$2*H5*H6)+($R$2*G5*G6)+($S$2*F5*F6)+($T$2*E5*E6)

Cells with Data Validation
Cell	Allow	Criteria
Q5:T5	Text length	=0
Q7:T8	Text length	=0
B1	List	=Details!$A$4:$A$13

 

[Hemar5]

Ramp Up Plan.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Process	Job4		Week --->	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
2	Total Monthly	1,003		Onboarded Employees	-	-	-	-	-	3	3	3	-	4	-
3	Per day	53		Ramp	-	-	-	-	-	1	2	3	4	5	6
4	Target per day	96		Ramp%	0%	0%	0%	0%	0%	5%	10%	15%	20%	30%	40%
5	Required FTE	1		Ramp Target per Employee	-	-	-	-	-	4.80	9.60	14.40	19.20	28.80	38.40
6				Number of Business Days	1	1	1	1	1	1	1	1	1	1	1
7				Weekly Ramp per Employee	-	-	-	-	-	5	10	14	19	29	38
8				Volume per Week	-	-	-	-	-	-	-	-	-	-	14.4
Summary

Cell Formulas
Range	Formula
E2:O2	E2	=VLOOKUP($B$1,Table1,COLUMN()+13,0)
E3	E3	=IF(E2>0,1,0)
F3:O3	F3	=IF(E3<1,IF(F2>0,E3+1,0),E3+1)
E4,N4:O4	E4	=IF(E3>0,VLOOKUP(E3,Refer!$A:$B,2,0),0)
F4:M4	F4	=IF(E3>1,VLOOKUP(F3,Refer!$A:$B,2,0),IF(F3>0,VLOOKUP(F3,Refer!$A:$B,2,0),0))
E5:O5	E5	=E$4*$B$4
B2	B2	=VLOOKUP($B$1,Details!$A:$N,5,0)
B3	B3	=B2/19
B5	B5	=B3/B4
E7:O7	E7	=E5*E6
E8	E8	=($E$2*E5*E6)
F8	F8	=($E$2*F5*F6)+($F$2*E5*E6)
G8	G8	=($E$2*G5*G6)+($F$2*F5*F6)+($G$2*E5*E6)
H8	H8	=($E$2*H5*H6)+($F$2*G5*G6)+($G$2*F5*F6)+($H$2*E5*E6)
I8	I8	=($E$2*I5*I6)+($F$2*H5*H6)+($G$2*G5*G6)+($H$2*F5*F6)+($I$2*E5*E6)
J8	J8	=($E$2*J5*J6)+($F$2*I5*I6)+($G$2*H5*H6)+($H$2*G5*G6)+($I$2*F5*F6)+($J$2*E5*E6)
K8	K8	=($E$2*K5*K6)+($F$2*J5*J6)+($G$2*I5*I6)+($H$2*H5*H6)+($I$2*G5*G6)+($J$2*F5*F6)+($K$2*E5*E6)
L8	L8	=($E$2*L5*L6)+($F$2*K5*K6)+($G$2*J5*J6)+($H$2*I5*I6)+($I$2*H5*H6)+($J$2*G5*G6)+($K$2*F5*F6)+($L$2*E5*E6)
M8	M8	=($E$2*M5*M6)+($F$2*L5*L6)+($G$2*K5*K6)+($H$2*J5*J6)+($I$2*I5*I6)+($J$2*H5*H6)+($K$2*G5*G6)+($L$2*F5*F6)+($M$2*E5*E6)
N8	N8	=($E$2*N5*N6)+($F$2*M5*M6)+($G$2*L5*L6)+($H$2*K5*K6)+($I$2*J5*J6)+($J$2*I5*I6)+($K$2*H5*H6)+($L$2*G5*G6)+($M$2*F5*F6)+($N$2*E5*E6)
O8	O8	=($E$2*O5*O6)+($F$2*N5*N6)+($G$2*M5*M6)+($H$2*L5*L6)+($I$2*K5*K6)+($J$2*J5*J6)+($K$2*I5*I6)+($L$2*H5*H6)+($M$2*G5*G6)+($N$2*F5*F6)+($O$2*E5*E6)

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=Details!$A$4:$A$13

 

[Hemar5]

Formula should be entered into the Volume per week row(Row 8).

Thanks

 

[Fluff]

Cross posted Dynamic formula to calculate ramp production based on recruitment

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.