|
Diligence for Excel |
|
Additional Tools: Formula Map
The formula map helps the user understand the structure of your workbook by giving them a visual sheet by sheet analysis of the formulae and data in the file. It is probably the single, simplest method by which material errors can be identified in a complex workbook, and has been responsible for discovering numerous errors in critical, high profile spreadsheets. A copy of the formula map is often used as part-evidence of the design principle and testing for submission to auditors. The map consists of two sections; A management summary sheet, followed by an additional detailed map for each selected worksheet.
The individual formula maps (sample extract above) highlight the fundamental structure of each worksheet by stripping away all formatting and replacing it with a simple colour-coded framework of the underlying formula and data that drive the spreadsheet. By applying the same colour to like-for-like formula, and displaying the formula directly, the user can quickly identify areas of interest, complexity and likely errors.
Understanding the Map The map is best understood as a ‘Paint by Numbers’ representation of the formula on your spreadsheet. Formulae whose structure is identical will appear in the same colour. The colour itself is arbitrary but the colour patterns quickly highlight anomalies. What we are looking for in our formula map is symmetry and order. Any formula map that appears simple, neat and symmetrical gives a good level of confidence in the spreadsheet design. It is important to understand that this does NOT indicate that the formulae are correct, merely that they are logical and consistent.
Important Notes Rows or Columns that were hidden in the original sheet are shaded with a dotted-pattern to indicate their ‘invisible’ previous status. As well as space for comments and audit approval (in yellow), the management summary displays additional factors to be taken into account when analyzing the sheet in detail, such as hidden rows and columns, and number of distinct formulae. Tell me more about the Formula Map …
|
|
Light touch, Audit quality spreadsheet compliance & governance. |
