Goal Seek finds an input that hits a target. Solver finds the inputs that make an output as large or small as possible, subject to constraints, and it can move many cells at once. Think of it as Goal Seek's grown-up sibling and your way into optimisation. The archetypal business use is resource allocation under a linear program (LP). You have products with different per-unit contributions and a shared, limited resource (machine hours, labour, materials). What mix maximises total contribution without breaking any capacity limit?
How do you set up a linear program in Solver?
Formulating an LP. An LP has three parts, and naming them cleanly is 80% of the skill:
- Decision variables are the cells Solver may change, here the number of units of each product. Solver calls them "By Changing Variable Cells".
- Objective is the single cell to maximise (or minimise), which here is total contribution
= Σ (unit contribution × units). In Solver it's "Set Objective", with Max selected. - Constraints are the limits. Each shared resource gets
Σ (resource used per unit × units) ≤ capacity. Add non-negativity,units ≥ 0, and integer constraints if units must be whole.
The objective and the constraints are all linear in the decision variables, so this is a linear program, and you must choose the Simplex LP engine in Solver. It's exact and fast for linear models, while the generic GRG Nonlinear engine can stall or find a local answer.
Advertisement
Why does the best answer sit at a corner?
Geometry explains why the answer ends up at a corner. The constraints carve out a feasible region, a polygon of every allowable mix. A linear objective's contours are straight, parallel lines. Push them as far as you can in the "more profit" direction and the last point of contact with the polygon is always a vertex (or, in a tie, an edge). So the optimal mix is at a corner of the feasible region, where the binding constraints intersect.
That gives LP optima a characteristic flavour. Some resources are used to the last unit, and those are the binding constraints. Others have slack. Knowing which constraints bind tells you where to invest next. Relax a binding constraint by buying more of that resource and profit rises. Relax a slack one and nothing happens.
How do you turn on and run Solver?
Solver is an add-in and it's off by default. You only have to enable it once. Go to File ▸ Options ▸ Add-ins. At the bottom, next to Manage:, choose Excel Add-ins ▸ Go…, then tick Solver Add-in ▸ OK. It now appears under Data ▸ Analyze group ▸ Solver. If it isn't there, check this first. A "wrong constraint" bug is often really a "Solver was never enabled" bug, which is common mistake 4.
Open Solver, set the objective cell and Max, and select the decision-variable cells. Then Add each constraint. The left side is a formula of the variables, the sense is <=, = or >=, and the right side is the capacity. Tick Make Unconstrained Variables Non-Negative, choose Simplex LP, and Solve. Solver reports the optimal variable values and offers Answer and Sensitivity reports. The Sensitivity report gives the shadow prices, the profit gained per extra unit of each binding resource. Worked example 2 builds and checks one from start to finish.