Posted by Andrew Berkley, Managing director at F1F9

Coming to accountancy studies late in life, I found myself in the classroom for my 30th birthday. We were on a light industrial estate on the outskirts of Cambridge and the topics for the day – taught by a tutor who is still a legend in the world of financial reporting – were deferred tax and accounting for pensions.

I’ve spent better birthdays – but at least this one was memorable.

The other memorable topic from my accounting studies that has served me well through my thirties and my forties has been Modigliani and Miller. I like the highly theoretical world in which they live: no taxes; no transaction costs; perfect information; infinite debt availability; and infinite projects with a positive NPV.

But like the best theories, I find that you can take Modigliani and Miller into the real world. Their theory of capital structure, for example, lies at the heart of model optimisation.

Their starting point is that capital structure is irrelevant – how you raise money has no impact on value. So we can conclude that there is no point in optimising models.

And that is not good enough when you start to consider taxes. Bring tax into the equation and debt finance becomes advantageous. Borrow as much as you possibly can, we conclude. So an optimised model would by default go for maximum gearing.

And that is not good enough when you take real debt investors into account. Or risks of bankruptcy. Or credit rating agencies. Or the particular features of debt markets: right here, right now.

So Modigliani and Miller tell us how far we need to travel from an idealised world to enter the real world. And as we start to breathe in the polluting atmosphere and swim the muddy waters, so models begin to have a role – describing the real world in some imperfect but nonetheless appropriate and useful way.

FAST financial models, with transparency at their core, are designed to satisfy key stakeholders quickly and easily that their objectives are being met. For debt and equity investors, it is all about Modligliani and Miller – finding the optimal mix of debt and equity at levels that satisfy appetites for risk. And a price conscious project sponsor will seek a price point such that the project generates sufficient cash to generate a minimum return for investors but not so much cash as to be uncompetitive.

Click here to download F1F9’s Model Optimisation ebook

The Business Analysis Lifecycle framework was developed by Tom Grossman. Like all works of great insight – it now seems obvious. However, when we first came across it, it was a real moment of revelation. It makes clear and explicit what had been unclear and implicit previously.

This framework has helped us to explain what it is that we do (“spreadsheet engineering”) and what we rely on our clients to provide us with (“conceptual models”).

In our training business, it has helped us as we explain to our students the different skills that they will need if they are to add value to their modelling assignments. We are deeply grateful to Tom.

The Business Analysis Lifecycle model divides the world in two – the real world, and the model world.

Analysts and financial modellers in particular, live in the model world. It is a well-ordered world where A leads to B, and where businesses can be neatly represented by calculation blocks and inputs.

Most people do not live in the model world. Communicating with people in the real world can be a challenge for those who live in the model world.

It is important for analysts to do the work of turning model insights into business insights. Managerial insights are aimed at people who are familiar with the business, and the industry in which the business operates, but who are not familiar with the model.

A key skill that allows an analyst to progress to more senior appointments is the ability to communicate insights about the business based on analysis of the model.

Our latest ebook “Business Analysis Lifecycle – From business concept to the financial model” gives practical advice on how you can effectively communicate and deliver these management insights.

It may seem a little odd that one of the key foundations of business over the last 25 years has been the humble spreadsheet. Yet it is hard to find a key financial decision that has not been based on calculations made in this ubiquitous software tool.

As the F1F9 e-book shows, nightmares lurk amongst the innocent rows and columns of figures for those that don’t take care with how they process their figures. Research has shown that 88% of spreadsheets contain errors of some sort and approximately 50% of financial models in use operationally in large businesses have material defects.

Many different organisations have been hit by spreadsheet problems in a variety of ways. The root causes of many are surprisingly simple errors. MI5 admitted that they had bugged 134 of the wrong phones because of a spreadsheet formatting problem. Oxford University candidates experienced interview upheaval when administrators muddled up spreadsheet numbers for registration and examination marks.

Unsurprisingly, spreadsheet problems are seen most in the world of finance where the values involved can be terrifying and the financial calculations can become extremely complicated.

An eagle-eyed reader of a US Federal Reserve statement spotted a hitherto unnoticed error in a spreadsheet worth up to 4 billion dollars. Who knows the value of the financial impact of the spreadsheet error found in the published research papers of leading Harvard economists, Carmen Reinhart and Kenneth Rogoff? Their flawed research has been claimed as a basis of several countries’ current economic austerity programmes.

