So let us now solve this problem using solver to find a decision that the HR manager should use for scheduling the number of staff on each day. So we're going to begin by first ignoring the integer constraints. Let's first do it with real values as we would have otherwise done, and then we're going to look at how to include these constraints that the decision variables have to be integer valued. So here, as you can see, we have the decision variables in cells B4 through H4. So let's pull up the solver, go to data, and then click on solver. We need to specify the objective value where the functional form with the objective function is going to be, and that's going to be cell I4, which is the sum of the decision variable values. So we specify that. Then we have to say whether this a maximization or minimalization problem. This one is obviously a minimization problem, because we want to have the minimum number of staff needed to meet the requirements. The decision variable values are going to be in the cells B4 through H4. And then we need to add the constraints. The constraints are there at the left hand side value for each of these D which is going to be given by the some product of the values, the binary values in that row for that particular day of the week multiplied with the number of corresponding number of workers who start their work on that particular day, would give you the total number of workers that are active on Monday, Tuesday, Wednesday, Thursday and so on for the different rows of this table. And these values have to be the left hand side values of the constraints have to be greater than equal to the right hand side value, so let's specify those the left hand side of the constraint are these cells. These have to be greater than equal to the right hand side values which are in cells are J7 through G30. And I'm going to say okay. So this is the set of all the constraints and we have set up the objective function. So let's solve it and see what result we get. We are making it non negative but we have not added the integer constraints on the decision variable values. So let's first solve this. And here, as you can see, it's saying that the total number of workers that I need to hire is 22.33. And these are the number of workers that you would need. Now if there was an option to maybe pay the workers by our fractional wage if it is possible to hire fractional workers, then you could use this as the optimal solution. But let's say that these are full time employees, so therefore you have to hire them for the whole day. And therefore these values need to be integer. One way to try to get to that would be to round this up to 23 and maybe you make some decisions as to whether you want this to be 7 and this one to be 3. This is 3 and this may be 2. Something along those lines, but again, this may not be. This can actually be shown with some examples that even if you were to round them up or down. That's not going to give you the optimal value necessarily. You need to undertake what is known as integer programming in order to make sure that the decision variable values are integer valued. Fortunately, solver will take care of all that, if you just specify that the decision variables have to be integer valued. So if we now run it again with the solver and here now, we're specifying the objective function, the cells with the decision variables are going to be the same B4 through H4, and we have added this additional constraint that deleted and again added. So here we have added integer value constraint that all these variables, the decision variables in D4 through H4 have to be integer. So here you can see that there are a couple of options to choose from. We have used this before less than equal to, equal to greater than, equal to. But there is also hint for integer which you can use if you want integer valued values in those cells or if you want it to be binary values of 0 and 1. In this case, we want the decision variables we integer values, so we're going to use hint and click Okay. And we are other constraints that remains the same. That is, you need to have a certain number of workers for the seven days of the week, and we're going to click solve. And this is going to give you an integer valued solution. So as you can see over here, it's saying that on Monday you should have six workers starting on Monday, and this workers will be active on the next five days of the week. Monday through Friday. Three additional workers need to start working on Tuesday, on Wednesday, another three seven new workers on Thursday start working. Nobody starts working on a Friday and again three and one starts working on Saturday and Sunday respectively. So that's the when you add these up, that's going to give you the total number of employees that you need to hire, which is going to be 23. And that's your optimal solution. And as you can see, these are the integer valued variables. Now this problem has multiple feasible optimal solution. So there could be other combination of values over here that would produce 23 as the objective function value. So there are multiple solutions for this problem. Multiple, they are optimal solutions, but the value of the objective function, that is how many people you need to hire will be 23 for this problem. So using this approach you can set up the scheduling problem or staffing problem and solve it using Excel and get integer valued variables. One other thing to keep in mind is that when you're using solver for integer valued problems, just make sure that if in case you're not getting integer valued, although you have specified that this decision variables have to be integer valued, just make sure that in the options when you click on options that this ignore integer constraint checkboxes, not check, because sometimes if this is checked, depending on what other problems have been working on before. If this is checked then It's going to ignore the integer constraints, so if you are getting some unexpected results and it's not giving you integer value, just make sure that you go in and check that this check box is not checked so that integer constraints are not being ignored. So that's one thing to keep in mind just to make sure that the integer constraint check box is not checked, otherwise it will ignore the integer constraints.
