Audit Excel Formulas to Reduce Spreadsheet Errors


Running Detective SPREADSHEETS are notoriously difficult to validate. Undetected errors have caused large financial losses and expensive litigation. The Spreadsheet Detective provides many features that can reduce errors and aid in model risk management.

The Case of the Invisible Spreadsheet Errors...


Collage

The mob wants to rub out the North Side gang, but will the massacre pay? No self respecting gangster would take such action without first building a spreadsheet. But it won't balance! Can the Spreadsheet Detective save the day? Stay tuned to find out...

Microsoft Excel Spreadsheet Audit Features

The Spreadsheet Detective is used by thousands of spreadsheet professionals to:-

In combination these clarify the structure of complex models.  They go well beyond Excel's built in auditing features.

Bottom Line —  Bloor Research Research

"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."
Read full extract.

Compliance Detective — End User Computing Model Risk Management (MRM)

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, and 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 can optionally utilize its own cloud services. It has been used effectively by a large financial services company that has thousands of spreadsheet analysts.  More...

Stress Testing Spreadsheet Models

A key aspect of model risk management is to stress test each model to determine how its predictions will vary based on different assumptions.  The Spreadsheet Detective provide an advanced scenario manager that can manage this process, as well as automatically generating different combinations of assumption parameters.

European Users Spreadsheet Risk Interest Group (EuSpRiG)

The EuSpRiG.org is the main center for research into the nature and seriousness of spreadsheet errors, as well as methods to avoid them.  Their website contains many academic and industry papers, plus links to researchers in the field.

They also provide a list of some very public failures at eusprig.org/horror-stories.htm.  However, most failures are never publicized.

The Spreadsheet Detective is a proud sponsor of EuSpRiG.
EuSpRiG

Excel Spreadsheets Risks & Sarbanes-Oxley (SOX)

The following is an extract from The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley Act PricewaterhouseCoopers:-

“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

The Spreadsheet 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.

The Spreadsheet Detective has thousands of active users.  Companies with site licenses include:

BDO Australia British Business Bank Chevron
Deloitte Ernst & Young Grant Thornton
Michael Kors Pitcher Partners PwC
Queensland Treasury Corporation KPMG S & P Global

(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 as can be seen on the Price List.

There is also a Free Download available.  

Excel versions are supported from Office 365 down to Excel 2010.  Please direct any questions to the email below.  The Spreadsheet Detective is an essential tool when building complex Excel spreadsheets.

E-Mail: info@spreadsheetdetective.com
Southern Cross Software
Queensland Pty Ltd
ABN 41 079 368 200
https://www.SpreadsheetDetective.com


See the Video Tutorials for details

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.
Workbook Report

Distinct and Copied Formulas

Cell shading clearly shows which cells have formulas, and how they have (or have not!) been copied throughout a spreadsheet.  Bold shading highlights inconsistent formulas on larger models. The shading is on the actual model, so that it can be seen while the model is being developed.
Formula Shading Example
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. 
Formula Map

Formula Dialog

Pretty prints the formula in the active cell so that complex formulas are easy to read.  Each of the cell references are hyperlinks so clicking on them goes quickly to the corresponding precedent.  Hovering over cells, functions or operators also shows their evaluated values.  Unused branches of IFs are grayed out.

Cell references are also annotated with green "English" AutoNames that clarify their meanings.  Issues and potential errors are shown in purple below, with the corresponding fragments of the formula shown in red.  These include things that might be hidden such as named range descriptions, locked cell status, validations and conditional formats.  Other annotations show the source of error values.

Formula Dialog

Original formula: =IF($C$8>$C$7,Allocations!$E$26*J101/($B$101-$I$101- $F$101),INDEX($F$14:$H$14, MATCH(J3, $F$5:$H$5)))+123

The dialog is non-modal and automatically tracks the active cell, although it is temporarily frozen when examining precedents.  There are also several keyboard shortcuts.

Dependent cells may also be displayed, with ranges of dependent cells collapsed into a single reference.  There is also an option to show how formulas are copied, and to display the dialog in a task pane.

Formula AutoName Report

Provides a complete list of all distinct formulas and named ranges.  AutoNames clarify cryptic A1 references. 
Formula Report with AutoNames

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 
Ticks and Crosses

Scenarios and Monte Carlo Simulation

Consider different scenarios or perform full Monte Carlo simulations.  Manage uncertainty and avoid the flaw of averages.

Scenarios and Monte Carlo Simulation

Full Graphical Formula Annotations

Completely describes a model by annotating the spreadsheet itself. 
Full Annotations Example

Worksheet Comparison

Clearly shows changes between different versions of a model even if rows or columns have been inserted or deleted.  Provides both annotations and a succinct report by unifying changed copies of a formula.  Shows which parts of complex formulas have changed.  Can also compare different parts of the same worksheet.
Compare Spreadsheets

Quick Model Navigator

Quickly navigate to worksheets or named ranges by just typing a few characters from their name.

Quick Navigate

A shortcut manager also enables arbitrary keyboard shortcuts to be assigned to any command, not just those from Spreadsheet Detective

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. 
Master Key Dialog

Precedents & Dependents Reports

Provide a detailed outline report to see where values is comming from and where they are going.
Precedence Report

Workbook Discovery

Discover where your spreadsheet models are, and what other models link to them. 
Workbook Discovery Report

Sensitivity Analysis

Obtain a new perspective by examining each formula's sensitivity. 
Sensitivity Report

Named Range Dialog

View and edit named ranges, easily verifiy their definitions and automatically update the formulas that use them.
Rename Named Ranges

See the Video Tutorials for details

 










Video Tutorials | Free Download | Price List | Compliance | Terms & Conditions | Order Form | Other Links | Comparing Auditing Tools | E-Mail The Detective

Extra Keywords: Excel Auditing Spreadsheet Errors Financial Models Add In Audit Addin SDetective SDetective_Library SDetective_TheAddIn SDetective_Order SDetective_Help Professional Oak Detective Financial Audit Operis excel savvy spreadsheetadvantage Auditing EXChecker Auditor Red Incisive Advantage Rainbow Space Excel Error AddIn Add-In tool xlspell Operis Space Analyst Rover 21 CFR Part 11 and Sarbanes-Oxley, validator validating  spreadsheet auditing tools.