Most organisations have corporate guidelines for modelling, especially for financial models used for important business decisions. A standard set of guidelines would include most, if not all of the list below:
use introduction sheets
formulas to be kept as simple as possible
clear ownership structure for the model
a documented system for archiving model versions, controlling model access, model updates
a consistent layout across sheets (same years in the same column etc)
colour coding system to distinguish between inputs, calculations and algorithm changes
full referencing of the source and date for key assumptions
log of model - and if applicable value - changes
use of data validation and conditional formatting on data inputs to control and/or warn users
graphing of key input assumption and output metrics
avoid using dynamic ranges (i.e., the OFFSET function) that can't be easily audited
avoid array formulas
keep VBA to a minimum
build shadow models to test the main model
peer review of model inputs
Common model errors
Even models built by skilled practitioners adhering to good design principles, can and frequently do contain errors. Two common sources of model errors are:
1) Design mistakes in the initial model build, for example
not copying formulae completely over rows,
absolute/relative formulae inconsistencies
2) User error or "enhancements", for example
paste special values over a block of formulae,
adding an "adjustment factor" to a cell than forgetting about it,
Mistakenly inserting links to external workbooks
Using Tools to assist the detection of errors
There are a number of professional products available that offer a suite of audit utilities to identify potential model errors such as these. See here for a list of these tools.
Having used several of these programs over the years I found the mapping utility to be the one key application that I used repeatedly. But I thought that there was considerable room for improvement on the scope, speed and presentation of the mapped output, especially given some of these tools need to make changes to the source files to work properly. This was the driver for me to create my own - and free - mapping addin, Mappit!.
The Mappit interface
1) Format Formulas
All formulas in the selected sheets are mapped to a corresponding new workbook as per the diagram below. Mappit! uses variant arrays to compare formulae as quickly as possible. Use conditional formatting on output map
This option applies conditional formatting to the map workbook to make any changes in the formula patterns easier to identify.
2) Additional Cell Formatting
Five options are available under Additional Cell Formatting:
Formulae that contains constants
Refers to other Workbooks
Refers to other Worksheets
Logicals (Formulae & Constants)
Errors (Formulae & Constants)
The first three options will simply change the colour - but not the content - of the mapped cells. Please note these three options work in descending priority, if all three are selected then the Formulae that contains contants takes precedence, then Refers to other Workbooks, lastly Refers to other Worksheets
The last two options will change the mapped cell text to "ERR" or "T/F" respectively. Note that the colours of these mapped cells will also be updated by the first 3 formatting options if applicable.
While I have avoided time consuming looping through ranges on these options, the processing here is more time consuming, especially the Refers to other Worksheets option. So users may wish to turn these options off if they are non-critical.
The "Formulae that contains constants" uses regular expressions (see Patrick Matthews excellent regexp in VBA article) to parse formula cells that contain both cell references, and a numeric constant (either as number, or as a number with a "%"). Unlike some other mapping tools Mappit! will not flag "false" formulas which contain numeric constants if they do not have a valid mathematical operator.
Mappit! will not flag
Mappit! will flag
Output sample 3) Format Constants
These options will retain the contents of the numeric and text cells in the mapped output and apply user selected colour formats.
4) Detailed inconsistent cell report
This option produces a one sheet summary listing all the unique formulas, by location and formula value.
Mappit! output sheets
Two summary sheets are produced by default
The first is a hyperlinked sheet summary listing the key outputs from the mapped worksheets
This sheet may prove handy in flagging potential issues such as:
Inconsistencies between supposedly identical sheets in terms of links to external workbooks
The presence of hidden cells
The number of "unique" cells detected during the Mappit! run
The second sheet provides a legend map, plus the selected users settings - a list of the options that were run and the formatting colours.
Mappit! floating navigator bar
I borrowed this idea from John Walkenbach's excellent PUP addin
The floating userform provides an easy to user interface to switch between the source workbook and the mapped output. It can be closed manually, and it will also be closed automatically if the user closes the map file and has macros enabled.
Save Settings will store the current option and colour settings in the registry
Restore Settings will delete any user saved options
I am certainly open to suggestions for improvements, the inevitable bug fixes and updates.
A huge thank you to Jerry Paladino (aka ProdOps) for his in depth testing and suggestions, especially in making the product more user friendly.
If you found the article helpful please click on the "YES" button after the question below.
This utility has been helpful to me and I hope you find an opportunity to use it as well.
If you didn't or otherwise want to vote No, please leave a comment and give me an
opportunity to respond. It will give me a chance to improve this article and others I
would like to write.
If you liked this article and want to see more from this author, please click here.