Marsden Fold Packaging buys 24,000 moulded trays a month from four suppliers. April's plan sat in the same workbook as March's, costed to the penny at £109,149. The invoices came to £111,906.
Nothing errored. The cost cell recalculated the moment April's prices went in, so the number on the screen was new. The quantities underneath it were five weeks old.
Solver does not leave a formula behind. It writes constants into the cells you let it change, and a constant does not know that Cowden's capacity was cut by two thirds on 1 April.
| Measure | March | April |
|---|---|---|
| Trays ordered | 24,000 | 24,000 |
| Cowden's capacity | 6,000 | 3,600 |
| Ashgrove's price | £4.10 | £4.38 |
| Quantities in the cells | solved | March's |
| Cost on the sheet | £107,385 | £109,149 |
| Cost on the invoices | £107,385 | £111,906 |
Three faults, and not one of them had a cell to its name. The plan was stale, the capacity that changed lived in a dialog box, and the quality rule the plan existed to meet could not be met in April at all.
What you need. Solver ships with Excel and arrives switched off. File → Options → Add-ins → Manage: Excel Add-ins → Go, then tick Solver Add-in; on Mac it is Tools → Excel Add-ins. It appears at the right-hand end of the Data tab.
Solver takes 200 changing cells and 100 constraints, which is the size of a plan rather than a model. This one uses four changing cells, three rules and one objective.
Packaging is only the example. Shift rotas, blend recipes, media budgets, loan drawdowns: anything with a total to hit, limits to respect and a cost to keep down.
01The Plan the Sheet Still Held
Four Suppliers, One Stale Plan, £2,757 of Expedite
April's reconciliation for Marsden Fold Packaging, in A2:I10. Column B holds April's prices and column D April's capacities, both typed in on the 1st. Column E holds the quantities Solver wrote into the sheet in March and nobody re-solved: 6,300, 7,500, 6,000 and 4,200 trays. Column F is the overrun on capacity, and it is zero on every row but Cowden, whose shutdown took it from 6,000 trays to 3,600. Column G is what actually shipped. Nothing on this sheet errored at any point: the cost cell is a live SUMPRODUCT, so it recalculated the moment April's prices arrived and showed £109,149 — March's split, April's prices, an answer to a question nobody asked. The invoices came to £111,906.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
The sheet above is April's reconciliation, not April's plan. One row per supplier: what the cells asked for, what the supplier could actually ship, and what the invoice said.
Column E is March's answer, still sitting where Solver typed it. Column D is April's capacity, typed in by the buyer on the 1st. Nothing on the sheet compared the two.
| Supplier | Plan in the cells | April capacity | Shipped |
|---|---|---|---|
| Ashgrove | 6,300 | 9,000 | 6,300 |
| Brantham | 7,500 | 7,500 | 7,500 |
| Cowden | 6,000 | 3,600 | 3,600 |
| Deeping | 4,200 | 5,100 | 6,600 |
Cowden came up 2,400 trays short. Deeping covered the gap at three days' notice, 1,500 of it at an expedite rate of £6.20 against its £5.05 contract rate.
Scenario: Open the last workbook Solver ran on. Put =MAX(0,E2-D2) beside each quantity and =SUM(F2:F5) under the lot, and read that before you read the cost. On this sheet it returns 2,400, and anything but nought means the plan is older than the limits.
02Solver Writes Numbers, Not Formulas
Solver and Goal Seek both work by typing into your sheet. They change the cells you nominate until the objective behaves, then leave the last numbers they tried sitting there.
The answer is a snapshot, not a link. Change a price, a capacity or the order quantity afterwards and nothing re-solves.
The objective does recalculate, because it is a formula, and that is exactly what makes a stale plan convincing. April's cost cell read £109,149: March's quantities, April's prices, arithmetic nobody had asked for.
One cell catches it. Keep the objective live in F14, and paste its value into F16 the moment you accept a solution:
=IF(ROUND(F14-F16,2)<>0,"RE-SOLVE","solved on these inputs")
F16 is the cost Solver signed off. F14 is what the same quantities cost today. The moment any input moves, the two part company and the cell says so in a word.
Scenario: Add that pair to your own model now. Paste the objective's value into the cell beside it, then change one price by a penny and watch the flag turn over before you have finished typing.
Try it in the grid03A Constraint Lives in a Dialog, Not on the Sheet
Solver's constraints are saved with the sheet, but they are not on it. They sit in the Solver Parameters box, invisible until somebody opens it, and perfectly happy to hold a figure that is out of date.
March's rule read $K$4 <= 20. Twenty pallets of Cowden, typed as a number, in March, by a buyer who has since moved teams.
April's capacity went into column D, where the sheet could see it. The dialog never heard. Type a reference into Add Constraint, never a number, and the rule reads the cell that the buyer updates.
A reference also survives a changed layout and shows up in Trace Precedents. A typed number does neither.
Scenario: Open Solver on your own model and read the constraint list out loud. Replace every bare number in it with the cell that holds that number, re-solve, and check the answer is the one you had.
Try it in the grid04Write Every Rule Where You Can See It
A rule nobody can see is a rule nobody checks. Give each one three cells on the sheet: what the plan uses, what the limit is, and the slack between them.
| Rule | Used | Limit | Slack |
|---|---|---|---|
| Cowden capacity | 6,000 | 3,600 | −2,400 |
| Order total | 24,000 | 24,000 | 0 |
| Average defect | 1.19% | 1.20% | 0.01% |
Then point Solver at those same cells. The sheet and the dialog now say the same thing, and the slack column tells you which rule is doing the work.
A rule with slack to spare is a rule you could have ignored. A binding rule is the one worth arguing about, because it is the one costing you money — here, the 1.20% cap.
Scenario: Build the three columns beside your model, with =D2-E2 for the slack, and sort them smallest first with =SORT(H2:H5). Whatever lands at the top is the constraint to take to whoever set it.
05The Objective Cell Must Be a Formula
Solver reads one cell and changes others. If the cell it reads does not depend on the cells it changes, it will report a solution having moved nothing at all.
The objective here is one cell, live, and no cleverer than it needs to be:
=SUMPRODUCT(B2:B5,E2:E5)
Prices times quantities. The model underneath it is six cells wide:
| Cells | Hold |
|---|---|
| K2:K5 | pallets — the changing cells, whole numbers |
| E2:E5 | trays, =K2*300 |
| D2:D5 | capacity in trays, typed in each month |
| F14 | the objective, =SUMPRODUCT(B2:B5,E2:E5) |
| F15 | average defect, =SUMPRODUCT(C2:C5,E2:E5)/SUM(E2:E5) |
| F16 | the cost Solver signed off, pasted as a value |
Minimise F14, change K2:K5, subject to E2:E5 ≤ D2:D5, SUM(E2:E5) = 24,000 and F15 ≤ 1.20%. That is the whole model.
One more thing to check before you solve: Make Unconstrained Variables Non-Negative. It is ticked by default in current Excel, and an unticked box is where a supplier with a negative order quantity comes from.
Scenario: Find the objective cell in your own model and press F2. If it is a number rather than a formula, or a formula that never reaches the changing cells, that is your whole bug: rebuild it as a SUMPRODUCT over the two columns.
06Simplex LP, GRG Nonlinear and Evolutionary
Solver has three engines and defaults to the one that suits spreadsheets like this least. The method box sits under the constraints and nobody reads it twice.
| Method | Use it when | What you get back |
|---|---|---|
| Simplex LP | every formula is a sum or a multiple of the changing cells | the proven optimum |
| GRG Nonlinear | smooth curves: squares, rates, compound growth | a local answer that depends on where it started |
| Evolutionary | IF, ROUND, MIN, MAX or VLOOKUP between the cells and the objective | a decent answer, slowly, with nothing proved |
This model is linear. A cost is a price times a quantity and a capacity is a sum, so Simplex LP solves it in a second and tells you there is nothing better.
GRG gets there too, usually. It also gets somewhere else when the starting values change, which is how two people with the same workbook end up with two plans and no way to tell which is right.
A model full of IF and ROUND has no slope to follow. Lookups, bands and rounding make steps, and a gradient method stalls on a step — the same staircase that defeats Goal Seek.
07The Pallet Somebody Rounded by Hand
March's optimum was 21.25 pallets of Ashgrove. Trays come 300 to a pallet, nobody ships a quarter of one, and a buyer rounded it up to 22 and took one off Deeping to keep the total.
The sheet got £285 cheaper. It also broke the only rule the plan existed to meet.
| Plan | Ashgrove | Deeping | Cost | Average defect |
|---|---|---|---|---|
| Solver, fractional | 21.25 | 13.75 | £107,313.75 | 1.20% |
| Rounded by hand | 22 | 13 | £107,100.00 | 1.22% |
| Solver, whole pallets | 21 | 14 | £107,385.00 | 1.19% |
Rounding moved a pallet from the cleanest supplier to the dirtiest, which is the direction rounding towards the cheap column always moves it.
Rounding is a decision, so let the model make it. Put the pallet counts in the changing cells, derive the trays with =K2*300, and add one constraint: $K$2:$K$5 = int.
ROUND and CEILING still have a job here — =CEILING(6375/300,1) tells you what a fractional answer would cost in whole pallets — but they describe an answer rather than choose one.
Scenario: Take a model whose answer you rounded by hand, add the integer constraint to the changing cells, and solve again. Compare the two costs, then check your rounded version against the rule it was supposed to respect.
Try it in the grid08Integer Optimality Is Not Zero Until You Set It
Add int and Solver quietly changes its definition of finished. It stops when the best whole-number answer it has found is within a set percentage of the best it could still hope for.
That percentage is not zero. Options → Integer Optimality (%): current Excel ships it at 1, older versions at 5, and the box is three clicks from anywhere anyone looks.
On a £107,385 plan, 1% is about a thousand pounds of permission to stop early. You will never see it happen, because Solver still says it found a solution.
Set it to 0 on a model this size and the solve still takes a second. Raise it deliberately, or not at all — on a model with hundreds of integer cells, that gap is the difference between an answer and a frozen afternoon.
09"Solver Could Not Find a Feasible Solution" Is an Answer
Run April's real numbers and Solver does not hand back a plan. It hands back a sentence, and the sentence is the most useful thing on this page.
April's four contracts came to 25,200 trays against an order of 24,000, so 1,200 trays of capacity had to go unused. With Cowden down to 12 pallets, the cleanest supplier in the list was rationed.
Drop the four worst pallets available — Ashgrove's, at 2.4% — and the average defect rate still lands at 1.30%. The cap was 1.20%. There was no plan that met April's rules, and no formula on the sheet was ever going to say so.
When Solver refuses, do not start deleting constraints. Copy the model, make the binding rule the objective instead, and ask by how much it misses:
=SUMPRODUCT(C2:C5,E2:E5)/SUM(E2:E5)
Minimise that, keep the capacities and the order total, and Solver returns 1.30% at a cost of £109,176. That is £2,730 less than April actually cost, and it was available on the 1st.
Infeasible has a date on it. Knowing on the 1st that the cap could not be met is a phone call to the customer. Knowing on the 28th is an expedite charge.
Scenario: Make one of your own constraints impossible on purpose — halve a capacity — and run Solver. Read the message, then re-run it with that constraint as the objective and write down the number it cannot beat.
Try it in the grid10Keep Solver Solution, and the Baseline It Overwrites
The results dialog offers Keep Solver Solution or Restore Original Values, and the default is Keep. Click OK and the answer is now your input column.
That is fine when you meant it. It is not fine when you were trying a scenario, because the plan you had before is gone and the sheet looks exactly as deliberate as it did a minute ago.
The same dialog has a Save Scenario button. It writes the current changing cells into Scenario Manager under a name, and that is the cheapest insurance in the add-in.
Ctrl+Z does undo a solve, but only right then. Save, close, reopen, and the numbers you had before are not coming back without the scenario you did not save.
11Five Checks Above the Table
Five cells, in the row above the model, read before anybody reads the cost:
- Is the plan still the answer?
=IF(ROUND(F14-F16,2)<>0,"RE-SOLVE","solved on these inputs")— the live objective against the one Solver signed off. - Does anything breach its limit?
=SUM(F2:F5)over=MAX(0,E2-D2)must be 0. April's returned 2,400. - Does the order still add up?
=SUM(E2:E5)-F7, where F7 is the quantity ordered. Nought, or the plan is for a different month. - Does the quality rule hold?
=SUMPRODUCT(C2:C5,E2:E5)/SUM(E2:E5)against the cap. The hand-rounded plan returns 1.22% against 1.20%. - Are they whole pallets?
=SUMPRODUCT(--(MOD(E2:E5,300)<>0))counts the rows that are not a multiple of 300, and=COUNTIF(E2:E5,"<0")catches the negative quantity that an unticked box allows.
12When the Model Does Not Need Solver
If capacity is the only rule, cheapest first is the optimum and an add-in is overkill. Sort the suppliers by price and fill one formula down:
=MIN(D2,$F$7-SUM($E$1:E1))
Each row takes what it can up to its capacity, or whatever is left of the order, whichever is smaller. =SORT(A2:D5,2) puts them in price order first.
That is the plan Solver returns for a one-rule model, and on March's numbers it costs £104,820 — cheaper than Solver's £107,385, and 1.42% defective against a 1.20% cap.
Solver earns its keep the moment two rules pull against each other. One rule is a fill; two is a trade-off, and a trade-off is where the £2,565 between those two plans is being spent deliberately.
13Ten Traps
- Solver writes values. Nothing re-solves when an input changes.
- Constraints hold typed numbers happily. Use references, every time.
- An objective that is a constant lets Solver report success having moved nothing.
- GRG is the default. On a linear model, Simplex LP proves what GRG only finds.
- IF and VLOOKUP make steps. Gradient methods stall on them.
- Integer Optimality is not 0. It is 1% in current Excel, 5% in older ones.
- Rounding by hand breaks constraints. The model should carry
int. - Non-negative is a tick box. Untick it and quantities can go below zero.
- Keep Solver Solution overwrites your inputs. Save Scenario first.
- Infeasible is information. It is the only message that tells you the truth about the month.
14Mini Exercises
- Stale it on purpose. Solve the model, then raise one price by 10% and watch the objective move while the quantities do not.
- Harden the rules. Replace every typed number in the constraint list with a reference, change a capacity on the sheet, and confirm the next solve respects it.
- Round it badly. Take the fractional answer, round it by hand, and run check 4 above against the quality cap.
- Switch engines. Solve the same model with Simplex LP and with GRG from a zero start, and compare both answers and both run times.
- Break it beyond repair. Cut a capacity until Solver refuses, then re-solve with the quality rule as the objective and write down what April could actually have achieved.
What to Take Away
Nothing on this workbook was broken. The cost cell was a correct SUMPRODUCT, the capacity column held April's real numbers, and Solver had done exactly what it was asked in March.
What nobody had written down is that a Solver answer is a photograph of a model at one moment: these prices, these limits, this order. The sheet keeps the photograph and goes on recalculating around it.
Three cells would have said so. The live objective against the signed-off one, the sum of the capacity overruns, and the average defect rate against its cap.
Three cells, against £2,757 of expedite freight and a quality cap that could not have been met in April whatever anybody typed.