Excel Solver

Excel Solver is an add-on that finds the optimal value of a target cell by changing a set of decision-variable cells, subject to constraints defined on other cells.

Installation

Solver ships with Excel but is not enabled by default.

• Open File > Options > Add-ins.
• At the bottom, set the Manage dropdown to Excel Add-ins and click Go.
• Check Solver Add-in and click OK.
• A Solver button appears in the Data tab, under the Analyze group.

On macOS, the path is Tools > Excel Add-ins instead of File > Options.

Setting up a Problem

Every problem is expressed on the worksheet before Solver is opened.

• Objective cell
  A single cell holding a formula for the objective function, in terms of the decision variable cells.
• Variable cells
  A range of empty or seeded cells representing the decision variables.
• Constraint cells
  Formulas whose values are compared against bounds using $\le$, $\ge$, or $=$.

In the Solver Parameters dialog:

• Set Objective references the objective cell, with Max, Min, or a target Value.
• By Changing Variable Cells references the decision variable range.
• Subject to the Constraints lists each constraint as a cell reference, an operator, and a bound.

Solving Methods

Solver offers 3 solving methods, chosen from the Select a Solving Method dropdown.

• Simplex LP
  Uses the simplex algorithm. Required when the objective and every constraint are linear.
• GRG Nonlinear
  Uses the generalized reduced gradient method. For smooth nonlinear problems.
• Evolutionary
  Uses a genetic algorithm. For non-smooth or discontinuous problems where GRG cannot compute gradients.

For a linear program, Simplex LP also produces sensitivity and limits reports from the results dialog, showing shadow prices and allowable ranges for the right-hand sides and objective coefficients.

When to Use

Solver is suited to problems small enough to lay out on a worksheet:

• Linear programs with a handful of decision variables and constraints.
• Integer and binary programs, by adding int or bin constraints on the variable cells.
• Transportation and assignment problems, modelled directly as a grid of supply and demand cells.
• Quick sensitivity checks on an already-solved model, without re-deriving the dual by hand.

Limitations

• The standard Solver caps a problem at 200 variable cells and a bounded number of constraints.
  Too small for large-scale industrial LPs. More than enough for this module.
• Simplex LP fails silently on genuinely nonlinear formulas.
  Reports the wrong optimum instead of an error if a non-linear formula is left in the objective or a constraint.
• GRG Nonlinear only finds a local optimum, since it relies on gradient information. The result depends on the initial values in the variable cells.
• Evolutionary gives no optimality guarantee.
  Reports the best solution found within its iteration or time limit, which may not be the global optimum.
• Results are cell values, not symbolic formulas. Re-deriving a general solution for a family of inputs requires re-running Solver for each input.
• Model logic is spread across worksheet formulas rather than a single readable statement, which makes large models harder to audit than a formulation in dedicated LP software.

Worked Example

A manufacturer makes tables and chairs. Profit per table is 40, and profit per chair is 30. Each table needs 2 hours of carpentry and 1 hour of finishing. Each chair needs 1 hour of carpentry and 2 hours of finishing. 40 hours of carpentry and 50 hours of finishing are available.

$x_1$ is the number of tables produced. $x_2$ is the number of chairs produced.

Subject to:

• $2x_1 + x_2 \le 40$
• $x_1 + 2x_2 \le 50$
• $x_1, x_2 \ge 0$

Coefficients are kept separate from the variables, so the same variable cells are reused by every SUMPRODUCT formula.

Worksheet layout:

B2:C2 are the decision variable cells. D4 is the objective cell, pairing the profit coefficients in B4:C4 with the variable cells. D6 and D7 are the constraint cells, pairing the carpentry and finishing coefficients with the same variable cells, compared against the hour limits in E6 and E7.

Solver Parameters:

• Set Objective references D4, set to Max.
• By Changing Variable Cells references B2:C2.
• Subject to the Constraints lists D6 <= E6 and D7 <= E7.
• Make Unconstrained Variables Non-Negative is checked.
• Select a Solving Method is set to Simplex LP.

Running Solver returns $x_1 = 10$, $x_2 = 20$, with D4 showing $z = 1000$. Both D6 and D7 equal E6 and E7 exactly, so both constraints bind at the optimum.