One of my previous jobs was to be a rocket scientist – financial jargon for somebody who is good at mathematics.

Naturally, this makes you the guy who works the most on the financial models that drive strategy.

A financial model simulates the future, and by manipulating the various inputs to the model you are supposed to get an idea of how the company will perform. From these scenarios you learn which strategy to pick.
What could go wrong? Every­thing.

The problem with financial models, usually built using Microsoft’s Excel application, is that people think they are easy to build correctly. I agree that they are easy to build. It is the “correctly” part that I find more difficult. Financial models these days require hundreds if not thousands of cells to be filled with numbers and formulas. It is far too easy to get something wrong.

Let me start with a personal anecdote. My team had hired an associate who seemed strong on paper and later turned out strong in actuality. My team gave him a model to build and present to me. I walked into the meeting room and started looking over the model. One of the important cells was supposed to be the product of two whole numbers greater than one. The number that the cell was showing was 13. For those who may have forgotten their mathematics, the number 13 is a prime number – there are no two whole numbers greater than one whose product is 13.

As you can guess, “13 is prime” became something of a gag in the office. But it also was a reminder to the team to follow procedure – one person designs the model, one codes it and one validates it. Sadly the first and third steps are usually skipped and people dive into coding the model willy-nilly. You don’t want to do things willy-nilly. At least not in business.

What does it mean to design a financial model? Isn’t it straightforward? Well no, it isn’t. Consider a company selling widgets. In the design phase you have to decide what the drivers are – what determines how many widgets get sold.
Is it the size of the population or some demographic? Is it the supply of widgets? Is it the number of salespeople?

Once you have the drivers agreed, you need to understand the relationships between drivers. If your driver is salespeople, then how many widgets per month can the average salesperson sell?

A subtle point here is what is the lead time for the salesperson to make a sale? It is rare that someone knocks on a door and immediately makes a sale. This is important, as in one forward-looking model the chief financial officer would change a driver, and since the output changed immediately he and his boss assumed no lead time. In reality the lead time was nine months, leading to massive losses. But not to worry, they blamed a subordinate for it and fired him.

So now that we’ve had a taste of the design phase, let us look at the other oft-neglected phase: validation. This does not only mean validating the model but also the assumptions involved. For example, building a model that shows sales of 1,000 widgets per year is pretty silly if the maximum annual production is only 500. This is validation of assumptions.

Trickier is validation of the correctness of the model. An example comes from negotiations to purchase a leveraged company where the market was lending at 14 per cent a year. One of the best ways to check a model is to change various inputs and check if the results make sense. The trouble we were having was that we were increasing the leverage ratio but the return on equity (ROE) was decreasing. This normally does not make sense.
So we started working backwards checking revenue, then return on assets and so on. We finally got to operating margin, which you can think of as the ROE before leverage. This number was 12.3 per cent. Think about this.

If your ROE without leverage is 12.3 per cent then why in the world would you borrow at 14 per cent? Each time you leverage will result in a decrease of 1.7 per cent in the ROE.

This insight was staggering as the leverage was more five times – the company was losing 8.5 per cent a year on its ROE because it was borrowing at a rate greater than its ability to make a profit. If it simply deleveraged, the ROE would have doubled.

It is all too easy to be pre­sented with the results of a spreadsheet and take it on faith that the numbers are correct. In my experience the opposite is true – financial models are usually wrong.

This article was originally published in The National.