Awards
- Community Pick
- Experts Exchange Approved
- Editor's Choice
Introduction
Good Excel modelling practice
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
- error checks
- row totals
- 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.
Output sample
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.
For example
Mappit! will not flag
=IF(A1>10,1,100)
Mappit! will flag
=IF(A1>10,1,100)+6.81
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.
Settings
Save Settings will store the current option and colour settings in the registry
Restore Settings will delete any user saved options
Updates
I am certainly open to suggestions for improvements, the inevitable bug fixes and updates.
Credits
A huge thank you to Jerry Paladino (aka ProdOps) for his in depth testing and suggestions, especially in making the product more user friendly.
Download the Mappit! addin here
=-=-=-=-=-=-=-=-=-=-=-=-=-
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.
=-=-=-=-=-=-=-=-=-=-=-=-=-
Free addins that I recommend for model auditing:
- FindLink by Bill Manville
- Name Manager by JKP and Charles Williams
- ASAP Utilities by Bastien Mensink
You can download both FindLink and Name Manager from The Excel MVP Page
ASAP utilities is available at http://www.asap-utilities
by: brettdj on 2010-03-16 at 22:35:17ID: 10932
Updated 17 March
- Glitch with Legend On/Off reporting fixed
- removed the "=" sign from the test for cells with both formula references and hardcoded numbers, ie
=IF(A1=10,1,100)
will no longer be flagged
- ran Ron Bovey's excellent Code Cleaner utility over Mappit! to compress the addin size