KomuraSoft LLC
Consult us
Menu

Optimizing Shift Schedules and Delivery Routes — When to Move from Manual Excel Work to Mathematical Optimization

· · Mathematical Optimization, Shift Scheduling, Delivery Planning, Excel, OR-Tools, Business Improvement

Post on X
Revision history (first version, published Nov 1, 2026)
First published
Cite this article(DOI: 10.5281/zenodo.23856488)

This article is archived on Zenodo. Below are both the DOI that always resolves to the latest version and the DOI pinned to the version you are reading.

Go Komura (2026). Optimizing Shift Schedules and Delivery Routes — When to Move from Manual Excel Work to Mathematical Optimization. KomuraSoft LLC. https://doi.org/10.5281/zenodo.23856488 https://comcomponent.com/en/blog/shift-route-optimization-excel-decision/

DOI (latest version)
10.5281/zenodo.23856488
DOI (this version)
10.5281/zenodo.23856489

When you build shift schedules or delivery routes by hand in Excel, the person doing it keeps all the rules in their head and finishes the plan by adjusting it a little at a time. This article collects what you need to judge whether that work can be handed to mathematical optimization, and whether it should be.

Here is the short answer. Mathematical optimization writes down the values to decide, the rules to keep, and the yardstick for good and bad as formulas, and has software search the plans that satisfy the rules for a good one. The more combinations there are than anyone can check by hand, the more clearly the rules can be put into words, and the more often the plan has to be rebuilt, the more there is to gain by moving. Conversely, when the problem is small and has few rules, or when the rules cannot be written down, either manual work is fine or the first step is to get the rules and data in order.

The scope is shift schedules (duty rosters) at small and mid-sized workplaces and delivery routes driven with a company’s own vehicles. How to write the formulas and the details of the programs are not covered. The numbers in the article are worked examples for explanation; they are not a particular company’s case or a measured effect.

What mathematical optimization decides

In mathematical optimization, a problem is written in three parts.

  • Values to decide (decision variables): who works which shift on which day, which vehicle goes to which delivery stop in what order
  • Rules to keep (constraints): the number of people each shift needs, at most one shift per day per person, the maximum load a vehicle can carry, and so on
  • The yardstick for good and bad (objective function): the number of requests granted, the total distance traveled, the length of the longest route, and so on

When you hand these three to software called a solver, it searches the plans that satisfy the rules for the best one by the yardstick, or a good enough one. Google’s OR-Tools describes combinatorial optimization as “finding the best solution to a problem out of a very large set of possible solutions”1.

Inputs and output of mathematical optimizationShows the flow of writing the values to decide, the rules to keep and the yardstick for good and bad as formulas, handing them to a solver, receiving a plan that satisfies the rules, and having a person check it.Values to decide (decision variables)SolverRules to keep (constraints)Yardstick for good and bad (objective function)A plan that satisfies the rulesA person checks and uses it

Figure 1: Write the values to decide, the rules to keep and the yardstick as formulas and hand them to a solver, and a plan that satisfies the rules comes back.

Excel has a feature built on the same idea. The Solver add-in changes the variable cells so that the value of the objective cell is maximized or minimized, while making the constraints hold. You can also add constraints that restrict variable cells to integers (int) or to 0 or 1 (bin)2.

Among linear optimization problems, those that restrict some of the values to decide to integers are called mixed-integer programming (MIP). In the OR-Tools explanation, Boolean variables are used for 0-or-1 decisions3. “Does this person work the early shift on this day?” in a shift schedule is exactly this kind of 0-or-1 decision.

Shift schedules

The OR-Tools employee scheduling example uses a nurses’ roster as its subject. It sets the rules that each shift on each day is assigned to one nurse, that a nurse works at most one shift per day, and that each nurse works at least a certain number of shifts over the period, and it spreads the assignments as evenly as possible. In the example that adds shift requests, it maximizes the number of requests granted. Both examples are solved with the OR-Tools CP-SAT solver4.

Writing a shift schedule as a mathematical optimization problemShows that a shift schedule decides with 0 or 1 who works which shift on which day, keeps rules such as required staffing and one shift per day, and uses the number of granted requests as the yardstick.Assignment to shifts (0 or 1)Shift schedule modelStaff needed for each shiftAt most one shift per dayMaximize granted requests

Figure 2: A shift schedule decides assignments to shifts as 0 or 1 and grants as many requests as possible under rules such as required staffing.

Delivery routes

The vehicle routing problem (VRP) is the problem of finding the best routes for several vehicles visiting several locations. OR-Tools describes it as almost the same as the traveling salesman problem (TSP), in which one vehicle makes the rounds5. In the OR-Tools example, the yardstick is to shorten the longest route among all vehicles5. Rules such as the maximum load a vehicle can carry can also be added1.

