Solver for Product Mix: Why the Highest-Margin Product Should Not Be Made
Two sensible heuristics give $59,160 and $63,000 of contribution. Solver gives $68,190 and makes none of the highest-margin product. Here is the setup, the constraint everyone forgets, and how to read the sensitivity report.
When Intuition Is Not Enough
With one scarce resource, product mix is easy: rank by contribution per unit of that resource and make the top one until the resource or the demand runs out. With three scarce resources and three products, no ranking works, because the best product on one resource is the worst on another.
That is what Solver is for. The setup takes five minutes and the answer here beats the best heuristic by $5,190 a period.
Try the break-even calculatorEstablish the contribution margins that a mix optimisation depends on.The problem
Swipe sideways to compare columns.
| Product | Contribution | Machine hours | Labour hours | Material kg | Max demand |
|---|---|---|---|---|---|
| A | $42 | 0.5 | 1.2 | 3 | 900 |
| B | $65 | 1.1 | 1.5 | 5 | 600 |
| C | $28 | 0.3 | 0.8 | 2 | 1,500 |
Swipe sideways to compare columns.
| Resource | Capacity |
|---|---|
| Machine hours | 900 |
| Labour hours | 1,800 |
| Material | 5,500 kg |
Two reasonable answers, both wrong
Swipe sideways to compare columns.
| Rule | Mix | Contribution |
|---|---|---|
| Highest contribution per unit first | 600 B, 480 A, 0 C | $59,160 |
| Highest contribution per machine hour first | 1,500 C, 500 A, 0 B | $63,000 |
| Solver | 0 A, 418 B, 1,465 C | $68,190 |
The sheet
Swipe sideways to compare columns.
| Block | Contents |
|---|---|
| Decision variables | One cell per product for units made, initially 0 |
| Objective | =SUMPRODUCT(Contributions, Units) |
| Constraint usage | =SUMPRODUCT(MachineHours, Units), and one per resource |
| Constraint capacity | The available figures, in cells beside the usage |
Put usage and capacity side by side. Solver takes a cell reference on each side of every constraint, and having them adjacent makes the model readable and the constraint entry fast.
Running Solver
- Enable it once: File, Options, Add-ins, Excel Add-ins, Go, tick Solver Add-in. It appears on the Data tab.
- Set Objective: the total contribution cell. Choose Max.
- By Changing Variable Cells: the three unit cells.
- Subject to the Constraints: add one per resource, usage cell less than or equal to capacity cell.
- Add one per product for maximum demand, units less than or equal to demand.
- Tick Make Unconstrained Variables Non-Negative. This is the constraint everyone forgets.
- Select Simplex LP as the solving method, since the model is linear.
- Solve, then keep the Solver solution and select the Sensitivity report.
Choose Simplex LP rather than GRG Nonlinear whenever the model is linear, meaning the objective and every constraint are sums of variables multiplied by constants. Simplex guarantees the global optimum on a linear model; GRG can stop at a local one and gives no warning.
The answer
Swipe sideways to compare columns.
| Value | Capacity | Slack | |
|---|---|---|---|
| Product A | 0 units | 900 demand | 900 |
| Product B | 418 units | 600 demand | 182 |
| Product C | 1,465 units | 1,500 demand | 35 |
| Machine hours | 899.3 | 900 | 0.7 |
| Labour hours | 1,799 | 1,800 | 1 |
| Material | 5,020 kg | 5,500 | 480 |
| Total contribution | $68,190 | — | — |
Machine hours and labour hours are both effectively fully used; material is not. That means material is not what is limiting the business, and buying more of it would change nothing. The Sensitivity report says this precisely.
The sensitivity report, which is the point
Swipe sideways to compare columns.
| Column | Meaning |
|---|---|
| Shadow price | Extra contribution from one more unit of that resource |
| Allowable increase and decrease | The range over which the shadow price holds |
| Reduced cost | How much a non-produced product's margin must rise before making it is worthwhile |
| Final value and slack | Whether the constraint actually binds |
The shadow prices are the operational answer. On this model an extra machine hour is worth $23.26 of contribution and an extra labour hour $26.28, so overtime at $20 an hour is profitable and at $30 is not. Material has a shadow price of zero, because 480 kg sits unused.
The reduced cost on product A is −$1.16. Its $42 contribution would have to reach $43.16 before making it is worthwhile, because the resources one unit consumes are worth $43.16 spent on B and C. That converts "why are we not making A?" from an argument into a number.
Common variations
- Minimum production commitments: add a constraint that units are greater than or equal to a contracted quantity.
- Whole units only: add an integer constraint. This changes the method to Simplex with branch and bound and slows it substantially; on large models, solve as continuous and round down.
- A make-or-buy decision: add a purchased-units variable per product with its own cost and no resource usage.
- Blending: constrain a weighted average, such as protein content, using SUMPRODUCT on both sides.
- Multiple periods: replicate the block per period and link inventory carried forward as a constraint.
What Solver does not know
- Whether the contribution margins are right. Everything depends on them, and allocated overhead sneaking into a contribution margin is the most common way a mix model gives the wrong answer.
- Whether the demand ceilings are real. A model that stops making a product because of a demand cap is only as good as that cap.
- Strategic effects. Dropping product A entirely may lose customers who buy B and C alongside it, which no constraint in this model expresses.
- Setup and changeover costs, which make a mix of three products more expensive to run than the model assumes.
- That capacity is fixed. The shadow prices exist precisely to tell you what relieving a constraint is worth, and the model itself will not suggest doing so.
- Uncertainty. The optimum is computed from point estimates and can sit on a knife edge. Rerun with a 10% margin change per product and see whether the mix holds.
Why does Solver make none of my highest-margin product?
Because contribution per unit is not the criterion when several resources are scarce. Product A here has a high margin and consumes a lot of the two binding resources. The units it would use are worth more when spent on B and C together, so the optimum excludes it.
Simplex LP or GRG Nonlinear?
Simplex LP whenever the model is linear, which product mix models usually are. It is faster and guarantees the global optimum. GRG Nonlinear is for models with multiplication between variables or other non-linear terms, and it can stop at a local optimum.
Why is Solver returning negative production quantities?
Non-negativity is not on. Tick Make Unconstrained Variables Non-Negative in the Solver dialog, or add explicit constraints requiring each variable to be at least zero. This is the most common Solver setup error.
What is a shadow price?
The extra contribution you would get from one more unit of a constrained resource. It is what an extra machine hour or an extra kilo of material is actually worth to you, and it turns overtime and purchasing decisions into a direct comparison against a price.
How do I force whole units?
Add a constraint with "int" as the relationship. Be aware that integer programming is much slower, and on a model with many variables it can take minutes or fail to finish. For large production quantities, solving as continuous and rounding down loses almost nothing.
Solver says it could not find a feasible solution. Now what?
The constraints contradict each other. Remove them one at a time until it solves, and the last one you removed is part of the conflict. Minimum commitments that together exceed capacity are the usual cause.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team