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:
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.
CLOSE the Ad Buy workbook. |