MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

Many organizations (such as banks, restaurants, and postal service companies) know what their labor requirements will be at different times of the day, and need a method to efficiently schedule their workforce. You can use Microsoft Office Excel Solver to easily solve workforce scheduling problems.

• How can I efficiently schedule my workforce to meet labor demands?

• How can I efficiently schedule my workforce to meet labor demands?

• Bank 24 processes checks 7 days a week. The number of workers needed each day to process checks is shown in row 14 of the file Bank24.xlsx, which is shown in Figure 28-1. 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 Bank 24 can have and still meet its labor requirements?

  Figure 28-1: The data we’ll use to work through the bank workforce scheduling problem.

• We begin by identifying the target cell, changing cells, and constraints for our Solver model.

  • Target cell. Minimize 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 our model, we need to track the number of employees working each day. I began by entering in the cell range A5:A11 trial values for the number of employees who start their five-day shift each day. For example, in A5, I entered 1, indicating that 1 employee begins work on Monday and works Monday through Friday. I entered each day’s required workers in the range C14:I14.

• To track the number of employees working each day, I entered 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.

• By copying from C12 to D12:I12 the formula =SUMPRODUCT($A$5:$A$11,C5:C11), I compute the number of employees working each day. 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 indeed the number of people working on Monday.

• After computing the total number of employees in cell A3 with the formula =SUM(A5:A11), I can enter our model in Solver as shown in Figure 28-2.

  Figure 28-2: The Solver Parameters dialog box filled in to solve the bank workforce problem

• In the target cell (A3), we want to minimize the number of total 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 each day. The constraint A5:A11=integer ensures that the number of employees beginning work each day is an integer. To add this constraint, I clicked Add in the Solver Parameters dialog box and filled in the Add Constraint dialog box as shown in Figure 28-3.

  Figure 28-3: This constraint defines as an integer the number of workers who start each day.

• I 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 checking these boxes in the Solver Options dialog box. After clicking Solve, we find the optimal solution that’s shown earlier in Figure 28-1.

• 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 target cell is created by adding together changing cells, and the constraint is created by comparing the result obtained by adding together the product of each changing cell times a constant (either 1 or 0) to the required number of workers.