The best way to test a financial model is by splitting roles

Business analysis lifecycle

Author:

Andrew Berkley

Published:

07 Oct 2014

Comments:

0 Comments

Category:

First you have the modelling role. This is the person who sits behind their computer working in Excel.  They end up with a financial model in Excel.

Then there is the conceptual role. This person understands the business case in depth. They understand all the risks and interdependencies.  They can do their job anywhere. All they need is a blank paper and a pen. The result of their hard work is a conceptual model. This can be sketched out in Excel and might also be drawn on paper or some other medium.

The modelling role is something that can be done by a junior person or it can be outsourced. The modelling role does not require a deep understanding of the business case (although there might be an advantage, if understanding does exist).

The conceptual role is best undertaken by a senior person. They should be an expert in the business case.

A few weeks ago, a colleague asked me to have a look at one of his models. The model behaved strangely. So I sat back and had a look at the model. At that moment, I realised that the split role exists when you test a model. You can test a model on its calculations (i.e. see how well the modelling role has been completed).  And you can run a conceptual test.

The modelling test is the easiest to perform. I had a look around in my company for somebody else and asked them to have a look at all the calculations.  It was not that difficult since the model is FAST.

Very soon I received the results and those were quite good. From a modelling perspective, this was a good model.

The harder part was the conceptual test. Does the model behave as it should do?  I filled in some parameters. The result was counter intuitive. How can that be explained? Why, when the business pays a lot of tax, does the tax disappear when I lower production?  These and other questions were what I was faced with.  There is no testing software that does the job for me; nor is there another colleague that I can call in.  Instead, I must rely on my knowledge of the business case.  Is it possible to test all possible combinations of parameters? I don’t think so.  I test the most plausible combinations and see what the outcome is.  This is hard work.

What I realised is that I do not spend enough time on testing the conceptual part of the model.  I felt a bit ashamed of that.  I asked my colleagues and they gave me the same answer: we do not spend enough time on conceptual testing.  That is a relief – I am not alone.  We should spend more time on testing the model before we use it.  I could detect more errors and avoid horror stories.

This also helps to explain why it is so difficult to make a generic model.  Not only does a generic model include a lot of modelling complexities, it also requires significant conceptual understanding.

The FAST standard is a big step forward.  It makes the modelling role a lot easier. Together with all the available software, modelling errors can be detected and corrected quickly and easily. The FAST standard also helps in testing conceptual understanding since the spreadsheet model is easier to review.

There is no software to my knowledge that can help with conceptual testing. For the moment we must continue to rely on our own intelligence and understanding.  Perhaps we need artificial intelligence. Something for the future to work on…

To be continued…


Andrew Berkley
Do you have a better solution than this? I’m curious to know. If you do, or if you have any thoughts on my blog, join the discussion below.If you’d like to receive more information on data visualisation, please sign up to the topic-specific mailing list.
Andrew Berkley
Andrew has spent one half of his career as a finance professional and the other half in learning and development running finance-based training courses. Andrew leads the senior team at F1F9. He has been with F1F9 since 2013.