a formula for Goal Seek to use to reach the goal. In the college fund example, you use the FV() function, which calculates the future value of an investment given an interest rate, term, and regular deposit.
When your worksheet is ready for action, here are the steps to follow to get Goal Seek on the job:
1 Select Data ⇒ What-If Analysis ⇒ Goal Seek.The Goal Seek dialog box appears.
2 In the Set Cell box, enter the address of the cell that contains the formula you want Goal Seek to work with.
3 In the To Value text box, enter the value that you want Goal Seek to find.
4 In the By Changing Cell box, enter the address of the cell that you want Goal Seek to modify.Figure 2-7 shows an example model for the college fund calculation as well as the completed Goal Seek dialog box.
5 Click OK.Goal Seek adjusts the changing cell value until it reaches a solution. When it’s done, the formula shows the value you entered in Step 3, as shown in Figure 2-8.
6 Click OK to accept the solution.
FIGURE 2-7: Using Goal Seek to calculate the annual deposit required to end up with $100,000 in a college fund.
FIGURE 2-8: Goal Seek took all of a second or two to find a solution.
You can get a more accurate solution by increasing the number of iterations that Goal Seek can use, by reducing the value that Goal Seek uses to mark a solution as close enough, or both. Choose File ⇒ Options and then choose Formulas. Increase the value of the Maximum Iterations spin button, decrease the value in the Maximum Change text box, or both, and then click OK.
Analyzing Data with Scenarios
Many formulas require a number of input values to produce a result. Previously in this chapter, in the “Working with Data Tables” section, I talk about using data tables to quickly see the results of varying one or two of those input values. Handy stuff, for sure, but when you’re analyzing a formula’s results, manipulating three or more input values at a time and performing this manipulation in some systematic way often help. For example, one set of values might represent a best-case approach, whereas another might represent a worst-case approach.
In Excel, each of these coherent sets of input values — known as changing cells — is called a scenario. By creating multiple scenarios, you can quickly apply these different value sets to analyze how the result of a formula changes under different conditions.
Excel scenarios are a powerful data-analysis tool for a number of reasons. First, Excel enables you to enter up to 32 changing cells in a single scenario, so you can create models that are as elaborate as you need. Second, no matter how many changing cells you have in a scenario, Excel enables you to show the scenario’s result with just a few taps or clicks. Third, because the number of scenarios you can define is limited only by the available memory on your computer, you can effectively use as many scenarios as you need to analyze your data model.
Group all your changing cells in one place and label them.
Make sure that each changing cell is a constant value. If you use a formula for a changing cell, another cell could change the formula result and throw off your scenarios.
Create a scenario
If scenarios sound like your kind of data-analysis tool, follow these steps to create a scenario for a worksheet model that you’ve set up:
1 Choose Data ⇒ What-If Analysis ⇒ Scenario Manager.The Scenario Manager dialog box appears.
2 Click Add.The Add Scenario dialog box appears.
3 In the Scenario Name box, type a name for the scenario.
4 In the Changing Cells box, enter the cells you want to change in the scenario.You can type the address of each cell or range, separating each by a comma, or you can select the changing cells directly in the worksheet.
5 In the Comment box, enter a description for the scenario. Your scenarios appear in the Scenario Manager dialog box, and for each scenario, you see its changing cells and its description. The description is often very useful, particularly if you have several scenarios defined, so be sure to write a detailed description to help you differentiate your scenarios later on.Figure 2-9 shows a worksheet model for a mortgage analysis and a filled-in Add Scenario dialog box.FIGURE 2-9: Creating a scenario for a mortgage analysis.
6 Click OK.The Scenario Values dialog box appears.
7 In the text boxes, enter a value for each changing cell.Figure 2-10 shows some example values for a scenario.FIGURE 2-10: Example values for a scenario’s changing cells.
8 To add more scenarios, click Add and then repeat Steps 3 through 7.
9 Click OK.The Scenario Values dialog box closes and then the Scenario Manager dialog box returns, showing the scenarios you’ve added.
10 Click Close.
Apply a scenario
The real value of a scenario is that no matter how many changing cells you’ve defined or how complicated the formula is, you can apply any scenario with just a few straightforward steps. Don’t believe me? Here, I’ll prove it:
1 Choose Data ⇒ What-If Analysis ⇒ Scenario Manager.The Scenario Manager dialog box appears.
2 Select the scenario you want to display.
3 Click Show.Without even a moment’s hesitation, Excel enters the scenario values into the changing cells and displays the formula result.
4 Feel free to repeat Steps 2 and 3 to display other scenarios. When it’s this easy, why not?
5 When you’ve completed your analysis, click Close.
Edit a scenario
If
