<

Mappit! - a free Excel model auditing addin

Published on
35,908 Points
19,008 Views
19 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick

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

pic71) 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.pic4
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.Pic11
Output sample
pic22) 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.
pic8While 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 pic13) Format Constants
These options will retain the contents of the numeric and text cells in the mapped output and apply user selected colour formats.
pic5
4)  Detailed inconsistent cell report
This option produces a one sheet summary listing all the unique formulas, by location and formula value.
pic10pic3

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
pic6
The second sheet provides a legend map, plus the selected users settings - a list of the options that were run and the formatting colours.
pic9  
 

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          
Mappit-1.11a.zip
 

Associated Article

Readers may find my article on Using XLM with Range Names and Conditional Formatting to automatically format spreadsheets according to cell conten a useful adjunct to this article.

 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
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.com/
19
Author:Dave
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free