MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• I’ve just been handed a 5000-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 in 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 our 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 Office Excel 2007 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 2007, display the Formulas tab of the Ribbon, and then view the Formula Auditing group. (See Figure 14-1.)

Figure 14-1: The Formula Auditing toolbar

We will discuss Trace Precedents, Trace Dependents, and Remove Arrows. These commands locate and display precedents and dependents for worksheet cells or formulas. A precedent is any cell whose value is needed to compute 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.

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

• I’ve just been handed a 5000-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 14-2.) Price and cost are in thousands of dollars. The parameter values assumed 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 3 percent per year. Which cells in the worksheet are dependents of this assumption?

  Figure 14-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 Formula tab. Excel displays the set of arrows shown in Figure 14-3, pointing to dependent cells.

  Figure 14-3: Tracing dependent cells

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

  Figure 14-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, our 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.

• 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 14-5.

  Figure 14-5: Direct precedents for Year 1 before-tax profit

• We find that 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 14-6.

  Figure 14-6: Click Trace Precedents repeatedly to show all precedents of Year 1 before-tax profit.

• We find that 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 14-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 14-7: Data for using the auditing tool with data on multiple worksheets

• Suppose we 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 Formula tab. You’ll see a dotted line, an arrow, and the worksheet icon shown in Figure 14-8.

  Figure 14-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 on the dotted line displays the Go To dialog box, shown in Figure 14-9.

  Figure 14-9: With the Go To dialog box, you can audit data in multiple worksheets.

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