Using the Solver
The Goal Seek command is handy for problems that involve an exact target value that depends on a single unknown value. For problems that are more complex, you should use the Solver add-in. The Solver can handle problems that involve many variable cells and can help you find combinations of variables that maximize or minimize a target cell. It also specifies one or more constraints-conditions that must be met for the solution to be valid.
| Note | The Solver is an add-in. If the Solver button does not appear on the Data tab on the Ribbon, click the Microsoft Office Button, Excel Options, Add-Ins category, and then click the Go button. Then select the Solver Add-In check box, and click OK to install it. Click Yes to confirm that you want to install the Solver add-in. |
As an example of the kind of problem that the Solver can tackle, imagine you are planning an advertising campaign for a new product. Your total budget for print advertising is $12,000,000; you want to expose your ads at least 800 million times to potential readers; and you've decided to place ads in six publications-we'll call them Publ through Pub6. Each publication reaches a different number of readers and charges a different rate per page. Your job is to reach the readership target at the lowest possible cost with the following additional constraints:
-
At least six advertisements should run in each publication.
-
No more than a third of your advertising dollars should be spent on any one publication.
-
Your total cost for placing advertisements in Pub3 and Pub4 must not exceed $7,500,000.
Figure 18-23 shows one way to lay out the problem.
Figure 18-23: You can use the Solver to determine how many advertisements to place in each publication to meet your objectives at the lowest possible cost.
On the CD You can find the 
Advertising.xlsx file in the Sample Files section of the companion CD.
| Note | This section merely introduces the Solver. A complete treatment of this powerful tool is beyond the scope of this book. For more details, including an explanation of the Solver error messages, see the online Help system. For background material about optimization, we recommend Financial Models Using Simulation and Optimization II: Investment by Wayne L. Winston (Palisade Corporation, 2001). |
You might be able to work out this problem yourself by substituting many alternatives for the values currently in D2:D7, keeping your eye on the constraints, and noting the impact of your changes on the total expenditure figure in E8. In fact, that's what the Solver does for you-but it does it more rapidly, and it uses some analytic techniques to home in on the optimal solution without having to try every conceivable alternative.
Click the Solver button on the Data tab to display the dialog box shown in Figure 18-24. To complete this dialog box, you must give the Solver three sets of information: your objective, or target (minimizing total expenditure); your variables, or changing cells (the number of advertisements you will place in each publication); and your constraints (the conditions summarized at the bottom of the worksheet in Figure 18-23).
Figure 18-24: Use the Solver Parameters dialog box to set up your problem.
Stating the Objective
In the Set Target Cell box, you indicate the goal, or target, that you want Solver to achieve. In this example, you want to minimize your total cost-the value in cell E8-so you specify your objective by typing E8 in the Set Target Cell box (or by clicking the cell). In this example, because you want the Solver to set your target cell to its lowest possible value, you select Min as the Equal To option.
| Note | It's a good idea to name all the important cells of your model before you put the Solver to work. If you don't name the cells, the Solver reports construct names based on the nearest column-heading and row-heading text, but these constructed names don't appear in the Solver dialog boxes. For more information, see "Naming Cells and Cell Ranges" on page 441. |
You don't have to specify an objective. If you leave the Set Target Cell box blank, then click Options, and finally select the Show Iteration Results check box, you can use the Solver to step through some or all the combinations of variable cells that meet your constraints. You will then receive an answer that solves the constraints but isn't necessarily the optimal solution.
For more information about the Show Iteration Results option, see "Viewing Iteration Results" on page 609.
Specifying Variable Cells
The next step is to tell the Solver which cells to change. In our example, the cells whose values can be adjusted are those that specify the number of advertisements to be placed in each publication, or cells D2:D7. Alternatively, you can click Guess, and the Solver proposes the appropriate changing cells based on the target cell you specified.
Specifying Constraints
The last step, specifying constraints, is optional. To specify a constraint, click Add in the Solver Parameters dialog box, and complete the Add Constraint dialog box. Figure 18-25 shows how you express the constraint that total advertising expenditures (the value in cell E8 in the model) must be less than or equal to the total budget (the value in cell G11).
Figure 18-25: Click Add in the Solver Parameters dialog box to add constraints.
Figure 18-26 shows how the Solver Parameters dialog box looks after we have specified all our constraints. Notice that the constraints are listed in alphabetical order, not necessarily in the order in which we defined them.
Figure 18-26: The Solver lists the constraints in alphabetical order and uses defined cell and range names whenever possible.
Notice also that two of the constraints have range references on the left side of the comparison operator. The expression $D$2:$D$7>=$G$15 stipulates that the value of each cell in D2:D7 must be 6 or greater, and the expression $F$2:$F$7<=$G$14 stipulates that the value of each cell in F2:F9 must be no greater than 33.30 percent. Each of these expressions is a shortcut way of stating six separate constraints. If you use this kind of shortcut, the constraint value on the right side of the comparison operator must be a single cell reference, a range of the same dimensions as the range on the left side, or a constant value.
After completing the Solver Parameters dialog box, click Solve. In the advertisement campaign example, the Solver succeeds in finding an optimal value for the objective cell while meeting all the constraints and displays the dialog box shown in Figure 18-27. The values displayed on your worksheet at that time result in the optimal solution. You can leave these values in the worksheet by selecting the Keep Solver Solution option and clicking OK, or you can restore the original values by selecting the Restore Original Values option and clicking OK (or by clicking Cancel). You also have the option of assigning the solution values to a named scenario.
Figure 18-27: When the Solver succeeds, it presents the Solver Results dialog box.
Specifying Integer Constraints
Notice that in Figure 18-27, the Solver arrived at 53.3 for the number of ads placed in Pub4. Unfortunately, because it's not possible to run three-tenths of an advertisement, the solution isn't practical.
To stipulate that your ad-placement variables be restricted to whole numbers, start the Solver, and click the Add button in the Solver Parameters dialog box. In the Add Constraint dialog box, you select the cell reference that holds your ad placement numbers- D2:D7. Click the list in the middle of the dialog box, and select int. The Solver inserts the word integer in the Constraint box, as shown in Figure 18-28. Click OK to return to the Solver Parameters dialog box.
Figure 18-28: To specify an integer constraint, select the item labeled int.
Note that when converting numbers to integers, Excel effectively rounds down; the decimal portion of the number is truncated. The integer solution shows that by placing 53 ads in Pub4, you can buy an additional ad in Pub5. For a very small increase in budget, you can reach an additional two million readers.
 |
