Difficulty: Intermediate | Prerequisites: Basic calculus (derivatives, first-order conditions), introductory microeconomics (cost curves, utility theory, profit maximisation).
This lab introduces numerical optimisation using Excel Solver, a tool that finds the best value of an objective function by adjusting decision variables, optionally subject to constraints. The three exercises move from a single-variable unconstrained problem (minimising average total cost) to two constrained problems (maximising profit given a resource limit, and maximising utility given a budget). The Lagrange multiplier, which Solver reports automatically, ties everything together: it tells you the marginal value of relaxing a constraint by one unit. If you are comfortable with setting up an objective, choosing decision variables, and reading a Solver sensitivity report, you have the core toolkit for every optimisation problem in this course.
Excel Solver finds the quantity, output mix, or consumption bundle that optimises an objective (minimise cost, maximise profit, maximise utility) by adjusting decision variables within constraints. The Lagrange multiplier from the sensitivity report tells you how much the objective improves if the constraint loosens by one unit. This lab walks through all three problem types so you can set up and interpret Solver output for any similar exam question.
Objective function
The mathematical expression you are trying to optimise (minimise or maximise). In simple terms, this is the thing you care about: total cost, profit, or utility.
Decision variable (choice variable)
The value Solver is allowed to change in order to improve the objective. Think of it as the lever you pull: quantity produced, output of each product, or amount consumed of each good.
Constraint
A limit on the decision variables. For example, total crude oil available cannot exceed 180 barrels, or total spending cannot exceed $150. Without constraints, the problem is unconstrained.
Average total cost (ATC)
Total cost divided by quantity produced. The U-shaped ATC curve reaches its minimum at the efficient scale of production. In this lab, ATC = 16.42 − 0.0424Q + 0.0001Q².
Lagrange multiplier (shadow price)
The rate at which the optimal value of the objective function changes per one-unit increase in the constraint. In simple terms, it answers: "If I had one more unit of the scarce resource, how much better off would I be?" A multiplier of zero means the constraint is not binding.
GRG Nonlinear (Generalised Reduced Gradient)
The Solver engine used for smooth, nonlinear problems. It works by computing gradients (slopes) and stepping toward the optimum iteratively.
Sensitivity report
A Solver output sheet that lists the final values of decision variables, the reduced gradient for each variable, and the Lagrange multiplier for each constraint.
Reduced gradient
The rate of change of the objective with respect to a decision variable at the optimal solution. A reduced gradient of zero means the variable is at an interior optimum (not pushed against a bound).
Setup
Objective: minimise ATC
Decision variable: quantity produced, Q
No constraints (Solver adjusts Q freely)
ATC function: ATC(Q) = 16.42 − 0.0424Q + 0.0001Q²
Solver result
Optimal Q ≈ 212 units
Minimised ATC ≈ $11.93
Why Q ≈ 212?
Take the first derivative of ATC with respect to Q and set it to zero: −0.0424 + 0.0002Q = 0, so Q = 0.0424 / 0.0002 = 212.
The second derivative is 0.0002 > 0, confirming this is a minimum.
Solver reaches the same answer numerically.
Interpretation
At 212 units the firm produces at its efficient scale, the lowest point on the U-shaped ATC curve.
Producing fewer or more units raises the per-unit cost.
Setup
Objective: maximise profit from two products
Decision variables: output of Product 1 (Y1) and Product 2 (Y2)
Constraint: total crude oil used ≤ available supply (varied from 60 to 300 barrels)
Solver engine: GRG Nonlinear
Key results (from the optimisation table)
Crude oil available | Max profit | Y1 output | Y2 output | Lagrange multiplier |
|---|---|---|---|---|
60 | $280.39 | 3.06 | 0.00 | 4.17 |
120 | $441.05 | 5.55 | 0.29 | 1.57 |
180 | $516.23 | 5.75 | 1.76 | 0.93 |
240 | $552.86 | 5.94 | 3.24 | 0.29 |
300 | $556.75 | 6.03 | 3.90 | 0.00 |
Patterns to notice
Product 1 always receives more output than Product 2 because it uses less crude oil per unit (higher profit per barrel of input).
Profit increases as more crude oil becomes available, but at a decreasing rate (diminishing returns to the resource).
The Lagrange multiplier falls as crude oil increases: when oil is scarce (60 barrels), one extra barrel is worth $4.17 in profit; when oil is abundant (300 barrels), the constraint is no longer binding and the multiplier is zero.
Reading the sensitivity report (180-barrel case)
Final values: Y1 = 5.75, Y2 = 1.76
Reduced gradients for both variables = 0, meaning neither variable is stuck at a bound; both are at interior optima.
Lagrange multiplier for crude oil = 0.93: if the firm could obtain one more barrel (181 instead of 180), profit would rise by approximately $0.93.
Setup
Objective: maximise utility U(X1, X2)
Decision variables: consumption of good X1 and good X2
Constraint: total spending ≤ available budget ($150 or $170)
Key results
Budget | Utility | X1 consumed | X2 consumed | Lagrange multiplier |
|---|---|---|---|---|
$150 | 490.65 | 281.25 | 190.375 | 2.6168 |
$170 | 542.32 | 318.75 | 123.95 | 2.5521 |
Patterns to notice
A larger budget raises total utility, but the Lagrange multiplier (marginal utility of income) falls slightly from 2.62 to 2.55. Each extra dollar of budget is still valuable, but marginally less so.
The optimal bundle shifts: X1 rises while X2 falls when the budget increases from $150 to $170, suggesting the utility function and prices make X1 the better value at the margin in this range.
The multiplier of 2.62 at a $150 budget means that if the consumer had $151 instead of $150, utility would increase by roughly 2.62 units.
ATC function (Part B)
ATC(Q) = 16.42 - 0.0424Q + 0.0001Q^2First-order condition for ATC minimum
\frac{dATC}{dQ} = -0.0424 + 0.0002Q = 0 \quad \Rightarrow \quad Q^* = 212Minimised ATC
ATC(212) = 16.42 - 0.0424(212) + 0.0001(212)^2 \approx 11.93Lagrange multiplier interpretation (general)
If the constraint is resource ≤ b, and the optimal objective value is V*(b), then the Lagrange multiplier λ = dV*/db. In words: λ tells you the marginal value of one more unit of the constrained resource.
Refineries use exactly this kind of constrained optimisation to decide how much gasoline, diesel, and jet fuel to produce from a limited supply of crude oil. The Lagrange multiplier tells a refinery manager the dollar value of securing one more barrel on the spot market.
Consumer budget problems mirror how households allocate income across goods. The marginal utility of income (the Lagrange multiplier) is why economists care about income transfers: a dollar is worth more to someone whose multiplier is high.
Students often think the Lagrange multiplier is a price. It is not a market price; it is the shadow price, the implied value of relaxing the constraint internally. It only equals a market price under perfect competition.
Students sometimes believe that a Lagrange multiplier of zero means the constraint is irrelevant. It means the constraint is not binding at the optimum, i.e. the firm already has more of the resource than it needs. The constraint still exists; it just does not limit the solution.
A common error is confusing the ATC-minimising quantity with the profit-maximising quantity. Minimising cost per unit is not the same as maximising total profit. Profit maximisation requires marginal revenue information as well.
Students often set up Solver with the wrong cell as the objective or forget to specify constraints, then wonder why the result is nonsensical. Always double-check which cell is the objective, which cells are adjustable, and which constraints are entered.
⚠️ You will almost certainly be asked to interpret a Lagrange multiplier in words. Practice the sentence: "If the constraint were relaxed by one unit, the optimal [profit/utility/cost] would change by approximately [multiplier value]."
⚠️ Know the difference between unconstrained and constrained optimisation setups. Unconstrained: set the first derivative to zero. Constrained: use Solver (or Lagrangian calculus) and report the multiplier.
⚠️ Be ready to explain why the Lagrange multiplier decreases as the resource becomes more abundant (diminishing marginal returns to the resource).
⚠️ Reduced gradient = 0 for a decision variable means it is at an interior optimum, not at a boundary.
True or false: The Lagrange multiplier tells you the total value of the constrained resource.
False. It tells you the marginal value of one additional unit of the resource.
Fill in the blank: The ATC function ATC(Q) = 16.42 − 0.0424Q + 0.0001Q² is minimised at Q = ______.
212 units.
True or false: A reduced gradient of zero for a decision variable means that variable is not contributing to the objective.
False. It means the variable is at an interior optimum; further adjustment in either direction would not improve the objective.
Fill in the blank: When Plume Petroleum has 300 barrels of crude oil, the Lagrange multiplier is ______, meaning the constraint is ______.
0; not binding (the firm has more oil than it needs at the optimum).
True or false: Increasing the budget from $150 to $170 raises the Lagrange multiplier for the budget constraint.
False. The multiplier falls from 2.62 to 2.55, reflecting diminishing marginal utility of income.
Q: Given ATC(Q) = 16.42 − 0.0424Q + 0.0001Q², find the output level that minimises ATC and state the minimised ATC value.
A: Set dATC/dQ = −0.0424 + 0.0002Q = 0. Solving gives Q* = 212. Substituting back: ATC(212) = 16.42 − 0.0424(212) + 0.0001(212²) ≈ 11.93.
Q: Plume Petroleum's Lagrange multiplier for crude oil falls from 4.17 (at 60 barrels) to 0 (at 300 barrels). Explain in economic terms why this happens.
A: As crude oil becomes more abundant, the firm can produce closer to its unconstrained optimum. Each additional barrel contributes less to profit because the highest-return production opportunities are exploited first. At 300 barrels, the firm reaches its unconstrained optimum and extra oil has zero marginal value.
Q: A consumer has a budget of $150 and maximises utility at U = 490.65 with a Lagrange multiplier of 2.6168. What does the multiplier tell you, and what would you expect utility to be (approximately) if the budget increased to $151?
A: The multiplier means one additional dollar of budget raises utility by approximately 2.62 units. At $151 the consumer's utility would be roughly 490.65 + 2.62 ≈ 493.27.
Q: In the sensitivity report for the 180-barrel crude oil problem, both decision variables have a reduced gradient of zero. What does this tell you about the solution?
A: Both Product 1 and Product 2 output levels are at interior optima. Neither variable is stuck at an upper or lower bound; Solver found a point where further adjustment in any direction cannot improve profit.
Q: Explain the difference between minimising ATC and maximising profit. Could the ATC-minimising output also be the profit-maximising output?
A: Minimising ATC finds the lowest per-unit cost, while profit maximisation finds the output where marginal revenue equals marginal cost. They coincide only in the special case where the market price happens to equal the minimum ATC (the zero-economic-profit / break-even point in perfect competition). In general, the profit-maximising output differs from the ATC-minimising output.
This lab connects directly to cost theory (APEC 3002 lectures on cost curves, economies of scale, efficient scale). The ATC minimisation problem is the numerical version of what you see drawn on the board.
The Lagrange multiplier reappears in consumer theory (utility maximisation subject to a budget constraint) and producer theory (input allocation across products). Understanding it here makes the theoretical derivation in later lectures far more intuitive.
Constrained optimisation with Solver is also the foundation for linear programming problems you may encounter in operations research or supply-chain modules.
Optimisation, optimization, Excel Solver, GRG Nonlinear, Lagrange multiplier, shadow price, average total cost, ATC minimisation, constrained optimisation, unconstrained optimisation, sensitivity report, reduced gradient, marginal value of a resource, diminishing returns, profit maximisation, utility maximisation, budget constraint, Plume Petroleum, APEC 3002, managerial economics, applied microeconomics, decision variables, objective function, first-order condition, efficient scale