Financial Modelling in Practice

Background, Objectives and Approach xv

About the Author xix

Acknowledgements xxi

1 Building Blocks: Selected Excel Functions and Tools 1

Core Functions for Financial Modelling 1

Arithmetic Operations 1

Logical Operations 3

Financial Calculations 6

Database Functions, Features and Pivot Tables 12

Statistical Functions 16

Lookup and Reference Functions 20

Text Functions 29

Information Functions 33

Array Functions, Formulae and Matrix Calculations 35

GoalSeek and Solver 40

The Analysis ToolPak and Other Add-ins 43

The Analysis ToolPak 44

Selected Excel Short-cuts 47

2 Principles of Modelling 49

What is a Good Model? 49

Model Design 50

Selection of Model Variables and their Dependencies 50

Hyper Sensitivity to a Variable 55

Level of Detail or Aggregation 55

Model Structure and Planning 60

Logical Flow 60

Compactness 62

Named Ranges 64

Circular References 69

Model Building 74

Formatting and Comments 74

Creating Robust Formulae 78

Results Presentation and Other Uses of Sensitivity Analysis 85

General Remarks on Presentation 86

Using Data Tables to Conduct Sensitivity Analysis 86

Hiding and Protecting Models 92

Model Auditing 94

3 Financial Statement, Cash Flow and Valuation Modelling 99

Financial Statement Modelling: Core Points and Example 99

General Comments 100

Income Statement Forecasting 102

Balance Sheet Forecasting 107

Cash Flow Statement Forecasting 110

Error Checks and Feasibility Checks 111

General Error Checking Tools 111

Feasibility Checking and Ratio Analysis 113

Adding Generality 116

Cash Flow Valuation 120

Calculation of Free Cash Flow 121

Discounting Free Cash Flow 121

Terminal Value Calculations 124

Further Adjustments 128

Sensitivity Analysis 129

4 Risk Modelling 133

Benefits and Challenges of Risk Modelling 133

The Risk Modelling Process 135

An Introduction to Simulation Techniques 136

The Language of Probability Distributions 137

Quick Guide to using @RISK 142

Types of Dependency Relationships in Risk Models 144

The Selection and Use of Distributions 147

Pragmatic Approaches and Distributions 148

Data-Driven Approaches and Distributions 153

Scientific Approaches and Distributions: The Basics 155

Further Aspects of the Science of Distributions 163

Further Example Models 167

5 Introduction to Options and Real Options Modelling 181

Financial Market Derivatives: An Introduction 181

Real Options Modelling 188

Uses and Relationships to Other Types of Analysis 189

6 VBA for Financial Modelling 203

Introduction 203

The Bare Essentials 204

Simple Examples 208

Building Blocks 212

Working with Ranges 212

Writing Robust Code 220

Further Topics 224

Object Orientation: An Introduction 224

Controlling Execution and Related Topics 227

Working with Functions 230

Checking and Debugging Code 232

Examples: Recording Macros and Related Topics 235

Introduction 235

Using GoalSeek and Solver 237

Examples: Simulation Modelling 240

Introduction 240

Examples: User-defined Functions 243

Creating Functions 243

Examples 244

Structure and Organisation: Further Topics 248

Further Reading 253

Index 255