Terminal Capacity Solver & Discrete Simulation Engine

Overview

At CargoBeamer, terminal capacity and trailer dwell time calculations historically depended on an 11-sheet manual Excel model containing nearly 16,000 formulas. This spreadsheet acted as the mission-critical fallback whenever central TMS scheduling was offline.

I led the technical architecture to replace this brittle workbook with a deterministic, mathematically provable constraint solver and simulation engine.

Engineering Architecture

1. Computational Core Extraction:

   • Audited 11 sheets with 15,947 raw formulas; parsed and mapped the active computational core down to 4,644 formulas.
   • Built a complete computational dependency DAG comprising 16,896 nodes, 21,356 edges, and 30 KPI subgraphs.

2. AST Formula Compiler (formula_to_cpsat.py):

   • Engineered an AST-based parser that translates complex, nested Excel logic (IF, MAX, MIN, ROUNDDOWN, table lookups) into boolean reification constraints for Google OR-Tools CP-SAT.

3. Discrete-Event Simulation (simpy_model.py):

   • Developed a discrete-event simulation modeling terminal equipment pools (Terminal Tractors, Reach Stackers, and Gate Modules).
   • Identified Reach Stacker handling as the operational bottleneck: proved that reducing handling from 75 to 60 minutes yields a ~23% turnaround gain across train rotations.

Impact & Verification

• Delivered a robust production codebase with 23/23 tests passing.
• Reduced multi-hour manual spreadsheet audits to instantaneous, reproducible Python executions.