Great to hear!

You're welcome!

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.

You're welcome!

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 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!!

Haha! Glad to have helped. Thanks for watching!

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

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.

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.

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)

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 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)

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!

Just place the holes (empty cells) wherever you need inside that green/white area.

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.).

Can you be more specific? What do you want to accomplish?

This is exactly what the Solver does for you once you run it.

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.

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 =).

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.)

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.

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!

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 sounds like it could be a graph coloring problem. It's very different from the problem I describe in this video.

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.

Just go back, re-watch the video and pause to double check all your formulas against mine.

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.

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.

Alô. Quem tá falando?

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.).