Adding integer constraints to a Solver problem can geometrically increase the problem's complexity, resulting in possibly unacceptable delays. The example discussed in this chapter is relatively simple and does not take an inordinate amount of time to solve, but a more complex problem with integer constraints might pose more of a challenge for the Solver. The Solver can solve certain problems only by using integer constraints. In particular, integer solutions are useful for problems in which variables can assume only two values, such as 1 or 0 (yes or no), but if you're looking for "yes or no" results, you can also use the bin (binary) option in the list in the middle of the Change Constraint dialog box.
|
Saving and Reusing the Solver Parameters
If you save a workbook after using the Solver, Excel saves all the values you typed in the Solver dialog boxes along with your worksheet data. You do not need to retype the parameters of the problem if you want to continue working with it during a later Excel session.
Each worksheet in a workbook can store one set of Solver parameter values. To store more than one set of Solver parameters with a given worksheet, you must use the Save Model option. To use this option, follow these steps:
-
Click the Solver button on the Data tab.
-
Click the Options button, and then in the Solver Options dialog box, shown in Figure 18-29, click Save Model. Excel prompts you for a cell or range in which to store the Solver parameters on the worksheet.
-
Specify a blank cell by clicking it or typing its reference, and then click OK. The Solver pastes the model beginning at the indicated cell and inserting formulas in as many of the cells below it as necessary. (Be sure that the cells below the indicated cell do not contain data.)
-
To reuse the saved parameters, click Load Model in the Solver Parameters dialog box, and then specify the range in which you stored the Solver parameters.
Figure 18-29: Load Model and Save Model in the Solver Options dialog box provide a way to store and retrieve your Solver parameters.
You'll find it easiest to save and reuse Solver parameters if you assign a name to each save model range immediately after you use the Save Model option. You can then specify that name when you use the Load Model option.
For more information about naming, see "Naming Cells and Cell Ranges" on page 441.
Assigning the Solver Results to Named Scenarios
An even better way to save your Solver parameters is to save them as named scenarios using the Scenario Manager. As you might have noticed, the Solver Results dialog box includes a Save Scenario button. Click this button to assign a scenario name to the current values of your variable cells. This option provides an excellent way to explore and perform further what-if analysis on a variety of possible outcomes.
For more information about scenarios, see "Using the Scenario Manager" on page 588.
Other Solver Options
The Solver Options dialog box contains several options that might need some explanation:
-
The Max Time and Iterations boxes tell the Solver, in effect, how hard to work on the solution. If the Solver reaches either the time limit or the number of iterations limit before finding a solution, calculation stops, and Excel asks you whether you want to continue. The default settings are usually sufficient for solving most problems, but if you don't reach a solution with these settings, you can try adjusting them.
-
The Solver uses the Precision setting to determine how closely you want values in the constraint cells to match your constraints. The closer this setting is to the value 1, the lower the precision is. If you specify a setting that is less than the default 0.000001, it results in a longer solution time.
-
The Tolerance setting applies only to problems that use integer constraints and represents a percentage of error allowed in the solution.
-
It's best to leave the Estimates, Derivatives, and Search options at their default settings, unless you understand linear optimization techniques. If you want more information about these options, click Help in the Solver Options dialog box.
Linear Models
A linear optimization problem is one in which the value of the target cell is a linear function of each variable cell; that is, if you plot XY (scatter) charts of the target cell's value against all meaningful values of each variable cell, your charts are straight lines. If some of your plots produce curves instead of straight lines, the problem is nonlinear.
You can turn on the Assume Linear Model option only for what-if models in which all the relationships are linear. Models that use simple addition and subtraction and worksheet functions, such as SUM, are linear in nature. However, most models are nonlinear. They are generated by multiplying changing cells by other changing cells, by using exponentiation or growth factors, or by using nonlinear worksheet functions, such as PMT.
The Solver can solve linear problems more quickly if you click Options in the Solver Parameters dialog box and then select the Assume Linear Model option. If you select this option for a nonlinear problem and then try to solve the problem, however, the Solver Results dialog box displays the message "The conditions for Assume Linear Model are not satisfied." If you are not sure about the nature of your model, it's best not to use this option.
|
If your problem is nonlinear, you must be aware of one important detail: Your choice of starting values can affect the solution generated by the Solver. With nonlinear problems, you should always do the following:
-
Set your variable cells to reasonable approximations of their optimal values before running the problem.
-
Test alternative starting values to see what impact, if any, they have on the Solver solution.
|
Viewing Iteration Results
If you're interested in exploring many combinations of your variable cells, rather than only the combination that produces the optimal result, you can take advantage of the Show Iteration Results check box in the Solver. Click Options in the Solver Parameters dialog box, and select the Show Iteration Results check box in the Solver Options dialog box. After each iteration, the Show Trial Solution dialog box appears, which allows you to save the scenario and then either stop the trial or continue with the next iteration.
You should be aware that if you use Show Iteration Results, the Solver pauses for solutions that do not meet all your constraints as well as for suboptimal solutions that do.
Generating Reports
In addition to inserting optimal values in your problem's variable cells, the Solver can summarize its results in three reports: Sensitivity, Answer, and Limits. To generate one or more reports, select the names of the reports in the Solver Results dialog box. Select the reports you want, and then click OK. Each report is saved on a separate worksheet in the current workbook.
The Sensitivity Report
The Sensitivity report provides information about how sensitive your target cell is to changes in your constraints. This report has two sections: one for your variable cells and one for your constraints. The right column in each section provides the sensitivity information.
Each changing cell and constraint cell appears in a separate row. The Changing Cell area includes a Reduced Gradient value that indicates how the target cell would be affected by a one-unit increase in the corresponding changing cell. Similarly, the Lagrange Multiplier column in the Constraints area indicates how the target cell would be affected by a one-unit increase in the corresponding constraint value.
The Answer Report
The Answer report lists the target cell, the variable cells, and the constraints. This report also includes information about the status of and slack value for each constraint. The status can be Binding, Not Binding, or Not Satisfied. The slack value is the difference between the solution value of the constraint cells and the number that appears on the right side of the constraint formula. A binding constraint is one for which the slack value is 0. A nonbinding constraint is a constraint that was satisfied with a nonzero slack value.
| Note | If you select the Assume Linear Model option in the Solver Options dialog box, the Answer report is the only report that the Solver produces for you (the Limits and Sensitivity reports are not meaningful when using integer constraints). |
The Limits Report
The Limits report tells you how much you can increase or decrease the values of your variable cells without breaking the constraints of your problem. For each variable cell, this report lists the optimal value as well as the lowest and highest values that you can use without violating constraints.
| TROUBLESHOOTING | The Solver can't solve my problem. The Solver is powerful but not miraculous. It might not be able to solve every problem you give it. If the Solver can't find the optimal solution to your problem, it presents an unsuccessful completion message in the Solver Results dialog box. The most common unsuccessful completion messages are the following:
|
EAN: 2147483647
Pages: 260