MICHAEL REES, [...]., MBA, operates globally to help senior executives to solve their most complex problems in the areas of decision support, business strategy, value-creation, risk assessment, and optimisation. He combines practical experience from top firms with an exceptional analytic record, and is among the world's leading authors and instructors in the field of financial and risk modelling. His special interest is in cases where issues in strategy, business economics, and valuation are best addressed using practical advanced quantitative approaches.

He has a Doctorate in Mathematical Modelling and Numerical Algorithms, and a B.A. with First Class Honours in Mathematics, both from Oxford University in the UK. He has an MBA with distinction from INSEAD in France. He also studied for the Certificate of Quantitative Finance, graduating top of the class for course work, and receiving the Wilmott Award for the highest final exam mark.

He has approximately 30 years' business and finance experience, in many sectors, including oil, gas, energy and resources, private equity, health care, biotechnology, chemicals, construction, engineering, and insurance.

Preface xxv

About the Author xxvii

About the Website xxix

Part One Introduction to Modelling, Core Themes and Best Practices 1

Chapter 1 Models of Models 3

Introduction 3

Context and Objectives 3

The Stages of Modelling 3

Backward Thinking and Forward Calculation Processes 4

Chapter 2 Using Models in Decision Support 7

Introduction 7

Benefits of Using Models 7

Providing Numerical Information 7

Capturing Influencing Factors and Relationships 7

Generating Insight and Forming Hypotheses 8

Decision Levers, Scenarios, Uncertainties, Optimisation, Risk Mitigation and Project Design 8

Improving Working Processes, Enhanced Communications and Precise Data Requirements 9

Challenges in Using Models 9

The Nature of Model Error 9

Inherent Ambiguity and Circularity of Reasoning 10

Inconsistent Scope or Alignment of Decision and Model 10

The Presence on Biases, Imperfect Testing, False Positives and Negatives 11

Balancing Intuition with Rationality 11

Lack of Data or Insufficient Understanding of a Situation 12

Overcoming Challenges: Awareness, Actions and Best Practices 13

Chapter 3 Core Competencies and Best Practices: Meta-themes 15

Introduction 15

Key Themes 15

Decision-support Role, Objectives, Outputs and Communication 16

Application Knowledge and Understanding 17

Skills with Implementation Platform 17

Defining Sensitivity and Flexibility Requirements 18

Designing Appropriate Layout, Input Data Structures and Flow 20

Ensuring Transparency and Creating a User-friendly Model 20

Integrated Problem-solving Skills 21

Part Two Model Design and Planning 23

Chapter 4 Defining Sensitivity and Flexibility Requirements 25

Introduction 25

Key Issues for Consideration 25

Creating a Focus on Objectives and Their Implications 26

Sensitivity Concepts in the Backward Thought and Forward Calculation

Processes 26

Time Granularity 30

Level of Detail on Input Variables 30

Sensitising Absolute Values or Variations from Base Cases 31

Scenarios Versus Sensitivities 32

Uncertain Versus Decision Variables 33

Increasing Model Validity Using Formulae 34

Chapter 5 Database Versus Formulae-driven Approaches 37

Introduction 37

Key Issues for Consideration 37

Separating the Data, Analysis and Presentation (Reporting) Layers 37

The Nature of Changes to Data Sets and Structures 39

Focus on Data or Formulae? 40

Practical Example 42

Chapter 6 Designing the Workbook Structure 47

Introduction 47

Designing Workbook Models with Multiple Worksheets 47

Linked Workbooks 47

Multiple Worksheets: Advantages and Disadvantages 48

Generic Best Practice Structures 49

The Role of Multiple Worksheets in Best Practice Structures 49

Type I: Single Worksheet Models 50

Type II: Single Main Formulae Worksheet, and Several Data Worksheets 50

Type III: Single Main Formulae Worksheet, and Several Data and Local Analysis Worksheets 51

Further Comparative Comments 51

Using Information from Multiple Worksheets: Choice (Exclusion) and Consolidation (Inclusion) Processes 52

Multi-sheet or "Three Dimensional" Formulae 53

Using Excel's Data/Consolidation Functionality 54

Consolidating from Several Sheets into a Database Using a Macro 55

User-defined Functions 56

Part Three Model Building, Testing and Auditing 57

Chapter 7 Creating Transparency: Formula Structure, Flow and Format 59

Introduction 59

Approaches to Identifying the Drivers of Complexity 59

Taking the Place of a Model Auditor 59

Example: Creating Complexity in a Simple Model 60

Core Elements of Transparent Models 61

Optimising Audit Paths 62

Creating Short Audit Paths Using Modular Approaches 63

Creating Short Audit Paths Using Formulae Structure and Placement 67

Optimising Logical Flow and the Direction of the Audit Paths 68

Identifying Inputs, Calculations and Outputs: Structure and Formatting 69

The Role of Formatting 70

Colour-coding of Inputs and Outputs 70

