A firm that needs several products can either make them in-house (using labour and materials) or buy them from an outside supplier at a fixed price per unit. Linear programming finds the cost-minimising mix of making and buying, subject to demand requirements and capacity constraints on resources like labour hours. The Excel Solver Answer Report and Sensitivity Report tell you what the optimal plan is, which constraints are limiting you, and how much each constraint or cost change would shift the total cost.
Difficulty: Intermediate. You should be comfortable with basic algebra, the idea of an objective function, and navigating Excel before tackling this material. Prior exposure to constrained optimisation (e.g. graphical LP solutions from earlier labs) is helpful.
This lab sits at the intersection of production economics and operations research. In managerial economics you study how firms allocate scarce resources to maximise profit or minimise cost; linear programming (LP) is the main quantitative tool for doing that when the relationships are linear. The make-or-buy (or "outsourcing") decision is one of the classic LP applications: a manufacturer with limited labour capacity must decide which products to produce internally and which to purchase from outside vendors. If you have followed the earlier APEC 3002 labs on cost functions and production, this is where those ideas meet a formal optimisation framework.
Objective function
The mathematical expression that the solver minimises or maximises. In this problem it is total cost: the sum of (units made × unit variable cost to make) + (units bought × unit cost to buy) across all models. Think of it as the single number you are trying to make as small (or large) as possible.
Decision variables
The quantities the solver is free to change. Here they are the number of units to make and the number of units to buy for each slip-ring model (A, B, C, D). In simple terms, these are the levers the manager can pull.
Constraints
Conditions the solution must satisfy. This problem has two types: demand constraints (total made + bought for each model must meet or exceed the number needed) and capacity constraints (total labour hours used for wiring and harnessing cannot exceed hours available).
Binding constraint
A constraint whose available resource is fully used up in the optimal solution, leaving zero slack. In simple terms, this is a bottleneck: the resource is completely consumed.
Non-binding constraint
A constraint with leftover capacity (positive slack) at the optimum. The resource is not fully used, so having a little more of it would not change the solution.
Slack
The difference between the amount of a resource available and the amount actually used. A binding constraint has slack = 0; a non-binding constraint has slack > 0.
Shadow price (dual value)
The amount by which the objective function improves if you increase the right-hand side of a binding constraint by one unit, holding everything else constant. Think of it as the value of one more unit of that scarce resource. For a minimisation problem, a positive shadow price on a demand constraint means one more unit of required demand raises total cost by that amount. A negative shadow price on a capacity constraint means one more hour of capacity reduces total cost by that amount.
Reduced cost
For a decision variable that is zero in the optimal solution, the reduced cost tells you how much its objective coefficient would need to improve before that variable would enter the solution (become positive). Think of it as the penalty for forcing one unit of a variable that the solver chose not to use.
Allowable increase / allowable decrease
The range over which you can change an objective coefficient or a constraint's right-hand side without altering the current basis (set of basic variables). Within this range, the shadow prices and reduced costs remain valid.
Simplex LP
The algorithm Excel Solver uses here. It moves along the edges of the feasible region (the set of all solutions satisfying every constraint) to find the vertex where the objective function is optimal.
Make-or-buy decision
A managerial choice about whether to produce a component internally or purchase it from an outside supplier. LP helps by finding the least-cost combination given internal capacity limits.
Electro-Poly Corporation manufactures four models of slip ring (A, B, C, D). Each model can be made in-house at a variable cost or bought from an outside supplier at a fixed purchase price. The firm has limited hours of two types of labour: wiring and harnessing.
Model A | Model B | Model C | Model D | |
|---|---|---|---|---|
Per-unit variable cost to make | $103.94 | $101.88 | $66.39 | $41.10 |
Unit cost to buy | $116.70 | $128.00 | $88.30 | $62.00 |
For every model, making is cheaper per unit than buying. The reason you would ever buy is that making uses scarce labour hours.
Model A | Model B | Model C | Model D | |
|---|---|---|---|---|
Units needed | 500 | 1,350 | 3,000 | 4,500 |
These are minimum requirements. The firm must supply at least this many of each model.
Model A | Model B | Model C | Model D | |
|---|---|---|---|---|
Wiring (hr/unit) | 0.97 | 0.98 | 0.50 | 0.64 |
Harnessing (hr/unit) | 0.48 | 0.33 | 0.62 | 0.35 |
Buying a unit from outside uses zero wiring and zero harnessing hours.
Units to make for each model: Mₐ, M_b, M_c, M_d
Units to buy for each model: Bₐ, B_b, B_c, B_d
All eight are continuous (fractional units allowed by Solver) and non-negative.
Min Z = 103.94·Mₐ + 101.88·M_b + 66.39·M_c + 41.10·M_d + 116.70·Bₐ + 128.00·B_b + 88.30·B_c + 62.00·B_d
Demand: Mₐ + Bₐ ≥ 500, M_b + B_b ≥ 1,350, M_c + B_c ≥ 3,000, M_d + B_d ≥ 4,500
Wiring capacity: 0.97·Mₐ + 0.98·M_b + 0.50·M_c + 0.64·M_d ≤ Wiring hours available
Harnessing capacity: 0.48·Mₐ + 0.33·M_b + 0.62·M_c + 0.35·M_d ≤ Harnessing hours available
Non-negativity: all variables ≥ 0
The lab runs the problem under different capacity levels to show how constraint tightness changes the optimal plan.
The Answer Report is the first output to check. It tells you three things: the optimal objective value, the optimal values of the decision variables, and the status of each constraint.
Optimal total cost: $593,450
Make all of Model A? No. Solver sets Units to Make Model A = 0 and Units to Buy Model A = 500 (all bought).
Model B: make all 1,350 in-house, buy none.
Model C: make 2,386.3, buy 613.7 (a split: some made, some bought).
Model D: make all 4,500, buy none.
All four demand constraints are binding (slack = 0), meaning the firm produces exactly what is needed, no surplus.
Wiring: 5,396.1 hours used out of 6,000 available. Not binding, slack = 603.9 hours.
Harnessing: 3,500 hours used out of 3,500 available. Binding, slack = 0. This is the bottleneck.
The key insight: even though making Model A is cheaper per unit ($103.94 vs $116.70), it is optimal to buy all 500 units of Model A. Why? Model A uses a lot of harnessing time (0.48 hr/unit), and harnessing is the scarce resource. The solver reallocates those harnessing hours to models where in-house production saves more money per harnessing hour consumed.
Optimal total cost rises to $605,820.
The same structure holds: Model A still fully bought, Model B and D still fully made.
Model C shifts further toward buying: make 1,821.8, buy 1,178.2 (more outsourcing because harnessing is even tighter).
Harnessing remains binding. Wiring remains non-binding.
Optimal total cost drops to $573,623.
Every model is made in-house: Units to Buy = 0 for all four models.
Neither wiring nor harnessing is binding. Both have positive slack (812 and 879.5 hours respectively).
When capacity is not scarce, the firm simply makes everything itself because making is cheaper for every model.
This progression shows the central lesson: as capacity tightens, the firm is forced to outsource the products where in-house production uses the bottleneck resource least efficiently relative to the cost saving.
Objective (total cost to minimise):
Z = \sum_{i} c^{M}_{i} \cdot M_{i} + c^{B}_{i} \cdot B_{i}where cᴹᵢ is the per-unit variable cost to make model i and cᴮᵢ is the per-unit cost to buy model i.
Demand constraint for each model i:
M_{i} + B_{i} \geq D_{i}Capacity constraint for each resource r (wiring, harnessing):
\sum_{i} a_{r,i} \cdot M_{i} \leq H_{r}where aᵣ,ᵢ is the hours of resource r needed per unit of model i made, and Hᵣ is the total hours of resource r available.
Shadow price interpretation (for a binding capacity constraint on a minimisation problem): if Hᵣ increases by 1, total cost Z decreases by the absolute value of the shadow price. For the harnessing constraint with shadow price of -35.34, one additional harnessing hour would reduce total cost by about $35.34.
Reduced cost interpretation: for a variable at zero (e.g. Units to Make Model A = 0, reduced cost = 4.20), the per-unit cost to make Model A would need to fall by $4.20 (from $103.94 to $99.74) before it would become worthwhile to start making Model A.
The make-or-buy framework is how manufacturers decide whether to outsource components. An automotive company choosing between stamping body panels in-house or buying them from a tier-1 supplier faces exactly this structure: lower variable cost in-house, but only if the press shop has enough capacity. The same logic applies to software firms deciding whether to build a feature internally or license it, and to hospitals choosing between running lab tests in-house or sending them to a reference lab.
Students often think the cheapest-to-make product should always be made in-house. That is wrong when capacity is scarce. The solver considers cost per unit of the bottleneck resource consumed, not just cost per unit of output.
Students confuse "binding" with "important." A non-binding constraint is not unimportant; it simply is not the active bottleneck at the current optimum. If conditions change, a previously non-binding constraint can become binding.
Shadow prices are frequently misread as the cost of the resource itself. The shadow price is the marginal value of relaxing the constraint by one unit, not the market price of the input.
The allowable increase/decrease ranges apply to one parameter at a time, holding all other parameters constant. Students sometimes try to change two parameters simultaneously and assume the shadow prices still hold. They may not.
⚠️ You will almost certainly be asked to identify which constraints are binding and explain why a product that is cheaper to make might still be bought.
⚠️ Shadow price interpretation is a staple exam question. Know the sign convention: for a ≤ capacity constraint in a minimisation problem, the shadow price is negative (more capacity reduces cost). For a ≥ demand constraint, the shadow price equals the cost of supplying one more unit via the cheapest available route.
⚠️ Be ready to use the sensitivity report to answer "what if" questions: e.g. "If harnessing capacity drops by 350 hours, what happens to total cost?" You check whether 350 is within the allowable decrease, then multiply the shadow price by 350.
⚠️ Reduced cost questions are common. If asked "how much would the cost to make Model A need to fall before we would start making it?", the answer is the reduced cost ($4.20 in Scenario 1).
True or false: A binding constraint always has a non-zero shadow price. (True)
Fill in the blank: The ______ tells you how much a variable's cost coefficient must improve before that variable enters the optimal solution. (reduced cost)
True or false: If both capacity constraints are non-binding, the firm should make everything in-house. (True, because making is cheaper for every model in this problem)
True or false: The shadow price of the harnessing constraint tells you the market wage rate for harnessing workers. (False, it tells you the marginal value of one more hour of harnessing capacity to the firm's total cost)
Fill in the blank: A constraint with slack > 0 is called ______. (non-binding)
Q: In Scenario 1 (harnessing = 3,500 hr), why does the solver set Units to Make Model A to zero, even though making Model A ($103.94) is cheaper than buying ($116.70)?
A: Harnessing is the binding constraint. Model A uses 0.48 harnessing hours per unit. By buying Model A, the firm frees 0.48 × 500 = 240 harnessing hours, which can be redirected to making Model C (which saves more per harnessing hour consumed). The net effect is a lower total cost.
Q: What is the shadow price of the harnessing constraint in Scenario 1, and what does it mean?
A: The shadow price is -35.34. This means that if harnessing capacity increased by one hour (from 3,500 to 3,501), total cost would decrease by approximately $35.34, holding everything else constant.
Q: In Scenario 3 (abundant capacity), why are all shadow prices on the capacity constraints equal to zero?
A: Both capacity constraints are non-binding (positive slack). Additional capacity has no value because the firm already has more than it needs. The shadow price of a non-binding constraint is always zero.
Q: The reduced cost for Units to Buy Model D is $8.53 in Scenario 1. What does this mean?
A: The purchase price of Model D ($62.00) would need to fall by $8.53, to $53.47, before it would become cost-effective to start buying Model D instead of making it.
Q: Harnessing capacity falls from 3,500 to 3,150 hours (a drop of 350). Is the shadow price of -35.34 valid for calculating the cost impact?
A: Check the allowable decrease for the harnessing constraint: 1,479.5 hours. Since 350 < 1,479.5, the shadow price is valid over this range. The estimated cost increase is 350 × 35.34 = $12,369. The actual change in total cost between the two scenarios ($605,820 - $593,450 = $12,370) confirms this, with minor rounding.
This material connects directly to opportunity cost: the reason to buy Model A is that the harnessing hours it would consume have a higher-value alternative use (making Model C). The shadow price is, in effect, the opportunity cost of the constrained resource.
It also links to the theory of the firm and vertical integration. The make-or-buy decision is the micro-level version of the broader question of what a firm should do internally vs. contract out, which appears in transaction-cost economics (Coase, Williamson).
The sensitivity analysis concepts here (shadow prices, allowable ranges) reappear in any constrained optimisation setting, including profit-maximisation problems, portfolio allocation, and transportation/logistics models you may encounter later in the course.
linear programming, LP, make or buy, make-or-buy decision, outsourcing decision, Excel Solver, Simplex method, sensitivity analysis, shadow price, dual value, dual price, reduced cost, binding constraint, non-binding constraint, slack variable, allowable increase, allowable decrease, right-hand side ranging, objective coefficient ranging, constrained optimisation, APEC 3002, managerial economics, Electro-Poly Corporation, slip ring, Ragesdale, Spreadsheet Modeling and Decision Analysis, operations research, cost minimisation, production planning, capacity constraint, bottleneck analysis, opportunity cost of capacity