LITMIR.BIZ

Скачать книгу

elements in the results. If you want to change the results, you need to select the entire data table and then run the Data Table command again. If you want to delete the results, you must select the entire array and then delete it.

      Skipping data tables when calculating workbooks

      Because a data table is an array, Excel treats it as a unit, so a worksheet recalculation means that the entire data table is always recalculated. Such a recalculation is not a big problem for a small data table that has just a few dozen formulas. However, it’s not uncommon to have data tables with hundreds or even thousands of formulas, and these larger data tables can slow down worksheet recalculation.

      If you’re working with a large data table, you can reduce the time it takes for Excel to recalculate the workbook if you configure Excel to bypass data tables when it’s running the recalculation. Here are the two methods you can use:

       Choose Formulas ⇒ Calculation Options ⇒ Automatic Except for Data Tables.

       Choose File ⇒ Options to open the Excel Options dialog box, choose Formulas, select the Automatic Except for Data Tables option, and then click OK.

      Now every time you calculate a workbook, Excel bypasses the data tables.

      

When you want to recalculate a data table, you can repeat either of the preceding procedures and then choose the Automatic option. On the other hand, if you prefer to leave the Automatic Except for Data Tables option selected, you can still recalculate the data table by selecting any cell in the data table and either choosing Formulas ⇒ Calculate Now or pressing F9.

Analyzing Data with Goal Seek

      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.

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.

      

When building a worksheet model, you can use a couple of techniques to make the model more suited to scenarios:

       Group all your changing cells in one place and label them.

       Make

Скачать книгу