Finding the Best Decision Within Real Constraints
Not "what could we do?" but "what is the mathematically best we can do given our limits?"
Every previous set in this series described, diagnosed, forecasted, explained, predicted, segmented, or assessed risk. Set 10 does something different: it finds the optimal solution. Given a clear objective and a set of real-world constraints, optimisation analysis identifies the single best decision — the one that maximises (or minimises) the outcome as far as constraints allow.
This is the domain of operations research and management science. It is used daily by airlines scheduling crews, manufacturers planning production, logistics companies routing deliveries, and retailers allocating shelf space — wherever resources are scarce and tradeoffs are complex.
Programming
types
Linear Programming (LP)
Maximises or minimises a linear objective function subject to linear constraints. The workhorse of optimisation — handles production mix, resource allocation, and blending problems.
Integer Programming (IP)
Extension of LP where some or all decision variables must be whole numbers. Required when decisions involve indivisible units — staffing levels, binary yes/no choices, facility locations.
Non-Linear Optimisation
Handles problems where the objective or constraints are non-linear (e.g. revenue = price × demand, where demand depends on price). More complex but reflects more realistic business relationships.
Goal Programming
Optimises multiple objectives simultaneously by minimising deviations from each goal. Used when there are competing priorities that cannot be combined into a single objective function.
Network Optimisation
Optimises flow through a network of nodes and arcs. Applications: shortest path, minimum cost flow, maximum flow. Logistics, supply chains, telecommunications routing.
Assignment Problems
Assigns resources (people, machines, vehicles) to tasks optimally to minimise cost or maximise performance. Classic operations research problem solved with the Hungarian algorithm.
Sensitivity Analysis in LP
After solving an LP, sensitivity analysis (shadow prices, reduced costs, ranging) reveals how much the optimal solution changes as constraints or objective coefficients are varied — without resolving.
Excel's built-in Solver add-in handles LP, IP, and non-linear optimisation for problems up to ~200 variables and 200 constraints. It is the most accessible entry point for business analysts. Python's PuLP and scipy.optimize libraries extend this to industrial-scale problems with thousands of variables. Both are covered in this set.
The Language of Optimisation
Every optimisation problem has the same structure: an objective, decision variables, and constraints.
Before using any tool — Excel Solver, Python PuLP, or specialist software — you must formulate the problem correctly. Optimisation problems always have three components:
Objective Function
The quantity you want to maximise or minimise. It must be expressed as a mathematical function of the decision variables.
Examples: Maximise profit · Minimise cost · Minimise delivery time · Maximise utilisation
Decision Variables
The quantities you control — the things the solver is allowed to change in search of the optimal solution.
Examples: Units of each product to produce · Staff assigned to each shift · Quantity to order from each supplier · Budget allocated to each channel
Constraints
The limits and requirements the solution must satisfy. Constraints reflect real-world restrictions on resources, capacity, regulations, or minimum service levels.
Examples: Machine capacity ≤ 200 hrs/week · Labour hours ≤ 5,000 · All decision variables ≥ 0 · Minimum order quantities
Non-Negativity & Integrality
Decision variables must typically be ≥ 0 (you cannot produce negative units). Integer constraints require variables to take whole-number values.
Binary variables: Special case where the variable is either 0 (don't do it) or 1 (do it). Used for facility location, project selection, yes/no decisions.
Decision variables: C = number of croissants to produce per day · M = number of muffins to produce per day
Objective: Maximise profit = 1.20C + 0.80M (profit per unit × quantity)
Constraints:
| Constraint | Expression | Reason |
|---|---|---|
| Oven time | 3C + 2M ≤ 240 minutes | Oven available 4 hours/day |
| Flour stock | 2C + 3M ≤ 300 grams | Limited flour in storage |
| Minimum croissants | C ≥ 20 | Standing daily order from café |
| Non-negativity | C ≥ 0, M ≥ 0 | Cannot produce negative quantities |
Solution (from Solver): C = 60, M = 40. Maximum daily profit = $72 + $32 = $104.
Once a problem is correctly formulated, the solver does the mathematics automatically. The analytical skill lies in translating a business problem into a mathematical formulation — identifying the right objective, the correct decision variables, and all the binding constraints. A perfectly running Solver on a wrongly formulated problem produces the wrong answer with high confidence.
Linear Programming
The most widely used optimisation technique. Linear objective, linear constraints, and a guaranteed global optimum — if the problem is feasible.
Linear Programming (LP) requires that both the objective function and all constraints are linear — meaning no squared terms, products of variables, or other non-linearities. This sounds restrictive, but it covers an enormous range of real business problems: production mix, resource allocation, blending, transportation, and staffing.
Why LP is reliable
The LP feasible region (the set of all solutions satisfying all constraints) forms a convex polygon (in 2D) or polytope (in higher dimensions). The optimal solution always lies at a corner (vertex) of this polygon. This geometric fact guarantees that the Simplex algorithm — which moves from corner to corner, improving the objective at each step — always finds the global optimum, never a local one. LP cannot get "stuck."
The shaded purple region is the feasible region — all (A,B) combinations satisfying all three constraints. The optimal solution (★) is always at a corner vertex. The dashed orange line shows the profit objective at its maximum value while still touching the feasible region.
LP Sensitivity Analysis — Shadow Prices
After solving an LP, sensitivity analysis reveals how much the optimal objective value changes per unit increase in a constraint's right-hand side. This is the shadow price (or dual price).
| Concept | Plain explanation | Business use |
|---|---|---|
| Shadow Price | How much the optimal objective improves if the constraint is relaxed by one unit. Shadow price = $3.20 means: if we had one more hour of machine capacity, profit would increase by $3.20. | Reveals which constraints are most valuable to relax. Justify investment in expanding the binding constraint. |
| Binding Constraint | A constraint that is exactly met at the optimal solution. The shadow price of a binding constraint is positive — it is actively limiting the objective. | Binding = where the bottleneck is. Focus capacity investment here first. |
| Non-Binding Constraint | A constraint with slack — the feasible region has room beyond the constraint at the optimal solution. Shadow price = 0. | This constraint is not limiting the objective — no value in relaxing it. |
| Reduced Cost | For variables at zero in the optimal solution: how much the objective coefficient would need to improve before it becomes worth producing that variable. | A product not in the optimal mix has a reduced cost showing exactly what margin improvement would make it worthwhile. |
Integer, Non-Linear & Goal Programming
Extensions of LP for when decisions are indivisible, relationships are curved, or you have multiple competing goals.
Integer Programming (IP)
Many business decisions are naturally discrete: you hire whole people, open whole warehouses, deploy whole vehicles. LP might suggest "hire 3.7 staff" — which is not a real option. Integer Programming (IP) requires some or all decision variables to take integer values.
All variables are integers
Number of trucks to deploy, shifts to open, machines to purchase. Every decision variable must be a whole number.
Some integer, some continuous
Most common in practice: some decisions are discrete (number of warehouses) while others are continuous (quantity shipped from each warehouse).
Variables are 0 or 1
"Do we open this location? (1=yes, 0=no)." Capital budgeting, project selection, facility location — any yes/no decision under a budget constraint.
IP is harder to solve than LP
Adding integer constraints turns a simple LP into an NP-hard problem. Solver may take much longer or fail on large problems. Use LP relaxation first to get an upper bound.
A company has a $5M investment budget and 6 candidate projects. Each project either receives full funding (x=1) or not (x=0). Each has a defined cost and NPV. Objective: maximise total NPV subject to total investment ≤ $5M.
Subject to: Cost₁x₁ + Cost₂x₂ + … + Cost₆x₆ ≤ $5,000,000
x₁, x₂, …, x₆ ∈ {0, 1}
This is the classic "0-1 Knapsack Problem." Excel Solver with binary integer constraints solves this instantly for 6 projects, and in seconds for up to ~30 projects.
Non-Linear Optimisation
When the objective or constraints contain non-linear terms — price × quantity (both variables), diminishing returns, quadratic cost curves — the problem becomes non-linear. Non-linear optimisation cannot guarantee a global optimum; it may find a local one. Use multiple starting points and verify the solution makes business sense.
Revenue = Price × Demand, where Demand = 1000 − 10×Price (linear demand function). Revenue = Price × (1000 − 10×Price) = 1000P − 10P² — a non-linear (quadratic) function. Excel Solver with "GRG Nonlinear" method finds the profit-maximising price.
Goal Programming
Goal programming optimises when there are multiple, sometimes conflicting objectives. Instead of combining all objectives into one, it sets a target (goal) for each and minimises the total weighted deviation from all goals simultaneously.
| Goal | Target | Priority | Direction |
|---|---|---|---|
| Minimise overtime | ≤ 200 hours/month | P1 (highest) | Minimise excess |
| Achieve revenue | ≥ $1,000,000 | P2 | Minimise shortfall |
| Maintain headcount | = 45 staff | P3 | Minimise both excess and shortage |
Network Optimisation & Assignment Problems
Routing, scheduling, and matching — the most commercially applied areas of optimisation.
Network Optimisation
Network problems model flow through a system of nodes (locations, facilities, storage points) and arcs (routes, links, connections). The objective is typically to minimise cost or time, subject to flow conservation (what flows in must flow out, unless it is a source or sink).
| Problem Type | Question | Business application |
|---|---|---|
| Shortest Path | What is the fastest or cheapest route from A to B? | Delivery routing, navigation, network latency minimisation |
| Minimum Cost Flow | How should we route product through a supply network to minimise cost? | Distribution planning, logistics networks, supply chain optimisation |
| Maximum Flow | What is the maximum throughput from source to sink given link capacities? | Network bandwidth planning, production throughput, pipeline capacity |
| Vehicle Routing Problem (VRP) | What is the optimal set of routes for a fleet of vehicles to serve all customers? | Last-mile delivery, field service scheduling, waste collection |
| Travelling Salesperson (TSP) | What is the shortest route visiting all locations exactly once? | Sales territory design, circuit board drilling, gene sequencing |
The transportation problem ships goods from multiple supply points (warehouses) to multiple demand points (customers) at minimum cost. It is a special case of LP: supply constraints at each warehouse, demand constraints at each customer, and a shipping cost for each route. In Python: or . Solvable to global optimality in seconds even for very large networks.
Assignment Problems
Assignment problems assign n agents (workers, machines, vehicles) to n tasks in a one-to-one matching that minimises total cost (or maximises total performance). The Hungarian algorithm solves assignment problems exactly and efficiently.
A contact centre has 5 agents and 5 shifts. Each agent has a performance score for each shift (based on historical data). The assignment problem finds the allocation of agents to shifts that maximises total performance — ensuring each agent gets exactly one shift and each shift has exactly one agent.
Without optimisation: the manager might intuitively assign agents — producing a total performance score of 78. The Hungarian algorithm finds the optimal assignment with a total score of 94 — an 20% improvement from the same 5 agents, just better matched to shifts.
In Python: — this is the most practical and fastest implementation.
What Excel Solver can handle (200 variables, 200 constraints) represents a tiny fraction of industrial optimisation. Major airlines solve crew scheduling problems with millions of variables. Amazon's logistics network uses large-scale mixed-integer programs updated in real time. For problems at that scale, specialist solvers (Gurobi, CPLEX, or commercial OR platforms) are used — but the same formulation principles apply. Learn to formulate correctly in Excel, and the skills transfer to any platform.
Microsoft Excel — Solver
Excel's built-in Solver handles LP, IP, and non-linear optimisation — no code required.
Solver is a built-in Excel add-in but may not be active by default. To enable: . Solver then appears under on the ribbon.
Rule: always use Simplex LP first — if it fails or the problem is non-linear, switch to GRG Nonlinear.
"Solver could not find a feasible solution": The constraints are contradictory — no solution satisfies all of them simultaneously. Review constraints for errors (e.g. minimum requirements that exceed available capacity). "Solver has converged to the current solution": GRG Nonlinear stopped at a local optimum — try different starting values. "The maximum iteration limit was reached": Increase iterations in Options, or simplify the problem.
Python — PuLP & scipy.optimize
Industrial-scale optimisation — from simple LP to large mixed-integer programs — in clean, readable code.
Optimisation in Business Practice
Concrete examples across five industries — from marketing budget allocation to production scheduling.
| Problem | Objective | Decision Variables | Key Constraints | Type |
|---|---|---|---|---|
| Production Mix | Maximise profit | Units of each product to produce | Machine hours, labour, materials, storage, minimum orders | LP |
| Marketing Budget Allocation | Maximise reach/leads | Budget allocated to each channel | Total budget cap, minimum spend per channel, diminishing returns | NLP |
| Staff Scheduling | Minimise labour cost | Number of staff on each shift | Minimum staffing per shift, maximum hours per person, rest periods | ILP |
| Portfolio Optimisation | Maximise return for given risk | % allocated to each asset | Total = 100%, no short-selling, sector limits, max single-stock weight | QP (quadratic) |
| Supply Chain Routing | Minimise transport cost | Quantity shipped on each route | Supply at each warehouse, demand at each customer, route capacity | LP (transport) |
| Project Selection | Maximise total NPV | Which projects to fund (0/1) | Total budget, resource constraints, dependency rules | Binary IP |
| Optimal Pricing | Maximise revenue or profit | Price per product/segment | Price floor/ceiling, competitor price bounds, margin requirements | NLP |
A company has $500K to allocate across four marketing channels: Digital, TV, Print, Events. Each channel has a diminishing returns curve (spend more, get proportionally less new reach). This makes it a non-linear problem.
| Channel | Max Budget | Min Budget | Reach at Min | Reach at Max |
|---|---|---|---|---|
| Digital | $250K | $50K | 1.2M | 2.6M |
| TV | $200K | $80K | 0.8M | 2.1M |
| $100K | $20K | 0.3M | 0.7M | |
| Events | $150K | $30K | 0.4M | 1.1M |
Optimal allocation (GRG Nonlinear): Digital $250K · TV $150K · Events $80K · Print $20K = Total $500K. Maximised reach: 6.1M unique impressions — vs 5.4M from an equal-split baseline ($125K each). The optimised allocation delivers a 13% reach improvement from the same budget.
Never present just the numbers. Always explain: (1) What was the objective? (Maximise reach within $500K budget.) (2) What are the decision variables? (Budget allocated to each channel.) (3) What are the binding constraints? (Total budget is fully used; Digital is at its maximum.) (4) What is the improvement over the baseline? (13% more reach than equal split.) (5) What happens if a constraint changes? (Shadow price: an extra $10K to Digital would yield 48K more reach.)
Three Simulators
Build intuition for LP formulation, resource allocation, and constraint sensitivity through live interaction.
Simulator 1 · Production Mix Optimiser
A factory makes two products. Adjust the profit margins and resource constraints, then solve to see the optimal production plan and which constraint is binding.
Simulator 2 · LP Problem Builder
Four business scenarios are described. For each, identify: (a) is this a maximisation or minimisation problem, and (b) which statement correctly identifies the objective function?
Simulator 3 · Shadow Price Interpreter
An LP Sensitivity Report has been produced. Interpret the shadow prices and decide which constraint it is worth investing to relax — and why.
Common Mistakes
Optimisation mistakes are uniquely dangerous — the solver always produces an answer, even if the problem is wrong.
Forgetting constraints — the "optimistic" model
A Solver model that ignores capacity, regulatory, or service-level constraints will produce a solution that is mathematically optimal but operationally impossible. Always list every real-world limit before running Solver. Test the solution manually to check it is physically achievable.
Using Simplex LP for a non-linear problem
If the objective or any constraint is non-linear (contains multiplication of two variables, squared terms, exponentials), Simplex LP will give a wrong answer without warning. Check: is the profit formula P×Q where both P and Q are decision variables? That is non-linear — use GRG Nonlinear.
Rounding LP solutions to get integers — instead of using IP
Rounding the LP solution (e.g. changing 47.3 to 47) does not give the true integer optimum. It may produce an infeasible solution or a suboptimal one. When variables must be whole numbers, use Integer Programming (add int constraints in Solver).
Treating the LP optimum as the only valid solution
The LP optimum assumes perfectly accurate input data. In practice, objective coefficients (profit margins) and constraint RHS values (capacities) have uncertainty. Always run sensitivity analysis: how much can inputs change before the optimal solution changes? If the shadow price is very high, small data errors have large consequences.
Not validating the solution with domain experts
An LP solution might be mathematically optimal but practically problematic — ignoring setup times between products, contractual obligations to specific customers, or quality constraints not in the model. Always review the optimal solution with operations staff before implementing.
Optimising the wrong objective
Maximising revenue when the real goal is profit. Minimising cost when the real goal is cost per unit of output. The single most important decision in any optimisation model is formulating the objective correctly. A perfectly solved model with the wrong objective leads to confidently wrong decisions.