This exercise was contributed by Dr. Rick Wilson of Oklahoma State University to illustrate the modeling capabilities of Excel Solver. You are working with a large set of temporary workers (collection of interns, retirees, etc.) to create a draft plan to staff a night-time call center (for the near future). You also have a handful of full-time workers who are your “anchors”—but you have already placed them in the schedule, and this has led to your staffing requirements. They (full-time workers) are of no concern to you in the model. These staffing requirements are by day: You need 15, 20, 19, 22, 7, 32, and 35 staff for M, T, W, Th, F, Sat, Sun (respectively). You have between 8 and 10 of the pool who cannot work on the weekend (Saturday or Sunday).

For these “Weekday Only” folks, there are three shifts possible: They will work 4 of the 5 weekdays, one shift will have Tuesday off, one shift will have Wednesday off, and one shift will have Thursday off. You must have at least eight people total assigned to these “Weekday Only” shifts. For all other shifts (and you are not constrained by size of employee pool), a person works 4 of the 7 days each week. Workers will work 2 weekdays and both weekend days (a “2/2” shift). All possible “2”-day combinations of days are relevant shifts—except any combinations where workers have three consecutive days off; those are not allowed, and should not be in the model. We are going with a very simple model—no costs. The objective of our model is to find the fewest number of workers that meet the stated minimum call center daily requirements and not have more than four extra workers (above min. requirements) assigned during any one day. Also, all shifts (“Weekday Only” or the 2/2 shifts) can have no more than six people “allocated” to them. Create a core model that satisfies these constraints and minimizes the total number of people needed to meet the minimum requirements. If it’s an issue, yes, whole numbered people.