Amal Nagm

the Solver Tool can be used with Microsoft Office Excel 2007 to perform what-if analysis. One of the most important benefits of spreadsheet software is that it performs a what-if analysis quickly and easily. Change key variables, and instantly see the effect. For example, if you're using Excel 2007 to decide whether to lease or purchase a car, you can test your financial model with different assumptions about interest rates and down payments, and you can see the effects of varying rates on bottom-line costs you will pay, such as the total interest.

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 Pub1 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 1 shows one way to lay out the problem.

Note This article merely introduces the Solver. A complete treatment of this powerful tool is beyond the scope of this article. For more details, 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 2. 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 1).

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 Minas 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.

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.

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 3 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 4 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.

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 5. 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.

Specifying integer constraints

Notice that in Figure 5, 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 6. Click OK to return to the Solver Parameters dialog box.

When the Solver succeeds, it presents the Solver Results dialog box.

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.