Financial Modelling in Practice

Michael Rees gained a BA with First Class Honours and a Doctorate in Mathematics from Oxford University in 1985 and 1988 respectively. In 1992 he gained an MBA with Distinction from INSEAD, and in 2003 graduated in first position on the Certificate in Quantitative Finance program, also winning the Wilmott award.Since 2002 Michael has worked independently as a consultant and trainer in financial modelling. Prior to this he worked as a strategy consultant with Braxton Associates and Mercer Management Consulting, and also as an analyst at J.P. Morgan.Michael lives in Richmond, UK. He was born in Canada, has lived in several countries, and is fluent in French and German.

• Background, Objectives and Approach xvAbout the Author xixAcknowledgements xxi1 Building Blocks: Selected Excel Functions and Tools 1Core Functions for Financial Modelling 1Arithmetic Operations 1Logical Operations 3Financial Calculations 6Database Functions, Features and Pivot Tables 12Statistical Functions 16Lookup and Reference Functions 20Text Functions 29Information Functions 33Array Functions, Formulae and Matrix Calculations 35GoalSeek and Solver 40The Analysis ToolPak and Other Add-ins 43The Analysis ToolPak 44Selected Excel Short-cuts 472 Principles of Modelling 49What is a Good Model? 49Model Design 50Selection of Model Variables and their Dependencies 50Hyper Sensitivity to a Variable 55Level of Detail or Aggregation 55Model Structure and Planning 60Logical Flow 60Compactness 62Named Ranges 64Circular References 69Model Building 74Formatting and Comments 74Creating Robust Formulae 78Results Presentation and Other Uses of Sensitivity Analysis 85General Remarks on Presentation 86Using Data Tables to Conduct Sensitivity Analysis 86Hiding and Protecting Models 92Model Auditing 943 Financial Statement, Cash Flow and Valuation Modelling 99Financial Statement Modelling: Core Points and Example 99General Comments 100Income Statement Forecasting 102Balance Sheet Forecasting 107Cash Flow Statement Forecasting 110Error Checks and Feasibility Checks 111General Error Checking Tools 111Feasibility Checking and Ratio Analysis 113Adding Generality 116Cash Flow Valuation 120Calculation of Free Cash Flow 121Discounting Free Cash Flow 121Terminal Value Calculations 124Further Adjustments 128Sensitivity Analysis 1294 Risk Modelling 133Benefits and Challenges of Risk Modelling 133The Risk Modelling Process 135An Introduction to Simulation Techniques 136The Language of Probability Distributions 137Quick Guide to using @RISK 142Types of Dependency Relationships in Risk Models 144The Selection and Use of Distributions 147Pragmatic Approaches and Distributions 148Data-Driven Approaches and Distributions 153Scientific Approaches and Distributions: The Basics 155Further Aspects of the Science of Distributions 163Further Example Models 1675 Introduction to Options and Real Options Modelling 181Financial Market Derivatives: An Introduction 181Real Options Modelling 188Uses and Relationships to Other Types of Analysis 1896 VBA for Financial Modelling 203Introduction 203The Bare Essentials 204Simple Examples 208Building Blocks 212Working with Ranges 212Writing Robust Code 220Further Topics 224Object Orientation: An Introduction 224Controlling Execution and Related Topics 227Working with Functions 230Checking and Debugging Code 232Examples: Recording Macros and Related Topics 235Introduction 235Using GoalSeek and Solver 237Examples: Simulation Modelling 240Introduction 240Examples: User-defined Functions 243Creating Functions 243Examples 244Structure and Organisation: Further Topics 248Further Reading 253Index 255