Light touch, Audit quality spreadsheet compliance & governance.

Diligence for Excel

Complexity Score Metrics

Level of Complexity is a hot topic amongst Spreadsheet Auditors and Analysts. The level of spreadsheet complexity will often dictate the likelihood of an audit investigation, the level of support required or the need to fully test and document the spreadsheet.

By its very nature ‘complexity’ is a subjective matter, and what may be simple to one user may appear complicated to others. Diligence uses a set of standard metrics to calculate its formula, worksheet and workbook complexity results. The figures produced are therefore consistent and can be used confidently to compare one workbook against another.

This summary is designed to give readers a meaningful understanding of the structure and implications of the Diligence complexity scores. A full list of qualifying complexity criteria and an example of a how a score may change is shown in Appendices at the foot of this page.

Workbook Complexity Overview.

In Diligence for Excel, the overall workbook complexity score is a number from zero to a theoretical maximum of 10, where zero represents an empty workbook and 10 an extremely complicated workbook.

The number is calculated using an exponential method, such that the score reaches mid-levels with a small number of complicated features, but as the score approaches 10 a significant increase in complexity will result in only a small increase in the score.

The key driver for workbook complexity is the complexity of the individual worksheets, but other factors such as hidden sheets, links, references and macro code are also taken into account.

Typical workbook complexity scores are as follows …

 

Worksheet Complexity Overview.

Individual worksheets are also given a complexity score. Unlikely the overall workbook complexity score this number is a simple addition of many weighted factors, such as the number and type of formulae, the use of hidden rows, and links to other worksheets and workbooks. In reality it is most unlikely that any one worksheet would score above 6.0. The overall workbook score is based heavily on the number and spread of the individual worksheet scores and will always be a figure the same or higher than the most complicated worksheet score. A good analogy to worksheet scores is the relevant amount of time it would take for a lay person to completely understand the working of that worksheet.

Formula Complexity Overview.

Each cell formula is also allocated a complexity score. Unlike the worksheet and workbook complexity score this figure has no upper limit, and is calculated according to the Functions used within the cell. A higher complexity score is allocated for the use of User Defined Functions (UDFs) and Array Formulas (also known as Curly Brackets Formulae) In addition, weighting is given to the level of nesting of functions within the formula.

Example Formula Scores:

=IF() ,Score=1.2

=VLOOKUP(), Score=1.5

=IF(SUM(SUM())), Score=2

=IF(ISERROR(VLOOKUP()),AVERAGE(ROUNDUP()))), Score=6

 

A score above 6 is defined as complex for the purpose of displaying in the complexity commentary.

Appendix A: Factors Used in the Complexity Scores

Factors used in the calculation of worksheet complexity…

· The number of formula cells on the worksheet

· The number of different formulae used

· The complexity of the formulae used  (Including nested formulae)

· The use of array formulas

· The number of different formula groups used (e.g. Statistical, Logical, Data)

· The number and complexity of any macros or VBA attached to the worksheet

· The number and spread of hidden rows and columns

· The number of linked worksheets within the workbook

· The number of linked worksheets in other workbooks

· The number and structure of contiguous ranges within the worksheet

· The overall used range of the worksheet

Factors used in the calculation of workbook complexity…

A figure is calculated based on the three highest worksheet complexity scores, the spread of complexity scores and number of worksheets (the base score). This figure is then augmented with the following factors …

· The number of hidden and very hidden worksheets

· The number of references to additional or third party functions (E.g. Bloomberg)

· The use of Real-Time-Data

· The amount and complexity of any macros or VBA, not included in the Worksheets above

 

Appendix B:  A Typical Example of Changing Complexity Scores

A workbook with a single worksheet and several formulae may have a worksheet complexity score of around 3.0. It has no links, VBA or other complexities, so its workbook score is also around 3.0

The same workbook with TWO worksheets of complexity score 3.0, and still no links, VBA or other complexities will have an overall workbook score of around 3.5

The same workbook with two worksheets of complexity score 3.0, but one small macro will have an overall workbook score of 4.5