In the diagram a solid line marks a relation that always holds and a dashed line marks a conditional one (the conditions are given per relation on the detail page). The full list of relations (10 in total, with evidence and certainty) and the definitions of the main concepts are collected on the knowledge map detail page (in Japanese). Data: JSON-LD / Turtle

Where manual work in Excel stalls

The number of combinations

With 10 delivery stops and one vehicle, the number of ways to order the stops is 10 × 9 × … × 1 = 3,628,800. With 15 stops it is about 1.3 trillion, and with 20 stops about 2.43 quintillion (each of these counts only the orderings of the stops and does not include actual road conditions).

Shift schedules are the same. If you choose among early shift, late shift and day off for 5 people over 30 days, there are 3 choices per person per day, so the number of combinations is 3 to the 150th power, about 3.7 × 10 to the 71st. Even in a problem as small as the OR-Tools example of 4 nurses, 3 days and 3 shifts, there are 5,184 rosters that satisfy the rules, and for most problems enumerating every plan is considered impractical4.

Number of delivery stops and number of visiting ordersShows that with one vehicle the number of orders grows to about 3.63 million for 10 stops, about 1.3 trillion for 15 stops and about 2.43 quintillion for 20 stops, beyond what can be compared by hand.10 stops: about 3.63 million orders15 stops: about 1.3 trillion20 stops: about 2.43 quintillionBeyond what can be compared by hand

Figure 3: Each time five more delivery stops are added, the number of visiting orders grows by orders of magnitude.

By hand, you can compare only a tiny fraction of these plans. Experience can bring the person building the plan close to a good one, but there is no way to confirm whether a better plan exists.

Software does not check everything either. For the traveling salesman problem, no method is known that solves the general case efficiently6. Real solvers put an upper limit on search time and return the good plan they found within that time. OR-Tools route search lets you set a time limit, and among the search statuses there is a value indicating that “the solution could be improved if the solver had more time”7.

Rules that must be kept and rules to keep if possible

Some rules make the plan invalid if broken; others are ones you would like to keep if possible. The former are called hard constraints and the latter soft constraints. The OR-Tools shift scheduling sample sets, for things such as the number of consecutive days off, both hard upper and lower bounds that must be kept and soft upper and lower bounds to keep if possible, and adds a penalty to the objective function for the amount by which the soft range is exceeded. Requests are also handled with weights, so they are decided in balance with the other rules8.

Handling hard and soft constraintsShows that hard constraints that must be kept remove plans that break them from the candidates, while soft constraints to keep if possible add the deviation to the objective function as a penalty and are decided in balance with the other rules.Shift rulesHard constraints (must keep)Soft constraints (keep if possible)Remove plans that break themAdd the deviation as a penaltyDecided in balance with other rules

Figure 4: Rules that must be kept narrow down the plans; rules to keep if possible are reflected in the yardstick as penalties.

In manual Excel work, this distinction and the order of priority live in the head of the person building the plan. If rules such as “this person does not work two night shifts in a row” or “this customer gets deliveries in the morning” are not written down anywhere, the same plan cannot be produced when that person is replaced. To move to mathematical optimization, these rules have to be written out in a form that can be turned into formulas. Put the other way round, writing them out is itself the work of turning a plan that depends on one person into something that can be handed over.

Rebuilding when the rules change

When people come and go, time off is requested, or delivery stops are added, part of the plan has to be rebuilt. By hand, the person building the plan follows the effects of each change one by one and checks that no other rule has been broken. With mathematical optimization, you change the data and have the solver solve again, and the solver searches afresh for a plan that satisfies all the written rules. When no plan satisfies them, you find that out. The OR-Tools CP-SAT solver returns the result INFEASIBLE when it has proved that no plan satisfies the rules9. The more often you rebuild, the bigger this difference becomes.

The size limit of Excel Solver

The Excel Solver add-in is also a mathematical optimization tool, but it accepts at most 200 variable cells2. Frontline Systems also states that the standard Excel Solver handles at most 200 decision variables for both linear and nonlinear problems10. Representing the earlier example of 5 people, 30 days and 3 kinds of shift with 0-or-1 variables gives 5 × 30 × 3 = 450 variables, which exceeds that limit. In addition, Excel for the web cannot use add-ins, so Solver is not available there2.

Number of shift-schedule variables and the Excel Solver limitShows that representing 5 people, 30 days and 3 kinds of shift with 0-or-1 variables takes 450 variables, exceeding the 200 variable cells the standard Excel Solver accepts, so an optimization library should be considered.5 people × 30 days × 3 kinds of shift450 variables of 0 or 1Standard Excel Solver limit is 200Consider an optimization library

