Decision Variables Are Not Spreadsheet Columns

One of the easiest ways to build a bad optimization model is to start with the spreadsheet.

The spreadsheet has rows for items, columns for weeks, filters for vendors, tabs for regions, and formulas that have been patched for three years. So the modeling instinct is to turn every cell into a variable, every formula into a constraint, and every exception into another rule. The result looks familiar to the business, but familiar is not the same thing as correct.

A spreadsheet is usually a record of how people currently think about the work. It is not necessarily a clean description of the decision.

This matters because optimization models do not optimize spreadsheets. They optimize decisions. If the decision is framed wrong, the model can be fast, elegant, and completely useless.

Start with the decision being made

Before writing variables, ask what action the system is allowed to recommend. Not what data exists. Not what report people look at. Not what columns already exist in the planning file.

What action changes the operation?

In a supply chain setting, the real decision might be:

• How much to order today.
• Which demand to fulfill now and which demand to defer.
• Which store, customer, or region gets constrained inventory.
• Whether to expedite, substitute, cancel, reserve, or hold back supply.
• Which supplier to use when lead time, cost, and reliability differ.
• When the next buying decision should happen.
• Which policy to run for the next month, not just which quantity to enter this week.

Those are different decisions. They may live in the same spreadsheet, but they are not the same model.

If you skip this step, the model inherits the mess. You end up with variables that represent accounting artifacts instead of operational choices. The solver can still produce an answer, but the answer may not correspond to anything a planner can actually do.

A useful framing pattern

A practical decision model needs four pieces: state, decision, uncertainty, and transition.

The state is what you know when the decision is made. Inventory on hand, open purchase orders, demand forecast, capacity, committed allocations, vendor calendars, current backlog, cube usage, cash limits, and service targets.

The decision is what you can control. Order quantities, allocation quantities, shipment priorities, reserve quantities, expedite flags, substitution choices, markdown triggers, or buying cadence options.

The uncertainty is what will be revealed after the decision. Demand, lead time, supplier fill rate, returns, cancellations, quality holds, transportation delays, forecast revisions, and competitor or promotion effects.

The transition is how today turns into tomorrow. Inventory decreases when demand is served. Inventory increases when orders arrive. Backlog grows when demand is deferred. Capacity is consumed when work is scheduled. Cash is tied up when inventory is purchased. Future choices are constrained by what you decided earlier.

That structure is simple, but it keeps you honest. It forces the model to answer the real question: given what we know now, what action should we take before the uncertain future arrives?

Example: constrained inventory allocation

Suppose a planner has limited inventory across a network. Demand exists across regions, customers, stores, or channels. The spreadsheet has a forecast by item, location, and week. It also has columns for inventory, open orders, target service, priority, manual overrides, and maybe a few penalty scores.

The naive model turns the spreadsheet into a grid and optimizes planned shipments against the forecast. That may work for a clean classroom problem. It usually breaks in the real world.

The better framing starts with the decision.

Are we deciding what to ship today? Are we deciding how much inventory to reserve for later demand? Are we deciding which customers are allowed to stock out? Are we deciding whether to violate a service rule or whether to spend money expediting supply? Are we deciding a one-week allocation, or are we choosing a policy that should behave well over the next several weeks?

Those questions change the variables.

For a basic MILP, the variables might include shipment quantities from node to demand location, inventory carried forward by node and item, unmet demand, reserved inventory, and binary indicators for using an expedite option or crossing a minimum shipment threshold. If substitution is allowed, there may be variables for substituting one item for another. If allocation fairness matters, there may be variables representing service shortfall by customer class or geography.

The model might look something like this conceptually:

• ship[item, source, destination, week]: units shipped.
• inventory[item, node, week]: ending inventory.
• short[item, destination, week]: unmet demand or service miss.
• reserve[item, node, week]: inventory intentionally protected for future demand.
• expedite[item, source, destination, week]: binary or quantity decision for premium movement.
• substitute[item_from, item_to, destination, week]: substitution flow when the business allows it.

This is not just notation. Each variable has to map to something the operation can execute or explain.

If nobody can reserve inventory in the actual process, do not create a reserve variable unless the project includes changing the process. If buyers cannot expedite from that supplier, do not let the model use expediting as a magic escape hatch. If stores cannot receive partial cases, do not let the model ship fractional units because the spreadsheet did.

The variable design is the operational design.

Constraints are not all created equal

A lot of models fail because they treat every business sentence as the same type of constraint.

Some constraints are physics. A unit cannot be shipped from a location where it does not exist. A truck has limited capacity. A supplier has a production calendar. A warehouse has receiving limits. Inventory balance has to hold.

Some constraints are contractual. Minimum order quantities, customer commitments, delivery windows, case packs, purchase agreements, shelf-life rules, and channel restrictions may not be optional.

Some constraints are policies. Service targets, fairness rules, priority tiers, presentation minimums, buying calendars, target weeks of supply, and allocation guardrails are often chosen by the business. They may be important, but they are not physical laws.

Some constraints are habits. People may say, “We always do it this way,” when what they really mean is, “The old tool forced us to do it this way,” or, “Changing it would require an uncomfortable conversation.”

Do not model all four categories blindly. Separate them. A physical constraint should be hard. A policy constraint may deserve a penalty, a threshold, or a tunable parameter. A habit should be challenged before it becomes math.

