Microsoft Office Tutorials and References
In Depth Information
Finding Optimal Solutions by Using Solver
Goal Seek is a great tool for finding out how much you need to change a single input
value to generate a desired result from a formula, but it’s of no help if you want to find
the best mix of several input values. 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 reach the greatest
number of readers. He asked the publishers for ad pricing and readership numbers, which
he recorded in a spreadsheet, along with the minimum number of ads per publication
(three) 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 costing the company 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 on your computer,
you see the Solver button on the Data tab in the Analysis group. If not, you need to
install the Solver Add-In. To do so, click the File tab, and then click Options. In the Excel
Options dialog box, click Add-Ins to display the Add-Ins page. At the bottom of the
dialog box, in the Manage list, 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.