SPREADSHEETS are notoriously difficult to
validate. Undetected errors have caused large financial losses and
expensive litigation. The Spreadsheet
Detective helps ensure model correctness in
Microsoft® Excel® by providing automated documentation
that highlights mistakes. The tool received a 5 Star
(top) rating from ZD Net.
Microsoft Excel Spreadsheet Audit Features
The Spreadsheet Detective
generates graphical annotations that can:-
- See the big picture with a comprehensive workbook
report, data flow analysis and error detection.
- Show which cells have formulas and how they have been
copied throughout a model.
- Clarify cryptic "A1" references using English AutoNames.
- Non modal window constantly describes the current formula.
- Easily follow complex Workbook Precedent/Dependent relationships.
- Compare different copies of a spreadsheet.
- Manage Scenarios, and perform Monte Carlo simulations.
- Encrypt sensitive workbooks without any risk of losing the password.
- Highlight bad sensitivity relationships.
- Assist with the understanding and manipulation of Named Ranges.
- And much more...
In combination these clarify the structure of complex models.
They go well beyond Excel's in built auditing features.
Bottom Line —
"Spreadsheet Detective is
one of the
longest established and most comprehensive suites of auditing tools
that we have seen. This is not to say that other products may not have
features that are not in Spreadsheet Detective or that, in some
instances, we might prefer a competitor’s implementation of a
particular feature but, overall, the product is clearly the market
leader, and deservedly so."
Compliance Detective — Risk Management
The Compliance Detective can record meta data about spreadsheet models that document their risk profile.
It can then produce company wide reports of the models, their risk profile, when they had formulas (as opposed to data) changed.
Rules can be provided as to when formal reviews are required, and the tool can then track who has reviewed them.
The Compliance Detective can quickly scan large repositories such as Box.com, and does not require any web interfaces.
It has been used effectively by a large financial services company that has thousands of spreadsheet analysts.
Excel Spreadsheets & Section 404 of Sarbanes-Oxley (SOX)
The following is an extract from The
Use of Spreadsheets: Considerations for Section 404 of the
“Anecdotal evidence suggests that 20% to 40% of spreadsheets have
errors, but recent audits of 54 spreadsheets found that 49 (or 91%) had
errors, according to research by Raymond R. Panko, a professor at the
University of Hawaii.”
"The Journal of Property Management stated, “30 to 90 percent of all spreadsheets suffer from at
least one major user error. The range in error rates depends on the
complexity of the spreadsheet being tested. In addition, none of the
tests included spreadsheets with more than 200 line items where the
probability of error approaches 100 percent.”
"Perform an on line search
for spreadsheet errors or spreadsheet audit, and you will find a number
of major failures attributed to spreadsheet inaccuracies that hit the
press in the past year alone."
The Original Spreadsheet Detective
Detective has been revealing modeling errors since
1997. Many features have been added since that time based on
extensive customer feedback. Other less established spreadsheet
auditing tools may offer
some superficially similar features, but they lack the depth and
quality of the Spreadsheet Detective.
Deloitte Touche Tohmatsu, Ernst & Young, PricewaterhouseCoopers,
KPMG, Grant Thornton, Morgan Stanley, CitiBank, Bankers Trust,
Australia Post, Bentleys, BDO Nelson Parkhill, AMP, Preston Resources, Westpac Bank,
William Mercer, Pannell Kerr Forster, Caladonian Life, Deutsche
Bank, Thiess Construction, Queensland Treasury, Duke Energy, ANZ Bank,
Guinness Brewery, Navigator Project Finance, General Motors, Lotus 123, ...
Libre/Open Office Calc Detective
(The Spreadsheet Detective should
not be confused with the "Detective" feature in Libre/Open Office Calc
which just provides similar features to Excel's in built audit
arrows. The Spreadsheet Detective
has been available since 1997, and the use of the same name for the
Calc feature is unfortunate. Generally, while Calc may be an
adequate tool for the casual spreadsheet user, it is not recommended
for developing serious quantitative models.)
Pricing and Support
The Detective is priced very reasonably
can be seen on the Price List.
There is also a Free
Excel versions are supported from Excel 2016 down to Excel 2007. Please direct any questions
to the email below. The Spreadsheet Detective is
an essential tool when auditing Excel spreadsheets.
Melissa and Anthony Berglas
Southern Cross Software
Queensland Pty Ltd
ABN 41 079
Phone +61 4 4838 8874 (email generally preferred)
Comprehensive Workbook Report
Provides a bird's eye view of complex models by summarizing each
worksheet, highlighting potential error conditions, showing data flow between each
worksheet, references to external workbooks etc.
Distinct and Copied Formulas
Cell shading clearly shows which cells have formulas, and how they have (or have not!) been copied
throughout a spreadsheet.
New bold shading highlights inconsistent formulas on larger models.
And the shading is right on the actual model, so that it can be seen while the model is being developed.
This information can also be shown on a condensed map report.
Symbols show how formulas have been copied, and comments show the original formulas.
The blue, yellow and red background colors indicate potential errors and issues that have been automatically detected.
AutoName Non-modal window
Shows the active cell's formula with "English" AutoNames
that clarify the meanings of the cryptic A1 references.
So the cryptic reference $E$26 below can be described with the AutoName InterestOnCapital
AutoNames are based on cell labels, so that unlike conventional named
ranges they are always up to date as a spreadsheet model evolves.
The dialog is non-modal so it can be constantly displayed to help you
while you edit the underlying model.
Hyperlinks make it easy navigate to precedents. But more
importantly AutoNames let you understand what most A1 references mean
without needing to navigate to the precedent cell.
This can also be displayed more fully as a task pane.
Formula AutoName Report
Provides a complete list of all distinct formulas and named ranges.
AutoNames clarify cryptic A1 references.
Audit Process Control
Mark formulas as verified, questionable or erroneous.
Collate them in a workbook audit status report.
Control your communications with the model author
Scenarios and Monte Carlo Simulation
Consider different scenarios or perform full Monte Carlo simulations. Manage uncertainty and avoid the flaw of averages.
Full Graphical Formula Annotations
Completely describes a model by annotating the spreadsheet itself.
Clearly shows changes between different versions of a model even if rows or
columns have been inserted or deleted.
Unifies changed copies of a formula in the optional report.
Shows which parts of complex formulas have changed.
Can also compare different parts of the same worksheet.
Quick Model Navigator
Quickly navigate to worksheets or named ranges by just typing a few characters from their name.
Master Key Encryption
You know you should encrypt your extremely sensitive workbooks, but
what if you lose the password?
Master key encryption makes protecting your data practical.
Precedents & Dependents Reports
Provide a detailed outline report to see where values is comming from and where they are going.
Discover where your spreadsheet models are, and what other models link to them.
Obtain a new perspective by examining each formula's sensitivity.
Named Range Dialog
View and edit named ranges, easily verifiy their definitions and automatically update the formulas that