The problems highlighted are not only embarrassing for those involved, they can be costly both in terms of money wasted and in jobs that are lost. At least one CEO has had to resign as a result of errors being discovered in figures that they had previously announced.

The stories which have attracted uncomfortable publicity are only the tip of the iceberg. There must be many more unpublicised spreadsheet skeletons lurking in the cupboards of many an accounting department.

What 3 things should managers responsible for building and operating spreadsheets do?

1. Adopt a standard for creating spreadsheets that can be followed by people in your organisation.

The FAST standard is the only internationally recognised standard for building spreadsheets. It is managed by an independent standards organisation, which is backed by F1F9 as well as international accounting firms, Grant Thornton and Mazars.

FAST defines a Flexible, Appropriate, Structured and Transparent approach for creating understandable spreadsheets and reducing errors. It still gives users the freedom they need to tailor their calculations to particular business needs but it also  imposes simple disciplines that mean that their work is understandable. Most importantly its adoption means that errors can be spotted.

2. Ensure that procedures are in place for managing spreadsheets and the IT environment where they are saved and shared.

Such procedures should include:

• Simple version control and file numbering processes which identify clear responsibility for who is in charge of spreadsheet updates. Many errors are caused because the wrong version of a spreadsheet is updated or viewed.
• Review of spreadsheet work ranging from sense-checks of model outputs to independent examination of the spreadsheet coding for more complicated applications.
• Routines for regular communication between those making and managing commercial decisions and those who are building spreadsheets to ensure that correct assumptions are used.
• Control and audit procedures to identify and track who can access and has updated spreadsheet files.

3. Pay attention to the human-side of spreadsheet control.

Make people aware of the risks of working with spreadsheets and provide training in how to build and manage them safely and efficiently. A culture of positive collaboration and improvement should be encouraged where good design and error elimination is promoted with clearly defined responsibilities.

Given that spreadsheets have been around for over 25 years, it seems surprising that businesses have not mastered ways to manage them yet. But the truth is that high profile errors keep appearing. Part of the problem seems to be a philosophical one. Because the nature of Excel use is everyday and accessible, many managers just don’t look on it as something that needs managed in the same controlled way as other computer systems. Some have already become enlightened. Others have not, to their cost.

Writing in the Financial Times recently about the spreadsheet “issues” that have caused the West Coast Mainline to be retendered, the “Undercover Economist” Tim Hardford wrote:

There’s a more worrying question: given how complicated the modern world is, aren’t such basic errors inevitable? This seems to have been a howler, but large spreadsheets are ubiquitous and their size makes it almost impossible to eliminate errors. The Office for National Statistics misreported gross domestic product last year, thanks to such an error.

Tim is right on both counts. Spreadsheets are ubiquitous and it is almost impossible to eliminate errors. However, it is not correct to say that say that error elimination is a function of size. According to research on spreadsheet errors, which we discussed in one of our first financial modelling podcasts with Ray Panko, spreadsheets are no different than other areas of human activity when it comes to making errors. Ray states:

Broadly speaking, when humans do simple mechanical tasks, such as typing, they make undetected errors in about 0.5% of all actions. When they do more complex logical activities, such as writing programs, the error rate rises to about 5%

Therefore large spreadsheets will have more errors due only to having more code. However their size is not what makes it “almost impossible to eliminate errors”. The possibility of identifying and eliminating errors is a function of the discipline of the modeller and the consistency and structure they have employed in the creation of the model. In my view it’s easier to identify and eliminate errors in a large model where a standard methodology such as FAST has been used, than it is to find errors in a small but chaotic model.

It’s not clear to me whether the errors in the West Coast models where conceptual errors, or structural errors. With two separate enquiries announced into the affair, we shall soon find out.

Where humans are involved there is always a risk of error (no software is ever completely bug free), but that doesn’t mean we should throw our hands up in the air and do nothing. The case for standards in modelling is clear.

An expert is one who has made all the mistakes which can be made in a very narrow field -Niels Bohr

The approach to modelling that I’m writing about in this book comes from years of modelling badly, and figuring our what doesn’t work. F1F9, the company of which I’m a director, keeps financial model building and financial modelling training courses as closely integrated as possible – so that that the things we learn in one area informs the other.

We aim to foster a company culture where mistakes are shared openly. Its only through our mistakes that we become experts in our field.