The Auditing Tool
Overview
- I’ve just been handed a 5000-row spreadsheet that computes the net present value (NPV) for a new car. In the spreadsheet, my financial analyst made an assumption about the annual percentage of the growth in the product’s price. What cells in the spreadsheet are affected by this assumption?
- I think my financial analyst made an error in computing year 1 before-tax profit. What cells in the spreadsheet 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 how a building is built. The structure of a spreadsheet 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 Excel auditing tool provides an easy method for documenting the structure of a spreadsheet, which makes understanding the logic underlying complex spreadsheet models easier. To use the auditing tool (in Excel 2002 or later), select Tools, Formula Auditing and then choose Show Formula Auditing Toolbar. (In earlier versions of Excel, select Tools, Auditing and then choose Show Auditing Toolbar.) You’ll see the toolbar displayed in the following figure.
| Note |
The Show Formula Auditing Toolbar command is a toggle switch. If you see the auditing toolbar and select Show Formula Auditing Toolbar, the toolbar disappears. |
In my experience teaching thousands of business analysts, I’ve found that most analysts use only the five commands labeled in the following figure. These commands locate and display precedents and dependents for spreadsheet 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 spreadsheet that computes the net present value (NPV) for a new car. In the spreadsheet, my financial analyst made an assumption about the annual percentage of the growth in the product’s price. What cells in the spreadsheet are affected by this assumption?
The worksheet Original Model in the file NPVAudit.xls contains calculations that compute the NPV of after-tax profits for a car expected to sell for 5 years. (See the following figure.) 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. What cells in the spreadsheet are dependents of this assumption?
To answer this question, select cell C8 (the cell containing the assumption of 3 percent price growth) and then click the Trace Dependents button on the Formula Auditing toolbar. Excel displays the set of arrows shown in the following figure, pointing to dependent cells.
By clicking the Trace Dependents button once, Excel points to the cells that directly depend on our price growth assumption. In the following figure, 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 the following Figure .
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 with the Remove Dependent Arrows button or the Remove All Arrows button.
I think my financial analyst made an error in computing year 1 before-tax profit. What cells in the spreadsheet model were used for this calculation?
Now we want to find the precedents for cell B15. These 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 the following figure.
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 the following figure.
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 spreadsheet model in the workbook AuditTwoSheets.xls, shown in the following figure. The formula in the worksheet named Profit computes a company’s profit ( unit sales*(price - variable cost) - fixed cost) from information contained in the worksheet named Data.
Suppose we want to know the precedents of the profit formula. Select cell D7 in the worksheet Profit, and then click Trace Precedents on the Formula Auditing toolbar. You’ll see the arrow and spreadsheet icon shown in the following figure.
The spreadsheet icon indicates that the precedents of the profit formula lie in another worksheet. Clicking on the arrow displays the Go To dialog box, shown in the following figure.
Now we can click on any of the listed precedents (cells D4:D7 in the worksheet Data), and Excel will send us to the precedent we selected.
Explore posts in the same categories: MS Excel Tune Up