Microsoft Office Tutorials: Using solver to schedule your workforce

Thursday, August 5, 2021

Using solver to schedule your workforce

Many businesses (such as banks, restaurants, and postal service companies) know what their labor requirements will be on different days of the week, and need a method to efficiently schedule their workforce. You can use Excel's Solver add-in to create a staffing schedule based on those requirements.

Schedule your workforce to meet labor demands (example)

The following example demonstrates how you can use Solver to calculate staffing requirements.

Contoso bank processes checks 7 days a week. The number of workers needed each day to process checks is shown in row 14 of the Excel worksheet shown below. For example, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, and so on. All bank employees work 5 consecutive days. What is the minimum number of employees the bank can have and still meet its labor requirements?

Data used in example

Start by identifying the objective cell, changing cells, and constraints for your Solver model.

Note: The objective cell is called the target cell in Excel 2007.
- Objective cell – Minimize the total number of employees.
- Changing cells – Number of employees who start work (the first of five consecutive days) each day of the week. Each changing cell must be a non-negative integer.
- Constraints – For each day of the week, the number of employees who are working must be greater than or equal to the number of employees required. (Number of employees working)>=(Needed employees)
To set up the model, you need to track the number of employees working each day. Begin by entering trial values for the number of employees who start their five-day shift each day in the cell range A5:A11. For example, in A5, enter 1 to indicate that 1 employee begins work on Monday and works Monday through Friday. Enter each day's required workers in the range C14:I14.
To track the number of employees working each day, enter a 1 or a 0 in each cell in the range C5:I11. The value 1 in a cell indicates that the employees who started working on the day designated in the cell's row are working on the day associated with the cell's column. For example, the 1 in cell G5 indicates that employees who started working on Monday are working on Friday; the 0 in cell H5 indicates that the employees who started working on Monday are not working on Saturday.
To compute the number of employees working each day, copy the formula =SUMPRODUCT($A$5:$A$11,C5:C11) from C12 to D12:I12. For example, in cell C12, this formula evaluates to =A5+A8+A9+A10+A11, which equals (Number starting on Monday)+ (Number starting on Thursday)+(Number starting on Friday)+(Number starting on Saturday)+ (Number starting on Sunday). This total is the number of people working on Monday.
After computing the total number of employees in cell A3 with the formula =SUM(A5:A11), you can enter your model in Solver as shown below.
In the objective cell (A3), you want to minimize the total number of employees. The constraint C12:I12>=C14:I14 ensures that the number of employees working each day is at least as large as the number needed for that day. The constraint A5:A11=integer ensures that the number of employees beginning work each day is an integer. To add this constraint,click Add in the Solver Parameters dialog box and enter the constraint in the Add Constraint dialog box (shown below).
You can also selected the options Assume Linear Model and Assume Non-Negative for the changing cells by clicking Options in the Solver Parameters dialog box and then selecting the check boxes in the Solver Options dialog box.
Click Solve. You'll see the optimal number of employees for each day.

In this example, a total of 20 employees is needed. One employee starts on Monday, three start on Tuesday, four start on Thursday, one starts on Friday, two start on Saturday, and nine start on Sunday.

Note that this model is linear because the objective cell is created by adding changing cells, and the constraint is created by comparing the result obtained by adding the product of each changing cell times a constant (either 1 or 0) to the required number of workers.