The Fragility of Industrial Spreadsheets

In modern intermodal freight transport, terminal throughput is the lifeblood of operations. Every minute a train spends at a terminal, or every unnecessary move a reach stacker makes in the yard, ripples across international rail schedules.

Yet, like many industrial enterprises, capacity planning and trailer dwell time estimations historically lived inside an 11-sheet manual Excel workbook containing nearly 16,000 formulas. When the central Transport Management System (TMS) was offline or undergoing maintenance, dispatchers and planners relied exclusively on this sheet to compute slot allocations and equipment constraints.

While impressive in its complexity, the model suffered from the classic failure modes of legacy spreadsheets:

  • No version control or audit trail: Formula edits went untracked.
  • Opaque dependency cycles: Circular logic and implicit cell dependencies hid calculation errors.
  • Inability to model stochastic variance: Spreadsheets assume static averages; real-world logistics is governed by queueing theory, stochastic arrival distributions, and equipment breakdowns.

Here is how we decomposed this system and rebuilt it into a deterministic constraint optimization solver and discrete-event simulation engine in Python.

1. Auditing the Dependency Graph

Before writing code, we needed to understand what the spreadsheet was actually computing.

Using Python’s openpyxl, we parsed all 15,947 formula cells across the 11 sheets. By mapping cell references and named ranges into a directed acyclic graph (DAG), we uncovered critical insights:

  • Out of 15,947 raw formulas, only 4,644 formulas represented active computation; the remainder were aesthetic formatting, replicated row formulas, or unused lookup tables.
  • The active core resolved into 16,896 nodes, 21,356 edges, and 30 distinct KPI subgraphs (track occupancy, reach stacker utilization, gate turnaround, buffer capacity).

Visualizing this topological sort allowed us to isolate the pure inputs, intermediate transformations, and final decision variables.

2. Compiling Excel Logic to CP-SAT via AST Parsing

To solve optimal train slot allocations and crane scheduling, we chose Google OR-Tools CP-SAT (Constraint Programming - Satisfiability). CP-SAT is exceptionally fast for scheduling problems with discrete integer constraints.

However, manually rewriting thousands of conditional rules (IF, MAX, MIN, ROUNDDOWN, table lookups) into mathematical constraints would be error-prone and unmaintainable.

Instead, we built an AST (Abstract Syntax Tree) compiler:

  1. Tokenize the Excel formula strings.
  2. Build an expression tree representing the mathematical and boolean operations.
  3. Emit equivalent boolean reification variables in CP-SAT.

For example, an Excel conditional like =IF(A1 > 50, B1 * 2, B1) compiles into CP-SAT boolean indicator constraints:

# Boolean reification pattern in CP-SAT
b = model.NewBoolVar('is_above_threshold')
model.Add(a1 > 50).OnlyEnforceIf(b)
model.Add(a1 <= 50).OnlyEnforceIf(b.Not())

model.Add(result == b1 * 2).OnlyEnforceIf(b)
model.Add(result == b1).OnlyEnforceIf(b.Not())

This automated compiler allowed us to reproduce the business logic with 100% mathematical fidelity while guaranteeing global optimality in seconds.

3. Dynamic Simulation with SimPy

While CP-SAT solves for optimal static schedules, operations never go strictly according to schedule. Trains arrive late, crane hydraulic checks take longer than expected, and trucks bottleneck at gate kiosks.

To model this dynamic reality, we built a discrete-event simulation model using SimPy:

import simpy

class TerminalEnvironment:
    def __init__(self, env, num_reach_stackers, num_tractors):
        self.env = env
        self.reach_stackers = simpy.Resource(env, capacity=num_reach_stackers)
        self.tractors = simpy.Resource(env, capacity=num_tractors)
        
    def process_wagon(self, wagon_id, handling_time):
        with self.reach_stackers.request() as req:
            yield req
            yield self.env.timeout(handling_time)

Running Monte Carlo simulations over 10,000 synthetic operational shifts yielded an immediate operational discovery: Reach Stacker (RS) cycle time was the primary bottleneck governing yard throughput.

The model demonstrated that reducing RS handling time from 75 minutes to 60 minutes produced an outsized ~23% improvement in overall train turnaround times, giving terminal engineering clear mathematical backing for crane electrification and workflow improvements.

Lessons Learned

  1. Don’t rewrite from scratch—compile from reality: Trying to invent brand-new business rules from meeting notes usually fails. Parsing existing operational spreadsheets provides an exhaustive, ground-truth specification of edge cases.
  2. Combine Optimization with Simulation: Optimization (CP-SAT) gives you the ideal operational target; simulation (SimPy) stress-tests that target against stochastic friction and real-world variance.
  3. Automated Testing is Non-Negotiable: With 23 automated regression tests verifying outputs against historical terminal datasets, we gave engineering and management the confidence to retire a decade-old spreadsheet.