Diagramas del Cliente

Questions answered in this chapter:

■ I’ve just been handed a five thousand–row worksheet that computes the net present value (NPV) of a new car. In the worksheet, my financial analyst made an assumption about the annual percentage of the growth in the product’s price. Which cells in the worksheet are affected by this assumption?

■ I think my financial analyst made an error computing Year 1 before-tax profit. Which cells in the worksheet model were used for this calculation?

■ How does the auditing tool work when I’m working with data in more than one worksheet or workbook?

When we hear the word structure, we often think about the structure of a building. The structure of a worksheet model refers to the way input assumptions (data such as unit sales, price, and unit cost) are used to compute outputs of interest, such as NPV, profit, or cost. The Microsoft Excel 2010 auditing tool provides an easy method for documenting the structure of a worksheet, which makes understanding the logic underlying complex worksheet models easier. To view the auditing options in Excel, display the Formulas tab on the ribbon, and then view the Formula Auditing group. (See Figure 16-1.)

FIGURE 16-1 The Formula Auditing toolbar.

In this chapter, I discuss the Trace Precedents, Trace Dependents, and Remove Arrows commands. I discuss the Evaluate Formula command in Chapter 22, “The OFFSET Function.”

Clicking Show Formulas serves as a switch that toggles the display of formulas in cells and the display of the values that result from the formulas. You can also press Ctrl+~ to toggle between showing formulas in cells and showing the formulas’ results.

By using the Watch Window you can select a cell or cells and then, no matter where you are in the workbook, you will see how the value of the selected cells change. For example, after selecting the Watch Window you can select Add Watch and add a formula (say to cell C3 in Sheet 1). Now even if you are working in a different workbook, the Watch Window shows you how cell C3 of Sheet 1 changes.

Trace Precedents and Trace Dependents locate and display precedents and dependents for worksheet cells or formulas. A precedent is any cell whose value is needed to compute

122 Microsoft Excel 2010: Data Analysis and Business Modeling

a selected formula’s value. For example, if you were analyzing a direct mail campaign, you would make assumptions about the number of letters mailed and the response rate for the mailing. Then you could compute the number of responses as response rate*letters mailed. In this case, the response rate and total letters mailed are precedents of the cell containing the formula used to compute total responses. A dependent is any cell containing a formula whose values can’t be computed without knowledge of a selected cell. In the previous example, the cell containing the total number of responses is a dependent of the cell containing the response rate. Excel marks precedents and dependents with blue arrows when you use the Auditing tool. The Remove Arrows button clears the display of the arrows.

Let’s apply the Auditing tool to some practical problems.

Answers to This Chapter’s Questions

I’ve just been handed a five thousand–row worksheet that computes the net present value (NPV) of a new car . In the worksheet, my financial analyst made an assumption about the annual percentage of the growth in the product’s price . Which cells in the worksheet are affected by this assumption?

The Original Model worksheet in the file NPVaudit.xlsx contains calculations that compute the NPV of after-tax profits for a car expected to be available from the manufacturer for five years. (See Figure 16-2.) Price and cost are in thousands of dollars. The parameter values as-sumed for the analysis are given in cells C1:C8 (with associated range names listed in cells B1:B8). I’ve assumed that the price of the product will increase by three percent per year.

Which cells in the worksheet are dependents of this assumption?

FIGURE 16-2 You can use the Auditing tool to trace formulas in complex spreadsheets.

To answer this question, select cell C8 (the cell containing the assumption of 3 percent price growth), and then click the Trace Dependents button in the Formula Auditing group on the

Formulas tab. Excel displays the set of arrows shown in Figure 16-3 pointing to dependent cells.

By clicking the Trace Dependents button once, Excel points to the cells that directly depend on the price growth assumption. In Figure 16-3, you can see that only the unit prices for Years 2–5 depend directly on this assumption. Clicking Trace Dependents repeatedly shows all formulas whose calculation requires the value for annual price growth, as shown in Figure 16-4.

FIGURE 16-3 Tracing dependent cells.

FIGURE 16-4 Clicking Trace Dependents repeatedly shows all the dependents of the price growth assumption.

You can see that in addition to unit price in Years 2–5, the price growth assumption affects Years 2–5 revenue, before-tax profits, tax paid, after-tax profits, and NPV. You can remove the arrows by clicking the Remove Arrows button.

124 Microsoft Excel 2010: Data Analysis and Business Modeling

I think my financial analyst made an error in computing Year 1 before-tax profit . Which cells in the worksheet model were used for this calculation?

Now we want to find the precedents for cell B15. These precedents are the cells needed to compute Year 1 before-tax profit. Select cell B15, and then click the Trace Precedents button once. You’ll see the arrows shown in Figure 16-5.

FIGURE 16-5 Direct precedents for Year 1 before-tax profit.

As you can see, the cells directly needed to compute before-tax Year 1 profit are Year 1 revenues and Year 1 cost. (Before-tax Year 1 profit equals Year 1 revenue minus Year 1 cost.) Repeatedly clicking the Trace Precedents button yields all precedents of Year 1 before-tax profit, as shown in Figure 16-6.

FIGURE 16-6 Click Trace Precedents repeatedly to show all precedents of Year 1 before-tax profit.

Here the only input assumptions that influence Year 1 before-tax profit are Year 1 sales, Year 1 price, and Year 1 cost.

How does the auditing tool work when I’m working with data in more than one worksheet or workbook?

Consider the simple worksheet model in the workbook Audittwosheets.xlsx, shown in Figure 16-7. The formula in the Profit worksheet computes a company’s profit (unit sales*(price–variable cost)–fixed cost) from information contained in the Data worksheet.

FIGURE 16-7 Data for using the Auditing tool on multiple worksheets.

Suppose you want to know the precedents of the profit formula. Select cell D7 in the Profit worksheet, and then click Trace Precedents in the Formula Auditing group on the Formulas tab. You’ll see a dotted line, an arrow, and the worksheet icon shown in Figure 16-8.

FIGURE 16-8 Results of tracing precedents with data on multiple worksheets.

The worksheet icon indicates that the precedents of the profit formula lie in another worksheet. Double-clicking the dotted line displays the Go To dialog box, shown in Figure 16-9.

FIGURE 16-9 With the Go To dialog box, you can audit data in multiple worksheets.

Now you can click any of the listed precedents (cells D4:D7 in the Data worksheet), and Excel will send you to the precedent you selected.

126 Microsoft Excel 2010: Data Analysis and Business Modeling

Problems

■ In the car NPV example, determine the following:

❑ The direct dependents and all dependents of the interest rate.

❑ The direct dependents and all dependents of the tax rate.

❑ The direct precedents and all precedents for Year 4 unit sales.

❑ The direct precedents and all precedents for Year 3 costs.

127

Chapter 17