Chartered Manager. Accountant. Here on Medium, I Curate, Write about Number Crunching Tools & Techniques that Lead to Better Money Management for Businesses.

Dec 6, 2015

Tips, Tricks & Tools for Building a Robust & Trustworthy Financial Model

This post originally appeared on Linkedin http://bit.ly/bpdas

“You must learn from the mistakes of others. You can’t possibly live long enough to make them all yourself.”

If you had to pick a technological development that has transformed the way we manage things in last 30 years, you couldn’t do better than Spreadsheet or Microsoft Excel. This is the program that has launched thousands of businesses, justified millions of job losses, enabled billions of budget cuts, even planned out weddings, household finances etc.

Those of us that are in the business of crunching numbers, cannot think of a single day without Excel. It gives us the means to play with numbers and ask, “How are we doing? Or what next? Or what if?” When I first started in accounting in 2006, I used to work with someone who saw the days of double entry bookkeeping with pen and paper. When he used to tell us how they worked, I used to feel a chill down my spine and thanked Bill Gates countless times.

However, many spreadsheets are created with little thought given to the design, documentation or integrity checks. Several cases of high profile spreadsheet errors, such as the one found in the model used to evaluate bids in the West Coast franchise completion in 2012 [page 12, Laidlaw Report] or in the VaR model used in 2012’s J.P. Morgan’s £6bn trading loss [page 127, Internal Report] have highlighted this problem. It is reported that close to 90% of spreadsheets contain errors and that approximately 50% of spreadsheet models in use operationally in large organisations have material defects.

You can find even more examples of spreadsheet blunders here, here & here.

Now, if you are responsible for financial control in your organisation, this is something must make you feel very awkward. This article is about sharing ideas for building a robust and trustworthy financial model or automated spreadsheet in Excel. Just so we’re clear, this post is most relevant to the accountancy professionals, especially to those that are directly involved in the design and use of complex financial spreadsheets (models).

There are two fundamental issues with spreadsheet -You may think it’s rudimentary, but there really are only two reasons why we get things wrong:

Spreadsheets look too professional, so most people assume there is substance behind them. So, when someone presents a number in a sleek spreadsheet, instead of asking ‘how do we know it’s accurate, we ask ‘what can we do with it’? We misjudge the importance of speed v accuracy both as developers and users/readers of spreadsheets.
Spreadsheets are too easy to be created by anyone (with little or no training) without a disciplined methodical approach for design, documentation or integrity checks. Therefore, they are just too easy to be broken. But, they (Excel) don’t tell us when they are broken, they just give us the wrong number.

But, only a bad workman blames his tools, right? We need to find a way of doing spreadsheet the right way. It (developing a spreadsheet/financial model) can be seen like going on a trip. We can walk, cycle, or take a bus, train, taxi or plain. The objective is to reach the destination safely and on time with least cost and maximum comfort. How? Here’re my six ideas for doing spreadsheets the right way:

Last things first “Speed v Accuracy”

When it comes to financial modelling, we often misjudge the relative importance of speed v accuracy. Our tendency is to rattle through a spreadsheet at superfast speed and then move on to the next one. The problem is — any positive impression gained from the quick turnaround will be undone if there are errors in the model. Remember ‘Credibility is a survival tool’ and once lost through errors, it is very difficult to get it back, not to mention the loss of valuable time, money and reputation for your organisation.

The trick is to check, double check and triple check the work; and to keep looking for errors, and more errors. Answering following questions will help to achieve accuracy:

Does the spreadsheet follow any Best Practice Standards or Quality Assurance process? The answer could be Yes or No. If ‘no’ then there must be a good reason for that. (Here’re three great examples of principles/standards from the ICAEW, F1F9 and BPM)
Do the results make sense (reasonable)? For example, if model forecasts a net profit of 200%, then either you got a fantasy business or rouge spreadsheet.
Have the results been compared against some other results from a 3rd party source? For example, industry average figures or last year’s known actual results. If there are material variances, are they explained?
Are the input data, and assumptions up to date?
Has the spreadsheet been independently checked by someone else? (peer review)
Are all the documentation (step 6) up to date?

Also some tips for checking work: most impressive checking is, of course, cell by cell inspection, but it’s tedious and takes a lot of time. Here’re some shortcuts:

Put zeroes in all the input cells and see what happens to the output. Are they still NIL?
Add one more unit to input cell, let’s say to ‘units sold’ and see if it increases pre-tax earnings by the gross profit on one unit.
Stress test the results at the outer limits of all data validation bounds. Do the results still make sense?
Well known and simple but still very effective, calculate check totals and see if there are discrepancies.
Print the spreadsheet and read it like the end user, perhaps on next day. You’ll be surprised with the things that you notice with a fresh pair of eyes.

“Fast is fine, but accuracy is everything”

Adhere to the basic rules

Following these rules will take time and slow you down initially but will offer paybacks in the long run. Don’t believe me? Try this: audit one of your current spreadsheets, then for next one month, build all your spreadsheets following these rules, then audit one of your new spreadsheets, and decide for yourself.

