Auditing a model

I’m not gonna lie, it’s not easy to write a couple page blog post on something where your model could be a patient headed for the morgue, or a superstar athlete that can perform everything they need to with grace and power.

That being said there are underlying principles which hold true no matter what state your model is in.

You’ve probably heard me talk a lot about these before

Transparency and flexibility. And robustness, by the way, but that’s really a subset of flexibility. That’s one of the indicators of health of your model.

Regardless of all the indicators of health, you don’t need a degree in this stuff to figure out if you’re in the ballpark.

And that’s what this is really about.

So how do you do that? Well it’s a lot about pairing your commercial knowledge, with your modeling knowledge, navigating around the model.

If I want to get a rough benchmark, I’m going to run Row Differences over the model (F5 >> Special >> Row Differences)… or shortcut “Ctrl + \” .

If I do that across each sheet, I’m going to get a quick feel of how best practice this model is, if there are going to be any landmines.

But overall getting a sense of the magnitudes of each item compared to the others (i.e. by checking the totals columns) and by checking the timing of each item, this will give you a lot of information.

In particular, I like to check what I call the transition points… for example, what happens when the debt tenor ends? Do assets depreciation in the time they’re given?  Are accounts flushed out at the end of the operations life time?

I suggest you start this on the Financial Statements first. This will give you the biggest bang for your buck, going through the cashflows, and then likely into the balance sheets, to check the most important accounts at the same time.

Hint – Use the shortcuts Alt + Page Up/Down to step across quickly!

Next you might like to use a more systematic approach for changing values in the model.  You can have a look at the tutorial “De-Risking a Model” for a more detail on this.

By using the tips contained in this blog and the tutorial; you can probably pick up 50-60% of the errors in the model.

It’s very difficult to know how healthy the model is from the outset, there are no real barometers of model health. One way of doing this is by using Warning & Alert Signals to perform integrity and commercial checks on the model – however that is more of a pre-emptive feature, and should be done by the model developer.

Good luck!