Proactive Spreadsheet Risk Management

More Information about XLAnalyst Standard

Purpose

XLAnalyst Standard works through a chosen workbook and assesses how likely it is to have material errors. Research suggests 30-90% of live commercial workbooks contain material errors.  Go to Eusprig for more info and links. If the research is right, then any organisation with more than a handful of spreadsheets has more than one which is materially incorrect.

XLAnalyst Standard is designed to help auditors and testers select which spreadsheets are most likely to contain errors, and where to focus their in-depth testing efforts. It is also aimed at developers and maintainers of complex spreadsheets who need a way to quickly understand what a model is doing so they can correct or enhance it.

XLAnalyst Standard is simple, fast to run, and gives a realistic estimate of the level of risk a spreadsheet contains. To give an idea of the test speed XLAnalyst Standard should analyse most 5Mb spreadsheets in under 20 seconds on a reasonably modern machine.

Functionality

XLAnalyst Standard produces 2 main reports.

1. The Overall Risk report covering common risk factors.
2. The Unique Formula List report that allows the user to interactively check each unique formula, in list form for structure and in context in use within the test workbook.

Overall Risk Report

XLAnalyst Standard performs a number of tests and produces a simple workbook report of the occurrence of over 20 of the most common causes of spreadsheet errors. It also provides some of the more common metrics.

These are categorised as follows:

Factors suggesting a high risk of an error

These are constructs that would normally be considered an error. In certain circumstances though, they may be a legitimate modeling technique. In general these are best avoided and can always be replaced with a more robust approach.

1. Circular References
2. Cells Displaying A Number But Storing Text
3. Mixed Formulas And Values
4. Formulas Evaluating To An Error
5. Vlookups Expecting An Ordered List
6. Hlookups Expecting An Ordered List

Factors suggesting a significant risk of an error

These factors are not in themselves errors, but they do make spreadsheet auditing and updating more difficult and hence increase the chance of errors. Most are not to be recommended, although pivot tables should be used (with care) wherever possible.

1. Links To External Workbooks
2. Presence Of Very Hidden Sheets
3. Hidden Rows Or Columns
4. "=+" Construct
5. Conditional Formatting

Factors suggesting complex logical modeling

Spreadsheets containing complex formulas and modeling logic are more difficult to audit and maintain. They are often not properly tested, and some of these constructs can make testing extremely difficult. Often the dependent cells are not obvious and a lot of investigation is required to follow the data flow.

1. Array Formulas
2. Nested If Statements
3. Use Of Sumif
4. Use Of Database Functions (Dsum Etc)
5. Use Of Indirect

Measures

Measures provide some indications of the contents of the spreadsheet such as size, number of sheets etc. In general larger models are harder to test and maintain. And large workbooks with long, complex formulas in are very hard to maintain.

1. Longest Formula
2. Most Complex Formula
3. Total Number Of Formulas
4. Total Number Of Unique Formulas
5. Workbook Size
6. No Of Worksheets
7. Total All Lines of VBA Code
8. Largest Formula Result

System messages

These are included to highlight if some elements of the model have not been tested.

1. Codematic Risk Management Components
2. Protected Worksheets
3. Protected Workbook Structure
4. Other

The XLAnalyst Standard report says 'Yes' an issue was found or 'No' it wasn't. The report also offers a risk factor for each construct. The total of these factors then gives a weighted estimate of risk for the workbook.  This is obviously a simplistic indicator, but in general, the higher the percentage, the more difficult the workbook will be to audit thoroughly.

XLAnalyst Standard also provides some common metrics to assess the overall complexity of the model. 

It obviously does not guarantee that a spreadsheet is correct. But a spreadsheet that has none of these risky constructs should be easier to audit thoroughly.

Unique formula list

In this mode as well as the standard report, the XLAnalyst Standard also produces a list of every unique formula used in the workbook.

This is the single quickest way to get a sense of the overall quality of the calculations in a model.

It is also the most compact and efficient way to review all formulas.

Models with over 300,000 formulas will generally reduce to 5,000 or less unique formulas.

It is simple to check for constants embedded in formulas, very complex formulas, and even structures that breach corporate best practice rules.

Of course formulas that link to external workbooks are also included.

This list is dropped into an excel worksheet so further analysis can be undertaken. An example address of formulas usage is also included. Double clicking any formula takes to the address so you can review the usage in context. An arrow on the toolbar takes you back to the list to continue your analysis.

Structure

XLAnalyst Standard is a small Excel Add-in with some custom VBA behind it.  There is no install, if you have Excel,  then you can run it by double clicking the .xla file. (Note: very occasionally VBA in Microsoft Office is disabled in some organisations - if this is is the case you should see your system administrators.)

Compatibility

Tested with Excel 2003, Excel 2002 (XP), Excel 2000.