![[Home]](images/bhome.gif){kind=small}
![[What's New]](images/bnews.gif){kind=small}
![[Products & Services]](images/bprdsrv.gif){kind=small}
![[Contents]](images/btoc.gif){kind=small}
![[Feedback]](images/bfeed.gif){kind=small}
![[Search]](images/bsrch.gif){kind=small}
![[Dividing Line Image]](div.gif){kind=small}
This page introduces some of the principles upon which the Solver is built: The types of problems that can be solved; typical applications of the Solver; how to construct Solver models; and practical hints for using the Solver effectively.
For an alternative, introductory tutorial that takes you step by step through the process of solving a sample model with the Microsoft Excel Solver, try the University of British Columbia's MBA program online tutorial for the Solver.
TO LEARN MORE, PLEASE CONSULT OUR PAGE ON BOOKS, SEMINARS, AND WEB RESOURCES.
The Solver can be used for both equation-solving (often called goalseeking or backsolving) and constrained optimization (using linear programming, nonlinear programming, and integer programming methods).
Equation-Solving. In the normal "what-if" operation of a spreadsheet, you enter or change input values, and the spreadsheet calculates the output values of various formulas which depend on your inputs. The Solver can be thought of as performing "what-if in reverse": You specify the output values, or ranges of values that you would like certain formulas to have, and the Solver determines the input values which calculate the output values you want.
When you ask the Solver to find the input value which results in a specific formula output value, you are solving an equation for an unknown (the input value). This is also called goalseeking or backsolving. You learned to do this algebraically in school; the Solver uses numerical methods, but the results are the same. The Solver can solve a set of simultaneous equations for several unknowns at once. The unknowns are spreadsheet input cells (often called adjustable cells) and the equations have the form A1 = B1 where A1 and B1 contain formulas involving the unknowns.
Constrained Optimization. You can also ask the Solver to find input values which satisfy of set of simultaneous equations and inequalities (involving <= or >=). When you do this, there is usually more than one satisfactory set of input values. So the Solver can find the "best" set of input values which maximizes or minimizes some other calculated formula that you specify. This is called constrained optimization; the equations or inequalities (you can use both) are called constraints.
The Solver can also simply maximize or minimize a formula without any constraints. For example, if you ask the Solver to find A1 such that (A1/2-1)^2 is minimized, the Solver will find A1=2, where the formula has the value 0.
The input values to be found by the Solver, which are called adjustable cells or decision variables, are simply cells containing numbers on your spreadsheet. The constraints each consist of a cell which calculates a formula, a relation (=, <= or >=), and another cell which calculates a formula. And the function to be maximized or minimized, called the objective function, is just another cell containing a formula.
When would you want to use the capabilities of the Solver? There are a variety of scientific and engineering applications where equations must be solved; a simple example is shown in the Engineering Design sheet of the SOLVSAMP.XLS workbook included with Microsoft Excel and the SOLVSAMP.123 workbook included with Lotus 1-2-3 97, where the value of a resistor in an electrical circuit is found.
But most often, applications of the Solver involve the idea of resource allocation: You have a set of productive resources -- which might be raw materials, people, money or something else -- which can be used in a variety of different places, times or ways. The problem is to determine the best way to use the resources, where "best" usually implies maximizing profits, minimizing costs, maximizing quality or minimizing risk of failure, and so on.
Each of the five other sheets in the SOLVSAMP workbooks illustrate this idea of resource allocation: In the Product Mix example, the resources are parts in inventory, which can be used to build several different products. In the Shipping Routes example, the resources are products which can be shipped by many different routes to destination warehouses. In the Staff Scheduling example, the resources are employees who can be assigned to different work shifts. And in the Maximizing Income and Portfolio of Securities examples, the resources are amounts of money, which can be invested in either certificates of deposit or stocks.
In these examples, the decision variables or adjustable cells represent different places, times or ways in which some amount of the resources can be used. The objective function is some measure of profits, costs, etc. which can be calculated based on the variable values. And the constraints describe various minimum requirements to be met (e.g. so many products are needed at each warehouse), policies (e.g. a certain maximum portfolio variance or riskiness is desired), or physical limits (e.g. we have only so many parts of each type on hand).
The Product Mix example (43K) will take you step-by-step through the process of using the Solver:
For further hints on how to effectively use the Solver, see:
Although the Solver can handle a wide range of constrained optimization problems, some are much harder to solve than others! The mathematical relationships between the decision variables and the objective function and constraints -- created by the formulas in your model -- determine whether a solution can be found, and how long it will take.
Many optimization problems can be expressed with relatively simple formulas which are linear functions of the decision variables, creating a linear programming problem which can be solved most quickly and reliably. At the other extreme, some spreadsheet models involve discontinuous functions of the variables, creating a problem which the Solver may not be able to solve at all. These concepts are discussed in the following pages:
