Table of Contents:
Introduction v
Acknowledgements vi
Chapter 1
Introduction to financial modelling 1
Chapter introduction 1
1.1 What is a financial model? 1
1.1.1 Overview 1
1.1.2 Why do we use models? 1
1.1.3 Elements of a financial model 2
1.1.4 Spreadsheet or financial model? 4
1.1.5 What isn’t a financial model 5
1.2 Types of financial models 6
1.2.1 Integrated financial statement forecast models 6
1.2.2 Valuation models 8
1.2.3 Project finance models 9
1.2.4 Pricing models 10
1.2.5 Reporting models 11
1.3 Modelling tools 12
1.3.1 Spreadsheet software 12
1.3.2 Add-ins for spreadsheet software 13
1.3.3 Other financial modelling software 15
1.3.4 Monte Carlo simulation software 15
1.4 What is a Chartered Accountant’s role? 17
1.4.1 The financial model lifecycle 17
1.4.2 The skills required 19
1.5 The future of modelling and technology 21
1.5.1 Convergence of technology 21
1.5.2 Increasing use of data science 22
1.5.3 Machine learning for developing assumptions 22
Chapter summary 22
References 22
Chapter 2
Best practice principles 23
Chapter introduction 23
2.1 What is best practice? 23
2.1.1 Common themes 24
2.1.2 Common elements 25
2.2 Model structure 25
2.2.1 Model mapping 25
2.2.2 Workbook structure 26
2.2.3 Worksheet design – general principles 30
2.2.4 Formats 33
2.2.5 Formulas 35
2.3 Microsoft Excel functionality 38
2.3.1 Navigating a worksheet 38
2.3.2 Simple functions 38
2.3.3 Logical functions 39
2.3.4 Aggregation functions 40
2.3.5 Lookup functions 42
2.3.6 What-if analysis tools 44
2.4 Keyboard shortcuts 46
Chapter summary 49
References 49
Chapter 3
Model reviews 51
Chapter introduction 51
3.1 What is a model review and why do we do them? 51
3.2 Types of review 52
3.2.1 Using someone else’s financial model 52
3.2.2 Formal model reviews and audits 54
3.3 Excel tools used to automate review processes 57
3.3.1 Formula auditing tools in Excel 57
3.3.2 Error checking tools in Excel 59
3.3.3 Document Inspector tool 59
Chapter summary 63
References 63
Chapter 4
Design and build a financial model 65
Chapter introduction 65
4.1 Design 65
4.1.1 Why are you building the model? 66
4.1.2 How are you going to build the model? 67
4.1.3 What will the model contain? 70
4.1.4 Who will use the model? 76
4.2 Scope 77
4.2.1 Why and how do you document scope for a model? 78
4.2.2 Structure of an example scoping document 78
4.3 Build 79
4.3.1 Example model design 80
4.3.2 The six build stages 81
4.4 Outputs 90
4.4.1 Building detailed outputs 90
4.4.2 Visualisation in modelling 94
4.4.3 Scenario management 100
4.5 ‘How to’ steps for common financial model types 103
4.5.1 One-way forecast cash flow model 104
4.5.2 Two-way forecast cash flow model 104
4.5.3 Three-way forecast cash flow model 107
4.5.4 Four-way forecast cash flow model 110
4.5.5 Budgeting for capital expenditure and funding models 111
4.5.6 Models that include rolling actuals 115
4.5.7 Discounted cash flow valuation model 119
4.5.8 EBITDA multiple valuation 120
4.5.9 Revenue multiple valuation 121
4.5.10 Loan book valuation model 121
4.6 Model maintenance 122
4.6.1 Manage multiple versions of the model 122
4.6.2 Ongoing model maintenance and checking post build 123
4.6.3 Modify versus rebuild 123
4.6.4 Modelling risk 123
4.6.5 Visual Basic for Applications 125
4.6.6 Further reading on tools and technologies for modelling 125
Chapter summary 126
References 126
Index 127
