By: Philip Howard
Format: Research Report

Extract pp. 95–98

Spreadsheet Detective

Fast facts

The market for providing complementary products to Microsoft Excel comes from two directions: business intelligence vendors aiming to provide additional functionality, especially in the area of automation and development, and a second group of suppliers that address this market from the perspective of governance and compliance, providing the auditing, security and control that is lacking in Excel. In this latter category there are two broad categories of products: control and compliance tools and auditor’s tools. Southern Cross Software’s Spreadsheet Detective is a suite of tools that addresses the auditor’s market.

Note that auditors in this context cover two distinct functions. In the first instance, best practice in the development of spreadsheets is for a segregation of roles between the author, editor and auditor of spreadsheets, where auditor in this context refers to a function that is internal to the company. Needless to say, auditing is also an external function. However, a further point to appreciate is that the more that you have internal processes in place (such as the segregation of roles) to ensure the validity of your spreadsheets then the less work will be required by external auditors and the lower their resulting fees. In other words, implementing internal auditing processes will save money not just because you are basing your decisions on more accurate and reliable information but also through reduced annual fees.

Key findings

In the opinion of Bloor Research the following represent the key facts of which prospective users should be aware:

• Spreadsheet Detective has the most extensive range of auditing tools that we have seen from any vendor in this class.
• Spreadsheet Detective has a number of facilities, such as its sensitivity report, which we have not found (or rarely found) elsewhere.
• Unlike many suppliers that have limited or no support for earlier versions of Excel, Spreadsheet Detective supports all versions of Excel (with the exception of 2007 which is shortly to be released) from Excel ’95 onwards.
• Like most products in its class, Spreadsheet Detective uses spreadsheets to display its analyses. Given the wealth of detail that Spreadsheet Detective can present we would like to see the company implementing more intuitive (and simple) visualisation techniques, including colour coding.

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.

Vendor background

Spreadsheet Detective from Southern Cross Software was first introduced in 1997. While there are other products on the market that were available for in-house use only prior to this date, as far as we know this makes Spreadsheet Detective the first product in the spreadsheet management market to be have been made commercially available.

Southern Cross, as its name implies, is an Australian company, which is privately owned. It is the leading supplier in both Australia and New Zealand and is also well established in the UK. On the other hand the company has rather neglected the North American market though it still has major customers there: for example a major automotive manufacturer that uses the product world-wide. There is also support for European languages though, again, the company has not focused on this area.

Sales are by download, starting at (US) $180 for organisations and $48 for individuals though you can have an evaluation copy on a free download basis. Support is via email.

Web address: www.spreadsheetdetective.com

Product availability

Given its longevity as a product it is perhaps not surprising that Spreadsheet Detective supports Microsoft Excel from version ‘95 onwards. This is extremely rare: we know of no other supplier that still supports ‘95 and many do not support ’97. That said, the product does not currently support Excel 2007, though this is under test. The company uses Microsoft style version numbering and the current version of the product is Spreadsheet Detective 2006, with the 2007 version due for release shortly.

In addition to Spreadsheet Detective, Southern Cross has also developed a product called 123 Detective, which provides similar functionality for Lotus 123 environments that was developed at the behest of Lotus (IBM) and which is available only from that company.

Introduction

Spreadsheet Detective consists of a number of different auditing tools and while these are all distinct (with a few exceptions) the various tools provided can be broadly categorised as belonging to four groups: formula investigation, precedent/dependent analysis, worksheet analysis, and other tools. We will consider the facilities provided under these headings. However, before doing that it is appropriate to describe the product’s ‘AutoName’ facility that is leveraged across the product. What this does is to provide a label (name) for all formulae and defined ranges, which means that in relevant tools you have a name (for example ‘FixedCost’) associated with the relevant entry rather than cryptic references to row numbers. Examples of Autonames feature in some of the figures that follow. There are also facilities to control and override AutoNames. Note too that Autonames in Spreadsheet Detective are based on heuristics that determine which cells contain useful text to make Autonames out of, rather than relying on a particular layout of the spreadsheet.

