How Not to Build a Financial Model – Part 2

CONSISTENT INCONSISTENCY

With bills to pay, mouths to feed and fun to have, in these uncertain times, it’s good to be in a steady job.  What better way than to make yourself indispensable?  If you are an analyst, it’s vital you create the most incomprehensible model you can with essentially a different formula in every cell and indecipherable hard code so that someone else cannot take over your role of maintaining or editing the model.

If you use 20 different formulae over one row instead of copying one across, it stands to reason it’s going to take you 20 times longer to build.  That means you will be busy.  And when people cannot understand your model that will make you a Guru.  Who has ever heard of a Busy Guru being made redundant?

You may laugh, but as model auditors we are convinced this must be how some people think.  There cannot be any other good reasons for some of the terrible models we have come across otherwise.

Elsewhere on our website we talk of the four lynchpins of “Best Practice Modelling”: >CRaFT – consistency, robustness, flexibility and transparency.  In this ramble, I am going to extol the joys of the first of those key qualities: consistency.

Models constructed consistently are easier to understand as users become familiar with both their purpose and content.  This will in turn give users more comfort about model integrity and make it easier to add / remove categories, numbers of periods, scenarios etc.

Consistent formatting and use of styles cannot be over-emphasised.  Humans take in much information on a non-verbal basis.  Consider the following ‘Print’ dialog box from Excel 2003:

We may not realise it but we have all been indoctrinated by Microsoft.  Whilst the above dialog box appears quite flexible, we know the only things we are able to change are the objects in white (for example, I know I cannot print out a list from the above dialog box since the selection has been greyed out).

Those of you familiar with the models I have attached to many of the Thought articles may now realise I exploit this mindset: the worksheets in my workbooks make it very clear without reading any instructions which cells may be changed and those which may not – we are all Pavlov’s dogs!

There are other key elements of a workbook that should be consistent.  These include:

• Formulae should be copied uniformly across ranges, to make it easy to add / remove periods or categories as necessary;
• Sheet titles and hyperlinks should be consistently positioned to aid navigation and provide details about the content and purpose of the particular worksheet;
• For forecast spreadsheets incorporating dates, the dates should be consistently positioned (i.e. first period should always be in one particular column), the number of periods should be consistent where possible and the periodicity should be uniform (the model should endeavour to show all sheets monthly or quarterly, etc.).  If periodicities must change, they should be in clearly delineated sections of the model.

This should reduce referencing errors, increase model integrity and enhance workbook structure.  And that’s what will keep you your job and make you a Trusted Guru.  Now, whoever heard of Trusted Gurus being let go..?

For more on the merits of consistency and how to find incongruent formulae, please check out our related articles on >Best Practice and >Consistent Formulae.