Microsoft Office Tutorials and References
In Depth Information
Performing What-If Analyses with Data Tables
Follow these steps to use the Goal Seek command to change the inputs in a
formula to achieve the results you want:
1. Select the cell with the formula whose arguments you want to
experiment with.
2. On the Data tab, click the What-If Analysis button and choose Goal
Seek on the drop-down list.
You see the Goal Seek dialog box shown in Figure 5-5. The address of the
cell you selected in Step 1 appears in the Set Cell box.
3. In the To Value text box, enter the target results you want from the
In the example in Figure 5-5, you enter 1750 or 2000, the monthly payment
you can afford for the 30-year mortgage.
4. In the By Changing Cell text box, enter the address of the cell whose
value is unknown.
To enter a cell address, select a cell on your worksheet. In Figure 5-5,
you select the address of the cell that shows the total amount you want
to borrow.
5. Click OK.
The Goal Seek Status dialog box appears, as shown in Figure 5-5. It lists
the target value that you entered in Step 3.
Book III
Chapter 5
6. Click OK.
On your worksheet, the cell with the argument you wanted to alter now
shows the target you’re seeking. In the case of the example worksheet
in Figure 5-5, you can borrow $316,422 at 6.5 percent, not $250,000, by
raising your monthly mortgage payments from $1,580.17 to $2,000.
Performing What-If Analyses with Data Tables
For something a little more sophisticated than the Goal Seek command
(which I describe in the preceding section), try performing what-if analyses
with data tables. With this technique, you change the data in input cells and
observe what effect changing the data has on the results of a formula. The
difference between the Goal Seek command and a data table is that with a
data table, you can experiment simultaneously with many different input
cells and in so doing experiment with many different scenarios.
Using a one-input table for analysis
In a one-input table, you find out what the different results of a formula would
be if you changed one input cell in the formula. In Figure 5-6, that input cell is
the interest rate on a loan. The purpose of this data table is to find out how
monthly payments on a $250,000, 30-year mortgage are different, given
different interest rates. The interest rate in cell B4 is the input cell.
Search JabSto ::

Custom Search