Source: Lab 7 Writeup, Excel Solver exercises
Tags: linear programming, LP, Excel Solver, production economics, constrained optimisation, shadow price, sensitivity analysis, binding constraints, slack, objective function, APEC 3002
Difficulty: Intermediate | Prerequisites: Basic algebra, introductory microeconomics (cost and profit concepts), familiarity with Excel.
Linear programming (LP) is a method for finding the best outcome in a mathematical model whose requirements are expressed as linear relationships. In applied economics, it is used to determine how a firm should allocate scarce resources (materials, machine time, assembly capacity) across products to maximise profit or minimise cost. This lab sits at the intersection of production economics and operations research: you are learning how to translate a real business problem into a system of equations and let a solver find the optimum. If you are comfortable with setting up equations and interpreting inequalities, you have the prerequisites.
Linear programming finds the profit-maximising (or cost-minimising) combination of outputs subject to resource constraints. You set up an objective function, define constraints as inequalities, and use Excel Solver to find the optimal solution. The sensitivity report then tells you which constraints are actually limiting your profit (binding) and how much an extra unit of each scarce resource would be worth (shadow price).
Linear programming (LP)
A mathematical method for determining the optimal allocation of limited resources to achieve a given objective (e.g. maximum profit), subject to a set of linear constraints. In simple terms, it is a structured way of answering: "Given what I have, what is the best I can do?"
Objective function
The equation you are trying to maximise or minimise. In this lab, it is the total profit function: Profit = 46.28 × Y1 + 27.77 × Y2. Think of it as the scoreboard: whatever combination of Y1 and Y2 you choose, this formula tells you how much profit results.
Decision variables
The quantities you are choosing. Here, Y1 (units of Product 1) and Y2 (units of Product 2). These are the unknowns the solver adjusts to find the optimum.
Constraints
Inequalities that limit what the decision variables can be. Each constraint represents a scarce resource. In this lab: raw materials, machine processing time, and assembly capacity for each division. Think of them as the walls of the box you are working inside.
Binding constraint
A constraint whose resource is fully used up at the optimal solution (slack = 0). It is actively limiting your profit. If it were relaxed, the optimal profit would change.
Non-binding constraint (slack constraint)
A constraint with leftover (unused) resources at the optimum. Relaxing it would not change the solution. The amount of leftover is called slack.
Slack
The difference between the available resource and the amount actually used at the optimal solution. A binding constraint has zero slack; a non-binding constraint has positive slack.
Shadow price (dual value)
The amount by which the objective function (profit) would increase if the right-hand side of a constraint were increased by one unit, all else equal. It tells you the marginal value of one more unit of that scarce resource. A non-binding constraint always has a shadow price of zero.
Reduced cost
For a decision variable at zero in the optimal solution, the reduced cost tells you how much its objective coefficient would need to improve before it would enter the solution. If a variable is already positive (in the basis), its reduced cost is zero.
Sensitivity report
An Excel Solver output that shows shadow prices, reduced costs, and the allowable ranges over which these values remain valid. It answers "what if" questions without re-running the solver.
Allowable increase / allowable decrease
The range over which an objective coefficient or a constraint's right-hand side can change before the current optimal basis (set of binding constraints) changes. Within this range, shadow prices and the current solution structure hold.
Identify the decision variables
What quantities are you choosing? In this problem: Y1 (units of Product 1) and Y2 (units of Product 2).
These must be continuous (or integer, depending on the problem) and non-negative.
Write the objective function
The function you want to maximise (or minimise).
Here: Maximise Profit = $46.28 × Y1 + $27.77 × Y2.
Each coefficient represents the per-unit profit contribution of that product.
Specify the constraints
Each constraint takes the form: (resource used by Y1) × Y1 + (resource used by Y2) × Y2 ≤ (resource available).
Raw materials: 4.5 × Y1 + 9 × Y2 ≤ 180
Machine processing time: 2.15 × Y1 + 0.95 × Y2 ≤ 40
Assembly capacity, Division 1: 1 × Y2 ≤ 18 (only Y2 uses Division 1)
Assembly capacity, Division 2: 1 × Y1 ≤ 12 (only Y1 uses Division 2)
Non-negativity
Y1 ≥ 0, Y2 ≥ 0. You cannot produce negative quantities.
Solver setup in Excel
Set the objective cell to the profit formula.
Set the changing cells to Y1 and Y2.
Add each constraint as a cell reference with the appropriate inequality.
Select "Simplex LP" as the solving method (because all relationships are linear).
Run Solver. It returns the optimal values of Y1 and Y2 and the maximum profit.
The Answer Report is the first output to check. It confirms whether Solver found a valid solution and shows which constraints matter.
Optimal solution from this lab
Y1 = 12 units, Y2 = 18 units.
Maximum profit = $1,055.22.
Profit from Y1: 12 × $46.28 = $555.36.
Profit from Y2: 18 × $27.77 = $499.86.
Constraint status and slack
Constraint | Resource available | Resource used | Status | Slack |
|---|---|---|---|---|
Raw materials | 180 | 54 + 162 = 216? (see note) | Not binding | 126 |
Machine processing time | 40 | 25.8 + 17.1 = 42.9? (see note) | Not binding | 14.2 |
Assembly, Div 1 (Y2) | 18 | 18 | Binding | 0 |
Assembly, Div 2 (Y1) | 12 | 12 | Binding | 0 |
Note: the "Total Y1" and "Total Y2" columns in the lab's table track each product's resource usage separately across both products' combined constraint. The slack values from the Answer Report (126 and 14.2 for materials and processing) confirm that those resources have plenty of room.
What binding means here
Y1 is capped at 12 because Assembly Division 2 can handle exactly 12 units, no more.
Y2 is capped at 18 because Assembly Division 1 can handle exactly 18 units, no more.
Raw materials and machine time are not the bottleneck. Even at full production of both goods, there is leftover capacity in those resources.
Key takeaway
If the firm wants to increase profit, it needs to expand assembly capacity, not buy more raw materials or machine time. The binding constraints point directly to where investment would pay off.
The Sensitivity Report is where the deeper analytical insight lives. It has two sections: Variable Cells and Constraints.
Variable Cells section
This section reports on each decision variable.
Final Value: the optimal quantity (Y1 = 12, Y2 = 18).
Reduced Cost: both are 0 here, because both products are in the solution (produced at positive levels). A reduced cost of zero means the variable is already "paying its way."
Objective Coefficient: the per-unit profit ($46.28 for Y1, $27.77 for Y2).
Allowable Increase / Decrease: how much the per-unit profit can change before the optimal production plan changes. Y1's profit could fall by up to $46.28 (to zero) or rise by any amount (1E+30, effectively infinity) and Y1 = 12 would still be optimal. Same logic applies to Y2.
Why the allowable ranges are so wide here: each product is limited by its own dedicated assembly constraint, so changes in profit coefficients do not cause the solver to trade one product for the other.
Constraints section
This section reports on each constraint.
Final Value: how much of the resource is used.
Shadow Price: the marginal value of relaxing the constraint by one unit.
Assembly Div 2 (Y1's constraint): shadow price = $46.28. One more unit of Y1 assembly capacity would let the firm produce one more Y1, adding $46.28 to profit.
Assembly Div 1 (Y2's constraint): shadow price = $27.77. One more unit of Y2 assembly capacity adds $27.77.
Raw materials and machine time: shadow price = $0. These resources are not scarce at the optimum, so a marginal unit of either adds nothing.
Constraint R.H. Side: the current resource limit.
Allowable Increase / Decrease: the range over which the shadow price stays valid.
For Assembly Div 2 (RHS = 12): can increase by about 6.6 units or decrease by 12 before the basis changes.
For Assembly Div 1 (RHS = 18): can increase by 2 or decrease by 18.
How to use the sensitivity report
Suppose you are asked: "If the firm could add 3 more units of Assembly Div 2 capacity, how much more profit would result?" Check: is 3 within the allowable increase of ~6.6? Yes. So the additional profit = 3 × $46.28 = $138.84. This works only within the allowable range; beyond it, you must re-solve.
Objective function (general form)
Maximise Z = c₁Y₁ + c₂Y₂
where c₁ and c₂ are per-unit profit contributions.
This lab's objective function
Maximise Z = 46.28Y₁ + 27.77Y₂
General constraint form
a₁Y₁ + a₂Y₂ ≤ b
where a₁ and a₂ are the per-unit resource requirements and b is the total resource available.
Shadow price interpretation
ΔProfit ≈ (shadow price) × (Δ in RHS of constraint)
Valid only within the allowable increase/decrease range.
Slack calculation
Slack = RHS – (actual resource usage at optimum)
If slack = 0, the constraint is binding. If slack > 0, the constraint is non-binding.
LP is used heavily in agriculture (crop mix decisions given land, water, and labour), manufacturing (product mix given machine and material limits), logistics (minimising transport costs across a network), and finance (portfolio allocation within risk constraints). Any time a manager faces "I have limited X, Y, Z and want to maximise profit," LP is the standard tool.
"A non-binding constraint does not matter." It matters in the sense that it still defines the feasible region. It simply is not the active bottleneck at the current optimum. If conditions change, a previously non-binding constraint can become binding.
"Shadow price tells you the total value of a resource." It does not. It tells you the marginal value of one additional unit, and only within the allowable range. Beyond that range, the shadow price itself changes.
"If the shadow price is $46.28, I should always pay up to $46.28 for an extra unit of that resource." Only if the cost of acquiring that unit is less than $46.28 and you are still within the allowable range. The shadow price is a ceiling on what you should be willing to pay, not a recommendation to pay it.
"Reduced cost means the product is unprofitable." Not exactly. A positive reduced cost on a variable at zero means its profit contribution is not high enough, given the current resource prices, to justify producing it. If its per-unit profit rose by the reduced cost amount, it would enter the solution.
⚠️ You will almost certainly be asked to identify binding vs non-binding constraints and explain what the distinction means for resource allocation decisions.
⚠️ Shadow price interpretation is a staple exam question. Be ready to calculate the change in profit from a marginal change in a constraint's RHS, and to state the range over which that calculation is valid.
⚠️ Know the difference between the sensitivity report's two sections (variable cells vs constraints) and what each column means.
⚠️ Be able to set up a simple two-variable LP from a word problem: identify decision variables, write the objective function, write constraints, and state the non-negativity conditions.
True or False: A binding constraint has positive slack.
False. A binding constraint has zero slack.
True or False: A shadow price of zero means the resource is scarce.
False. A zero shadow price means the resource is not scarce (the constraint is non-binding).
Fill in the blank: The shadow price tells you the change in the ______ for a one-unit increase in a constraint's right-hand side.
Objective function value (profit, in a maximisation problem).
True or False: If both decision variables have a reduced cost of zero, both are produced at positive levels in the optimal solution.
True (in the standard LP case).
Fill in the blank: Shadow prices are valid only within the ______ reported in the sensitivity analysis.
Allowable increase/decrease range.
Q: In the lab's LP problem, which constraints are binding and why?
A: Assembly Division 2 (limiting Y1 to 12) and Assembly Division 1 (limiting Y2 to 18) are binding. Their resources are fully used at the optimum, with zero slack. Raw materials and machine processing time are non-binding, with slack of 126 and 14.2 respectively.
Q: The shadow price on Assembly Division 2 is $46.28. If capacity could be expanded by 5 units, what is the expected increase in profit? State any conditions.
A: Expected increase = 5 × $46.28 = $231.40, provided 5 is within the allowable increase (approximately 6.6 units). Since 5 < 6.6, the calculation is valid. Beyond 6.6 units, the shadow price would change and you would need to re-solve.
Q: Why do the decision variables in this problem both have a reduced cost of zero?
A: Because both Y1 and Y2 are produced at positive levels in the optimal solution. Reduced cost is only meaningful (non-zero) for variables that are at their lower bound (typically zero) in the optimum.
Q: Explain why increasing the raw materials budget from 180 to 200 would not change the optimal profit.
A: Raw materials is a non-binding constraint with 126 units of slack. The firm is already using far less than the 180 available. Adding 20 more units does nothing because the bottleneck is assembly capacity, not raw materials.
Q: A new product Y3 is proposed with a per-unit profit of $30 and resource requirements of 3 (materials), 1 (machine time), 0 (Assembly Div 1), 0 (Assembly Div 2). Should the firm produce it?
A: Based on shadow prices, the resources Y3 uses (materials and machine time) both have shadow prices of $0, so Y3 does not compete for scarce resources. If it can be produced without needing assembly capacity in either division, and its profit is positive, it should be produced to the extent feasible. However, you would need to add it to the LP and re-solve to confirm, since a new variable may create new binding constraints.
This material connects directly to production theory in microeconomics: the idea that firms face production possibility frontiers defined by resource constraints, and that profit maximisation means choosing the point on (or inside) that frontier where marginal returns are highest. It also links to marginal analysis, as shadow prices are the LP equivalent of marginal value products. In later coursework, these ideas extend to integer programming (when you cannot produce fractional units), nonlinear programming (when relationships are not straight lines), and general equilibrium models.
Linear programming, LP, constrained optimisation, constrained optimization, Excel Solver, Simplex method, objective function, decision variables, binding constraint, non-binding constraint, slack variable, shadow price, dual value, dual price, reduced cost, sensitivity analysis, sensitivity report, answer report, allowable increase, allowable decrease, right-hand side, RHS, production economics, product mix, resource allocation, APEC 3002, managerial economics, production possibility frontier, marginal value product