547:: Auditing your financial models

You’ve finally finished your Excel model. It looks good, there are even dashboards included and you reckon your financial model or workflow is good to go. Think again.

There is a thought lingering in your head: is the model sound? Is it sustainable? And most of all: are the results correct?

Well, can’t be that hard, can it? You just run a couple of tests and scenarios and if the numbers are adding up, we are good to go. Don’t fret over it. We’ll be right mate. Or will we???

You see, the big problem is in our own psyche: we can’t find or see our own errors. I’m not talking about a plain visible #N/A in one of the cells. I’m talking about a formula which is not right. I’m talking about a sum-total adding up all numbers in a column, but somehow missing the last three lines out of 547. I’m talking about the dreaded hard-coded number where a formula used to sit…

You can’t see your own errors, and believe me, I have yet to see an Excel model, where there is no error or inconsistency whatsoever.

And I am rather (un)happy to admit, that I am making the same mistakes myself.

I’ve been there, you build a model, you test it, you test it once, you test it twice, you test it three times and you are content and happy. Now someone else is using it and suddenly a total different scenario pops up and you realise that you must have overlooked something in the first place.

So what can we do, to find these errors? Simple: give your finished model to your colleagues and let them test it. Ask them to operate your model as if their job would depend on it. Observe silently what they are doing and you will not only witness them finding inconsistencies or errors, but you will also learn how they are using your financial model, giving you insights of how to make it more user-friendly.

If the model is bigger and maybe mission critical for your organisation, then you might want to have an external specialist looking at your model. We call this an audit.

Auditing means there is another pair of eyes looking at your spread sheet and at your formulas. And whether the financial model or the model you are producing is actually doing what it should do. As a result from an audit you receive a classification whether your formulas are consistent and that the results are around the expectations you’ve set.

What are the benefits?

That is quite obvious, the benefits are, that your model is tested, you are getting feedback, you see where your errors are and you have now the time and opportunity to fix them.

 What are the disadvantages?

Well the disadvantages are to be spending time and money to be quite honest. Auditing is not cheap. First you need the time to explain to someone, what it is, your model shall do and then obviously you have to pay that person.

You might even ask for a certificate at the end, a kind of guarantee or call it a stamp underneath your model that it has been audited and approved.

If you have macros or visual basic applied to your model the audit gets more time consuming and more complicated.

As you are basing your $1m+ decisions on results from an Excel model, you are better off by getting that model tested, approved and audited by an expert beforehand. Better safe than sorry.

The proof is in the pudding.