Practical Financial Modelling

The Development and Audit of Cash Flow Models

Jonathan Swan

• Publisher: Butterworth-Heinemann
• Year: 2015
• eBook ISBN: 9780081005880
• Edition: 3
• Topic: Business
• Subtopic: Finance
• Index: Business

Practical Financial Modelling, 3e is a book on model development and model assurance. It enhances the modelling process by emphasizing controls and checks for accuracy and reliability. New content on validation and verification, model use and sensitivity analysis is presented alongside a modelling methodology that underpins the various proprietary standards used in financial modelling today. It provides more details than other books and yet is general enough for applying its methodology to many applications. This book isn't just about the details of building cash flow models, it's about building better cash flow models. - This new edition increases the number of worked examples and introduces new material on the audit sheet and audit workbook methodologies, and the delta sheet approach to sensitivity analysis - It provides the developer with a toolkit of modelling techniques and a framework of error controls to reduce the risk of spreadsheet error - The methodology and structure conforms with the modelling principles defined by the Institute of Chartered Accountants of England and Wales; and the model assurance processes ensure compliance with the UK public sector Macpherson Report and regulatory requirements such as Sarbanes-Oxley

Part I

The Modelling Environment

Outline

Chapter 1. Quality Assurance

Chapter 2. Quality Control

Chapter 3. Model Structure

Chapter 1

Quality Assurance

Abstract

The financial model plays a central and highly visible rôle in the project finance sector. As the nexus between the raft of contracts, agreements, covenants and operational forecasts these models are examined and reviewed by bankers, accountants, lawyers and project sponsors in a way not seen in corporate modelling. With such a heavy reliance on models this sector has been at the frontline in the development of modelling standards and practices and the implementation of quality assurance systems. This chapter examines the external factors that have influenced quality assurance in spreadsheet modelling and describes the principles of spreadsheet governance and risk controls in the modelling environment that provide a framework for modelling quality control and standards.

Keywords

The Macpherson report; Model audit; Model review; Modelling compliance officer; Modelling standards; Project finance; Public–private Partnerships (PPP, P3); Quality assurance; Quality control; Senior responsible owner; Risk assessment and risk controls

The Modelling Environment

When I wrote the first edition of this book my opinion was that most financial analysts and managers had no idea what a good financial model looked like, nor did they have the relevant skills to prepare one. I was also of the opinion that this was through no fault of their own; most of the time the models, forecasts and budgets they prepared seemed to do the job, and there seemed little incentive to progress. I raised the issues of good practice, quality control and modelling standards, and I hoped that these would be as equally relevant to the realities of life and work in the busy finance department as they would to the glamorous worlds of project and corporate finance. There has been a great deal of progress since and my current view is far less negative: modelling standards and training have received much attention, with the outcome that models and modellers generally are of a much higher standard; but there is still a huge challenge when we provide these models to a nonmodelling audience. There is an increasing recognition on the part of management and others that they simply don’t understand what the model does, and they possess few of the skills required to examine a spreadsheet in a meaningful way. One of our commonest course requests over the last couple of years has been for training on ‘how to understand financial models’.

We have also seen the global financial crisis and many people have attempted to attribute at least some of the blame to the financial models used by the banks and financial institutions. This is clearly wrong: a model is only ever going to be a representation of reality, subject both to the limitations of the inputs supplied to the model, and the calculation rules being used for the analysis. The quantitative modellers learned that lesson with Black-Scholes, and we cash flow modellers followed behind. The model is a tool; potentially a highly sophisticated one, and it is the poor worker who would seek to blame the tool. But we return to the theme of good models being incorrectly used or interpreted: there continues to be a constant flow of stories in the financial press concerning corporate disasters involving financial models. There is also anecdotal evidence to suggest that many cases never appear in the open. But one very public story has had a major impact in the United Kingdom.

The railway system in Britain was broken up and privatised in the 1990s. One route, the InterCity West Coast franchise, was awarded to Sir Richard Branson’s Virgin Trains. In 2012, the British Department for Transport (DfT) put the franchise out to the market in a competitive tendering exercise. To Virgin’s immense surprise they lost to the First Group, and Sir Richard promptly obtained a judicial review to examine the way in which the bids were assessed by the DfT, which subsequently led to the cancellation of the competition. The Laidlaw Enquiry (2012) and the Public Accounts Committee report (2013) made it very clear that modelling, and in particular the interpretation of the modelling, was at the heart of this very public fiasco. The eventual outcome was the Macpherson report¹, an ambitious and overarching approach to modelling standards in the UK public sector, and which will be discussed in some detail in this chapter.

Part of the significance of Macpherson is that in the United Kingdom historically there has been little in the way of imposing a regulatory structure on the development, use and control of models and spreadsheets; and within the financial services industry there is a long tradition of the self-taught amateur, lacking any formal training in the disciplines of financial model development but seemingly capable of doing the job. The National Audit Office (NAO) produces frequent reports into public–private partnership (PPP) projects and often comments on the nature of the models used, but it seems reluctant to suggest that there might be a standard way of producing the complex financial models seen in the sector. This is despite the basic similarity within some of the initiatives and there has long been a feeling in the private sector that some form of standardisation may be appropriate.

The Project Finance Sector

The PPP concept is a public sector approach to government procurement by engaging the private sector in the provision of facilities and services. Developed in the UK originally as the private finance initiative it has evolved into PF2; and this model has been adopted elsewhere in the world where it is known as PPP, or in North America as P3 (and in Canada as alternative finance procurement or AFP). The process is heavily dependent on financial modelling and because of this project finance modelling has in many ways influenced the discussion and debate about modelling standards. The distinctive feature is that the model is used by so many parties – the project bidders, the banks, investors, lawyers, government advisers and the project sponsors, amongst others. Unlike corporate models which stay within the organisation, these models are exposed to considerable external attention. It could be argued that this open and active environment has led to project finance modelling becoming the benchmark for modelling standards generally, in terms of model specification and development, documentation and audit methodologies.

The expectations of PPP models may have improved with increased NAO experience and the maturation of the PPP sector and it would seem that this top-down pressure has fed down through the supply chain, as both private and public sector organisations now clearly realise the critical importance of the financial model. The last decade has seen a growth in the number of firms which can provide the specialist independent financial model audit services that NAO now requires of models submitted in the PPP bid process, but this is extremely unusual outside this particular sector.

The Regulatory Environment

The UK regulatory environment is set out in company law and by the Financial Conduct Authority. At the moment spreadsheets and models might loosely be covered by the various reporting requirements, which would include record keeping, but no formal risk controls have yet bee...

Table of contents

• Cover image
• Title page
• Table of Contents
• Copyright
• Dedication
• About the Author
• Preface to the Third Edition
• Preface to the Second Edition
• Preface to the First Edition
• Acknowledgements
• About Operis Group
• Introduction
• Part I. The Modelling Environment
• Part II. The Modelling Process
• Appendix 1. Good Modelling Practice
• Appendix 2. Keyboard Shortcuts
• Index
• Sync with Jellybooks