This article was adapted from Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston. Visit Microsoft Learning to learn more about this book. This classroom-style book was developed from a series of presentations by Wayne Winston, a well known statistician and business professor who specializes in creative, practical applications of Excel. So be prepared — you may need to put your thinking cap on.

Sample files  You can download the sample files that relate to excerpts from Microsoft Excel Data Analysis and Business Modeling from Microsoft Office Online. This article uses the file Bank24.xls.

Many organizations (banks, restaurants, postal services) know what their labor requirements will be at different times and need a method to efficiently schedule their workforce to meet their labor requirements. You can use the Excel Solver to easily address a problem such as this. Here’s an example.

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 given in row 14 of the file Bank24.xls, which is shown in the following figure. For example, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, and so on. All bank employees work five consecutive days. What is the minimum number of employees Bank 24 can have and still meet its labor requirements?

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 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, 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 in each cell in the range C5:I11 a 1 or a 0. 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 the following figure.

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 then filled in the Add Constraint dialog box as shown in the following figure.

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 selecting these options in the Solver Options dialog box. After clicking Solve, we find the optimal solution.

A total of 20 employees is needed. One employee starts on Monday, 3 start on Tuesday, 0 start on Wednesday, 4 start on Thursday, 1 starts on Friday, 2 start on Saturday, and 9 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.

Problems

1. Suppose our bank had 22 employees, and that their goal was to schedule employees so that they would have the maximum number of weekend days off. How should the workers be scheduled?
2. Suppose Bank 24 employees are paid $150 per day the first 5 days they work and can work a day of overtime at a cost of $350. How should the bank schedule its employees?
3. The number of telephone reservation operators needed by an airline during each time of day is as follows:

   Time	Operators Needed
   Midnight–4 A.M.	12
   4 A.M.–8 A.M.	16
   8 A.M.–Noon	22
   Noon–4 P.M.	27
   4 P.M.–8 P.M.	31
   8 P.M.–Midnight	22

   
   

   Each operator works one of the following 6-hour shifts: midnight–6 A.M., 6 A.M.–noon, noon–6 P.M., 6 P.M.–midnight. What is the minimum number of operators needed?

4. Shown below are the number of people in a variety of demographic groups who watch various TV shows, and the cost (in thousands of dollars) of placing a 30-second ad with each show. For example, it costs $160,000 to place a 30-second ad on Friends. The show is watched by 6 million males between the ages 18 and 35, 3 million males between 36 and 55, 1 million males over 55, 9 million women between 18 and 35, 4 million women between 36 and 55, and 2 million women over 55. The data also includes the number of people in each group (in millions) that we want to see the ad. For example, the advertiser wants at least 60 million 18 to 35 year old males to see its ads. What is the cheapest way to meet our goals?