S&OP Engineering III: The End of Excel (Linear Programming for Supply Planning)

Source: Dev.to

“We always want 4 weeks of coverage.”
This phrase, repeated like a mantra in every S&OP meeting on the planet, is financially toxic.

Why? Because it’s a fixed rule applied to a dynamic system.
If your demand in January is 200 units and in July is 20, you’re forcing yourself to maintain 800 and 80 units respectively “just in case.” January falls short. July immobilises capital for no reason.

The alternative isn’t more sophisticated intuition. It’s mathematics.

Executive Summary

In this chapter we connect the probabilistic forecast from Chapter 2 with a Linear‑Programming engine (PuLP) that calculates the exact production plan minimising total cost (production + storage) while respecting safety‑stock constraints. We move from passive prediction to active prescription.

From Forecast to Decision: Architecture

In Chapter 1 we cleaned the signal.
In Chapter 2 we predicted demand.
Now we take the step Excel can’t: optimise.

Our pipeline on GitHub connects to Supabase, reads the demand_forecasts table (the future we predicted), and generates a new supply_plans table. The system has evolved from

• Descriptive – what happened?
• Predictive – what will happen?
• Prescriptive – what should we do?

This is Operations Research – the same discipline that optimises airline routes, military logistics, and global supply chains – and we run it with ~50 lines of Python.

The Mathematics of Business

We’re not hiding the equations; they’re the heart of the decision. Below is the core fragment from our SupplyOptimizer class:

# Objective Function: Minimise total cost
problem += pulp.lpSum(
    production_cost * production[t] + holding_cost * inventory[t]
    for t in range(T)
), "Total_Cost"

# Constraint: Inventory Balance (Conservation of Mass)
for t in range(T):
    prev_inv = initial_inventory if t == 0 else inventory[t - 1]
    problem += (
        inventory[t] == prev_inv + production[t] - demand[t],
        f"Balance_t{t}"
    )

# Constraint: Safety Stock (Risk Policy)
for t in range(T):
    problem += (
        inventory[t] >= safety_stock,
        f"SafetyStock_t{t}"
    )

Three engineering decisions worth explaining

1. Objective Function – It doesn’t seek “lots of stock” or “high production.” It seeks the minimum financial cost, i.e., the equilibrium between expensive manufacturing and expensive storage. The solver automatically finds the exact point where the combined cost is minimal.

2. Mass Balance – A physical constraint: you can’t sell what you don’t have. Today’s inventory equals yesterday’s inventory plus today’s production minus today’s demand. No magic; the equations forbid cheating.

3. Safety Stock – The risk policy: never let inventory drop below a safety minimum (in our case, 1.5 months of average demand). The system calculates this, not a spreadsheet with a number pulled from thin air.

The Master Plan: From Algorithm to Business Decision

This is what the Operations Director needs to see.
The algorithm doesn’t produce uniformly. If it detects a massive demand spike in one period, it “pre‑builds” inventory in preceding months to flatten the production load. If holding cost is high, it keeps the warehouse empty and manufactures Just‑in‑Time. Excel can’t do this on its own; mathematics can.

Result from our solver with test data

This number is not an estimate. It’s the provable global minimum given the constraints. If someone proposes a cheaper plan with the same parameters, they’re violating a constraint.

Open Kitchen: Play with the Solver

I distrust theories that can’t be put into practice. That’s why I’ve prepared a Google Colab where you can run the optimiser on a snapshot of our real data.

Experiment: Change holding_cost to an extremely high value (e.g., €50/unit). Watch the algorithm automatically decide to manufacture Just‑in‑Time and keep the warehouse practically empty. Then lower the production cost and watch it prefer bulk production and storage. Mathematics adapts; Excel’s fixed rules don’t.

📎 Open the Interactive Google Colab

Modify the costs, capacity constraints, safety stock. Do engineering, not faith.

The Complete Chain: From Data to Decisions

With this third chapter we’ve built an end‑to‑end S&OP system that goes from a dirty ERP CSV to an optimal production plan:

Next Step: Scaling to Enterprise

We now have the perfect plan in our database – but only for one product. What happens when you add three SKUs sharing the same factory?

Continue the journey in Chapter 4.

Engineering – Part 4 – Enterprise

We break the MVP: we inject multi‑product data with radically different profiles, parallelize the forecasting with MLOps, and build a unified Linear Programming model where products compete mathematically for shared production capacity.

The difference between an Operations Director who plans and one who optimizes is an objective function between their intuition and reality.