Goal Seek turns one dial until one cell hits one target. Real decisions are rarely that polite: split the marketing budget across four channels, within a total, minimum £2k each, to maximise return. Many dials, hard limits, and not “a” value but the best one. That’s optimisation, and Excel has shipped a genuine optimiser for decades: Solver — free, built in, and unknown to almost everyone who’d benefit.
(One-time setup: File → Options → Add-ins → Manage: Excel
Add-ins → Go → tick Solver Add-in. It appears at the right end
of the Data tab.)
The three-part sentence
Every Solver problem is one sentence with three clauses, and the dialog is just that sentence as a form:
- Objective — one formula cell to maximise, minimise, or hit: total return, total cost, total profit from the model you already built.
- Variable cells — the dials Solver may turn: the four channel budgets. (Goal Seek allowed one; Solver takes many.)
- Constraints — the rules of reality:
total ≤ 20000,each ≥ 2000, quantities as integers where half a lorry isn’t a thing.
Click Solve, and it searches the space of allowed combinations for the best objective — trying, adjusting, converging, at machine speed, through the same model your own what-ifs use.
What it’s honestly for
The everyday wins are humbler than the textbook ones, and better: allocation (budget across channels, hours across the studio’s own jobs, stock across depots); selection — the knapsack shape: binary yes/no variables choosing which projects fit a budget for maximum value; scheduling and mixing (shifts meeting rota rules, cheapest blend meeting a spec); and the personal-finance version — cheapest way to clear several debts given a fixed monthly sum, constraints being the minimum payments.
The prerequisite is the one this stage keeps re-teaching: Solver can only turn dials that exist. Objective as one formula, inputs as named cells, constraints as computable quantities — a model built the course’s way is Solver-ready by accident. A model with buried constants gives it nothing to hold.
Reading the answer like an adult
Three disciplines separate use from misuse. Check the exit message: “found a solution — all constraints satisfied” is a result; “could not find a feasible solution” means your rules contradict each other, which is a finding about reality worth having. Rerun from different starting values for nonlinear models — Solver finds a peak, and complex landscapes have several; same-answer-from-elsewhere builds trust, per the Goal Seek caveat grown up. And treat the optimum as advice, not scripture — it’s the best answer inside your model; if the recommended plan feels absurd, the model is missing a constraint the real world enforces, and saying which one is exactly the conversation the checks row would want you to have.
(Choosing the engine, briefly: Simplex LP for models that are sums-of-things — fastest and provably optimal; GRG Nonlinear for smooth curves; Evolutionary as the patient last resort. Start with Simplex; escalate only when it refuses.)
One objective, some dials, the rules of reality. Most people solve allocation problems by argument and habit — you now own the third method, and it shows its working.