This is where problem framing matters more than solver choice. The difference between an infeasible model and a useful model is often not a clever algorithm. It is knowing which rules are real and which rules are negotiable.

Where uncertainty belongs

The spreadsheet usually hides uncertainty by giving you one number. One forecast. One lead time. One capacity estimate. One cost. One service target.

The operation does not live in one-number land.

Demand can spike. Lead times can slip. Suppliers can short-ship. Forecasts can be revised after the order is already placed. Transportation can miss a planned arrival. A promotion can pull demand forward. A substitute can cannibalize demand from another item. A customer can cancel.

The model does not always need to be a giant stochastic program on day one, but it does need to acknowledge where uncertainty changes the decision.

For many practical systems, start with scenario evaluation. Generate demand and lead-time scenarios. Evaluate candidate policies under common random numbers. Compare not only average performance but also downside cases. Ask whether the recommendation is stable when the future changes. If a policy wins only under the average forecast and collapses under a plausible tail scenario, it is not a strong policy.

For a MILP, uncertainty can enter in several ways. You can optimize against a conservative demand quantile. You can use scenarios with expected cost or risk penalties. You can build a deterministic model for the first decision and then simulate the policy afterward. You can add reserve logic for tail risk. You can penalize shortage asymmetrically when lost sales are much worse than excess inventory.

The right method depends on the decision. The wrong method is pretending uncertainty disappeared because the spreadsheet rounded it to one number.

Metrics that actually tell you something

A model objective is not the same as business performance.

The objective might include revenue, margin, holding cost, transportation cost, shortage penalty, expediting cost, markdown risk, labor cost, and penalty terms for policy violations. That is useful, but it is not enough.

You also need output metrics that explain the recommendation:

• Expected profit or contribution margin.
• Service level by customer, channel, region, or item class.
• Stockout units and stockout dollars.
• Inventory dollars, units, cube, and weeks of supply.
• Expediting spend and frequency.
• Number of manual overrides required.
• Receiving workload and capacity usage.
• Decision stability from run to run.
• Tail risk under bad demand or lead-time scenarios.

Decision stability is underrated. A model that gives a slightly better objective but changes its recommendation wildly every day may be operationally worse than a steadier policy with almost the same economics. Real planners need to execute the decision, explain it, and live with it. Volatility has a cost.

Implementation notes for practitioners

Start small, but start correctly.

Pick one item family, one region, one supplier, or one constrained node. Build the smallest version of the model that still contains the real decision. Do not begin with every SKU, every location, every exception, and every corporate rule. That is how you create a monster before you understand the animal.

Create a variable dictionary in plain English. For each variable, write what operational action it represents, who owns that action, what system executes it, and whether the value must be integer, binary, continuous, case-pack rounded, or manually approved. If you cannot explain a variable in operational language, it may not belong in the model.

Create a constraint dictionary too. Label every constraint as physical, contractual, policy, or habit. This will save you when the model becomes infeasible. Instead of arguing vaguely about why the model failed, you can ask which type of rule caused the contradiction and whether that rule should be hard.

Save run artifacts. Store the input snapshot, model version, objective value, solution metrics, solver status, gap, runtime, key constraint slacks, infeasibility diagnostics when needed, and the final recommendation. A production optimization system without run history becomes folklore immediately.

Finally, build an explanation layer from the beginning. Do not wait until the model is mathematically impressive. A useful recommendation should say why it was made: inventory is constrained, demand risk is high, lead time is unreliable, MOQ economics dominate, service exposure is unacceptable, capacity is binding, or expediting is cheaper than lost sales.

Failure modes

The first failure mode is modeling the current process instead of the decision. This preserves bad habits and makes the optimizer look like a complicated spreadsheet macro.

The second failure mode is creating variables for things nobody can do. The model recommends actions that are impossible, and then everyone blames optimization instead of the framing.

The third failure mode is making every rule hard. This turns policy preferences into infeasibility. The model should distinguish between laws of physics and business preferences that can be traded off with cost.

The fourth failure mode is optimizing the average. The recommendation looks great until demand, supply, or lead time moves in a very normal but inconvenient direction.

The fifth failure mode is reporting only the objective value. Nobody trusts a recommendation just because the objective improved. They need to see service, inventory, cost, risk, and operational impact.

The sixth failure mode is ignoring the user interface. If the system cannot explain why it allocated inventory away from a customer, why it ordered early, why it held back supply, or why it violated a target, the model will get overridden.

What to do in practice

Before building the model, write a one-page decision frame.

Name the decision. Name the decision owner. Name the timing. Name the available actions. Name the information available at decision time. Name the uncertainty that arrives later. Name the physical constraints. Name the policy constraints. Name the metrics that define success. Name the fallback policy if the model fails.

Then build the formulation from that frame, not from the spreadsheet layout.

The spreadsheet is still useful. It tells you what data people use, what exceptions exist, what language the business understands, and where the current process is painful. But it should not be allowed to define the model by accident.

A good optimization model is not a prettier spreadsheet. It is a decision system. The variables are not cells. They are commitments the business is willing to make under uncertainty.

Once you see it that way, the modeling conversation changes. You stop asking, “How do I reproduce the planning file?” and start asking the question that actually matters:

What decision are we making, what future are we exposed to, and what policy deserves to be trusted?