FINDING CIRCULAR REFERENCES
If you inadvertently link a formula to itself somehow, this will cause a circular reference, which is a form of error because Excel doesn’t know how to calculate it. For more infor- mation on what causes circular references and how to correct them, turn to Chapter 14.
If you know that circular references are causing problems in your model, the Error Checking tool has a quick way to fi all the off circular references in the model. Under the Error Checking drop-down arrow in the Formula Auditing section of the Formulas tab on the Ribbon, as shown at the top of Figure 5-13, under the Trace Error you see a Circular References option. If circular references exist in your model, the Circular References icon will no longer be grayed out.
FIGURE 5-14:
The Watch window.
The current value of $19,624 appears in the Watch window. This window remains onscreen no matter where you go in the model, and always shows you the current value. This tool is particularly useful in sensitivity analysis. For example, by going to the Assumptions page and changing the consumables cost from 45 cents to 50 cents, the value in the Watch window changes from
$19,624 to $18,567, as shown in Figure 5-15. You can see this in the Watch window easily without having to jump backward and forward in the model to see how input changes impact the model’s outputs.
FIGURE 5-15:
Using the Watch
window to monitor the impact of changes in inputs
to ending cash flow.
» Show Formulas: If you want to look at all the formulas and not the resulting values, you can use the Show Formulas option in Excel. This is also a very quick and easy way to see if any hard-coded values exist. For example, in Figure 5-16,
you can see that there are hardcoded “/12” values in rows 20 and 21.
Note that Show Formulas can also be applied using the Ctrl+` shortcut. (Note that ` is usually located on the ~ key at the upper-left of the keyboard.)
FIGURE 5-16:
Using Show Formulas to
uncover hard-coded
values.
SHARING YOUR WORK
When you’ve spent a lot of time building a find model, you take pride in your work and want to “own” the entire model. Your instinct might be to keep the model to your-
self, and not let anyone mess things up. This approach doesn’t work very well in a cor- porate environment, however. Modeling should be a collaborative, team eff You should build models in such a way that others can understand, edit, use, and interpret the results
This book is focused on making you a good finder modeler — one who knows how to
build a find model that others can easily follow. As a model user, however, you
don’t always know where the model has come from and whether the model builder has built the model well. The tools outlined in this chapter enable you to take apart, validate, and verify models you’ve inherited so that you can use them with confim
