|
|
|
|
|
|
Advanced FeaturesPrecedent / Dependent Reports |
||||||||||||||||||||||||||
| The precedent
report
describes how the
Profitability cell (D37)
was calculated. The report lists the formula in D37, then the
precedent
cell referenced by it, and then the precedents of those cells
recursively.
Note that cell Profit!D35 refers to Fixed!C19, and so row 13 of the
report
describes formulas on the Fixed worksheet. Being able to see
inter-sheet
calculations is particularly useful when analyzing complex models that
contain multiple worksheets. The report is an outlined spreadsheet, and so clicking on a "+" or "-" will expand or collapse a set of precedents respectively. Double clicking on a line within the report will go to the corresponding cell in the original worksheet. Pressing Excel's normal F5 function key will return to the report. |
Note the way that
the "#"
makes it clear which referenced cells are input values and so avoids
the
need to look at those referenced cells explicitly. Other reports show all the unique formulas in a given worksheet, and highlight potential problems such as references to non-numeric cells and unprotected formulas. There is also a Dependent report that works in an analagous manner. |
| This worksheet has been modified and then compared with the original worksheet. The "D" in D35 indicates that the Gross Profit formula has changed, while the "e"s in row 38 show that these values are new because this row has been inserted. The heavy dashed vertical line indicates that column G (Qtr 3) had been deleted. | Changes are only marked for cells with formulas if the formula changes. Thus cell F39 has not been marked as being different even though its calculated value has increased from 4.5% to 6.4% because the formula has not changed. This is important because one small change in an input value can change the values of many calculated cells which can make it difficult to see the underlying cause. |
| It is important to be able to compare a draft with a final version to ensure that new errors have not been introduced. It can also substantially reduce the time required to check a new version of a model. Note that unlike Excel this feature compares workbooks directly and does not require the restrictive share mode. | |
| Full annotations and shading provide
a detailed view of
formulas in
the small. The worksheet data flow report provides a powerful
summary
of complex spreadsheets in the large. The report is a Pivot Table that describes the number of unique formulas that reference cells in each worksheet. Thus the Vincent worksheet contains two formulas that reference cells in the Trimix worksheet. This is of concern because a well designed model should have a clear flow from input sheets to output sheets but the Trimix worksheet also has six formulas that reference the Vincent worksheet. |
The report is an ordinary pivot
table
so double clicking on a
cell
will drill down to show the individual formulas that are doing the
referencing.
The totals on the right show the total number of unique formula
references
within each worksheet which is an excellent metric of the sheet's
complexity. The rightmost columns show references to worksheets in different workbooks. This compliments the inter workbook summary report which provides an even higher level overview of which workbooks call other workbooks. |
| The
Workbook Discovery and Precedent report helps discover where
spreadsheets are located within your organization, and how they relate
to each other. The report is produced in two phases. The first discovery phase scans a folder and optionally its sub folders for spreadsheet files. This can be repeated mulitple times if necessary to scan different folders. The second precedent phase opens each of the spreadsheets to determine their precedent worksheets as well as their Excel properties such as their title. A row is added to the report for each precedent that is found. Optionally the report can also recurse through any precedent workbooks that were not found in the original discovery scan. The report can then be sorted to highlight either workbook precedents or workbook dependents. |
So in the example we can see that Prec11.xls references Prec21.xls and Prec22.xls, and that Prec21.xls references Prec33.xls etc. Thus the report provides a flexible and powerful view of all of the spreadsheets in an enterprise, and the relationships between them. |
| While the Spreadsheet Detective's AutoName facilities reduce the need for Named Ranges, they are still useful for commonly referenced fields. Excel makes it easy to create Named Ranges, but provides very little assistance with changing their definitions as the structure of a model changes. The Rename Names feature enables a Named Range's name to be changed and provides explicit information about local, global and hidden Names. | More importantly, the Detective can
automatically
update
all the formulas that used the Named Range that has been
renamed.
The generous dialog box shows all details
of all
Named Ranges including local and hidden Names. These can be
very
confusing without the Spreadsheet
Detective. (This is no replacement for showing named range definitions right on the worksheets they refer to as is done in the Shading and Full Annotations.) |
| The Sensitivity Report shows how sensitive a selected output value is to all the input values. The report is produced by replacing each constant or formula with a constant that is10% (say) larger than its current value. The difference in the output value is then recorded in the corresponding cell in the report before the original value or formula is replaced and next cell selected. | Thus the above report shows that the
Profitability is 63%
sensitive
to Qtr1 Sales, but is not sensitive to Qtr3 Sales. This
highlights
the error in the formula in cell G35. Sensitivity analysis can produce a much deeper understanding of a model and can highlight many types of errors that are not obvious from absolute values. More details can be found in the Spreadsheet Detective documentation. |
|
Basic Features |
