How can I use Solver to schedule my employees (Healthcare)?

Good Afternoon,

I am trying to figure out an optimization model to determine how many nurses should start at any given time, based on the average demadnd. I've attached a file that shows my census per hour. The maximum patients a nurse can have at one time is 4. Nurses have 12-hour shifts, 3 days per week. Scheduling is as follows currently:

- 4 RNs from 0700 to 1900
- add 1 Rn from 1000 to 2200
-add 1 from 1100-2300
-add 1 from 0100-1300
-add 1 from 0300 to 1500

I have limited experience with Solver and all examples I have seen online deal with weekly/daily scheduling needs, whereas my needs are hour-based.

I think this is a difficult question to solve, but I thought I'd put it out there in case someone has done something like before.

If the nurses are working a 12-hrs shift perhaps a solver model like this could be of use. On the other hand I don't think the nurses would be too happy with the solver solution.

Neither am I as this solution is not optimal i.e. cell B8 is 7 says solver but this can manually be changed to 6 still there will be a sufficient number of nurses available but solver don’t see it and I don't know why.

Running the "Evolutionary" solver instead of the "GRG Nonlinear" solver gives the same solution in number of nurses and surplus capacity but the staff distribution is changed with 7 nurses staring at 00:00 and 9 starting at 12:00 but this solution is not possible to change i.e. changing B7 from 7 too 6 will result in a lack of capacity.

So solver can be used in staff planning but working with people one should not forget to add "the human touch" a thing solver never can do.

Alf

Changing the setup a bit i.e. starting “time” at 07:00 and adjusting the number of patients one can then see that if a graph is drawn using “time” as the x-axis and the number of patients as the y-axis the result would look like a Gaussian distribution model.

Solver seems more “happy” with this setup than the previous one. All three models “works” with this new setup. All solutions are the same i.e. number of nurses and excess time. The difference is when staff should start working.

As all these solutions only require 15 nurses they are better solutions than my previous file. The Simplex LP and the Evolutionary solver runs for about 70 seconds before a solution are found. The “GRG Nonlinear” requires a bit more than 10 minutes in order to find a solution.

Alf

Wow, thank you!

I assume this clears up your problem so could you please mark the thread "Solved" as per forum rules.

Alf

Ps

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

You are welcome.

Alf

The opportunities of man are limited only by his imagination - Kettering, Charles F.

In response to a PM to me I've added some new comments to this thread:

Hi Giussep

You can ignore the VBA found in my uploaded model, you can just run Solver as a standalone without any macro. This was just to model the solver setup as I was testing my solution with the three different solver engines.

As the algorithm for Solver is written by Frontline Systems and probably modified a bit by Microsoft I don't know what this algorithm look like and as you found out it's password protected.

As to getting different results when you run the Evolutionary engine, well that could depend a bit on the starting point. As my understanding of the Evolutionary engine is rather sketchy I can offer no better explanation.

Looking at the OP's model you will see that the demand for staff is 24 at 0:00 and then drops down 12 at 05:00 and starts increasing at 07:00 and reach the highest value 34 at 19:00 and then it drops steadily from 21:00 to midnight.

As I assumed a nurse works 12 hrs, a nurse staring at 0:00 will then work until 12:00 so I could not take advantage of the slack hrs between 05:00 to 07:00. This is why I decided to start the shift cycle at 07:00.

Result starting at 07:00 I needed 15 nurses and "lost" 126 hrs, starting at 06:00 gave the same result i.e. 15 nurses and a loss of 126 hrs.

If in the other hand if shift started at 0:00 I needed 16 nurses and lost 174 hrs.

As this is a simple solver problem the "Simplex LP" should be what you need. If you build a bigger model you may have problem with the Excel solver as this is limited in size.

Another option is to Google for the OpenSolver build by the University of Auckland N.Z. This model is free and integrates nicely with Excel 2003 - 2010. This is a much more powerful Solver with no limitations in size.

I'll also upload an old Microsoft file "Solver_staffplaning.xls" to show a different way of planning.

Alf

I am not able to download this file. Please help

I am not able to download this file.

Tested to download this file and had no problems doing so. As this is your first posting in the forum could it be caused by some forum rule? Perhaps you could ask a moderator for help.

Alf

Its solved now. Thanks.