Figure 5: Even a shift schedule for 5 people over 30 days needs more 0-or-1 variables than the standard Excel Solver allows.

For problems beyond this size, use an optimization library such as OR-Tools or HiGHS. OR-Tools is open source, includes a route search library and a constraint programming solver called CP-SAT, and can be used from C++, Python, C# and Java11. HiGHS is a solver for linear programming, mixed-integer programming and more, released under the MIT license and usable from Python and other languages12.

Criteria for deciding whether to move to mathematical optimization

Go through the items in the following table in order to decide whether to move. The more situations in the middle column apply, the more there is to gain by moving; the more in the right column apply, the more likely it is that manual work is fine or that preparation comes first.

What to look at Much to gain by moving Manual work is fine, or prepare first
Size of the problem Many people, days or delivery stops; the combinations cannot all be compared by hand A few people or a few stops; the person building the plan can see all of it
Writing down the rules The rules can be put into words, and their priority can be decided The rules exist only as the planner’s intuition and cannot be put into words
How often the plan is rebuilt Rebuilt daily or weekly Built only a few times a year
Data Availability, qualifications, delivery addresses and quantities exist as electronic data They exist only on paper or in ad hoc communication
Impact of a bad plan Staff shortages, overtime, late deliveries or fuel costs change significantly The quality of the plan barely changes the outcome
Burden on the planner Only a particular person can build the plan, and it takes a large share of their time Anyone can build it in a short time
Flow for deciding whether to move to mathematical optimizationShows checking whether the combinations can be compared by hand, then whether the rules can be written down and whether the data is available, trying it on a small scale if they are, and otherwise getting the rules and data in order first.YesNoNoYesNoYesCan the combinations be compared by hand?Manual work is fineCan the rules be written down?Get the rules and data in order firstIs the data available?Try it on a small scale

Figure 6: Look at the size, the writing down of rules, and the data in that order, and if they are in place, try it on a small scale.

Of the items in the table, writing down the rules and the data help make the plan transferable whether or not you use mathematical optimization. Even if you decide not to move right away, a list of the rules and a fixed place for the data used in planning will save you trouble when the person building the plan changes.

What to prepare before moving

A list of the rules

First, write down the rules the current plan is built on. Interview the person who builds it, look at past plans, and sort the rules as follows.

  • Rules that must be kept: what laws or work rules require, how much a vehicle can carry, promised delivery time windows
  • Rules to keep if possible: shift requests, how many consecutive working days to avoid, familiarity between staff and delivery destinations
  • The yardstick: what counts as a good plan (number of requests granted, distance traveled, overtime hours, fairness)

Give the rules to keep if possible an order of priority or weights. Weights are something you adjust later, so rough values are fine at first.

Data

For shift schedules, put together, in table form, the staff list, qualifications and the work each person can do, the days each person can and cannot work, and the required staffing. For delivery routes, put together the addresses or locations of the delivery stops, delivery quantities, the time windows when deliveries can be received, the number of vehicles and how much each can carry, and the distances or travel times from the depot.

A yardstick to evaluate the current plan

To compare whether a plan from mathematical optimization is better than the current manual plan, measure the current plan with the same yardstick. If you also count the number of requests granted or the total distance for the current plan, you can tell from the numbers whether the comparison shows an improvement.

Three things to prepare before movingShows that with a list of the rules, the data used in planning and a yardstick for the current plan in place, the plan from mathematical optimization and the current plan can be compared on the same terms.List of rules (must / if possible)Comparable on the same termsData used in planningYardstick for the current plan

Figure 7: Compare only after the list of rules, the data and the yardstick are in place.

How to start small

Rather than switching every workplace or every vehicle at once, it is safer to narrow the scope, try it, and expand after seeing the results.

  1. Decide the scope of the trial, such as one month of shift schedules at one workplace or one day of deliveries from one depot.
  2. Turn the list of rules you prepared into a form that can be written as formulas. Separate the rules that must be kept from the weighted rules to keep if possible.
  3. Solve it with a solver. If it is small, you can try Excel Solver. If it is large, use OR-Tools or HiGHS. Decide an upper limit on search time in advance.
  4. Compare the resulting plan with the current manual plan using the same yardstick.
  5. Have the person who builds the plan check it, and if there are points that would not work on site, ask why.
  6. Add the reasons you heard as rules and solve again. Once the results are stable, expand the scope.
Starting small and expandingShows narrowing the scope, writing the rules as formulas, solving with a solver and comparing with the current plan, having the planner check it and adding the reasons it would not work as rules, and expanding the scope once it is stable.Some points would not workStableNarrow the scopeWrite the rules as formulasSolve with a solverCompare with the current planThe planner checks itAdd the reasons as rulesExpand the scope

