Teaching note: A VBA driven staff scheduling optimization tool
Use VBA to change Solver models
spreadsheets
excel
teaching
modeling
Author
Mark Isken
Published
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:
How an optimization model can be used to explore the cost implications of different managerial policies. For example, we’ll start by solving a model with only 8-hr shifts allowed and we’ll note the total cost (equivalently, the scheduling efficiency). Then we’ll explore different combinations of allowable shift lengths and note the relative cost performance of each.
How different modeling assumptions (i.e. the no understaffing allowed vs penalized understaffing) lead to different, yet closely related models,
How to build a simple spreadsheet based decision support tool using things like worksheet controls, data validation, and a little VBA,
How to use VBA to control Solver.
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.
