Method 1 – Calculating Minimum Number of Telephone Reservation Operators to Meet Labor Demands
Look at the dataset before explaining the situation.
It 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., and 6:00 P.M. to midnight. The minimum number of operators needed is shown in the range C12:H12.
Determine the objective, changing cells, and constraints of this problem.
Objective: Minimize the total number of employees
Changing cells: The number of employees working each 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, information was put in the range C5:H8. The number of employees working each of those six-hour shifts will be filled into the range I5:I8. Since the numbers are unknown and need to be solved, we will leave them blank now.
We prepared the sheet by the usage of the SUM and SUMPRODUCT functions.
Follow these steps to prepare the dataset and optimize the schedule in Excel.
Steps:
- Enter the following formula into cell C14 to calculate the total number of employees.
=SUM($I$5:$I$8)
- Press Enter.
- Calculate the number of employees available from midnight to 4 A.M., and enter the following formula into cell D10.
=SUMPRODUCT($I$5:$I$8,C5:C8)
- Press Enter.
- Click and drag this to the right till cell H10 replicates the formula.
- Click on the Data tab and then click on Solver in the Analysis group to open the Solver Parameters dialog box.
- Fill in the Solver Parameters box with the essential information as shown in the following figure.
- Click Solve.
- A Solver Results box will appear like the following. Click OK.
The cells of the Excel spreadsheet will be filled out as follows, indicating the optimum number of operators required in this schedule optimization problem.
Method 2 – Calculating Minimum Number of Bank Employees to Meet Labor Demands
For solving this schedule optimization problem in Excel, we will use the following dataset.
The number of workers needed for a bank is shown in cells from C15 through I15. 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 can this bank have to meet its labor requirement?
The numbers of employees who start work (the first of five consecutive days) each day of the week will be recorded in the range J5:J11. The total number of employees – is put into cell C17. The range C5:I11 is used to track whether 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 C5:G5 means that employees start working on Monday and work Monday through Friday. We prepared the sheet by the usage of the SUM and SUMPRODUCT functions
Follow the steps to solve the schedule optimization problem in Excel.
Steps:
- To count the total number of employees who are working on Monday is filled into cell C13 by using the following formula.
=SUMPRODUCT($J$5:$J$11,C5:C11)
- Replicate it by clicking and dragging the fill handle icon to the right.
- Click on the Data tab and Solver in the Analysis group to open the Solver Parameters dialog box.
- Fill in the Solver Parameters box with the essential information shown in the following figure.
- Click Solve.
- A Solver Results box will appear like the following. Click OK.
The cells of the Excel spreadsheet will be filled out as follows, indicating the optimum number of employees required to meet labor demands in this schedule optimization problem.
Method 3 – Minimizing Salary That Banks Should Pay Employees
If the employees in the same bank (in case 2) are paid $150 per day for the first five days, and they work a day of overtime at a cost of $350. How should the bank schedule its employees?
There are two parts to the dataset. The range C8:I14 provides information that can be used to compute constraints, while the range C21:I27 offers information for calculating how much the bank should pay its employees. In this case, are the same as those in case 2. And similar to case 2, we have prepared the sheet using the SUM and SUMPRODUCT functions.
Now we can follow these steps once we have the spreadsheet ready at our disposal.
Steps:
- Go to the Data tab on the ribbon.
- Select Solver from the Analyze group.
- Fill out the Solver Parameters box with the essential information in the figure below.
- Click on Solve.
- In the Solver Results box, click OK.
The cells of the Excel spreadsheet will get filled up like the following indicating the minimum salary the bank can pay employees in this schedule optimization problem.
Method 4 – Maximizing Number of Weekend Days Off with a Fixed Number of Employees
The bank has 22 employees. How should the workers be scheduled so that they would have the maximum number of weekend days off?
This spreadsheet has two parts too.
We prepared the sheet by the usage of the SUM and SUMPRODUCT functions. Once the sheet is prepared, you can follow these steps to tackle this schedule optimization problem in Excel.
Steps:
- Go to the Data tab on the ribbon.
- Select Solver from the Analyze
- The Solver Parameters box will appear. Now, fill out the Solver Parameters box with the essential information shown in the figure below.
- Click on Solve.
- In the Solver Results box, click OK.
The Excel spreadsheet will get filled up like the following indicating the minimum number of weekend days off with a fixed number of employees in this schedule optimization problem.
Download the working file from the link below.
Related Articles
- How to Solve Linear Optimization Model in Excel
- How to Solve Network Optimization Model in Excel
- How to Calculate Optimal Product Mix in Excel
- Mean Variance Optimization in Excel
- Perform Multi-Objective Optimization with Excel Solver
<< Go Back to Optimization in Excel | Solver in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thank you very much, Zhiping. Examples you provided refreshed my memory, which are helpful. Good work, much appreciated indeed.
Hi Peter,
Thank you.
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.
Greetings, Huda.
I appreciate you asking this question. Solved examples are available about distribution or transportation problems. You can find it here “Solving Transportation or Distribution Problems using Excel Solver”.
In method 2, why is it says “Objective Cell contents must be a formula”?
Hello Cris,
The error “Objective Cell contents must be a formula” occurs because Excel Solver requires the objective cell to contain a formula that calculates the outcome to optimize, such as the total cost or profit.
If the cell only contains a value or lacks a calculative formula, Solver cannot determine how to adjust the variables to achieve the desired goal. Make sure the objective cell references other cells through a formula that depends on the decision variables.
Regards
ExcelDemy