Basic Formatting Operations 73

Conditional Formatting 73

Custom Formatting 75

Creating Documentation, Comments and Hyperlinks 76

Chapter 8 Building Robust and Transparent Formulae 79

Introduction 79

General Causes of Mistakes 79

Insufficient Use of General Best Practices Relating to Flow, Formatting,

Audit Paths 79

Insufficient Consideration Given to Auditability and Other Potential Users 79

Overconfidence, Lack of Checking and Time Constraints 80

Sub-optimal Choice of Functions 80

Inappropriate Use or Poor Implementation of Named Ranges, Circular

References or Macros 80

Examples of Common Mistakes 80

Referring to Incorrect Ranges or To Blank Cells 80

Non-transparent Assumptions, Hidden Inputs and Labels 82

Overlooking the Nature of Some Excel Function Values 82

Using Formulae Which are Inconsistent Within a Range 83

Overriding Unforeseen Errors with IFERROR 84

Models Which are Correct in Base Case but Not in Others 85

Incorrect Modifications when Working with Poor Models 85

The Use of Named Ranges 85

Mechanics and Implementation 86

Disadvantages of Using Named Ranges 86

Advantages and Key Uses of Named Ranges 90

Approaches to Building Formulae, to Testing, Error Detection and Management 91

Checking Behaviour and Detecting Errors Using Sensitivity Testing 91

Using Individual Logic Steps 93

Building and Splitting Compound Formulae 94

Using Absolute Cell Referencing Only Where Necessary 96

Limiting Repeated or Unused Logic 96

Using Breaks to Test Calculation Paths 97

Using Excel Error Checking Rules 97

Building Error-checking Formulae 98

Handling Calculation Errors Robustly 100

Restricting Input Values Using Data Validation 100

Protecting Ranges 101

Dealing with Structural Limitations: Formulae and Documentation 102

Chapter 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105

Introduction 105

Key Considerations 105

Direct Arithmetic or Functions, and Individual Cells or Ranges? 105

IF Versus MIN/MAX 107

Embedded IF Statements 109

Short Forms of Functions 111

Text Versus Numerical Fields 112

SUMIFS with One Criterion 112

Including Only Specific Items in a Summation 113

AGGREGATE and SUBTOTAL Versus Individual Functions 114

Array Functions or VBA User-defined Functions? 115

Volatile Functions 115

Effective Choice of Lookup Functions 116

Chapter 10 Dealing with Circularity 117

Introduction 117

The Drivers and Nature of Circularities 117

Circular (Equilibrium or Self-regulating) Inherent Logic 117

Circular Formulae (Circular References) 118

Generic Types of Circularities 119

Resolving Circular Formulae 119

Correcting Mistakes that Result in Circular Formulae 120

Avoiding a Logical Circularity by Modifying the Model Specification 120

Eliminating Circular Formulae by Using Algebraic (Mathematical) Manipulation 121

Resolving a Circularity Using Iterative Methods 122

Iterative Methods in Practice 123

Excel's Iterative Method 123

Creating a Broken Circular Path: Key Steps 125

Repeatedly Iterating a Broken Circular Path Manually and Using a VBA Macro 126

Practical Example 128

Using Excel Iterations to Resolve Circular References 129

Using a Macro to Resolve a Broken Circular Path 129

Algebraic Manipulation: Elimination of Circular References 130

Altered Model 1: No Circularity in Logic or in Formulae 130

Altered Model 2: No Circularity in Logic in Formulae 131

Selection of Approach to Dealing with Circularities: Key Criteria 131

Model Accuracy and Validity 132

Complexity and Transparency 133

Non-convergent Circularities 134

Potential for Broken Formulae 138

Calculation Speed 140

Ease of Sensitivity Analysis 140

Conclusions 141

Chapter 11 Model Review, Auditing and Validation 143

Introduction 143

Objectives 143

(Pure) Audit 143

Validation 144

Improvement, Restructuring or Rebuild 145

Processes, Tools and Techniques 146

Avoiding Unintentional Changes 146

Developing a General Overview and Then Understanding the Details 147

Testing and Checking the Formulae 151

Using a Watch Window and Other Ways to Track Values 151

Part Four Sensitivity and Scenario Analysis, Simulation and Optimisation 153

Chapter 12 Sensitivity and Scenario Analysis: Core Techniques 155

Introduction 155

Overview of Sensitivity-related Techniques 155

DataTables 156

Overview 156

Implementation 157

Limitations and Tips 157

Practical Applications 160

Example: Sensitivity of Net Present Value to Growth Rates 160

Example: Implementing Scenario Analysis 160

Chapter 13 Using GoalSeek and Solver 163

Introduction 163

Overview of GoalSeek and Solver 163

Links to Sensitivity Analysis 163

Tips, Tricks and Limitations 163

Practical Applications 164

Example: Breakeven Analysis of a Business 165

Example: Threshold Investment Amounts...