Now that we have identified a formulation for this problem, we are going to figure out a way to set it up in Excel. We will look at the objective function and the constraints that we have identified in order to find a convenient layout in Excel spreadsheet so that we can use Solver to solve this problem. And here is one example of what your layout might look like. So on top here we have the number of number of staff beginning work on each of the day, from Monday through Sunday, indexed by numbers 1 through 7. And these are therefore going to be our decision variables in cells B4 through H4, for the seven days of the week. This is the number of staff or workers that are beginning their work on this particular day, Monday through Sunday. And the total number of workers is simply going to be the sum of these decision variables, which is your objective function value z. Now, in the table below, we are going to have this whole matrix of who is working on which day. So let's consider the seven days of the week, Monday through Sunday. And we are going to look at whether a person who starts working on a specific day of the week will be working on this particular day. If they do, then we're going to put 1, If not, we are going to put 0. So let's look at the first column here, do workers who started their work on Monday work on this day? So do they work on Monday if they start Monday? Yes, so this is going to be 1. What about workers who started their work schedule on a Tuesday? So if somebody started working on a Tuesday, they would be working from Tuesday through the next, for a total of five consecutive days. So Tuesday, Wednesday, Thursday, Friday, and Saturday, and they won't be working on Sundays and Mondays. Therefore, this value is going to be 0 for Monday. What about workers who started work on Wednesday? Well, they also won't be working on Monday, because that will be a day off for them. Workers who started work on Thursday, so they would be working for five consecutive days. So if somebody starts working on a Thursday, they need to work on Thursday, Friday, Saturday, Sunday, Monday, therefore, this is going to be 1. And you can follow this logic along to fill out 1s and 0s for all the different days of the week. Similarly for Tuesday, who are working on Tuesday? Let's look at those. If somebody started working their work schedule on a Monday, will they be working on Tuesday? Yes, because Tuesday's right after Monday. So somebody starts working on a Monday, they will have to work on Tuesday as well, so this is 1. Similarly, somebody will start working on a Tuesday will be working on Tuesday. Somebody who started working on Wednesday or Thursday, they are not going to be active on Tuesday, because they would have Tuesday as the day off. People who started their work on Friday, they're going to be working on Tuesday. Because if somebody starts working on a Friday, you're going to be working on Friday, Saturday, Sunday, Monday, Tuesday. So you're going to have to be active, therefore this is 1, and so on. With this logic, you can fill out this whole matrix of 1s and 0s, where 1 indicates that the person who started working on the specified day would be working on the day in consideration in the row. And when you add these up by multiplying the exact number of how many people began their work on each of these days, you would get the left-hand side of the constraint. So here this value is going to be x1, so this value x1 times 1 plus x2 times 0 plus x2 times 0 plus x4 times 1 plus x5 times 1 plus x6 times 1 plus x7 times 1. Because then you have this expression which is the left-hand side of the constraint for Monday, x1 plus x4 plus x5 plus x6 plus x7. So you're going to take the sum product of the values of the decision variables multiplied with the corresponding value of the binary variable, 0 or 1, indicated in this row. So this is going to be a sum product of these binary values multiplied with the fixed values in these cells of the decision variables. So we are making an absolute reference to these cells, B4 through H4. So we are going to have these cross products and then sum over that. That's going to give me the total number of people that are working on Monday, that's the left-hand side of the constraint for Monday. And obviously, when we use the Solver, we have to specify that this value here must, this value here must be greater than equal to 17. And once you have filled out the formula, which is sum product of these values and these values, we can just carry over this to the remaining column, so we can, to the remaining rows. You can just copy, and the formula would adjust such that you are multiplying the binary variables for that row with the corresponding values of the decision variables from the cells B4 through H4. And this will give you the total number of people that are active on Wednesday in this cell. And that has to be greater than or equal to 15. And so on for each of these rows. So this is how you can set up your problem in Excel.