Figure 8: Solve and compare, add the reasons a plan would not work as rules and solve again, and expand once it is stable.

Keep a final step where a person checks the plan

The solver keeps only the rules written into the formulas. Circumstances not written there, such as a sudden absence on the day or a road closed for construction, are not taken into account. Keep a step in your operations where the person who builds the plan checks the result and can adjust it. If you add the reason for each adjustment to the list of rules, the solver will keep that rule from the next time on.

Even when you have an outside company build the system, much of the list of rules and the yardstick can be prepared on the ordering side, and having them makes estimates and comparisons easier. How to choose a vendor is covered in “How to choose a software vendor when your company has no developers.”

References

  1. Google OR-Tools, About OR-Tools. Explanation of combinatorial optimization, with example problems such as vehicle routing and scheduling. ↩ ↩2

  2. Microsoft Support, Define and solve a problem by using Solver. Objective cell, variable cells and constraints; the limit of 200 variable cells; unavailability in Excel for the web. ↩ ↩2 ↩3

  3. Google OR-Tools, Mixed-Integer Programming. Mixed-integer programming, which restricts some variables to integers, and the Boolean variables used for 0-or-1 decisions. ↩

  4. Google OR-Tools, Employee Scheduling. The nurse roster example, its rules, how requests are handled, the number of solutions, and the CP-SAT solver. ↩ ↩2

  5. Google OR-Tools, Vehicle Routing Problem. Definition of the vehicle routing problem, its relation to the traveling salesman problem, and the yardstick of shortening the longest route. ↩ ↩2

  6. University of Waterloo, The Problem (Traveling Salesman Problem). Definition of the traveling salesman problem and the fact that no method for solving the general case is known. ↩

  7. Google OR-Tools, Routing Options. Time limits for route search, how initial solutions are built, and search statuses. ↩

  8. Google OR-Tools, shift_scheduling_sat.py. A shift scheduling sample that uses hard upper and lower bounds and penalties for deviating from the soft range. ↩

  9. Google OR-Tools, CP-SAT Solver. The kinds of result the CP-SAT solver returns (OPTIMAL, FEASIBLE, INFEASIBLE, MODEL_INVALID, UNKNOWN), and that INFEASIBLE is the result of proving that no solution exists. ↩

  10. Frontline Systems, Standard Excel Solver - Dealing with Problem Size Limits. The decision variable limit of the standard Excel Solver. ↩

  11. Google OR-Tools, OR-Tools. Open-source optimization software, supported languages, and included solvers. ↩

  12. HiGHS, HiGHS - High-performance software for linear optimization. A solver for linear, mixed-integer and quadratic programming; MIT license; supported languages. ↩

Related Articles

Recent articles sharing the same tags. Deepen your understanding with closely related topics.

Related Topics

These are topic pages close to this theme. Starting from the article, you can go on to related services and other articles.

Services Connected to This Topic

This article leads to the following service pages. Start from the entry point closest to your situation.

Frequently Asked Questions

Questions commonly asked at the time of consultation about the topic of this article.

What does mathematical optimization actually do?
You write down, as formulas, the values to decide (decision variables), the rules to keep (constraints), and the yardstick for good and bad (the objective function). Software called a solver then searches the plans that satisfy the rules for the best one by that yardstick, or a good enough one. For a shift schedule it decides who works which shift on which day; for delivery routes it decides which vehicle visits which stops in what order.
Can I build a shift schedule with Excel Solver?
For a small problem, yes. But the standard Excel Solver accepts at most 200 variable cells. For example, representing 5 people, 30 days and 3 kinds of shift as 0-or-1 values takes 450 variables, which exceeds that limit. For larger problems, use an optimization library such as OR-Tools or HiGHS.
When is it fine to keep doing it by hand?
When there are few people or delivery stops, few rules, and the plan rarely needs to be rebuilt, manual work is often enough. If you cannot put the rules into words, or the data you need is not available, it is also not yet time to move to mathematical optimization. Get the rules and data in order first.
Can I use the plan the optimization software produces as is?
The software only keeps the rules written into the formulas. Circumstances that are not written down are not taken into account, so keep a step where a person checks and adjusts the plan. If you add the reasons for each adjustment as new rules, the next plan gets better.

Author Profile

Profile page for the article author.

Go Komura

Representative of KomuraSoft LLC

Focused on Windows software development, technical consulting, and bug investigation, with particular strength in projects where existing assets remain and in investigating failures whose cause is hard to see.

Contact
Back to the Blog