Keep formatting consistent for cells of the same type. It makes the model look professional and adds credibility.
Use the same time period in the same column across all worksheets. Keep the time periods consistent and keep the formula constant across each row.
Use consistent sign convention. Income, assets and cash inflows are all positives. Expenses, liabilities and cash outflows are all negative.
Minimise the use of merging cells since this makes it difficult to navigate, print or change spreadsheets.
Do not (yes, I’m firm on this) hardcode inputs and formulas. If you change your assumptions you’ll struggle to find the formula.
Ensure information flow from top to bottom, front to back; the logical way of how people see things.
Limit circular references or iterative calculations as this adds unnecessary confusion to the model.
Turn on autosave or auto recovery, no excuse for loosing work just because the computer crashed.
Use conditional formatting to highlight dubious results, if your model forecasts a net profit of 80%, then you got a fantastic business or rouge spreadsheet.
Lock all cells except input cells but share the password, the objective is to avoid inadvertent changes not to prevent needed changes (of course with appropriate version control process).
Design the model in a way that allows it to grow. For example, allowing for new items to be added without needing to change formulas.
For spreadsheets used repeatedly as a model, keep a read only ‘golden source’ for future reference.
Overall, follow your organisation’s Spreadsheet Standards or Quality Assurance (QA) process. If you’re, and finding errors, then the QA process needs to be reviewed and improved.

“We are what we repeatedly do. Excellence, then, is not an act, but a habit”

Be aware of common errors & avoid them at all costs:

Pointing to the wrong cell during formula construction.
Changing some but not all of a series of copied cells.
Confusion between relative and absolute references (insertion, deletion, copy, sorting or moving a cell from one place to another can cause errors in absolute and relative references that are almost impossible to find).
Incomplete ranges (cells are omitted from one end of a range or the other, often when a range is expanded, but the formula that relies on the range isn’t changed).
Temporary fixes (a calculated cell is replaced by the amount it calculates, perhaps to make the rounding come out right but is not changed back to a formula when the input values change later).
Incorrect units (mixing different kind of units with wrong formats).
Function arguments in wrong order.

“A smart man makes a mistake, learns from it, and never makes that mistake again. But a wise man finds a smart man and learns from him how to avoid the mistake altogether”

Divide & conquer

“Divide et impera” may be an outdated principle in politics but still valuable for developing a financial model. This enables a smoother quality assurance (error checking) process and ensures formulas or input values are not accidentally overwritten.

Keep input, output and calculation worksheets separate and label each worksheet clearly
Have a different panel or worksheet for constants and variable inputs.
Colour code input cells so that input sources can be identified easily.

Sophistication is in simplicity

Financial modelling isn’t about showing off Excel skill, it’s about assisting the end users utilise data and make decisions effectively and efficiently.

Keep the model simple and formulas short. Avoid unnecessarily complex functions, such as nested IFs and macros wherever possible.
Allow only one unique formula per row or per column. Design in a way so that each attribute uses only a single formula. If a single attribute must use two formulas, write them in the two legs of an IF statement, using reflective condition to distinguish them.
Don’t conceal rows or columns as it is not immediately obvious to the user that there is information hidden.
Minimise use of links to external workbooks. They often result in outdated data and a flood of error messages.

“Simplicity is the ultimate sophistication”

Writing is rewarding

The success of a good spreadsheet lies in any stranger being able to follow a model/spreadsheet blissfully. It’s only possible when all instructions, notes are available in as much details as possible. Also write as you develop, don’t wait until the end. Here’s a handy list:

The purpose of the model and how to use it (with comments and navigation links to assist end users).
Description of inputs, outputs and calculations including setting out data sources and links.
Description of assumptions, uncertainties and caveats.
A version history log describing all different versions of the model.
A quality assurance log with a list of identified issues and remedial action taken.
Identify and make a list of sponsors, developers, readers, auditors and users of the model.
A dependency graph such as one below, so that all variables can be identified and how the model works can be viewed at a glance.

“We do not write in order to be understood; we write in order to understand.”

FINAL note:

Despite all these precautions here, I have to admit, mistakes still can happen. To err is Human. Right? The worst thing to do is to cover it up and hope it goes away. Errors do have a nasty habit of coming out in the end. It’s smarter to find out what went wrong, inform everyone (ASAP) and put measures in place to ensure it doesn’t happen again.

Also, while every spreadsheet or financial model is different, they all have one thing in common: rubbish into the model equals rubbish coming out. Make sure the assumptions you feed into your model are reasonable and can be justified.

Additional Resources:

No matter how good you get at Excel there is too much for one person to be great at everything Excel can do. Here’re are some fantastic resources:

Financial Modelling Training:

FREE Financial Modelling by F1F9 | FREE Practical Step by Step Financial Modelling by WSM

Excel Gurus:

MrExcel | Chandoo | SpreadsheetPage | Contextures | ExcelUser

YouTube Channels:

MrExcel | ExcelIsFun | LearnExcelFunctions | BIExcel

Templates:

***Bonus** Do you invest in the stock market?

Check out the Portfolio Slicer package for Excel 2010, a free template that comes with special macros that fetch up-to-the-minute stock quotes from Yahoo.

Excel Tips & Recommendations:

Excel Alternatives for Dashboard or Data Visualisations:

Tableau and Qlik allow you to connect a spreadsheet or file, creating complex data visualisation for the web. The array of options is huge, and you can pull different visualisations together to create a dashboard. [Windows — only, FREE versions available for personal use]

Infoactive.co and Infogr.am allow you to manipulate icons, graphs and texts in chosen styles and are really simple to use. [FREE versions available but websites’ watermarks remain on the final product]

Further Reading:

Five common judgment biases that can affect accounting decisions. In my view, some if not all of these apply to financial modelling too.

Would we be using Excel 100 years from now? Here’s the transcript of a stimulating conversation of the accounting profession’s three top technology experts.

Macpherson Review of Analytical Models Used in Government (some fantastic practical tips included)