Navigator Utilities - Trace tutorial

Trace tutorial

Formula Trace

Precedents – Cells used in a formula

Dependents – Where this cell is used elsewhere in the spreadsheet

	Navigator Trace will help the user understand complex formulas
	Navigator Trace will help the user see the dependents of a cell. If you delete these cell you will create #Ref errors in your spreadsheet.

Case Study Problem:

An overworked analyst at a Debt Lending Bank is asked to understand the Debt formulas in a model sponsored by a building company prior to agreeing to a credit limit.

The formula is:

Solution:

Navigator Utilities Trace module allows the user to understand complex formulas in a fraction of the time of traditional methods

Step 1: Use the quick keys Ctrl + T to launch Navigator Trace

The complex formula is broken down into its precedent cells with other useful information:

Step 2: You navigate through all of the Precedents using the keyboard arrows or mouse pointer. As you select a Precedent you will be taken to this cell in the spreadsheet even if it is on another sheet or even in a separate spreadsheet.

When you select a Precedent notice that the precedent is highlighted as it appears in the original formula allowing the user to not only see where the cell comes from but see how it is used in the formula.

When a Precedent is selected the user is also shown the formula that is in the Precedent cell. The user can then change this formula in the window And use the button to Not accept those changes or the button to apply the changes to the spreadsheet.

The user can then set this Precedent cell as the new Origin and the Navigator Trace will then show the user the Precedents of this new cell. This can be done many times Precedents and Dependents alike.

This creates a history and you can use history navigation keys to navigate through the history you create.

*** You can always get back to the first cell by pressing

*** You can always clear the history by pressing

Step 3: You can use All of the above functionality for both Precedents and Dependents with the press of one button

Step 4: Blue Display arrows for Precedents and Dependents or both can be displayed in combination with the above functionality

By checking the appropriate box

*** Note: That Navigator Trace window can be placed according to your preference {Please see the Navigator Audit Case Study}

*** Note: That ALL Navigator Windows are modal. You can keep them opened and still edit your Excel Spreadsheet

*** Note: All User preferences like having Precedents blue display arrows being shown are remembered ( as per your last choice) by Navigator Utilities and these customised setting are respected and shown next time you launch Navigator Utilities.

Step 5 – Using Navigator Trace the overworked Analyst was able to see where the Precedents come from and how they are used in the formula and used this information to quickly ascertain what the formula was doing. It took him about 1 minutes to understand this complex formula:

The formula is simply doing the following:

=Opening Balance x (1+ Interest Rate)^(Day count)

Also noted using Navigator Trace was: The formula uses a logic flag to handle both Nominal Debt and Indexing Debt.

And YES it is correct.

[ Home ]