Solver is part of a suite of commands sometimes called what-if analysis (what-if analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments.) tools. With Solver, you can find an optimal value for a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) in one cell — called the target cell — on a worksheet. Solver works with a group of cells that are related, either directly or indirectly, to the formula in the target cell. Solver adjusts the values in the changing cells you specify — called the adjustable cells — to produce the result you specify from the target cell formula. You can apply constraints (constraints: The limitations placed on a Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are directly or indirectly related to the target cell.) to restrict the values Solver can use in the model, and the constraints can refer to other cells that affect the target cell formula.
Use Solver to determine the maximum or minimum value of one cell by changing other cells — for example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount.
Example of a Solver evaluation
In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (cells B5:C5), up to a total budget constraint of $20,000 (cell F5), until the value for total profit reaches the maximum possible amount. The values in the adjustable cells are used to calculate the profit for each quarter, so they are related to the formula in target cell F7, =SUM(Q1 Profit:Q2 Profit).
After Solver runs, the new values are as follows:
Solver sample worksheets
Microsoft Excel includes a workbook, Solvsamp.xls in the Office\Samples folder, that demonstrates the types of problems you can solve.
You can use the sample worksheets in Solvsamp.xls to help you set up your problems. To use any of the six worksheets — Product Mix, Shipping Routes, Staff Scheduling, Maximizing Income, Portfolio of Securities, and Engineering Design — open the workbook, switch to the worksheet you want to use, and then click Solver on the Tools menu. The target cell, adjustable cells, and constraints for the worksheet are already specified.
Algorithm and methods used by Solver
The Microsoft Excel Solver tool uses the Generalized Reduced Gradient (GRG2) nonlinear optimization code developed by Leon Lasdon, University of Texas at Austin, and Allan Waren, Cleveland State University.
Linear and integer problems use the simplex method with bounds on the variables, and the branch-and-bound method, implemented by John Watson and Dan Fylstra, Frontline Systems, Inc. For more information on the internal solution process used by Solver, contact:
Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
Web site: http://www.frontsys.com
Electronic mail: firstname.lastname@example.org
Portions of the Microsoft Excel Solver program code are copyright 1990, 1991, 1992, and 1995 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.