A 25-facility network built around a different business
A major North American logistics distributor had grown its facility network over time across the US, Mexico, and Canada — but the network's structure reflected historical decisions, not current volume flows. With significant business wins and losses reshaping demand, and lease expirations creating natural inflection points, the question was: what does the optimal network actually look like, and how much is the current one costing?
The engagement covered 13 weeks — from raw shipment data through validated baseline modeling, adjusted demand forecasting, nine distinct network scenarios, and a final recommended roadmap presented to executive leadership.
13 weeks, end to end
Data Collection & Validation
456K+ shipment records cleaned, matched, and validated against actual costs within 5%
Baseline Modeling & Dashboards
SQL pipelines in SSMS, historical baseline flows, Tableau dashboards shared with client leadership
Adjusted Baseline & Scenarios
Demand adjusted for wins/losses; 9 network scenarios modeled in Optilogic Cosmic Frog
Recommendations & Roadmap
5-step prioritized roadmap with facility sizing, lease timing, and greenfield opportunities
The baseline is only as good as the data
Before any scenario could be run, the underlying shipment data had to be validated, cleansed, and structured. This was the critical first gate — a model that can't reproduce history can't be trusted to predict the future.
Shipment validation
472K raw shipment records processed. Irregular flows, circular routes, misclassified direct/consolidation shipments, and shuttle flows identified and excluded systematically.
SQL pipeline & data prep
Raw shipment records queried, joined, and restructured in SQL Server and SSMS — cleaning lane classifications, matching consolidation stops, and producing aggregated model inputs across origin, destination, mode, and product dimensions.
Tableau dashboards
Modeling results linked directly to interactive Tableau dashboards for drill-down by facility, customer, lane, flow type, and direction. Shared with and used by client leadership throughout the engagement.
Demand adjustments
Adjusted baseline accounted for 14 lost customer programs and 2 major customer wins, plus mid-year go-live annualization for 14 partial-year accounts and growth assumptions at a key border facility.
Cost validation
Baseline transportation and facility costs validated against actual 2024 operating data. Modeled total came within 5% of actual across both transportation and facility cost categories.
Aggregation & dimensionality
Origins compressed from 11,419 to 4,684 ZIP-level clusters. Destinations from 2,980 to 1,642. Products defined across four dimensions: source ZIP, country, flow type, and top customer.
Nine scenarios, one recommended network
Each scenario tested a targeted network change against the adjusted baseline — opening new facilities, closing underutilized ones, or consolidating overlapping locations. Individual scenarios were then combined to find the optimal portfolio of changes.
| Scenario | Total Cost Impact | Recommendation |
|---|---|---|
| Add Midwest Hub | −0.61% | Proceed |
| Add Northeast Hub | −0.49% | Proceed |
| Close Small Border Facility | −0.11% | At lease expiry |
| Add Secondary Midwest Site | −0.25% | If Midwest Hub insufficient |
| Close West Coast Facility | +1.73% | Do not close — downsize to ~13k SF |
| Consolidate Ohio Valley Sites | +0.34–0.41% | Keep both — reevaluate on lease |
| Relocate Southeast Hub | +0.02% | Only if strategic reason |
- Retain the strategically critical West Coast facility — essential for cross-border volume through a major southern border crossing. Downsize to ~13k SF to improve utilization.
- Close the underutilized small border facility at lease expiration, and add partner-operated Midwest and Northeast hubs to better serve regional volume.
- Conduct detailed capacity analysis for the two highest-volume border facilities — both approaching capacity with future volume growth projections.
- Revisit Ohio Valley consolidation when lease terms or volume levels change significantly.
- Stay in current Southeast and Central locations unless a strategic operational reason or cost increase emerges.
From data pipeline to executive recommendation
A network optimization model is only credible if it can reproduce history. Getting the baseline within 5% of actual costs — across 456K shipment records, 25 facilities, and a complex mix of consolidation, direct, and cross-dock flows spanning the US, Mexico, and Canada — required substantial upfront data work. The SQL pipelines and Tableau dashboards weren't supporting artifacts; they were the mechanism by which the client could interrogate and trust every number the model produced.