Is scheduling costing your business customers, profit, and growth opportunity? In this article, we will look at a variety of ways that scheduling plays a key role in the operations and growth of your business, look at a case study of how the addition of automated scheduling to a legacy spreadsheet instantly makes a business more profitable and opens new opportunities. I will describe how this solution is integrated into existing office documents, and link to all of the tools used in the solution.
Businesses of all sorts do scheduling as routine part of business. Appointment scheduling is the lifeblood of all sorts of professional practices . Service businesses that conduct installations, repairs, or projects need to schedule crews for timely completion of jobs at the convenience of customer requests. If a business is to thrive and grow, these jobs should be scheduled efficiently — meaning as many jobs get completed as possible within the time and resources available to your company.
Many businesses start as small operations, scheduling one job at a time as the needs arise, for a single person or a small crew. As you add customers and expand your service just a little, the same simple schedules, often kept on a spreadsheet or even updated on paper, strain to keep up. Signs that your scheduling is holding back your business growth might include:
Signs that your scheduling is holding back your business growth
- Routine production or schedules consume more time and effort than you have.
- Your time, your employees’ time, your equipment are not being used efficiently when in service.
- Your capacity to add customers and jobs decreases, or your profit growth slows as you grow.
- You cannot schedule service at the convenience of most customers, and your scheduling.
Introducing Notional Landscaping, Co.
Let’s take a look at an example with a case study I will fictionalize and call Notional Landscaping Co.
This business started as a single-crew operation, and has grown to service over two hundred weekly yard care customers. This year for the first time, the company has added equipment and temporary hires to equip a second crew to handle the growing demand. Customers are getting serviced, but Notional has heard gripes from customers that their jobs are not regularly done on the days they requested. Both crews are working through the week, sometimes on long days and other times with short ones. Some customers have bailed for the reliability of larger operations, and Notional cannot add more crews without a more efficient way to utilize them.
This is a scheduling problem. When Notional signs up a new weekly yard care customer, the customer states the day or days that they prefer to have their yard serviced. They can also designate days that they prefer not to have it serviced. More on this later. On a weekly basis, Notional must make a schedule so that:
- Each yard is scheduled to be serviced weekly;
- Within the capacity of the crews to complete in a standard work day;
- On a day that the customer is happy with.
And ideally, if the scheduling could be done really well, Notional would pursue two new goals:
- Retain happy customers by maximizing the number of yards cut on the most preferred days, and
- Save expenses by running the second crew only when needed.
Notional’s Legacy Scheduling
As we’ll see, a schedule that meets these goals will make the business instantly more successful in both customer satisfaction and profitability. First, a quick overview of Notional’s legacy scheduling. Notional’s client base is listed in a spreadsheet, and one sheet is used to record the customer preferences for yard service days. On signup, customers identify their most preferred days:
Notional had been doing a pretty good job manually scheduling customers when the client base was small. As the company added clients, the company developed a system to click in the cells to designate who to service on what days. Even with that automation, the manual scheduling takes about a half-hour for the 220 clients in this list, and much longer to make adjustments to improve customer satisfaction and job leveling. The manual solution we start with for this week does manage to schedule all customers on “preferred” and “acceptable” days, with 97/220 clients (44%) getting service on their #1 preferred days. And since the company typically assigns up to twenty jobs to a crew on a day, it has both crews busy on each day:
BEFORE:
| Mon | Tues | Wed | Thurs | Fri | Sat | Sun |
| 27 clients | 29 clients | 27 clients | 26 clients | 28 clients | 40 clients | 43 clients |
| 2 crews | 2 crews | 2 crews | 2 crews | 2 crews | 2 crews | 2 crews |
44% (97/220) clients serviced on their most preferred day
14 Crew-days
Optimizing schedule production
An automated scheduling solution added to this existing spreadsheet improves this schedule to increase to 80% the customers serviced on their #1 choice of day. The company does not need to invest in expensive enterprise software or to solve this problem. Here I summarize how Notional’s scheduling problem can be modeled so that it can be solved in seconds by free (and sophisticated) optimization programs, and demonstrate the solution in the video below. A plain-language statement of the constraints that are sent to the solver are the following:
- Number of clients, number of days to schedule
- Number of visits each client is to be scheduled for in the week (default, 1)
- The minimum and maximum number of crews that can be scheduled on a work day (1 and 2 in this scenario)
- Number of jobs each crew can complete in a day (20 in this scenario)
- The minimum and maximum number of jobs we wish to be scheduled per day
- Each client’s stated preferences as described above
Scheduling problems such as this can be modeled in a number of ways for efficient solving. Since all of these constraints are naturally expressed as integers, we can solve this as what is known as a “mixed-integer programming model”. All we need to know about that for this demonstration is that there are a number of free solvers that do an excellent job solving these problems. I solve this problem using a model written in a modeling language that allows us to solve the problem for any week, provided the above constraints are passed in to describe that week’s needs. This way, the solution stays usable no matter how the company grows or changes the way it wants to divide up the work. To do this, we have added a new worksheet to Notional’s Excel scheduling file. The user can adjust the numbers on this worksheet, click a button, and solve the problem. While this can be done without the need to directly run the solver, in this demonstration I will show the steps individually to give you an idea where the power lies in such a solution. In the video below, the constraints are instantly generated for our model based on the Client Requests. We then pass those constraints to the solver and run the model. The model takes around 30 seconds to calculate the optimal solution. Again for the purpose of demonstration, we copy and paste that solution (a list of numbers representing the schedule) into our Excel file, and the Service Schedule is automatically populated.
Video Demonstration
Results
This process is hugely more efficient than manual scheduling, and the resulting schedule is a vast improvement over those results. Notice here that our solver increased customer satisfaction (as measured by percentage of clients serviced on a first-priority day) from 44% to 80%, while also reducing operating costs (completing the jobs in 11 crew-days, down from 14).
AFTER:
| Mon | Tue | Wed | Thus | Fri | Sat | Sun |
| 20 clients | 20 clients | 20 clients | 40 clients | 40 clients | 40 clients | 40 clients |
| 1 crew | 1 crew | 1 crew | 2 crews | 2 crews | 2 crews | 2 crews |
80% (177/220) clients serviced on their most preferred day
11 Crew-days
What’s more, the schedule can still be manually adjusted after the solver, or even partially-specified before sending the preferences to the solver, so that the user retains as much or as little control over schedule production as desired.
New tools, New insights
When scheduling can be optimized automatically, a business will begin to see new opportunities to grow. This particular schedule happened to reveal how Notional could use the crews more cost-effectively — and also revealed hidden capacity to service perhaps 60 additional new clients. Looking at this from a planning perspective, this also tells the business how many customers they would require to justify adding even more crews. In fact, since the solver finds optimal schedules quickly, Notional’s owner can change constraints and re-run the scheduler to explore different ways to organize the work and look for new ways to do business. This kind of what-if analysis is the beginning of business intelligence that informs long-range planning for continued growth and success.
Resources
Aside from the Microsoft Excel file that we started with, the only tools used to solve this problem are the constraint optimization modeling software, MiniZinc www.minizinc.org, and one of the solvers bundled with that tool, called the COIN-OR Cbc solver https://projects.coin-or.org/Cbc. These modeling tools and solvers are wonderful, free alternatives to their commercial counterparts, many of which have very steep licensing plans. I expect they will make their appearance in future case studies. Many thanks to the developers who release and maintain these and other excellent tools. I encourage anyone inclined to do this kind of modeling to download and try these out. And of course, please do not hesitate to contact me for help on your project.
Until next time,
Jim
(your Office Expert)