Principles of Financial Modelling PDF

Principles of Financial Modelling PDF

Download Principles of Financial Modelling PDF book free by Michael Rees Principles of Financial Modelling – Model Design and Best Practices Using Excel and VBAcovers the full spectrum of financial modelling tools and techniques in order to provide practical skills that are grounded in real-world applications. Buy from Amazon

Principles of Financial Modelling PDF

Based on rigorously-tested materials created for consulting projects and for training courses, this book demonstrates how to plan, design and build financial models that are flexible, robust, transparent, and highly applicable to a wide range of planning, forecasting and decision-support contexts. This book integrates theory and practice to provide a high-value resource for anyone wanting to gain a practical understanding of this complex and nuanced topic. Highlights of its content include extensive coverage of:

  • Model design and best practices, including the optimisation of data structures and layout, maximising transparency, balancing complexity with flexibility, dealing with circularity, model audit and error-checking
  • Sensitivity and scenario analysis, simulation, and optimisation
  • Data manipulation and analysis
  • The use and choice of Excel functions and functionality, including advanced functions and those from all categories, as well as of VBA and its key areas of application within financial modelling

The companion website provides approximately 235 Excel files (screen-clips of most of which are shown in the text), which demonstrate key principles in modelling, as well as providing many examples of the use of Excel functions and VBA macros. These facilitate learning and have a strong emphasis on practical solutions and direct real-world application.

For practical instruction, robust technique and clear presentation, Principles of Financial Modelling is the premier guide to real-world financial modelling from the ground up. It provides clear instruction applicable across sectors, settings and countries, and is presented in a well-structured and highly-developed format that is accessible to people with different backgrounds.

Table of Content

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
viii Contents Principles of Financial Modelling PDF
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 Principles of Financial Modelling PDF
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 Principles of Financial Modelling PDF
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 Principles of Financial Modelling PDF
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
x Contents
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 Principles of Financial Modelling PDF

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
Contents xi
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 Principles of Financial Modelling PDF
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 Principles of Financial Modelling PDF
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 166
Example: Implied Volatility of an Option 167
Example: Minimising Capital Gains Tax Liability 167
Example: Non-linear Curve Fitting 169
CHAPTER 14
Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171
Introduction 171
Practical Applications 172
Example: Running Sensitivity Analysis Using a Macro 172
Example: Running Scenarios Using a Macro 173
Example: Using a Macro to Run Breakeven Analysis
with GoalSeek 173
Example: Using Solver Within a Macro to Create a Frontier of
Optimum Solutions 175
Contents xiii Principles of Financial Modelling PDF
CHAPTER 15
Introduction to Simulation and Optimisation 177
Introduction 177
The Links Between Sensitivity and Scenario Analysis,
Simulation and Optimisation 177
The Combinatorial Effects of Multiple Possible Input Values 177
Controllable Versus Non-controllable: Choice Versus
Uncertainty of Input Values 178
Practical Example: A Portfolio of Projects 179
Description 179
Optimisation Context 180
Risk or Uncertainty Context Using Simulation 180
Further Aspects of Optimisation Modelling 182
Structural Choices 182
Uncertainty 183
Integrated Approaches to Optimisation 183
Modelling Issues and Tools 184

Preface – Principles of Financial Modelling PDF

This text aims to address key topics in the design and building of financial models, so that such models are appropriate to decision support, are transparent and flexible. It aims to address the issues that are generally applicable in many applications, highlighting several core themes: ◾ Building models that meet their decision-support role. ◾ Designing models so that sensitivity analysis can be conducted as relevant, and which meet core requirements for flexibility. ◾ Minimising the complexity, subject to the model meeting the core requirements. ◾ Structuring models to have an effective layout and flow, with audit (dependency) paths as short as possible. ◾ Focusing on the creation of transparency. ◾ Using Excel functions and functionality (and perhaps VBA/macros) in the most effective and appropriate way (requiring one to have a good knowledge of the possibilities and options available). ◾ Employing problem-solving skills in an integrated way. The work is structured into six Parts: ◾ Part I presents a framework to describe modelling processes, discusses the role of models in decision support and summarises some key themes and best practices. ◾ Part II discusses model design, focusing on sensitivity and flexibility requirements, and the optimisation of data structures and layout. ◾ Part III covers the process of building models, focusing on maximising transparency, using the appropriate Excel functions, and creating models which are errorfree and easy to audit. ◾ Part IV covers sensitivity and scenario analysis, simulation and optimisation. ◾ Part V provides practical examples of the use of Excel functions and functionality in financial modelling. ◾ Part VI covers an introduction to VBA and its key areas of application within financial modelling. Note that Part V and Part VI are relevant on a stand-alone basis and could be read before the other Parts. This allows the earlier Parts of the text to focus on the general issues relating to model design, build and use, even as, on occasion, they refer to the later Parts.

Related Books(Free PDF Books)

About the Author

Michael Rees gained a B.A. 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, U.K. He was born in Canada, has lived in several countries, and is fluent in French and German.

More information can be found at www.michaelrees.co.uk.

Download Principles of Financial Modelling PDF

Share this:

Comment