Linear Programming: Employee Scheduling with Excel Solver
HTML-код
- Опубликовано: 29 сен 2024
- Enjoyed this content & want to support my channel? You can get the spreadsheet I build in the video or buy me a coffee! Links below:
Buy me a coffee: www.buymeacoff...
Buy complete spreadsheet: www.buymeacoff...
Buy me an item from my wishlist: www.buymeacoff...
Given a set of potential weekly work schedules, how many people should I hire to staff a restaurant properly while minimizing costs?
Anyone who got 6.333333 for X1 instead of 1.333333?
This problem has alternative optimal solutions. As long as you still get 22.333 in cell H16 and all the constraints are satisfied, you're fine.
@@TallysYunes thank you!
Think you just saved my life. My head couldn't take one more blow with my textbook
Thank you very much for your effort.
Hi Tally, thanks for such an informative video
how can we optimize the scheduling of the employees in a case where we have 3 categories of employees working for 9 hours + OT if required, 9 hours strictly, 12 hours in one day.
On the top of how can we compute the head count for entire month considering that some employees will be there on annual vacation also.
My model involves computation of ideal driver count for a restaurant based on hourly requirement of peak and non peak hours.
Hi Kartik. Each column of 0's and 1's is an employee. If there are 3 kinds of employee, this simply means you can split your columns into 3 groups. Depending on the group, the 1's and 0's will be in different positions and the cost of that column will be different. So the essence of the table of 0's and 1's remains the same. You just need to be careful where you put the 0's and 1's. In this example we solve for 1 week and consider the each day's demand. In your case, to consider hourly demands you'll need 24 rows per day (instead of 1). If you want to solve for the entire month, you simply need to add more rows (one set of 24 rows per day, so 720 rows for 30 days) and create a bigger 0/1 matrix. In summary, your problem is just a larger version of the one I solve here (more columns and more rows), plus cost data for the columns (variables) which I don't have but explain how to add in the video.
Hi Tally, thanks for your revert...
Let me explain the business case i am working on...
1) The Co. is into food delivery business which operates around 100 stores and the model requires to compute optimum number of delivery driver count that is required for each of the store.
2) As an input we have hourly requirement of drivers for every operating hour computed using expected trx trend and assumed driver efficiency.
3) On the supply side, we have 2 different kind of drivers.
(i) Category-A driver : Fix shift of 9 hours + OT of 3 hours per day. (1/2/3 hours)
(ii) Category-B driver : Fix shift of 12 hours with no OT.
4) Hence the objective is to compute ideal driver count for every store with the optimized shifts of every category of employee.
i think explaining my model on text messages is a bit difficult..
do you mind if we get on a short google meet call to explain the problem and then hear your suggestions...
i can explain the whole business model to you..
Thanks in advance!!
Thanks for the useful video sir.
My problem is a bit different - I need to decide the number of workers and their shift timings(I have the total number of ppl working) based on their efficiency (already calculated) and #orders received for each shift (have that). Can you please help me with this ?
I need a better and more detailed explanation of what you are trying to accomplish. A concrete example of what your inputs are and what the desired output should look like would also help.
@@TallysYunes Sure Sir.
The inputs would be number of ppl, number of orders received for each 2 hour slot and avg order complexity for each slot, efficiency of each person. The expected output is : final shift timings(9 hrs shift - from 6AM - 10PM) for each worker (decided on the basis of the #orders , worker's efficiency and the complexity of orders in the slots covered by his shift i.e. a person with more efficiency should be handling a shift wherein there are more complex orders)
Thank you so much!
I considered another constraint of keeping at least one worker on a workday, as a result, total number of minimum workers came as 22.667, number of staffs available on workdays 5.67,2.67,3,6.67,1,2.67,1 respectively and total staffs 17, 13, 15, 19, 19,16,14.33 respectively against the constraint of 17,13,15,19,14,16,11 as minimum for workdays accordingly. please let me know if the solution is OK
Thank you very for your nice presentation.
Please answer me one question: If We keep days off any two days in the week rather than consecutive two, what will be the formulation?
Thanks in advance.
Having non-consecutive days off is simply a matter of placing the 1's and 0's in different positions in the matrix below the gray cells. The consequence in the formulation is that different X's will be on or off on each given day. Each day's constraint, however, continues to be:
(sum of all X's that are not off duty on this day) >= (this day's demand for workers)
@@TallysYunes Thank you very much
@@TallysYunes so, constraints will be as like (sum of xi>=Di, where i =1 to 5) is this okay?
This is too simplistic and insufficient. To use the summation notation , you need to specify unambiguously what the indices are. And it won't be the same subscript i on both sides of the inequality.
@@TallysYunes thank you
Hello Prof, did go thru with your video and its help my understanding but I cant relate how should i do for my problem below. Appreciated if you can help me on this. The question is, the minimum number of research assistants needed is 2 between 8:00am and 10:00am, 3 between 10:01am and 11:00am, 4 between 11:01am and 1:00pm, and 3 between 1:01pm and 5:00pm. Each research assistant is allotted 3 consecutive hours (except for those starting at 3:01pm who work for 2 hours and those starting at 4:01pm who work for 1 hour). Due to their flexible schedule, research assistants can usually report to work at any hour during the work day, except that no student wants to start work at lunch time (12:00 noon). Determine the minimum number of students the Industrial Engineering department should employ. working time is from 8am to 5pm. I got my answer as 10 but dont know if its right. Tq in advance sir.
Your problem is very similar to the example in the video. Instead of days of the week, you have time slots in the day. That's the only difference. Think of the slot from 8 to 10am as being the Monday, the slot from 10 to 11, as being the Tuesday, etc.
Hi Tallys! I came across your RUclips video on 'Linear Programming: Employee Scheduling with Excel Solver'. What would I do if the problem does not have any rest days for the employees? Would optimization still work on that kind of problem?
Yes, it would. It's just a matter of understanding what other details are part of the problem (if there are salaries, if there are rest breaks during the day, if the constraints are one per day like in my example or one per period of the day, say, morning, afternoon, evening, etc.).
Very very good bro! That helped me solved my class assignent. Continue please to add more videos
Hi Tallys, Thank you for this demonstration.
I have somewhat of a similar problem, however i need to keep the number of employees constant (row 5), and have solver tell me which employee/day should have a 1 (A8:G14).
I have set my constraints in solver according to what i need, however solver isn't able to give me a correct answer. Any ideas?
Then just select A8:G14 as the changing cells and make them binary as well. I'm guessing there will probably be constraints imposed on the A8:G14 cells to satisfy some work-week requirements. What's your objective function?
Hi Tallys! I came across your RUclips video on 'Linear Programming: Employee Scheduling with Excel Solver'. What would I do if the problem does not have any rest days for the employees? Would optimization still work on that kind of problem?
Yes, it would. It's just a matter of understanding what other details are part of the problem (if there are salaries, if there are rest breaks during the day, if the constraints are one per day like in my example or one per period of the day, say, morning, afternoon, evening, etc.).
Thank you for the clear tutorial. Manage to understand this clearly now.
Great to hear!
Hi Yunes, thank you for this video, I was wondering if it would be possible to set up a rotation of shifts for last mile delivery drivers? Thank you!
Most likely yes. Would need more details about the problem. I just Googled "scheduling last mile delivery drivers optimization" and there are several relevant hits, many of which are specialized software.
Thanks Tallys, this video helped me so much clarifying my understanding about scheduling problems. Amazing job!
You're welcome!
This is very helpful. Please continue to post related examples!
I tried recreating this, but my x1, x7 are different, they are 6.33 and 0 respectively. Sundays workers also says 12.66 instead of 17.66. Total workers is the same.
Here is what it looks like: docs.google.com/spreadsheets/d/12Qhtw8i7nMd6CgnY2Kr1heCT8Oz5fZmvIK64NQoJUsI/pubhtml?gid=0&single=true
Hi Michael. This is totally normal. It means this problem has alternative optimal solutions (different ways of reaching the optimal cost). My computer found one, and your computer found another. Both answers are correct.
every IE/business/management professor uses the same problem in the world while covering this subject
If you're teaching the concept of scheduling to people who have never seen it before, this is a very effective first example, so why re-invent the wheel? You can teach more complicated versions later.
Good explanation. I have an added situation... My situation involves an employee being rotated to a different area every day due to workload issues. No one employee should be placed in a heavy load area for more than two days. I have ten areas that need to be staffed on a daily basis, and the problem is to fairly schedule employees to those areas. some areas require two employees others three or even four. 5 day week two days off not all have the same days off like in your example.
Your table will need one row per work area per day, so 70 in total. Each column will be one employee with 0's and 1's showing where they are each day. The rules you described will dictate what columns or 0's and 1's are valid and which aren't.
Outstanding, simple explanation!
I have a bigger problem for you to help me solve
What if I want the days off doesn't necessarily need to be next to each other
It's just a matter of placing the 1's under the variable in whatever position you want. That is: the green cells (where the 1's go) can be separated from each other with empty cells in between. Nothing else needs to change. Only the position of the 1's.
Thank you for making this easy to understand.
How do you figure out the number of workers whose work week starts on each day?????? there were no numbers in that variable
This is exactly what the Solver does for you once you run it.
Clear and very useful, absolutely perfect!
Professor Yunes,
Not sure if you still read these but I am loving these videos which help me in my job. My question is, could you force the variable cells to be integers?
Thank you! I'm glad the videos are helping. Yes, you can add an "int" constraint on the gray cells. I don't do it in this video because I use this example to illustrate the ideas I go over at the end of the video to my students.
@@TallysYunes Can I ask what book you recommend? I see a few popular recommendations online. What would you choose?
Here's one that I like:
www.amazon.com/Spreadsheet-Modeling-Decision-Analysis-Introduction/dp/130594741X
Hi Tallys. Great video! So easily explained- why can’t you be my professor 😂. Quick question: If I’m only allowed to use 50% of one group of workers at any time, how would I define that as a constraint (resp. as a linear equation)?
Hi Berin. I'm glad to hear you enjoyed the video. Since you mentioned professor, I'm guessing this is a question related to your homework. So I can give you some help but I can't give you the answer. I first need to understand your model better. Are your variables binary? (use that type of worker or not) or are they integer (how many workers of that type are used). And also: how do you define a group of workers?
@@TallysYunes The assignment notes that the variables may also take on non-integer values. At this point, I don't know if the model is binary or not (hasn't been covered in the lecture). I guess it's the same as in your video then, due to the fact that you also get a result of 1.3333 (as you said, those could be part time workers).
The goal function is to find the optimal number of workers that is going to minimize total cost.
Workers are separated into three Types:
Type A (*a6, a8, a10): starts their shift at 6,8, and 10 and works for 4 consecutive hours, takes a two hour break and then continues working for another 2 hours (i.e. works a total of 6 hours)
Type B (*b6, b10, b14): Start their shift at 6, 10, and 14:00 and works for 4 consecutive hours.
Type C (*c10, c12): Starts their shift at 10 and 12 and works for 2 consecutive hours.
I've managed to set up all the constraints outlined in the assignment except for this one:
"No more than half of the total personnel assigned shall be Type A employees."
You just have to take that sentence, piece by piece, and translate it to math. For example, rewrite that sentence as:
(total number of type A employees) (no more than) (one half of) (total number of employees)
very useful! great video!
How can I setup a formula for a needed set of workers when they car pool? I think I still have to do this MANUALLY.
Can you be more specific? What do you want to accomplish?
Thank you very much! What if the staff can take any 2 days off per week?
Just place the holes (empty cells) wherever you need inside that green/white area.
thank you sooo much,you help me a lot
I'm really confused about your second interpretations
Week 1: 1 person works M-F, Week 2: 1 person works M-F, Week 3: 2 people work M-F, continue like this the entire year. So your X1 values each week are: 1, 1, 2, 1, 1, 2, 1, 1, 2, 1, 1, 2, etc. If you average these numbers, this average is 1.333. That means: on average you have 1.333 people working M-F, but on any given week you either have 1 or 2 people working M-F. In the video I mistakenly say "every 3 days you have 4 people, so 4/3 = 1.333." I should have said "every 3 weeks."
This helped me infinitely. Thank you!
You're welcome!
THANKS ITS 5:15 AM YOU SAVED ME
Haha! Glad to have helped. Thanks for watching!
what is I have an hourly schedule and need to decide between part-time and fulltime working? Part-time workers can only work 4 hours and fulltime 8 hours, day starts at 9AM and ends at 5pm.
Instead of one row per day, you'd have one row per hour to be covered. The columns would be as many as there are different daily schedules. Some of them would be 4-hour workers and some 8-hour workers.
@@TallysYunes What if there is a requirement of limiting part-time hours to a maximum of 50% of the day’s total
requirement and the total requirement is odd number? Should I take the lower integer or stick with exactly 50% but with a decimal? Since we are talking about workers, which should be integers right
@@muhdhafiz2081 Then your part-time hour limit becomes one half of the day's total requirement rounded down (i.e. throw away the 0.5).
@@TallysYunes is it possible to have a non-integer values as the optimal solutions? Should they be rounded off as well?
@@muhdhafiz2081 Of course. I talk about this at minute 9:56, where the solution comes out fractional. You can either do what I say in the video or add another constraint in Solver requiring the range A5:G5 to be integer (select those cells and choose "int" where you normally choose one of the symbols =).
Hmmm, I got 26. Not sure why
Just go back, re-watch the video and pause to double check all your formulas against mine.
Hi Sir! Facing the current pandemic, I wanted to apply this method in creating a schedule for a public market. Despite the strict rules on physical and social distancing, hundreds of people are still flocking the public market every day to buy their food. I have data on the number of people per district that can shop at the public market, but the market is only open for them from 5AM to 11 AM. I want to create a schedule, but I only know the number of people per district, the capacity of the market, and that people should be at least 6 feet apart. Is this method applicable? If so, how will I go about it? If not, what can you suggest that I use? I look forward to your reply :D
Can you also use this method if the employees have different salaries and positions?
Yes, of course! Add the salaries to the objective function and instead of one row per day, you need one row for each combination of position and day.
@@TallysYunes Thank you for your help! I still don't really get it though. So I'll have to make three Monday rows if I have three workers? And where do I enter the salary?
Salaries go in front of the variables in the objective cell (I explain this in the video). If there are 3 types of workers on Monday, this means you'll have 3 numbers for Monday (how many of each of the 3 types of workers are needed). Therefore, there should be 3 Monday rows, one for each type of worker. It's possible that, depending on worker type, they'll have different salaries and different work schedules (for example, the breaks could be in different positions). This would be reflected as different variables (i.e. different columns) for each worker type.
like in 5:20 when l try to select A8:G8 it gives me error, saying that "The formula you typed contains an error". why is that happening ? how to fix it
I'd have to see what formula you're trying to type.
Tallys Yunes same formula u used
If Excel is complaining, there must be a little typo in there. Can you copy and paste what you typed in here? (Or email your Excel sheet to thyunes@gmail.com.)
6:41 how did you do that?
If you're referring to adding the dollar signs to the highlighted cell range, you can press Command+T on a Mac, or F4 on Windows. Depending on your keyboard configuration on Windows, it may need to be Fn+F4.
6:38 Does anyone know what is the shortcut to add in the "$"
On a Mac, it's Cmd+T. On Windows it's F4 (depending on your keyboard configuration, you may need to hold down the Fn key and then press F4).
@@TallysYunes thank you!
Alô
Alô. Quem tá falando?
Thank you for sharing your knowledge, it was really helpful
How can you solve a problem of scheduling exam class rooms to n number of students so that the exams never clash with each other
This sounds like it could be a graph coloring problem. It's very different from the problem I describe in this video.