One of the most time-consuming and tedious tasks in financial modelling is model review. This task becomes more difficult the larger and more complex the model gets.
This is where Excel mapping software can be useful because, for each sheet, it shows the cells which contain input/formulae/titles, and highlights inconsistent formulae.
There are a number of different software packages that can be used to automatically generate Excel maps, they can all be used to simplify the process of auditing your models.
But how do we decide which ones to use, and which of these are better suited to our needs? Here is your guide to Excel mapping software.
Excel mapping software – Spreadsheet Advantage
Spreadsheet Advantage has strong advantages in terms of user-friendliness. It allows you to:
- Generate nicely coloured and easy-to-use maps
- Easily switch between the map and the actual cell in the model
- Compare sections within the model (using bookmarks), compare sheets within the model, and compare different versions of the models with a handy “row and column realligner” for easier comparison
- Easily identify circularity (circular references are highlighted in a user friendly way)
- List range names (although we can do this easily in 2007)
- Trace precedents by selecting a cell containing a formula and using the hotkey Alt+T+U+T
However, some disadvantages of Spreadsheet Advantage include:
- Maps are produced on a sheet that is separate to the model, which means that the user needs to have both sheets open simultaneously in order to examine the unique formulae
Excel mapping software – Spreadsheet Professional
One of the key advantages of using Spreadsheet Professional lies in the useful error reports that it is able to produce. These error reports highlight:
- Blank cells being referenced
- Cells with no precedents (i.e. cells which are not being used)
- Nested IF statements (which introduce risk through formula complexity)
- Links between sheets (useful in finding out which sheets are referenced more often)
The ability to find out which sheets are referenced more often can provide a basis for starting the auditing process, as most-referenced sheets tend to be more important in the model as well as having a greater probability of error.
However, there are some short-comings when it comes to using Spreadsheet Professional:
- Maps are produced on a sheet that is separate to the model, thus forcing the user to have both sheets open in order to identify possible errors in the original model
- It overestimates the unique formula count since, if there is a blank row, Spreadsheet Professional incorrectly identifies a unique formula after the blank row (even though the formula may actually be of the same form as the formula above the blank row) which makes auditing more difficult as extra checking is required
- Spreadsheet Professional produces paper intensive “translation”, making it relatively slow
Excel mapping software – Spreadsheet Detective
Spreadsheet Detective can be useful because:
- It produces a more accurate unique formula count (compared to Spreadsheet professional), making it useful in the initial stages of model auditing when the goal is to get an accurate estimate of the extent of unique formulae in the model
- Unlike other software, it colours in the sheets of the model (to indicate the presence of unique formulae) rather than producing separate maps
Nonetheless, there are some disadvantages: - It can be difficult to spot the cells which are shaded, especially if the shading (automatically produced by Spreadsheet Detective) is similar to the background colour of neighboring cells
- It does not look as visually appealing as the other software, and it is not as user-friendly
Excel mapping software – which software is right for you?
Personal preference will influence which mapping software you should choose, there is no “single best” solution. It’s also important to note that the software listed here can be used in conjunction.
For example, Spreadsheet Detective can be used in the early stages of model audit, as it enables you to find a relatively good and accurate unique formula count, whilst Spreadsheet professional can be used for more detailed error checking and for identifying the most-referenced sheets in a model. Spreadsheet Advantage is useful to generate coloured, easy-to-use maps.
Something else to keep in mind is the actual version – since features change over time.
