Teaching note: A VBA driven staff scheduling optimization tool

Use VBA to change Solver models


Mark Isken


December 18, 2013

One of the examples I use in my business analytics class to illustrate the power of optimization based decision support tools involves a simple shift scheduling problem. I’ve developed two different spreadsheet models for a one day, 24 hour, shift scheduling problem. Here’s a screenshot from the second model:

In the first model (ShiftSchedulingModel1.xlsm), the staffing requirements in each hour of the day are treated as hard constraints -no understaffing allowed. In the second model (ShiftSchedulingModel2.xlsm) shown below, understaffing is allowed but is penalized with a piecewise linear understaffing function. At the far left, the user can specify which shift lengths are allowed (8, 10, or 12 hr or any combination of them) by changing the values in row 31. These Yes/No cells have data validation that only allows these two possible values. After modifing the allowable shift lengths, the user pushes the Update Scheduling Model button which uses VBA to update the underlying model used by Solver. Then click Solve and voila.

Shift Scheduling Model 2: Understaffing penalized

In class, I use this to show a number of things:

You can find the files along with a link to a video screencast showing how I use the file in class at this page in the course website.