Dear experts,
I am trying to figure out how to put together a hire/release plan for temporary employees. I am beginning to think I’ve bitten way more than I can chew so any help or suggestions will be greatly appreciated. I apologise in advance but this is going to be a long post.
What I have:
Weekly Gap: difference between hours currently available (from existing workforce) and hours required to fulfil planned workload. This changes by week and can be either positive or negative. At present I am looking at 12 weeks worth as we are nearing the end of the year but this could potentially be as many as 52 at the start of the year.
Attrition: percentage of workforce expected to leave each week. This is a user controlled parameter entered for each week.
Working hours: amount of hours each employee is expected to work per week. This is a combination of a standard 40 hours and an overtime uplift, which is a user controlled parameter and could change week on week.
Learning curve: shows how the productivity of an employee expected to develop week by week. Example: L1=40%, L2=70%, L3=90%, L4=95%; meaning the productivity in the first week is expected at 40%, second week at 70% etc. Currently the longest learning curve is 8 elements long and this is not expected to change.
Training schedule: hours of away training each employee needs by week. Example T1 =12, T2=1; meaning in the first week an employee will require 12 hours training away from the workstation, second week -1 hour of away training. Currently the training schedule is 2 elements long but this could increase in future.
To summarise: Hours Gap, Attrition and Working hours are related to weeks of the year and stored in one table; Learning curve and Training schedule are related to employee start week and stored in separate tables.
What I need is a way of translating all of the above into hire/ release plan. I have worked out the logic manually but have no idea how to translate it into code. I can achieve half of my aim using an excel formulae by building a triangular matrix, however this is a rather messy solution and only works for hires but falls down on releases. I believe the logic should work like this:
Abbreviations: G for gap, H for working hours, T for training, A for attrition, L for learning curve, P for people needed and R for hours to fulfil.
Start week (whatever the next week is, say week 41)
R41 = G41
P41 =(R41/(H41-T1))/L1
Start week +1 :
R42 = G42-P41*(1-A42)*(H42-T2)*L2
P42 = (R42/(H42-T1))/L1
Start week +2:
R43 = G43-P41*(1-A42-A43)*(H43-T3)*L3-P42*(1-A43)*(H43-T2)*L2
P43 =(R43/(H43-T1))/L1
Start week +3:
R44 = G44-P41*(1-A42-A43-A44)*(H44-T4)*L4-P42*(1-A43-A44)*(H44-T3)*L3-P43*(1-A44)*(H44-T2)*L2
P44=(R44/(H44-T1))/L1
And so on and so forth until the last week of the year is reached. The added complication: each calculation step should test whether the output (people) is positive or negative. If the output is positive then the steps continue as normal. However if the output is negative then the previous people must be re-set. For example, if the calculation above generated P41=50, P42=90, P43=10, P44= -30; then the output of the calculation is -30 (ie the model suggests to release 30 temps) but the people used in future calculations will be newP44=0, newP43=0, newP42=70, newP41=50. I.e. the negative output is subtracted backwards until the result is greater than 0 (this is the part that I struggle to replicate in excel using formulae). If the counting backwards had reached the starting position and the result is still negative than the subsiquent requirement must be adjusted by this negative result first, then the calculations can resume.
Apologies again for this mammoth of a post. I’ve been wrecking my brain trying to find a solution for a couple of days now and have not come up with anything that does the job properly. If there’s anyone out there willing to take on this riddle I’ll be delighted to hear from you.
I am trying to figure out how to put together a hire/release plan for temporary employees. I am beginning to think I’ve bitten way more than I can chew so any help or suggestions will be greatly appreciated. I apologise in advance but this is going to be a long post.
What I have:
Weekly Gap: difference between hours currently available (from existing workforce) and hours required to fulfil planned workload. This changes by week and can be either positive or negative. At present I am looking at 12 weeks worth as we are nearing the end of the year but this could potentially be as many as 52 at the start of the year.
Attrition: percentage of workforce expected to leave each week. This is a user controlled parameter entered for each week.
Working hours: amount of hours each employee is expected to work per week. This is a combination of a standard 40 hours and an overtime uplift, which is a user controlled parameter and could change week on week.
Learning curve: shows how the productivity of an employee expected to develop week by week. Example: L1=40%, L2=70%, L3=90%, L4=95%; meaning the productivity in the first week is expected at 40%, second week at 70% etc. Currently the longest learning curve is 8 elements long and this is not expected to change.
Training schedule: hours of away training each employee needs by week. Example T1 =12, T2=1; meaning in the first week an employee will require 12 hours training away from the workstation, second week -1 hour of away training. Currently the training schedule is 2 elements long but this could increase in future.
To summarise: Hours Gap, Attrition and Working hours are related to weeks of the year and stored in one table; Learning curve and Training schedule are related to employee start week and stored in separate tables.
What I need is a way of translating all of the above into hire/ release plan. I have worked out the logic manually but have no idea how to translate it into code. I can achieve half of my aim using an excel formulae by building a triangular matrix, however this is a rather messy solution and only works for hires but falls down on releases. I believe the logic should work like this:
Abbreviations: G for gap, H for working hours, T for training, A for attrition, L for learning curve, P for people needed and R for hours to fulfil.
Start week (whatever the next week is, say week 41)
R41 = G41
P41 =(R41/(H41-T1))/L1
Start week +1 :
R42 = G42-P41*(1-A42)*(H42-T2)*L2
P42 = (R42/(H42-T1))/L1
Start week +2:
R43 = G43-P41*(1-A42-A43)*(H43-T3)*L3-P42*(1-A43)*(H43-T2)*L2
P43 =(R43/(H43-T1))/L1
Start week +3:
R44 = G44-P41*(1-A42-A43-A44)*(H44-T4)*L4-P42*(1-A43-A44)*(H44-T3)*L3-P43*(1-A44)*(H44-T2)*L2
P44=(R44/(H44-T1))/L1
And so on and so forth until the last week of the year is reached. The added complication: each calculation step should test whether the output (people) is positive or negative. If the output is positive then the steps continue as normal. However if the output is negative then the previous people must be re-set. For example, if the calculation above generated P41=50, P42=90, P43=10, P44= -30; then the output of the calculation is -30 (ie the model suggests to release 30 temps) but the people used in future calculations will be newP44=0, newP43=0, newP42=70, newP41=50. I.e. the negative output is subtracted backwards until the result is greater than 0 (this is the part that I struggle to replicate in excel using formulae). If the counting backwards had reached the starting position and the result is still negative than the subsiquent requirement must be adjusted by this negative result first, then the calculations can resume.
Apologies again for this mammoth of a post. I’ve been wrecking my brain trying to find a solution for a couple of days now and have not come up with anything that does the job properly. If there’s anyone out there willing to take on this riddle I’ll be delighted to hear from you.
