Schedule Your Workforce using Excel Solver [4 Case Studies]

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?

Time Operators needed
Midnight-4 A.M. 12
4 A.M.-8 A.M. 16
8 A.M.-noon 22
Noon-4 P.M. 30
4 P.M.-8 P.M. 31
8 P.M.-midnight 22

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.

Schedule workforce using Excel Solver Image 1

Figure 1.1

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.

Schedule workforce using Excel Solver Image 2

Figure 1.2 [click on the image to get a full view]

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?
Schedule workforce using Excel Solver Image 3

Figure 1.3

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?

Read More: GRG Multistart and Evolutionary Excel Solver Engines [2 Case Studies]

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.

Schedule workforce using Excel Solver Image 4

Figure 2.1

Use the same approach to filling Solve Parameters dialog box.

Schedule workforce using Excel Solver Image 5

Figure 2.2 [click on the image to get a full view]

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.
Schedule workforce using Excel Solver Image 6

Figure 2.3

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?

Read More: Financial Planning with Excel Solver [2 Case Studies]

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.

Schedule workforce using Excel Solver Image 7

Figure 3.1 [click on the image to get a full view]

The content that we filled into the Solver Parameters dialog is the same as that in case 2.
Schedule workforce using Excel Solver Image 8

Figure 3.2 [click on the image to get a full view]

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.
Schedule workforce using Excel Solver Image 9

Figure 3.3

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?

Read More: Deal with Sequencing Problems Using Excel Solver!

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.

Schedule workforce using Excel Solver Image 10

Figure 4.1

From Figure 4.2, we can see that there are one more constraint – “$G$2 = 22”.

Schedule workforce using Excel Solver Image 11

Figure 4.2 [click on the image to get a full view]

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.
Schedule workforce using Excel Solver Image 12

Figure 4.3

Download working file

Download the working file from the link below.

Zhiping Yan

Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

2 Comments
  1. Thank you very much, Zhiping. Examples you provided refreshed my memory, which are helpful. Good work, much appreciated indeed.

  2. Thank you for your effort in the examples you provided, they are clear and understandable……Are there any solved examples about distribution or transportation problem using solver?. I appreciate your response. Thank you very much.

Leave a reply

ExcelDemy
Logo