Page 259 - Excel Workbook for Dummies

26_798452 ch18.qxp 3/13/06 7:45 PM Page 242
242 Part V: Doing Data Analysis
When setting up the problem in your worksheet model to be solved by the Solver, you
define the following items:

Target cell, which is the cell in your worksheet whose value is to be maximized,
minimized, or made to reach a particular value
Changing cells, which are the cells in your worksheet whose values are to be
adjusted until the answer is found

Constraints, which are the limits you impose on the changing values or the
target cell

After you finish defining the problem with these parameters and you have the Solver
solve the problem, the program returns the optimum solution by modifying the
values in your worksheet. At that point, you can choose to retain the changes in the
worksheet or restore the original values to the worksheet. You can also save the solu-
tion as a scenario to view later before you restore the original values.

You can use the Solver with the Scenario Manager to help set up a problem to solve
or to save a solution so that you can view it at a later date. The changing cells that
you define for the Scenario Manager are automatically picked up and used by the
Solver when you select this command, and vice versa. Also, you can save the Solver’s
solution to a problem as a scenario (by clicking the Save Scenario button in the Solver
dialog box) that you can then view with the Scenario Manager.

Try It

Exercise 18-5: Using Solver to Modify Multiple Input Values in a Spreadsheet
Open the Exercise18-5.xls workbook file in your Chapter 18 folder in the My Practice
Spreadsheets folder on your hard disk or in the Excel Workbook folder on the work-
book CD-ROM. The workbook contains a copy of the Sales Forecast worksheet with
the Sales Forecast for 2007 table that you will use to practice using the Solver add-in
to reach a target income based on changes to multiple parameters:
1. Choose Tools➪Add-Ins and then select the Solver Add-In check box in the Add-
Ins dialog box before you select OK.
If the Solver add-in is not currently installed as part of Excel, the program dis-
plays an alert dialog box indicating that the feature is not currently installed
and prompting you to install it. Go ahead and install Solver by selecting the Yes
button in this alert dialog box. After Excel finishes installing the Solver add-in,
proceed to step 2.
2. Position the cell pointer in the Total_Income cell, G8, and then choose
Tools➪Solver to open the Solver Parameters dialog box.
This dialog box displays Target_Income in the Set Target Cell text box. Next, you
need to set the target equal to a new value and then indicate the changing cells.
3. Select the Value Of option button to the right of Equal To and then type 650000
in the text box to its immediate right.
4. Select the By Changing Cells text box and then click the COGS cell, H5, in the
worksheet and Ctrl+click the Expenses cell, H7.
The By Changing Cells text box now contains references to COGS and Expenses
separated by a comma.
5. Select the Solve button in the Solver Parameters dialog box.