Setting the problem's criteria in Solver

- So far in this chapter, we have created a worksheet that specifies the number of nurses we need on staff during a series of two hour time segments. The right side of the worksheet contains a list of the shift start and end times, while the left side has our requirements, and uses the number of nurses on each shift to figure out how many are on hand at any given time. In this movie, we will create a Solver model that uses this framework to find the staffing pattern that minimizes the total nurses required to meet our needs.

To open Solver I will click the Data tab on the ribbon and then click Solver. Now that I'm in Solver, I can create the Objective. I want to minimize the total staff provided. So with the insertion point flashing in the Set Objective box, I'll click cell C16, which is the total number of staff provided, or total staff hours. I want to minimize that value, and I want to change variable cells, those are over on the right side of the worksheet, so I'll click the collapsed dialogue button next to the By Changing Variable Cells box, and I'll select cells G4 through G15.

And then click the expand dialogue button, and I can see the range in the By Changing Variable Cells box. Now I need to set a number of constraints so that the staff provided are at least equal to the staff required for each segment. So I'll click Add to start creating my constraints. I'll move the Add Constraint box up a little bit so the data isn't obscured. The first constraint I will add makes sure that the staff provided, which appears in cells C4 through C15, are greater than or equal to the staff required, that's in B4 to B15.

So, with the insertion point flashing in the Cell Reference box, I'll select cells C4 to C15, I'll change the comparison operator to greater than or equal to, and then with the insertion point flashing in the Constraint box, I'll select cells B4 through B15, and click Add to create the constraint. The only other constraint that I need to add is to make sure that the number of nurses working in a given shift is an integer: you can't have 1/3 of a nurse, for example.

So with the insertion point flashing in the Cell Reference box, I'll select cells G4 through G15, and then I'll click the comparison operator's down arrow, and click on int, which is short for integer. Like I said, those are the only constraints I need to add, so I'll click OK, take a quick look to make sure everything looks good. My Unconstrained Variables are negative, so I can't have -3 nurses working on a particular shift, and I have Simplex LP as my solving method.

I'll go ahead and click the Solve button. Solver did find a solution, so I'll click OK. And there it is. We have six nurses starting at midnight, one starting at 4am, the other numbers are available here, and you can see that I have covered all of my needs, which is 194 staffing hours, with just 208 hours total. You can see that I have a little bit of overage from 2am to 4am, 4-6, and 6-8, and everything else is exactly on target. This solution looks pretty good.

We have eight hour shifts starting every two hours, but we got all of our segments covered with just a little bit of overage.