Zhiping Yan
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 along with schedule optimization in Excel 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 a decision variable that we can adjust or change to optimize our objective. And Subject to the Constraints is our explicit or implicit constraints.
Download Practice Workbook
Download the working file from the link below.
What Is Solver in Excel?
Solver is a Microsoft Excel add-in program. The Solver is part of the What-If Analysis tools that we can use in Excel to test different scenarios. We can solve decision-making issues using the Excel tool Solver by finding the most perfect solutions. They also analyze how each possibility impacts the worksheet’s output.
How to Enable Solver Feature in Excel
You can access Solver by choosing Data ➪ Analyze ➪ Solver. Sometimes it may happen that this command isn’t available, you have to install the Solver add-in using the following steps:
- First of all, choose the File
- Secondly, select Options from the menu.
- Thus, the Excel Options dialog box appears.
- Here, go to the Add-Ins
- At the bottom of the Excel Options dialog box, select Excel Add-Ins from the Manage drop-down list and then click Go.
- Immediately, the Add-ins dialog box appears.
- Then, place a checkmark next to Solver Add-In, and then click OK.
Once you activate the add-ins in your Excel workbook, they will be visible on the ribbon. Just move to the Data tab and you can find the Solver add-in on the Analyze group.
Techniques for Schedule Optimization
By schedule optimization, we generally mean to make sure that the whole schedule (every individual task and action) aligns with a specific purpose. Schedule optimization is a win-win scenario for any aspect imaginable. You can efficiently work around your resources, manage employees and times better, reduce the workload, etc. To optimize your schedule, you can work around the following important techniques.
- Identifying priority: The one that should be obvious from any schedule optimization definitions.
- Assessing the to-do list: To make sure that your tasks align with your purpose or not, you need to assess and judge the actions of your to-do list.
- Formulating actions: You don’t have to plan everything to the perfect point. But every good job starts with a good plan. So you should aim to formulate one that aligns most with your purpose.
- Reassessing plans: Not everything will be perfect on the first go. You may slip up things here and there. Also, you can miss out on important steps or actions or find a better way to do things. So it is very important to reassess your plans continuously even if they are seemingly perfect at a glance.
- Executing: An average plan in action is better than a perfect plan on paper. So make sure to follow up with the plans and send them out to your employees or peers and follow the adjustments accordingly.
Can You Create a Schedule in Excel?
In short, yes, you definitely can. Microsoft Excel is a wonderful tool to record and track schedules. In fact, Excel has some built-in weekly, daily, and monthly schedule templates built into it. You can simply select the File tab and search for your desired schedule from the backstage view.
Or you can make one on your own. Follow these simple steps for that.
- First, select some cells to record time in them and put them in your preferred alignment. For example, we are putting them in a vertical alignment.
- Then add the days of the week on the right as headers.
- Finally, add some borders to the to-be-used cells.
There you have it- a weekly schedule in its simplest form made in Excel. You can track your work progress or plan ahead by putting the values in the cells beside the intended time.
4 Cases of Schedule Optimization for a Particular Workforce Using Excel Solver
Here, we are going to use Excel’s solver feature for schedule optimizations. We are going to look at four different cases of the same organization. And we are going to optimize them in four different ways depending on what we need in each case.
Case 1: Calculating Minimum Number of Telephone Reservation Operators to Meet Labor Demands
Let’s look at the dataset before explaining the situation.
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., and 6:00 P.M. to midnight. The minimum number of operators needed is shown in the range C12:H12.
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 the range C5:H8. The number of employees working each of that 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 have 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:
- First, enter the following formula into cell C14 to calculate the total number of employees.
=SUM($I$5:$I$8)
- Then press Enter.
- To calculate the number of employees available from midnight to 4 A.M., enter the following formula into cell D10.
=SUMPRODUCT($I$5:$I$8,C5:C8)
- Then press Enter.
- Now click and drag this to the right till cell H10 replicates the formula.
- Next, click on the Data tab and then click on Solver in the Analysis group to open the Solver Parameters dialog box.
- After that, fill in the Solver Parameters box with the essential information as shown in the following figure.
- Now click on Solve.
- Consequently, a Solver Results box will appear like the following. Click OK on it.
Finally, the cells of the Excel spreadsheet will get filled up like the following indicating the optimum number of operators required in this schedule optimization problem.
Read More: How to Make an Hourly Schedule in Excel (with Easy Steps)
Case 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. 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 the figure again. 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. Our objective – the total number of employees – is put into cell C17. The range C5:I11 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 C5:G5 means that employees start working on Monday and work Monday through Friday. We have prepared the sheet by the usage of the SUM and SUMPRODUCT functions
Now 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)
- Then replicate it by clicking and dragging the fill handle icon to the right.
- Next, click on the Data tab and then click on Solver in the Analysis group to open the Solver Parameters dialog box.
- After that, fill in the Solver Parameters box with the essential information as shown in the following figure.
- Now click on Solve.
- Consequently, a Solver Results box will appear like the following. Click OK on it.
Finally, the cells of the Excel spreadsheet will get filled up like the following indicating the optimum number of employees required to meet labor demands in this schedule optimization problem.
Read More: How to Create a Project Schedule in Excel (with Easy Steps)
Similar Readings
- How to Make Price Optimization Models in Excel (with Easy Steps)
- Make a Class Schedule on Excel (With Easy Steps)
- How to Solve Network Optimization Model in Excel (3 Cases)
- Mean Variance Optimization in Excel (With Easy Steps)
Case 3: Minimizing Salary That Banks Should Pay Employees
Suppose those 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?
Generally, 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. Constraints, 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:
- First, go to the Data tab on the ribbon.
- Then select Solver from the Analyze group.
- Now fill out the Solver Parameters box with the essential information shown in the figure below.
- After that, click on Solve.
- In the consequent Solver Results box, click on OK.
As a result, 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.
Read More: Excel Optimization with Constraints (3 Case Scenarios)
Case 4: Maximizing 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?
Just like the previous one, this spreadsheet has two parts too.
We have 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 particular schedule optimization problem in Excel.
Steps:
- First, go to the Data tab on the ribbon.
- Then select Solver from the Analyze
- Consequently, the Solver Parameters box will appear. Now fill out the Solver Parameters box with the essential information as shown in the figure below.
- After that, click on Solve.
- In the consequent Solver Results box, click on OK.
As a result, the cells of 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.
Read More: How to Create a Workback Schedule in Excel (with Easy Steps)
Conclusion
That was all for the schedule optimization in Excel. Hopefully, you have grasped the idea of using the solver feature to optimize schedules in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
For more guides like this, visit Exceldemy.com.
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”.