Swan Practical Financial Modelling

The Development and Audit of Cash Flow Models
3. Auflage 2015
ISBN: 978-0-08-100588-0
Verlag: Elsevier Science & Techn.
Format: EPUB
Kopierschutz: 6 - ePub Watermark

E-Book, Englisch, 336 Seiten

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

Jonathan Swan's work has encompassed a range of consultancy and development assignments with investment banks and others in the City and throughout Europe. He is currently Director of Training at Operis Group plc, the UK's leading independent financial modelling consultancy.

Autoren/Hrsg.

Swan, Jonathan

Inhaltsverzeichnis

1;Practical Financial Modelling;4
2;Copyright;5
3;Dedication;6
4;Contents;8
5;About the Author;18
6;Preface to the Third Edition;20
7;Preface to the Second Edition;22
8;Preface to the First Edition;24
9;Acknowledgements;26
10;About Operis Group;28
10.1;CONSULTING AND ADVICE;28
10.2;FINANCIAL MODELLING TRAINING;28
10.3;OPERIS ANALYSIS KIT;29
11;Introduction;30
11.1;THE CONTEXT OF FINANCIAL MODELLING;30
11.2;THREE PRINCIPLES;32
11.3;THIS BOOK;34
11.4;PART 1: THE MODELLING ENVIRONMENT;35
11.5;PART 2: THE MODELLING PROCESS;35
11.6;MICROSOFT EXCEL CONVENTIONS;37
11.7;KEYBOARD SHORTCUTS;38
11.8;FURTHER INFORMATION;39
12;Part I - The Modelling Environment;40
12.1;Chapter 1 - Quality Assurance;42
12.1.1;THE MODELLING ENVIRONMENT;42
12.1.2;RISK ASSESSMENT;55
12.1.3;RISK CONTROLS;55
12.1.4;QUALITY ASSURANCE AND THE MODELLING METHODOLOGY;56
12.2;Chapter 2 - Quality Control;58
12.2.1;INTRODUCTION;58
12.2.2;UNDERSTANDING ERROR;58
12.2.3;ERROR RECOGNITION;61
12.2.4;AUDIT TOOLS AND TECHNIQUES;61
12.2.5;ERROR VALUES;66
12.2.6;THE MODEL REVIEWER AND THE REVIEW PLAN;69
12.2.7;THE AUDIT SHEET;71
12.2.8;ENVIRONMENTAL CHECKS;74
12.2.9;STRUCTURAL CHECKS;78
12.2.10;ARITHMETICAL CHECKS;85
12.2.11;FINANCIAL CHECKS;88
12.2.12;CHANGE CHECKS;92
12.2.13;MODEL MAP;93
12.2.14;USING THE AUDIT SHEET;95
12.2.15;THE AUDIT WORKBOOK;95
12.2.16;THIRD PARTY MODEL AUDIT TOOLS;103
12.3;Chapter 3 - Model Structure;106
12.3.1;INTRODUCTION;106
12.3.2;CHOOSING THE RIGHT TOOL;106
12.3.3;THE THREE PRINCIPLES;107
12.3.4;TWO APPROACHES;107
12.3.5;WORKBOOK STRUCTURE;112
12.3.6;THE INPUTS OR ASSUMPTIONS SHEET;113
12.3.7;THE WORKINGS OR CALCULATIONS SHEET;113
12.3.8;OUTPUTS;115
12.3.9;AUDIT OR CONTROL SHEET;116
12.3.10;VARIATIONS;116
12.3.11;DOCUMENTATION;119
12.3.12;KEY INFORMATION;120
12.3.13;MICROSOFT BACKSTAGE;120
12.3.14;FILE PROPERTIES;122
12.3.15;PRINTING AND REPORTING;125
12.3.16;MODEL DEVELOPMENT;128
12.3.17;NAVIGATION;132
13;Part II - The Modelling Process;138
13.1;Chapter 4 - Referencing and Range Names;140
13.1.1;RELATIVE AND ABSOLUTE REFERENCES;140
13.1.2;R1C1 REFERENCING;141
13.1.3;RANGE NAMES;143
13.1.4;CREATING NAMES;149
13.1.5;DEFINING NAMES;151
13.1.6;NAME MANAGER;151
13.1.7;USING NAMES;152
13.1.8;ADDITIONAL NAME FUNCTIONALITY;160
13.2;Chapter 5 - Mainly Formulas;170
13.2.1;INTRODUCTION;170
13.2.2;BODMAS OR PEMDAS;171
13.2.3;TIMING;171
13.2.4;TECHNICAL FORMULAS;176
13.2.5;CHANGING PERIODICITY;179
13.2.6;CIRCULARITIES AND ITERATION;183
13.2.7;ADVANCED FORMULAS;193
13.3;Chapter 6 - Mainly Functions;196
13.3.1;INTRODUCTION;196
13.3.2;LATE BINDING FUNCTIONS;196
13.3.3;ERROR HANDLING;197
13.3.4;GOOD OLD IF;198
13.3.5;STARTING AGAIN;201
13.3.6;REVOLVING CREDIT;211
13.3.7;LOOKUP;214
13.3.8;LOOKUP WITHOUT USING LOOKUPS;217
13.3.9;THE CONCEPT OF THE POOL;223
13.3.10;DATES;232
13.3.11;FINANCIAL;235
13.3.12;OTHER USEFUL FUNCTIONS;239
13.4;Chapter 7 - Model Use;246
13.4.1;INTRODUCTION;246
13.4.2;RISK CONTROLS;246
13.4.3;PROTECTION;247
13.4.4;GROUPING AND OUTLINING;250
13.4.5;DATA INPUTS;252
13.4.6;CONDITIONAL FORMATTING;258
13.4.7;CUSTOM FORMATTING;262
13.4.8;CHARTING;268
13.5;Chapter 8 - Sensitivity Analysis and Scenarios;270
13.5.1;INTRODUCTION;270
13.5.2;MODEL INTERROGATION;270
13.5.3;SENSITIVITIES;272
13.5.4;SCENARIOS;277
13.5.5;DELTA SHEETS;287
13.5.6;SOLVER;289
13.5.7;RISK;289
13.6;Chapter 9 - Automation;294
13.6.1;INTRODUCTION;294
13.6.2;RECORDED MACROS;296
13.6.3;ASSIGNING MACROS;300
13.6.4;WRITTEN MACROS;303
13.6.5;QUARTERLY/ANNUAL MACRO;306
13.6.6;ERROR HANDLING;307
13.6.7;DEBT SCULPTING;308
13.6.8;DEBUGGING;314
13.6.9;USER-DEFINED FUNCTIONS;317
14;Appendix 1 - Good Modelling Practice;322
14.1;Good Modelling Practice;322
14.1.1;THE PRINCIPLE OF ERROR REDUCTION;322
14.1.2;MODEL RISK FACTORS AND THE RISK ASSESSMENT;322
14.1.3;THE RULES OF GOOD MODELLING;323
15;Appendix 2 - Keyboard Shortcuts;328
15.1;THE RIBBON;328
16;Index;330

