Finding Optimal Solutions with Solver


Goal Seek is a great tool for finding out how much you need to change a single input to generate a desired result from a formula, but it's of no help if you want to find the best mix of several inputs. For example, marketing vice president Craig Dewar might want to advertise in four national magazines to drive customers to Consolidated Messenger's Web site, but he might not know the best mix of ads to place among the publications. He asked the publishers for ad pricing and readership numbers, which are reflected in the spreadsheet shown as follows, along with the minimum number of ads per publication (3) and the minimum number of times he wants the ad to be seen (10,000,000). Because one of the magazines has a high percentage of corporate executive readers, Craig does want to take out at least four ads in that publication despite its relatively low readership. The goal of the ad campaign is for the ads to be seen as many times as possible without spending more than the $3,000,000 budget.

Tip

It helps to spell out every aspect of your problem so that you can identify the cells you want Solver to use in its calculations.


If you performed a complete installation when you installed Excel 2007 on your system, you see the Solver item on the Data tab in the Analysis group. If not, you need to install the Solver Add-In. To do so, click the Microsoft Office Button and then click Excel Options. In the Excel Options dialog box, click Add-Ins to display the Add-Ins page. At the bottom of the dialog box, click the Manage field down arrow, click Excel Add-Ins, and then click Go to display the Add-Ins dialog box. Select the Solver Add-in check box and click OK to install Solver.

Note

You might be prompted for the Microsoft Office system installation CD-ROM. If so, put the CD-ROM in your CD-ROM drive and click OK.


After the installation is complete, Solver appears on the Add-Ins tab in the Menu Commands group. Click Solver to display the Solver Parameters dialog box.

The first step of setting up your Solver problem is to identify the cell that reflects the results of changing the other cells in the worksheet. To identify that cell, click in the Set Target Cell box, click the target cell, and then select the option button representing whether you want to minimize the cell's value, maximize the cell's value, or make the cell take on a specific value. Next you click in the By Changing Cells box and select the cells Solver should vary to change the value in the target cell. Finally, you set the limits for the values Solver can use by clicking Add to display the Add Constraint dialog box.

You add constraints to the Solver problem by selecting the cells to which you want to apply the constraint, selecting the comparison operation (less than or equal to, greater than or equal to, requiring the value to be an integer, and so on) by clicking the down arrow in the middle box, clicking in the Constraint box, and selecting the cell with the value of the constraint. You could also type a value in the Constraint box, but referring to a cell makes it possible for you to change the constraint without opening Solver.

Note

After you run Solver, you can use the controls in the Solver Results, save the results as changes to your worksheet, or create a scenario based on the changed data.


In this exercise, you use Solver to determine the best mix of ads given the following constraints:

  • You want to maximize the number of people who see the ads.

  • You must buy at least eight ads in three magazines and at least ten in the fourth.

  • You can't buy part of an ad (that is, all numbers must be integers).

  • You can buy no more than 20 ads in any one magazine.

  • You must reach at least 10,000,000 people.

  • Your ad budget is $3,000,000.

USE the Ad Buy workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Alternatives folder.

OPEN the Ad Buy workbook.


1.

If Solver doesn't appear on the Add-Ins tab, in the Menu Commands group, click the Microsoft Office Button and then click Excel Options.

The Excel Options dialog box appears.

2.

Click Add-Ins.

The Add-Ins page of the dialog box appears.

3.

Click the Manage field down arrow and then click Excel Add-Ins.

4.

Click Go.

The Add-Ins tab appears.

5.

Select the Solver Add-In check box and then click OK.

6.

On the Add-Ins tab, in the Menu Commands group, click Solver.

The Solver Parameters dialog box appears.

7.

Click in the Set Target Cell box and then click cell G9.

$G$9 appears in the Set Target Cell field.

8.

Select the Max option button.

9.

Click in the By Changing Cells field and select cells E5:E8.

$E$5:$E$8 appears in the By Changing Cells field.

10.

Click Add.

The Add Constraint dialog box appears.

11.

Select cells E5:E8.

$E$5:$E$8 appears in the Cell Reference field.

12.

Click the down arrow in the middle field and select int.

13.

Click Add.

Excel 2007 adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

14.

Click cell F9.

$F$9 appears in the Cell Reference field.

15.

Click in the Constraint field and then click cell G11.

$G$11 appears in the Constraint field.

16.

Click Add.

Excel 2007 adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

17.

Click cell G9.

$G$9 appears in the Cell Reference field.

18.

Click the down arrow in the middle field and select =.

19.

Click in the Constraint field and then click cell G12.

$G$12 appears in the Constraint field.

20.

Click Add.

Excel 2007 adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

21.

Select cells E5:E7.

$E$5:$E$7 appears in the Cell Reference field.

22.

Click the down arrow in the middle field and select =.

23.

Click in the Constraint field and then click cell G13.

$G$13 appears in the Constraint field.

24.

Click Add.

Excel 2007 adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

25.

Click cell E8.

$E$8 appears in the Cell Reference field.

26.

Click the down arrow in the middle field and select =.

27.

Click in the Constraint field and then click cell G14.

$G$14 appears in the Constraint field.

28.

Click Add.

Excel 2007 adds the constraint to the Solver problem, and the Add Constraint dialog box clears to accept the next constraint.

29.

Select cells E5:E8.

$E$5:$E$8 appears in the Cell Reference field.

30.

Click in the Constraint field and then click cell G15.

$G$15 appears in the Constraint field.

31.

Click OK.

Excel 2007 adds the constraint to the Solver problem, and the Solver Parameters dialog box reappears.

32.

Click Solve.

The Solver Results dialog box appears, indicating that Solver found a solution. The result is displayed in the body of the worksheet.

33.

Click Cancel.

The Solver Results dialog box disappears.

34.

Click Close. If you are asked if you want to save your changes, click No.

The Solver dialog box disappears.

CLOSE the Ad Buy workbook.





MicrosoftR Office ExcelR 2007 Step by Step
MicrosoftВ® Office ExcelВ® 2007 Step by Step (Step By Step (Microsoft))
ISBN: 073562304X
EAN: 2147483647
Year: 2004
Pages: 143
Authors: Curtis Frye

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net