Page 258 - Excel Workbook for Dummies

26_798452 ch18.qxp 3/13/06 7:45 PM Page 241
Chapter 18: Performing What-If Analysis 241

5. Click the Undo button on the Standard toolbar or press Ctrl+Z to restore the orig-
inal values to the Sales Forecast table and Clustered Bar chart.
6. Click the Redo button on the Standard toolbar or press Ctrl+Y to return to the
values entered as the result of your goal seeking.
7. Click somewhere on the embedded Clustered Bar chart in the Sales Forecast
worksheet to select it and display the Chart toolbar in the Excel window.
8. Select Series “Income” on the Chart Objects drop-down list box on the Chart
toolbar.
Excel selects the bars representing the projected income for the four quarters in
the Clustered Bar chart.
9. Hold down the Ctrl key as you click income bar for Qtr 1 at the bottom of the
embedded chart.
Holding down the Ctrl key as you click ensures that you select only the Qtr 1
income bar in the chart (and not all the bars representing the Income data
series).
10. Release the Ctrl key and then position the mouse pointer at the end of the Qtr 1
income bar. When the pointer changes to a double-headed arrow, drag this bar
until it reaches the $350,000 tick mark in the chart (indicated by a reading of
350000 from the ToolTip).
The moment you release the mouse button after dragging the Qtr 1 income bar
to $350,000 tick mark in the Chart, the Goal Seek dialog box appears, this time
with values in both the Set Cell and To Value text boxes.

11. Click cell C4 with the Qtr 1 Sales in the worksheet to enter its absolute cell refer-
ence in the By Changing To text box and then select OK.
The Goal Seek Status dialog box replaces the Goal Seek dialog box, indicating
that a solution has been found in cell C8.
12. Select OK to close the Goal Seek Status dialog box, and then select cell A1. Save
your work in a new workbook file named Solved18-4.xls in your Chapter 18
folder in the My Practice Spreadsheets folder and then close the workbook file.

Creating Complex Models with Solver

Although the Data Table and Goal Seek commands work just fine for simple problems
that require determining the direct relationship between the inputs and results in a
formula, you need to use the Solver add-in when dealing with more complex prob-
lems. For example, you would use the Solver to find the best solution when you need
to change multiple input values in your model and you need to impose constraints on
these values or the output value.

The Solver works by applying iterative methods to find the “best” solution given the
inputs, desired solution, and the constraints that you impose. During each iteration,
the program applies a trial-and-error method (based on the use of linear or nonlinear
equations and inequalities) that attempts to get closer to the optimum solution.
When using the Solver, you need to keep in mind that for many problems, especially
the more complicated ones, there are many solutions. Although the Solver returns the
optimum solution, given the starting values, the variables that can change, and the
constraints you define, this solution is often not the only one possible and, in fact,
may not be the best solution for you. To be sure that you are finding the best solu-
tion, you may want to run the Solver more than once, adjusting the initial values each
time you solve the problem.