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

Hemar5

New Member
Joined
Jul 15, 2020
Messages
5
Office Version
  1. 2010
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Hemar5

New Member
Joined
Jul 15, 2020
Messages
5
Office Version
  1. 2010
1.PNG
 

Attachments

  • 2.PNG
    2.PNG
    9.3 KB · Views: 9

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 15, 2020
Messages
5
Office Version
  1. 2010
Ramp Up Plan.xlsx
ABCDEFGHIJKLMNOPQRST
1Process Job1 Week --->Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16
2Total Monthly 1,000 Onboarded Employees 3 3 3 3 3 3 3 3 2 10 10 10 10 10 10 10
3Per day53 Ramp 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
4Target per day 96 Ramp% 5%10%15%20%30%40%50%60%70%80%90%100%100%100%100%100%
5Required FTE1 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
RangeFormula
E2:T2E2=VLOOKUP($B$1,Table1,COLUMN()+13,0)
E3E3=IF(E2>0,1,0)
F3:T3F3=IF(E3<1,IF(F2>0,E3+1,0),E3+1)
E4,N4:T4E4=IF(E3>0,VLOOKUP(E3,Refer!$A:$B,2,0),0)
F4:M4F4=IF(E3>1,VLOOKUP(F3,Refer!$A:$B,2,0),IF(F3>0,VLOOKUP(F3,Refer!$A:$B,2,0),0))
E5:T5E5=E$4*$B$4
B2B2=VLOOKUP($B$1,Details!$A:$N,5,0)
B3B3=B2/19
B5B5=B3/B4
E7:T7E7=E5*E6
E8E8=($E$2*E5*E6)
F8F8=($E$2*F5*F6)+($F$2*E5*E6)
G8G8=($E$2*G5*G6)+($F$2*F5*F6)+($G$2*E5*E6)
H8H8=($E$2*H5*H6)+($F$2*G5*G6)+($G$2*F5*F6)+($H$2*E5*E6)
I8I8=($E$2*I5*I6)+($F$2*H5*H6)+($G$2*G5*G6)+($H$2*F5*F6)+($I$2*E5*E6)
J8J8=($E$2*J5*J6)+($F$2*I5*I6)+($G$2*H5*H6)+($H$2*G5*G6)+($I$2*F5*F6)+($J$2*E5*E6)
K8K8=($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)
L8L8=($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)
M8M8=($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)
N8N8=($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)
O8O8=($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)
P8P8=($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)
Q8Q8=($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)
R8R8=($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)
S8S8=($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)
T8T8=($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
CellAllowCriteria
Q5:T5Text length=0
Q7:T8Text length=0
B1List=Details!$A$4:$A$13
 

Hemar5

New Member
Joined
Jul 15, 2020
Messages
5
Office Version
  1. 2010

ADVERTISEMENT

Ramp Up Plan.xlsx
ABCDEFGHIJKLMNO
1Process Job4 Week --->Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11
2Total Monthly 1,003 Onboarded Employees - - - - - 3 3 3 - 4 -
3Per day53 Ramp - - - - - 1 2 3 4 5 6
4Target per day 96 Ramp% 0%0%0%0%0%5%10%15%20%30%40%
5Required FTE1 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
RangeFormula
E2:O2E2=VLOOKUP($B$1,Table1,COLUMN()+13,0)
E3E3=IF(E2>0,1,0)
F3:O3F3=IF(E3<1,IF(F2>0,E3+1,0),E3+1)
E4,N4:O4E4=IF(E3>0,VLOOKUP(E3,Refer!$A:$B,2,0),0)
F4:M4F4=IF(E3>1,VLOOKUP(F3,Refer!$A:$B,2,0),IF(F3>0,VLOOKUP(F3,Refer!$A:$B,2,0),0))
E5:O5E5=E$4*$B$4
B2B2=VLOOKUP($B$1,Details!$A:$N,5,0)
B3B3=B2/19
B5B5=B3/B4
E7:O7E7=E5*E6
E8E8=($E$2*E5*E6)
F8F8=($E$2*F5*F6)+($F$2*E5*E6)
G8G8=($E$2*G5*G6)+($F$2*F5*F6)+($G$2*E5*E6)
H8H8=($E$2*H5*H6)+($F$2*G5*G6)+($G$2*F5*F6)+($H$2*E5*E6)
I8I8=($E$2*I5*I6)+($F$2*H5*H6)+($G$2*G5*G6)+($H$2*F5*F6)+($I$2*E5*E6)
J8J8=($E$2*J5*J6)+($F$2*I5*I6)+($G$2*H5*H6)+($H$2*G5*G6)+($I$2*F5*F6)+($J$2*E5*E6)
K8K8=($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)
L8L8=($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)
M8M8=($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)
N8N8=($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)
O8O8=($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
CellAllowCriteria
B1List=Details!$A$4:$A$13
 

Hemar5

New Member
Joined
Jul 15, 2020
Messages
5
Office Version
  1. 2010
Formula should be entered into the Volume per week row(Row 8).

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,001
Messages
5,628,063
Members
416,290
Latest member
antoniokobe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top