Practical Financial Modelling 4
Copyright 5
Dedication 6
Contents 8
About the Author 18
Preface to the Third Edition 20
Preface to the Second Edition 22
Preface to the First Edition 24
Acknowledgements 26
About Operis Group 28
CONSULTING AND ADVICE 28
FINANCIAL MODELLING TRAINING 28
OPERIS ANALYSIS KIT 29
Introduction 30
THE CONTEXT OF FINANCIAL MODELLING 30
THREE PRINCIPLES 32
THIS BOOK 34
PART 1: THE MODELLING ENVIRONMENT 35
PART 2: THE MODELLING PROCESS 35
MICROSOFT EXCEL CONVENTIONS 37
KEYBOARD SHORTCUTS 38
FURTHER INFORMATION 39
Part I - The Modelling Environment 40
Chapter 1 - Quality Assurance 42
THE MODELLING ENVIRONMENT 42
RISK ASSESSMENT 55
RISK CONTROLS 55
QUALITY ASSURANCE AND THE MODELLING METHODOLOGY 56
Chapter 2 - Quality Control 58
INTRODUCTION 58
UNDERSTANDING ERROR 58
ERROR RECOGNITION 61
AUDIT TOOLS AND TECHNIQUES 61
ERROR VALUES 66
THE MODEL REVIEWER AND THE REVIEW PLAN 69
THE AUDIT SHEET 71
ENVIRONMENTAL CHECKS 74
STRUCTURAL CHECKS 78
ARITHMETICAL CHECKS 85
FINANCIAL CHECKS 88
CHANGE CHECKS 92
MODEL MAP 93
USING THE AUDIT SHEET 95
THE AUDIT WORKBOOK 95
THIRD PARTY MODEL AUDIT TOOLS 103
Chapter 3 - Model Structure 106
INTRODUCTION 106
CHOOSING THE RIGHT TOOL 106
THE THREE PRINCIPLES 107
TWO APPROACHES 107
WORKBOOK STRUCTURE 112
THE INPUTS OR ASSUMPTIONS SHEET 113
THE WORKINGS OR CALCULATIONS SHEET 113
OUTPUTS 115
AUDIT OR CONTROL SHEET 116
VARIATIONS 116
DOCUMENTATION 119
KEY INFORMATION 120
MICROSOFT BACKSTAGE 120
FILE PROPERTIES 122
PRINTING AND REPORTING 125
MODEL DEVELOPMENT 128
NAVIGATION 132
Part II - The Modelling Process 138
Chapter 4 - Referencing and Range Names 140
RELATIVE AND ABSOLUTE REFERENCES 140
R1C1 REFERENCING 141
RANGE NAMES 143
CREATING NAMES 149
DEFINING NAMES 151
NAME MANAGER 151
USING NAMES 152
ADDITIONAL NAME FUNCTIONALITY 160
Chapter 5 - Mainly Formulas 170
INTRODUCTION 170
BODMAS OR PEMDAS 171
TIMING 171
TECHNICAL FORMULAS 176
CHANGING PERIODICITY 179
CIRCULARITIES AND ITERATION 183
ADVANCED FORMULAS 193
Chapter 6 - Mainly Functions 196
INTRODUCTION 196
LATE BINDING FUNCTIONS 196
ERROR HANDLING 197
GOOD OLD IF 198
STARTING AGAIN 201
REVOLVING CREDIT 211
LOOKUP 214
LOOKUP WITHOUT USING LOOKUPS 217
THE CONCEPT OF THE POOL 223
DATES 232
FINANCIAL 235
OTHER USEFUL FUNCTIONS 239
Chapter 7 - Model Use 246
INTRODUCTION 246
RISK CONTROLS 246
PROTECTION 247
GROUPING AND OUTLINING 250
DATA INPUTS 252
CONDITIONAL FORMATTING 258
CUSTOM FORMATTING 262
CHARTING 268
Chapter 8 - Sensitivity Analysis and Scenarios 270
INTRODUCTION 270
MODEL INTERROGATION 270
SENSITIVITIES 272
SCENARIOS 277
DELTA SHEETS 287
SOLVER 289
RISK 289
Chapter 9 - Automation 294
INTRODUCTION 294
RECORDED MACROS 296
ASSIGNING MACROS 300
WRITTEN MACROS 303
QUARTERLY/ANNUAL MACRO 306
ERROR HANDLING 307
DEBT SCULPTING 308
DEBUGGING 314
USER-DEFINED FUNCTIONS 317
Appendix 1 - Good Modelling Practice 322
Good Modelling Practice 322
THE PRINCIPLE OF ERROR REDUCTION 322
MODEL RISK FACTORS AND THE RISK ASSESSMENT 322
THE RULES OF GOOD MODELLING 323
Appendix 2 - Keyboard Shortcuts 328
THE RIBBON 328
Index 330