11. Solver: How to Set Up the Problem

Goal Seek changes one cell to reach one value. Solver is smarter: it changes several cells at once to maximize or minimize a result while respecting constraints. Typical problems: a production plan, cutting stock, shift schedules, an advertising budget split, an optimal purchase.

Where to find it: enable the add-in once — File › Options › Add-ins › Manage: Excel Add-ins › Go, and check "Solver Add-in". After that the Solver button appears on the Data tab.

Every Solver problem has three parts:

• The objective cell — the formula you optimize: profit (maximize), cost (minimize), or an exact value;
• The variable cells — the numbers Solver adjusts: how many of each product to make. They must hold numbers, not formulas;
• Constraints — conditions such as "used ≤ available", "plan ≤ demand", "plan ≥ 0", "plan is an integer" (int).

Solving methods: Simplex LP is for linear models, where the objective and constraints are sums of products (SUMPRODUCT); it is fast and finds the exact optimum. GRG Nonlinear is for smooth nonlinear problems. Evolutionary is for "jagged" models with IF, VLOOKUP and the like; it is slow and does not guarantee the best answer.

You should almost always check "Make Unconstrained Variables Non-Negative": you cannot bake −5 croissants.

An honest note about this trainer: it has no built-in Solver. In the next lesson you will build the model the way it is prepared for Solver (objective, constraints, "OK/Violated" checks), and find the optimal plan yourself by reasoning and testing. In real Excel the "Solve" button does that last step for you.