Page 257 - Excel Workbook for Dummies

26_798452 ch18.qxp 3/13/06 7:45 PM Page 240
240 Part V: Doing Data Analysis

15. Move the Scenario Summary worksheet so that it follows the Sales Forecast
worksheet, and then click cell A1 in the Sales Forecast worksheet. Save your
work in a new workbook file named Solved18-3.xls in your Chapter 18 folder in
the My Practice Spreadsheets folder and then close the workbook file.

Performing Goal Seeking

Sometimes, you know the outcome that you want to realize in a spreadsheet and you
need Excel to help you find the input values necessary to achieve those results. This
procedure, which is just the opposite of the what-if analysis you’ve been doing so far,
is referred to as goal seeking.

When you only need to find the value for a single variable that will give the
desired result in a particular formula, you can perform this simple type of goal
seeking with Excel’s Goal Seek command. If you have charted the data and created
a two-dimensional column, bar, or line chart, you can also perform the goal seeking
by directly manipulating the appropriate marker on the chart.

To use the Goal Seek command, you simply select the cell containing the formula that
will return the result you are seeking (referred to as the set cell), indicate what value
you want this formula to return, and then indicate the location of the input value that
Excel can change to return the desired result.

Try It

Exercise 18-4: Doing Goal Seeking in a Spreadsheet
Open the Exercise18-4.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 version of the Sales Forecast for 2007 table
that you worked with in the previous exercise with an embedded Clustered Bar chart
immediately below it. You will use this table and chart to practice doing goal seeking
using both the Goal Seek command and direct manipulation of the data series in the
Clustered Bar chart:

1. Position the cell cursor in cell C8 containing the forecasted income for the first
quarter of 2007 and then choose the Tools➪Goal Seek command.
Excel opens the Goal Seek dialog box with cell C8 entered in its Set Cell text box.
The Goal Seek dialog box also contains a To Value text box, where you enter the
target value you’re seeking, and a By Changing Cell text box, where you indicate
the cell whose value should be changed to reach the target value.
2. Type 300000 in the To Value text box.
3. Select cell C4 containing the Qtr 1 sales figure in the worksheet to enter its
absolute cell reference, $C$4, in the By Changing Cell text box, and then select
OK.
As soon as you select OK closing the Goal Seek dialog box, Excel opens the Goal
Seek Status dialog box. In this case, the dialog box indicates that the program
has found a solution that increases the income in cell C8 to the target value,
$300,000.
Note that not only does Excel update the values in the Sales Forecast for 2007
table to reach the target value, but it also redraws the embedded Clustered Bar
chart to suit as well.
4. Select OK in the Goal Seek Status dialog box to close it.