Search and Find
Service
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
All prices incl. VAT