Project overview
The question was practical: can the current network handle these orders, and is there a cheaper assignment that still works? Excel and Power Query checked examples. Python repeated those checks across the full dataset. A mixed-integer linear program chose the warehouse, the shipping route, and the processing day together.
This is a public portfolio case built on the Brunel Supply Chain Logistics Problem Dataset. It is not a client engagement.
Key results
| Measure | Saved result |
|---|---|
| Orders assigned / daily capacity violations | 9,215 / 0 |
| Historical assignment warehouse cost at current rates | $15,633,221.91 |
| New plan warehouse cost | $15,491,530.16 |
| Warehouse-cost reduction | $141,691.76 · 0.9% |
| Orders moved to another warehouse | 1,611 |
| Plan freight / total cost | $22,065.48 / $15,513,595.64 |
| Remaining possible improvement | $4,394.34 · 0.0283% |
| Final solver time | 3.66 seconds |
The solver met its 1% tolerance and kept the starting plan. Exact optimality was not proved. Solver time excludes preparation. The remaining gap is possible improvement, not savings already achieved.
Dataset and tools
Scope. 9,215 orders, 19 warehouses, and 1,540 freight-rate records. Seven tables: OrderList, FreightRates, PlantPorts, ProductsPerPlant, VmiCustomers, WhCapacities, and WhCosts.
Tools. Excel, Power Query, Python, pandas, NumPy, openpyxl, SciPy, and HiGHS (highspy).
Visual findings
The charts use the same colors as the rest of the site. Navy is the plan, slate is the historical baseline, copper is the constraint, and gold marks a remainder that is real but small.
1. PLANT03 is the bottleneck
Historical assignments exceed the current seven-day capacity by 1,450 orders. This comparison does not establish that those orders were late in history.
2. Most orders have no warehouse alternative
7,562 orders have one eligible warehouse. 1,653 have alternatives. PLANT03 must handle 7,043 of them, leaving only 48 spare slots over seven days. A required warehouse can still leave the route and the day open.
3. Reassignment reduces dependence
1,611 orders changed warehouse. The chart shows net workload changes. PLANT03's load fell by 1,498 orders.
4. Warehouse costs fall
Both assignments use the same current warehouse rates. Incomplete historical freight matches prevent a full total-cost savings comparison.
5. Freight is a small share under this model
Freight is 0.14% of scenario cost. The slice is drawn at its true size and has not been enlarged.
Decision trees
These show the decision rules and the reasoning. They are not separate experiments for every branch.
Choose the approach
Validate examples in Excel
Historical routing fits seven-day capacity?
No · PLANT03 exceeds capacity
Build a feasible reassignment
Another eligible warehouse?
Yes · 1,653 orders
Allow warehouse choices
No · 7,562 orders
Keep the required warehouse
Keep valid route and day choices
Use Python and MILP
Yes
Keep the baseline and assess cost
This path skips reassignment and joins the freight question below.
Complete historical freight baseline?
No · this dataset
Compare warehouse cost. Report plan freight separately.
Yes
Compare total costs on the same basis.
Why MILP? Orders stay whole, while warehouse capacity and shared freight pricing connect their decisions.
Solve and validate
Original search: over six hours without a plan
Simplify pricing and remove unnecessary options
Build a starting schedule
Coverage, eligibility, capacity, and weight checks pass?
No
Repair and recheck
Return to the same checks until they pass.
Yes
Save the starting plan. Search with choices open.
Requested cost gap reached?
Yes
Save the best plan and report the gap.
No
Time budget exhausted?
No
Keep searching with choices open.
Yes
Keep the best checked plan. Report that the search is unfinished.
Obstacles and decisions
| Obstacle | Response |
|---|---|
| No customer feedback; unclear timing fields | Tested network feasibility without claiming service failures. |
| Spare capacity and independent cheapest choices were misleading | Counted orders, enforced eligibility, and shared capacity. |
| Million-row Excel joins; missing Column12; connection-only sheets | Fixed query steps, loaded summaries, and automated the checks in Python. |
| Rounded IDs, missing pip, read-only arrays | Preserved original IDs and corrected the setup. |
| Historical carrier and weight-band mismatches | Avoided forced matches. Limited the savings comparison to warehouse costs. |
| Actual shipping groups unknown | Modeled compatible orders that share a route and a day. |
| Six-hour search returned no plan | Simplified equivalent calculations and supplied a checked starting plan. |
| Grouping could restrict choices | Kept all orders and allowed final warehouse, route, and day changes. |
| Other studies reported different totals | Compared scope and pricing rules, and kept one run's results together. |
Assumptions and limits
Seven days is a planning scenario. All orders are available at the start. Processing and dispatch share a day. No delivery deadlines or delay penalties are modeled.
Whole orders must satisfy product, customer, port, service, and daily order-capacity rules. Warehouse cost is quantity times the unit rate, not a daily holding cost.
Compatible orders on the same route and day share a weight band. AIR charges apply per order. GROUND charges apply per group. Customer-arranged freight costs the company nothing in freight, and the warehouse cost still applies.
The 48-slot PLANT03 cushion is arithmetic. A capacity-loss stress test has not been completed.
Project files and running
| Files | Purpose |
|---|---|
| README.md, images/ | Project story, decision trees, and five charts |
| Supply chain logisitcs problem.xlsx | Original seven-table data |
| supply chain analysis.xlsx | Excel and Power Query workflow |
| supply chain analysis.py | Automated checks, model, and exports |
| requirements.txt, .gitignore | Dependencies and excluded working files |
| starting_plan_orders.csv, best_plan_orders.csv | Initial and retained order assignments |
| starting_plan_daily_capacity.csv, best_plan_daily_capacity.csv | Daily warehouse workloads |
| starting_plan_freight_groups.csv, best_plan_freight_groups.csv | Group weights, bands, and freight charges |
| starting_plan_cost.json, best_plan_cost.json | Cost totals |
| solve_summary.json, solver.log | Settings, status, runtime, bound, and gap |
Keep the script beside the original workbook.
python -m pip install -r requirements.txt python "supply chain analysis.py"
Each run creates a timestamped folder under shipping_results/. Keep its ten outputs together. The final search uses a 300-second budget and a 1% gap target.