Formula investigation

Spreadsheet Detective provides three major formulae tools:

1. Audit formulae with shading—this is illustrated in Figure 1
   

   Figure 1: Auditing formulae using cell shading

   where horizontal shading means that the formula is the same as in the cell to the left, vertical shading where it is the same above, cross-hatching indicates that this is a new formula and speckled (diagonal hatching) means that this formula is a copy of a non-adjacent cell. In other words this tool is designed to enable the visual identification of inconsistencies. Note also the native Excel green and red triangles: these are intended to highlight potential errors but are not a reliable guide, hence the need for more detailed inspection. Our only concern with this tool is that it might be preferable to use different colours rather than all of these blue lines, which can be confusing. However, the advantage of stripes is that they can be superimposed on any existing cell colouring without having to turn that off. If cells have been coloured in blue then Spreadsheet Detective will use pink stripes instead.
2. Audit formula report—this is a report of all the unique formulae (in other words each formula appears only once) and defined ranges within a spreadsheet, along with their label (autoname) initial value. Formulae themselves are colour coded to identify errors.
3. Full annotations—this is illustrated in Figure 2. Apart from the autonames shown, the important feature here is the use of lines, dots and circles, and so on, as follows:
4. Red dots mean that the cell contains the same formula as the cell to the left while and empty dot means that there is no formula.
5. Red boxes show new formulae.
6. The various green figures (such as #) also have various meanings.

While you can turn red dots off we are concerned that this diagram is over-complicated though, to be fair, we have not seen any other product that provides this wealth of detail.

Other formula-based capabilities include a formula map that represents each cell as a single character; the ability to identify and list formulae that reference other workbooks; the ability to flag cells that are, or are not, referenced by any unique formula; and a facility to visualise array formulae.

Precedent/dependent analysis

There are two tools in this category: precedent/dependent dialog and precedent and dependent reports. The former is illustrated in Figure 3. Here, the active cell (H37) is described in the central box, with precedent cells (B2, I37 and H38) to the left and dependents (H36) to the right. You can click on any precedent or dependent and automatically make that the active cell.

While the dialog is primarily about moving backwards and forwards to/from precedents and dependents, the precedent report describes how the active cell was calculated, allowing you to drill down through successive levels within the precedent (or dependent) tree.

Worksheet Analysis

In this area there are two major capabilities. The first is a spreadsheet comparison tool that allows you to compare different versions of the same spreadsheet or different spreadsheets. Relevant symbols are inserted to indicate when a new formula has changed or where a row or column has been inserted, and so on. Once again, however, we would prefer the use of colour coding rather than symbols. In addition, the comparison is presented as a single spreadsheet rather than as two spreadsheets side-by-side: the latter has the advantage that you can insert blank rows or columns rather than, again, using symbols.

Secondly, there is a Worksheet Summary report that shows the number of formulae in any particular worksheet that refer to other worksheets. This is illustrated in Figure 4. You can drill down to these formulae by double clicking on the relevant cell. In particular, you can identify when two worksheets reference each other, which is not best practice when it comes to developing spreadsheets. The report also provides details of circular references.

In addition to these tools there is also a facility to show how multiple workbooks are, or are not, related; a report that provides a summary of all worksheets in a book; and the ability to highlight formulae copied between worksheets in three dimensional models.

Other tools

Perhaps the most significant of the other tools provided in Spreadsheet Detective is the Sensitivity Report. This shows how sensitive a selected output value is to all relevant input values. While this may be valuable in its own right it can also be used to highlight anomalous results: such as a zero sensitivity that may suggest an error in the spreadsheet.

Further tools provide facilities for manipulating name ranges and for chart documentation.

Summary

Spreadsheet Detective is, deservedly, a market leader for auditing tools, both in terms of sales and functionality. However, the product is starting to look its age with respect to the visualisation used. Other, newer entrants to the market may not offer as rich functionality as yet but they do so in a way that is clearer and easier for the auditor. In our view, Southern Cross needs to focus on this area in forthcoming releases if the company is to retain its leadership position.