Читать книгу Excel Data Analysis For Dummies - Paul McFedries - Страница 39

What if you already know the formula result you need and you want to produce that result by tweaking one of the formula’s input values? For example, suppose that you know that you need to have $100,000 saved for your children’s college education. In other words, you want to start an investment now that will be worth $100,000 at some point in the future.

This is called a future value calculation, and it requires three parameters:

 The term of the investment

 The interest rate you earn on the investment

 The amount of money you invest each year

Assume that you need that money 18 years from now and that you can make a 4 percent annual return on your investment. Here’s the question that remains: How much should you invest each year to make your goal?

Sure, you could waste large chunks of your life guessing the answer. Fortunately, you don’t have to, because you can put Excel’s Goal Seek tool to work. Goal Seek works by trying dozens of possibilities — called iterations — that enable it to get closer and closer to a solution. When Goal Seek finds a solution (or finds a solution that’s as close as it can get), it stops and shows you the result.

You must do three things to set up your worksheet for Goal Seek:

 Set up one cell as the changing cell, which is the formula input cell value that Goal Seek will manipulate to reach the goal. In the college fund example, the formula cell that holds the annual deposit is the changing cell.

 Set up the other input values for the formula and give them proper initial values. In the college fund example, you enter 4 percent for the interest rate and 18 years for the term.

 Create 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.

In some cases, Goal Seek might not find an exact solution to your model. Goal Seek stops either after 100 iterations or if the current result is within 0.001 of the desired result.

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.