I have written an article on how to determine the optimal product mix using Excel Solver. Today, I’d like to discuss how to schedule the workforce with the same method. Schedule a workforce is also a Linear Programming problem. If you are not familiar with LP or Excel Solver, you can read my previous post – Use Solver to determine the optimal product mix -for details. It will make this presentation easier to understand.
Here is to refresh your mind before moving forward to solve problems. There are three parts essential to Excel Solver: Set Objective, By Changing Variable Cells and Subject to the Constraints. Set Objective represents our objective in the LP model. By Changing Variable Cells represents decision variable which we can adjust or change to optimize our objective. And Subject to the Constraints is our explicit or implicit constraints.
Case 1: Calculate the minimum number of telephone reservation operators to meet labor demands
Here shows the number of telephone reservation operators needed by an airline during each time of day. Each operator works one of the following six-hour shifts: midnight to 6:00 A.M, 6:00 A.M. to noon, noon to 6:00 P.M., 6:00 P.M. to midnight. What is the minimum number of operators needed?
|4 A.M.-8 A.M.||16|
|4 P.M.-8 P.M.||31|
Let’s determine the objective, changing cells and constraints of this problem first.
|Objective||Minimize the total number of employees|
|Changing cells||The number of employees who work each of that six-hour shift.|
|Explicit Constraints||For each time of day, the number of employees who are working must be greater than or equal to the number of employees required|
|Implicit Constraints||Each changing cell must be a non-negative integer|
To set up the model for this problem, problem information was put in range D5:I8. The number of employees working each of that six-hour shifts will be filled into range B5: B8. Since the numbers are unknown and need to be solved, I will leave them blank now. The number of employees required for each time of day will be entered into range D12: I12. The formula “=SUM ($B$5: $B$8)” is entered into cell D2 to calculate the total number of employees. To calculate the number of employees available from midnight to 4 A.M., the formula “=SUMPRODUCT ($B$5: $B$8, D$5: D$8)” will be filled into D10. Then click on cell D10, use CTRL + C and CTRL + V to copy-paste this formula into cells E10: I10 to calculate the number of employees available at other times of day.
Click on the Data tab and then click on Solver in the Analysis group to open the Solver Parameters dialog box and fill in essential information as shown in Figure 1.2.After clicking Solve and Ok showed in Figure 1.2, Excel will return a solution in range B5:B8 and cell D2. You can see that we only need 86 employees to meet requirements. If you add together numbers in range D12:I12, you will get 133. Much fewer resources, right?
Case 2: Calculate the minimum number of bank employees to meet labor demands
The number of workers needed for a bank is shown in cells from D13 through J13. For example, 17 workers are needed on Monday, 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 that this bank can have to meet its labor requirement?
Look at Figure 2.1. Numbers of employees who start work (the first of five consecutive days) each day of the week will be recorded in range B5:B11. Our objective – the total number of employees – is put into cell D2. Range D5:J11 is used to track if employees work or not. 1 means that the employee will work on that weekday while 0 indicates that the employee will not work on that day. For example, 1 in D5: H5 means that employees start working on Monday and work Monday through Friday. The total number of employees who are working on Monday is filled into cell D13. Then we copy the formula “=SUMPRODUCT ($B$5: $B$11, D$5: D$11)” in cell D13 and paste the formula into cells E13: I13 to compute the number of employees who are working on other days.
Use the same approach to filling Solve Parameters dialog box.After clicking Ok in figure 2.2, we will get a solution as below. It tells this bank needs at least 20 employees to meet its demand.
Case 3: Minimize salary that bank should pay the employees
Suppose those employees in the same bank (in case 2) are paid $150 per day the first five days and they work a day of overtime at a cost of $350. How should the bank schedule its employees?
Generally, there are two parts in Figure 3.1. Range D5:J11 provides information that can be used to compute constraints while range D18:J24 offers information for calculating how much the bank should pay its employees. Constraints, in this case, are the same as those in case 2.The content that we filled into the Solver Parameters dialog is the same as that in case 2. The solution that Excel returns is showed in Figure 3.3. The bank has to hire 17 employees and pay them $18,700 each week.
Case 4: Maximize the number of weekend days off with a fixed number of employees
Suppose that the bank has 22 employees. How should the workers be scheduled so that they would have the maximum number of weekend days off?
The formula =SUMPRODUCT ($B$5: $B$11, $I$18: $I$24) + SUMPRODUCT ($B$5: $B$11, $J$18: $J$24)” was entered into cell D2 to calculate the number of weekend days off. And this is our objective cell. Plus the constraints which are the same as those in case 2, we also have another explicit constraint – the total number of employees should be 22 which can be calculated by “=SUM ($B$5: $B$11)” in cell G2.
From Figure 4.2, we can see that there are one more constraint – “$G$2 = 22”.After clicking Ok in the Solver Results dialog box, we get the below solution. 10 employees start to work on Monday, 3 employees start to work on Wednesday, 1 employee start to work on Thursday, 5 employees start to work on Friday and 3 employees start to work on Sunday. In total, all 22 employees can have 23 weekend days off.
Download working file
Download the working file from the link below.