Very often the most intimidating aspect of coming into a new spreadsheet is figuring out where to start, particularly when there are multiple tabs. The Tab Dependency Report is designed to give you "the lay of the land" of the entire plan. Spending a few minutes with it can save you hours of hunting and pecking.
Each of the sheets (tabs) of the model will be displayed as a separate line or section in the report, with sub-sections depending on how the tab relates to others. Then for each tab, you'll see a bunch of useful information.
Go to the Analyze option at the Main Menu and click the Tab Dependency Report option.
The report will run automatically. Here’s an example of what you’ll see:
The Parent Tabs and Child Tabs sections describe how the tabs relate to each other: A tab has one or more Parent Tabs if one or more of its cells has a computational dependency on cells in another other tab -- the parent. Likewise, if a tab drives another tab, that latter tab is its Child Tab. This is analogous to how cells relate to each other. If cell A3 = A1 + A2, A1 and A2 are considered Parents of A3, and if A4 = A3*10, A4 is considered a Child of A3.
Tabs with no parents often represent the computational starting points of the model.
Tabs with no children often represent the computational ending points of the model.
Scan over the number of rows and columns of each tab, as well as the total number of cells. You’ll get a quick sense of where the bulk of the model can be found.
|Root Cells||These are shown in blue and are the start of a computation chain, and therefore, have no computational Parents). We can use the Cell Relationships Navigator to easily traverse an entire chain -- more on that later.|
|Formula Cells||These are shown in green and will always have one or more computational parents, which may be other Formula cells, or Root cells.|
|Terminal Cells||These are shown in red and will always be Formula Cells, too, but will have no computational Children -- the action stops there.|
|Computed Constants||These cells contain hardcoded formulas, like this: A1 = 12345.67 / 12.3. In practice they behave like Root Cells.|
|Other cells||These are just standalone pieces of data or text that don’t drive anything and don’t result from anything. A typical example is a cell that labels an adjacent cell.|
Tabs with many Root Cells typically represent starting points in a model.
Tabs with many Terminal Cells typically represent ending points in a model.
We can review the exact locations of all of these cell types with the Cell Type buttons, described later, like this:
|Circular dependencies||These relationships are highlighted in yellow. If Tab A is a Parent of Tab B, but Tab B is also a Parent of Tab A, they are circular. Depending on the exact structure of the model, this may or may not be a problem, but in general this type of design should be avoided as it may force your audience to repeatedly loop back and forth between tabs when reviewing things.|
|Large tabs||Any particular tab that is dramatically larger than the others may be a candidate for simplification.|
|Similarly structured tabs||Tabs which appear to be very similar to each other may represent scenarios of a core model found elsewhere.|
|Computed constants||At the minimum, these should be examined and documented. They’re almost never a good practice because they contain otherwise hidden pieces of knowledge. In the example above, what does the 12345.67 represent? Or the 12.3?|
For circular dependencies, you may be able to factor out the circular portion of the logic into another tab. For example, if Assumptions are present on Tab A, which drive values on B, whose values loop back to Tab A, these Assumptions could be moved to a new Tab, C. C would drive cells on both A and B, with some values from B contributing to A as well. The Tab Dependency report would then report the Assumptions tab as having no parents, and a user would be more likely to start reviewing -- and understanding -- the model from there.
Large tabs can sometimes be broken into additional tabs, ones that others may more easily understand.
Tabs that are in fact scenarios of a core model can often be deleted entirely, with their content and associated analysis being reproduced via ClearFactr’s scenario tools. Doing so could be a opportunity for simplification, bulk reduction, and error and risk reduction.
The terms within hard-coded formulas should be broken out into other cells that are properly labeled.