Leseproben

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 report1, 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 been imposed. The Institute of Chartered Accountants of England and Wales (ICAEW) has recently published its Twenty Principles for Good Spreadsheet Practice2, an ambitious if slightly anodyne list of good practices for the accounting profession.

The story around the rest of the world is similar. The Basel II requirements concerning operational risk controls, introduced in 2006, were aimed at banks and international financial institutions, and the latest implementation (Basel III, 2010) addresses further risks in the banking sector such as capital adequacy and the stress testing. These may not appear to relate to other areas of the financial sector but as I have previously noted financial models and spreadsheets are being identified as potential risks and the regulatory authorities will become increasingly interested in the controls used in managing the use of such models. These controls can be expected to filter down from the financial sector to other areas of business. I believe that the day of ad hoc spreadsheet development in the financial sector has drawn to a close.

Across the Atlantic the situation changed with the implementation of the Sarbanes–Oxley Act (2002) (SOX). Stringent controls have been imposed on firms in the production of statutory financial statements and reports. Section 404 of SOX relates directly to the controls on the development and maintenance of spreadsheets, and senior management in US organisations had to face up to the fact that the development and use of financial models and spreadsheets had to be properly controlled. This development highlighted the main problem that there had been an historic lack of discipline or rigour involved in preparing and using models and that those involved lack the skills and experience to impose the standards required.

The effects of Basel II and III and SOX are being seen in the UK and in Europe and it would make sense for financial managers to anticipate the cost in time and additional staff resources to their organisations of any regulatory framework that might eventually be imposed on the use of financial models and spreadsheets. Professor Ray Panko3, a noted researcher and commentator on financial modelling issues, has coined the term ‘spreadsheet governance’ to reflect new approach. The objective of SOX (and indeed of senior management)...