Principles of Financial Modelling E-Book

Michael Rees

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Finance Series
• Sprache: Englisch

Seitenzahl: 796

Veröffentlichungsjahr: 2018

Beschreibung

The comprehensive, broadly-applicable, real-world guide to financial modelling 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. 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.

Leseprobe

Table of Contents

Cover

Title Page

Preface

About the Author

About the Website

PART One: Introduction to Modelling, Core Themes and Best Practices

CHAPTER 1: Models of Models

INTRODUCTION

CONTEXT AND OBJECTIVES

THE STAGES OF MODELLING

BACKWARD THINKING AND FORWARD CALCULATION PROCESSES

CHAPTER 2: Using Models in Decision Support

INTRODUCTION

BENEFITS OF USING MODELS

CHALLENGES IN USING MODELS

CHAPTER 3: Core Competencies and Best Practices: Meta-themes

INTRODUCTION

KEY THEMES

PART Two: Model Design and Planning

CHAPTER 4: Defining Sensitivity and Flexibility Requirements

INTRODUCTION

KEY ISSUES FOR CONSIDERATION

CHAPTER 5: Database Versus Formulae-driven Approaches

INTRODUCTION

KEY ISSUES FOR CONSIDERATION

PRACTICAL EXAMPLE

CHAPTER 6: Designing the Workbook Structure

INTRODUCTION

GENERIC BEST PRACTICE STRUCTURES

USING INFORMATION FROM MULTIPLE WORKSHEETS: CHOICE (EXCLUSION) AND CONSOLIDATION (INCLUSION) PROCESSES

PART Three: Model Building, Testing and Auditing

CHAPTER 7: Creating Transparency: Formula Structure, Flow and Format

INTRODUCTION

APPROACHES TO IDENTIFYING THE DRIVERS OF COMPLEXITY

OPTIMISING AUDIT PATHS

IDENTIFYING INPUTS, CALCULATIONS AND OUTPUTS: STRUCTURE AND FORMATTING

CREATING DOCUMENTATION, COMMENTS AND HYPERLINKS

CHAPTER 8: Building Robust and Transparent Formulae

INTRODUCTION

GENERAL CAUSES OF MISTAKES

EXAMPLES OF COMMON MISTAKES

THE USE OF NAMED RANGES

APPROACHES TO BUILDING FORMULAE, TO TESTING, ERROR DETECTION AND MANAGEMENT

CHAPTER 9: Choosing Excel Functions for Transparency, Flexibility and Efficiency

INTRODUCTION

KEY CONSIDERATIONS

CHAPTER 10: Dealing with Circularity

INTRODUCTION

THE DRIVERS AND NATURE OF CIRCULARITIES

RESOLVING CIRCULAR FORMULAE

ITERATIVE METHODS IN PRACTICE

PRACTICAL EXAMPLE

SELECTION OF APPROACH TO DEALING WITH CIRCULARITIES: KEY CRITERIA

CHAPTER 11: Model Review, Auditing and Validation

INTRODUCTION

OBJECTIVES

PROCESSES, TOOLS AND TECHNIQUES

PART Four: Sensitivity and Scenario Analysis, Simulation and Optimisation

CHAPTER 12: Sensitivity and Scenario Analysis: Core Techniques

INTRODUCTION

OVERVIEW OF SENSITIVITY-RELATED TECHNIQUES

DATATABLES

PRACTICAL APPLICATIONS

CHAPTER 13: Using GoalSeek and Solver

INTRODUCTION

OVERVIEW OF GOALSEEK AND SOLVER

PRACTICAL APPLICATIONS

CHAPTER 14: Using VBA Macros to Conduct Sensitivity and Scenario Analyses

INTRODUCTION

PRACTICAL APPLICATIONS

CHAPTER 15: Introduction to Simulation and Optimisation

INTRODUCTION

THE LINKS BETWEEN SENSITIVITY AND SCENARIO ANALYSIS, SIMULATION AND OPTIMISATION

PRACTICAL EXAMPLE: A PORTFOLIO OF PROJECTS

FURTHER ASPECTS OF OPTIMISATION MODELLING

CHAPTER 16: The Modelling of Risk and Uncertainty, and Using Simulation

INTRODUCTION

THE MEANING, ORIGINS AND USES OF MONTE CARLO SIMULATION

KEY PROCESS AND MODELLING STEPS IN RISK MODELLING

USING EXCEL AND VBA TO IMPLEMENT RISK AND SIMULATION MODELS

USING ADD-INS TO IMPLEMENT RISK AND SIMULATION MODELS

PART Five: Excel Functions and Functionality

CHAPTER 17: Core Arithmetic and Logical Functions

INTRODUCTION

PRACTICAL APPLICATIONS

CHAPTER 18: Array Functions and Formulae

INTRODUCTION

PRACTICAL APPLICATIONS: ARRAY FUNCTIONS

PRACTICAL APPLICATIONS: ARRAY FORMULAE

CHAPTER 19: Mathematical Functions

INTRODUCTION

PRACTICAL APPLICATIONS

CHAPTER 20: Financial Functions

INTRODUCTION

PRACTICAL APPLICATIONS

OTHER FINANCIAL FUNCTIONS

CHAPTER 21: Statistical Functions

INTRODUCTION

PRACTICAL APPLICATIONS: POSITION, RANKING AND CENTRAL VALUES

PRACTICAL APPLICATIONS: SPREAD AND SHAPE

PRACTICAL APPLICATIONS: CO-RELATIONSHIPS AND DEPENDENCIES

PRACTICAL APPLICATIONS: PROBABILITY DISTRIBUTIONS

PRACTICAL APPLICATIONS: MORE ON REGRESSION ANALYSIS AND FORECASTING

CHAPTER 22: Information Functions

INTRODUCTION

CHAPTER 23: Date and Time Functions

INTRODUCTION

PRACTICAL APPLICATIONS

CHAPTER 24: Text Functions and Functionality

INTRODUCTION

PRACTICAL APPLICATIONS

CHAPTER 25: Lookup and Reference Functions

INTRODUCTION

PRACTICAL APPLICATIONS: BASIC REFERENCING PROCESSES

PRACTICAL APPLICATIONS: FURTHER REFERENCING PROCESSES

PRACTICAL APPLICATIONS: COMBINING MATCHING AND REFERENCE PROCESSES

PRACTICAL APPLICATIONS: MORE ON THE OFFSET FUNCTION AND DYNAMIC RANGES

PRACTICAL APPLICATIONS: THE INDIRECT FUNCTION AND FLEXIBLE WORKBOOK OR DATA STRUCTURES

PRACTICAL EXAMPLES: USE OF HYPERLINKS TO NAVIGATE A MODEL, AND OTHER LINKS TO DATA SETS

CHAPTER 26: Filters, Database Functions and PivotTables

INTRODUCTION

ISSUES COMMON TO WORKING WITH SETS OF DATA

PRACTICAL APPLICATIONS: FILTERS

PRACTICAL APPLICATIONS: DATABASE FUNCTIONS

PRACTICAL APPLICATIONS: PIVOTTABLES

CHAPTER 27: Selected Short-cuts and Other Features

INTRODUCTION

KEY SHORT-CUTS AND THEIR USES

OTHER USEFUL EXCEL TOOLS AND FEATURES

PART Six: Foundations of VBA and Macros

CHAPTER 28: Getting Started

INTRODUCTION

MAIN USES OF VBA

CORE OPERATIONS

SIMPLE EXAMPLES

CHAPTER 29: Working with Objects and Ranges

INTRODUCTION

OVERVIEW OF THE OBJECT MODEL

WORKING WITH RANGE OBJECTS: SOME KEY ELEMENTS

CHAPTER 30: Controlling Execution

INTRODUCTION

CORE TOPICS IN OVERVIEW

PRACTICAL APPLICATIONS

CHAPTER 31: Writing Robust Code

INTRODUCTION

KEY PRINCIPLES

FURTHER APPROACHES TO TESTING, DEBUGGING AND ERROR-HANDLING

CHAPTER 32: Manipulation and Analysis of Data Sets with VBA

INTRODUCTION

PRACTICAL APPLICATIONS

CHAPTER 33: User-defined Functions

INTRODUCTION

BENEFITS OF CREATING USER-DEFINED FUNCTIONS

SYNTAX AND IMPLEMENTATION

PRACTICAL APPLICATIONS

Index

End User License Agreement

List of Illustrations

Chapter 1

FIGURE 1.1 A Generic Framework for Stages of the Modelling Process

FIGURE 1.2 Modelling as a Combination of a Backward Thought Process and a Forward Calculation Process

Chapter 3

FIGURE 3.1 The Best Practice Efficient Frontier: Minimising Complexity for Given Flexibility

Chapter 4

FIGURE 4.1 Initial Approach to Labour Cost Breakdown

FIGURE 4.2 Modified Approach Based on a Possible Sensitivity Driver with Absolute Variation

FIGURE 4.3 Modified Approach Based on a Possible Sensitivity Driver with Absolute and Percentage Variation

FIGURE 4.4 Approach Sensitivity Driver Categories

FIGURE 4.5 Sensitivity Driver Categories with Flexible Data Entry

FIGURE 4.6 Parameter Reduction to Aid Sensitivity Analysis

FIGURE 4.7 Model Built Using Category Totals as Direct Inputs

FIGURE 4.8 Absolute and Percentage Sensitivities

Chapter 5

FIGURE 5.1 Classification of Modelling Situations According to the Focus on Data or on Formulae

FIGURE 5.2 The Reports Worksheet Linking to Specific Cells

FIGURE 5.3 The Feb Data Worksheet, Containing Cost Data for February

FIGURE 5.4 Underlying Data Set as a Single Database

FIGURE 5.5 The Reports Worksheet Using SUMIFS, Database Functions or a PivotTable

Chapter 6

FIGURE 6.1 Generic Best Practice Model Structures

FIGURE 6.2 Structure of Worksheets in the Workbook

FIGURE 6.3 A Typical Data Sheet for the Summing Across Worksheets Example

FIGURE 6.4 Summing Across Ranges of Worksheets

FIGURE 6.5 Using Excel's Data/Consolidation Feature

FIGURE 6.6 Results of Consolidating Without Links to Source Data

FIGURE 6.7 Results of Consolidating with Links to Source Data

Chapter 7

FIGURE 7.1 An Initial Simple and Transparent Model

FIGURE 7.2 Initial Model Without Formatting

FIGURE 7.3 Initial Model Without Formatting and Some Labels

FIGURE 7.4 Restructured Model with Moved Items

FIGURE 7.5 Simple Forecast with Centralised Assumptions

FIGURE 7.6 Centralised Assumptions May Inhibit Copying and Re-using Model Logic

FIGURE 7.7 The Corrected Model and Its Audit Paths for the Centralised Structure

FIGURE 7.8 Centralised Inputs with Transfer Areas to Create Modular Structures

FIGURE 7.9 Audit Paths for Final Model with Centralised Inputs, Transfer Areas and Modular Structures

FIGURE 7.10 Fully Modular Structure with Localised Assumptions

FIGURE 7.11 Reuseable Logic and Short Audit Paths in the Modular Structure

FIGURE 7.12 A Frequent (Often Acceptable) Violation of the “Model as You Read” Principle

FIGURE 7.13 Strict Adherence to the “Model as You Read” Principle May Not Always Be Best from the Perspective of Simplicity and Transparency

FIGURE 7.14 The Optimal Placement of Summary Information May Compromise the Flow Principle

FIGURE 7.15 Example of Conditional Formatting Applied to Dates

FIGURE 7.16 Using Conditional Formatting to Highlight Values Less Than 1%

FIGURE 7.17 Using Conditional Formatting to Highlight the Top Two Values

FIGURE 7.18 Examples of Custom Formatting

Chapter 8

FIGURE 8.1 The Behaviour of Blanks and Text Fields as Model Inputs

FIGURE 8.2 Standard Modelling Approaches Often Use Inconsistent Formulae

FIGURE 8.3 Multi-cell Named Ranges Used to Create Formulae

FIGURE 8.4 Errors Arising as a Formula Using Multi-cell Named Ranges is Moved

FIGURE 8.5 Referring to Full Ranges or Individual Values Within a Multi-cell Named Range

FIGURE 8.6 Possible Application of MAX and SUM Functions to Multi-cell Ranges

FIGURE 8.7 Possible Application of the NPV Function to Multi-cell Ranges

FIGURE 8.8 Sequence of Text Functions Shown as Separate Steps

FIGURE 8.9 Sequence of Text Functions Shown as Separate Steps

FIGURE 8.10 Substitution Process Using a Repeat Starting Cell

FIGURE 8.11 Applying the Compound Formula to a Larger Data Set

FIGURE 8.12 Excel's Error Checking

FIGURE 8.13 A Lower Value-added Error Check

FIGURE 8.14 Example of a Value-added Error Check

FIGURE 8.15 Conditional Formatting Highlights the Presence of an Error

FIGURE 8.16 Custom Criteria with Data Validation

FIGURE 8.17 Model with an Extended Range

Chapter 9

FIGURE 9.1 OR Function with a Contiguous Input Range

FIGURE 9.2 OR Function with Separate Input Cells

FIGURE 9.3 OR Function with a Contiguous Input Range and a Long Audit Path

FIGURE 9.4 Possible Implementations of Allocating an Amount to Either Excess Cash or Additional Borrowings

FIGURE 9.5 Parameters for an Income-allocation Example

FIGURE 9.6 Completed Income-allocation Example

FIGURE 9.7 Core Revenue Forecast for Two Product Lines

FIGURE 9.8 An Example of Using Lookup Functions in Place of Embedded IF Statements

FIGURE 9.9 The SUMPRODUCT Function When Using Short-form Logic

FIGURE 9.10 Direct Cell Reference Approach

FIGURE 9.11 Flexible Identifier Approach

Chapter 10

FIGURE 10.1 Example of a Circular Reference Arising from Implementing Equilibrium Logic

FIGURE 10.2 Illustrative Iterative Process by Using Multiple Copies of a Model

FIGURE 10.3 Results of Allowing Excel to Iterate the Bonus Model Which Contains Circular References

FIGURE 10.4 Creating a Broken Circular Path

FIGURE 10.5 Results After One Paste of the Broken Circular Path Approach

FIGURE 10.6 Results After Two Pastes of the Broken Circular Path Approach

FIGURE 10.7 Resolving a Broken Circular Path Using a Macro

FIGURE 10.8 Using Excel's Iterative Process in a Model with Circular References

FIGURE 10.9 Using a Macro to Resolve Circular References

FIGURE 10.10 Using Algebraic Manipulation to Eliminate Circular References

FIGURE 10.11 Altering the Model to Eliminate Circularity Using Starting Balances

FIGURE 10.12 Altering the Model to Eliminate Circularity Using Starting Balances and Interim Non-interest Cash Flows

FIGURE 10.13 Example of a Divergent Circular Reference

FIGURE 10.14 The Result of One Iteration with a Floating Circularity

FIGURE 10.15 The Result of Two Iterations with a Floating Circularity

FIGURE 10.16 The Result of Three Iterations with a Floating Circularity

FIGURE 10.17 The Values of Each Item at Each Iteration with a Floating Circularity

FIGURE 10.18 Initial Forecast Model

FIGURE 10.19 Modified Forecast Model with a Floating Circularity

FIGURE 10.20 Initial Error Propagation due to a Divergent Circularity

FIGURE 10.21 Errors Remaining After Correction of the Cause of the Divergent Circularity

FIGURE 10.22 Errors Remaining After Partial Rebuild of Model Formulae

FIGURE 10.23 Successful Rebuild of One Column of the Model

FIGURE 10.24 Errors in a Model with a Broken Circular Path

FIGURE 10.25 Simple Process to Correct Errors in a Model with a Broken Circular Path

Chapter 11

FIGURE 11.1 The Go/To Special Menu

FIGURE 11.2 Excel's In-built Error-checking Options

Chapter 12

FIGURE 12.1 Populating a DataTable by Linking Its Values to a Model Input Cell

FIGURE 12.2 Examples of One- and Two-way DataTables

FIGURE 12.3 Combining a DataTable with a CHOOSE Function to Run Scenarios

Chapter 13

FIGURE 13.1 Initial Model with Completed GoalSeek Dialog

FIGURE 13.2 Results of Using Goal Seek for Breakeven Analysis

FIGURE 13.3 Running GoalSeek with a Scaled Target Value for Improved Accuracy

FIGURE 13.4 Using GoalSeek to Determine the Implied Volatility of a European Option

FIGURE 13.5 Model for Calculation of Sales Proceeds and Capital Gains

FIGURE 13.6 Application of Solver to Optimise Sales Allocation

FIGURE 13.7 Results of Running Solver to Determine Optimum Sales Allocation

FIGURE 13.8 Results of Using Solver to Fit a Non-linear Curve to Data

Chapter 14

FIGURE 14.1 Model Before Running the Sensitivity Analysis Macro

FIGURE 14.2 Model After Running the Sensitivity Analysis Macro

FIGURE 14.3 Using a Macro to Run Scenarios

FIGURE 14.4 Use of GoalSeek Within a Macro to Calculate the Price–Volume Breakeven Frontier

FIGURE 14.5 Results of Running Solver with a Macro to Determine Proceeds for Various Tax Thresholds

Chapter 15

FIGURE 15.1 Basic Distinction Between Choice and Uncertainty as an Extension of Traditional Sensitivities

FIGURE 15.2 Variable Launch Dates for Each Project Within a Portfolio

FIGURE 15.3 Using Solver to Determine Optimal Start Dates

FIGURE 15.4 A Random Scenario for Project Start Dates and its Implications for the Calculations

FIGURE 15.5 Distribution of NPV that Results from the Simulation

FIGURE 15.6 Structural and Combinatorial Optimisation

Chapter 16

FIGURE 16.1 Simple Model that Generates Random Samples in Excel and Uses a Macro to Simulate Many Outcomes

FIGURE 16.2 Using @RISK in the Simple Model

Chapter 17

FIGURE 17.1 Data Set Used as a Basis for Several Examples in the Early Part of Chapter 17

FIGURE 17.2 Examples of the IF and AND Functions Using the Formula View

FIGURE 17.3 Examples of the IF and AND Functions Showing the Results

FIGURE 17.4 Examples of the MIN and MAX Functions

FIGURE 17.5 Example of the MAXIFS Functions

FIGURE 17.6 Examples of the COUNT, COUNTA, COUNTIF and Similar Functions

FIGURE 17.7 Examples of the SUM and AVERAGE Functions

FIGURE 17.8 Examples of the SUMIF, SUMIFS, AVERAGEIF and AVERAGIFS Functions

FIGURE 17.9 Using PRODUCT to Calculate Probabilities of Occurrence and Non-occurrence

FIGURE 17.10 Using PRODUCT to Calculate Probabilities of Shared Birthdays

FIGURE 17.11 Using PRODUCT to Calculate Future Values

FIGURE 17.12 Portfolio Returns Using the SUMPRODUCT

FIGURE 17.13 Depreciation Calculations Using SUMPRODUCT

FIGURE 17.14 Function Numbers When Using SUBTOTAL

FIGURE 17.15 Use of SUBTOTAL by Direct Insertion

FIGURE 17.16 Insertion of SUBTOTAL Functions by Category Using the Wizard

FIGURE 17.17 Insertion of SUBTOTAL Functions by Category Using the Wizard

FIGURE 17.18 Final Result After Insertion of SUBTOTAL Functions Using the Wizard

FIGURE 17.19 Possibilities for the Calculations to be Performed by the AGGREGATE Function

FIGURE 17.20 Possibilities for Ignoring Input Data when the AGGREGATE Function Evaluates

FIGURE 17.21 Examples of the Application of the AGGREGATE Function

FIGURE 17.22 Example of the IFERROR Function

FIGURE 17.23 General Example of the SWITCH Function

FIGURE 17.24 Using SWITCH to Change a Text Description into a Number

Chapter 18

FIGURE 18.1 Using TRANSPOSE to Transpose a Data Range

FIGURE 18.2 Using TRANSPOSE Embedded Within a Formula

FIGURE 18.3 Use of TRANSPOSE with SUMPRODUCT in Cost Allocation

FIGURE 18.4 Use of MMULT in Cost Allocation

FIGURE 18.5 Using Array Functions for Matrix Calculations to Calculate Cost Driver Coefficients

FIGURE 18.6 Calculation of Sums of the Powers of Integers Using MINVERSE

FIGURE 18.7 Combining the MIN and IF in an Array Formula to Find Dates of First Cash Flows

FIGURE 18.8 Application of Multiple Criteria Using Array Formulae to Calculate Conditional Maxima and Minima

FIGURE 18.9 Example of Using an Array Formula with AGGREGATE to Calculate the Conditional Maximum of a Data Set

FIGURE 18.10 Using the Non-array Form of the AGGREGATE Function Based on Explicit Individual Calculations

Chapter 19

FIGURE 19.1 Periodic Returns and Reforecast Prices Using LN and EXP

FIGURE 19.2 Periodic Returns and Reforecast Prices Using Traditional Corporate Finance Approach

FIGURE 19.3 Calculating Absolute Values Using ABS, IF, MAX and MIN

FIGURE 19.4 Using the SIGN Function, and Global Error Checking Using ABS

FIGURE 19.5 Examples of INT and Various ROUND-type Functions for Positive and Negative Inputs

FIGURE 19.6 Examples of Using INT and Various ROUND-type Functions to Calculate Quarters

FIGURE 19.7 Using MROUND to Round to Nearest Multiples, and Adapting the Calculations Using ABS and SIGN

FIGURE 19.8 Examples of the CELING.MATH Function

FIGURE 19.9 Using MOD to Allocate Items to Groups

FIGURE 19.10 Examples of the POWER and SQRT Functions and Comparison with Direct Arithmetic

FIGURE 19.11 A Recombining Tree Using Simple Absolute Periodic Price Changes of 10%

FIGURE 19.12 Number of Possibilities to Achieve Each Outcome in the Binomial Tree

FIGURE 19.13 Final Probability of Each Ending State

FIGURE 19.14 Random Possible Price Paths Using RAND()

FIGURE 19.15 Using ASIN to Find the Angle Within a Triangle

FIGURE 19.16 Conversion Between Decimals and Equivalents in Other Numerical Bases

FIGURE 19.17 Conversion Between Arabic and Roman Equivalents

Chapter 20

FIGURE 20.1 Examples of the FVSCHEDULE Function

FIGURE 20.2 Use of FVSCHEDULE to Calculate Total and Average Growth Rates

FIGURE 20.3 Examples of the FV Function

FIGURE 20.4 Examples of the PV Function

FIGURE 20.5 Examples of Uses of PMT, IPMT, PPMT, CUMIPMT and CUMPRINC Functions

FIGURE 20.6 Example of the NPER Function

FIGURE 20.7 Use of NPV and IRR for a Buy-versus-lease Decision

FIGURE 20.8 Results of IRR Function for Some Non-standard Cash Flow Profiles

FIGURE 20.9 Comparison of NPV and IRR When There is a Possibility of Delayed Cash Inflows

FIGURE 20.10 IRR as Function of the Year of Sale of a Positive NPV Project

FIGURE 20.11 Examples of the SLN, DDB and VBB Functions

FIGURE 20.12 Calculation of a Full Depreciation Profile Based on a CapEx Profile

FIGURE 20.13 Use of YIELD Function and Comparison with IRR

FIGURE 20.14 Calculations of Duration, Macaulay Duration and Modified Duration of Cash Flows

FIGURE 20.15 Use of DURATION and MDURATION and Comparison with Explicit Calculations

FIGURE 20.16 Example of PDURATION and Its Equivalent Calculated Directly

Chapter 21

FIGURE 21.1 Use of the MODE-type Functions

FIGURE 21.2 Use of MODE-type Functions in Situation Containing Only Unique Values

FIGURE 21.3 Examples of TRIMMEAN, GEOMEAN and HARMEAN

FIGURE 21.4 Using LARGE and SMALL to Sort and Manipulate Data

FIGURE 21.5 Examples of the RANK.EQ Function

FIGURE 21.6 RANK.EQ and RANK.AVG in the Case of Tied Items

FIGURE 21.7 Use of the PERCENTILE-type and MEDIAN Functions

FIGURE 21.8 Examples of PERCENTRANK Functions

FIGURE 21.9 Using the FREQUENCY Function

FIGURE 21.10 Use of COUNTIFS as an Alternative to FREQUENCY

FIGURE 21.11 Variance and Standard Deviation of Returns Based on a Sample of Data

FIGURE 21.12 Reconciliation of Sample and Population Statistics

FIGURE 21.13 Examples of the DEVSQ and AVEDEV Functions

FIGURE 21.14 Examples of SKEW.S and KURT Functions

FIGURE 21.15 Using an Array Formula to Calculate the Semi-deviation of a Data Set of Returns

FIGURE 21.16 Using Scatter Plots and the Relationship Between Slope and Correlation

FIGURE 21.17 Calculation of Ranked Correlation Using Explicit Steps and an Array Formula

FIGURE 21.18 Calculation of the Covariance Between Two Data Sets Using Various Functions

FIGURE 21.19 Portfolio Volatility Calculation Based on Data for Underlying Assets

FIGURE 21.20 Portfolio Volatility Calculation Based on Data for Weighted Assets

FIGURE 21.21 Use of the BINOM.DIST Function in Density and Cumulative Form

FIGURE 21.22 Use of the BINOM.DIST.RANGE Function in Density, Cumulative and Range Form

FIGURE 21.23 The Normal Distribution and the Frequency of Outcomes in Ranges Around the Mean

FIGURE 21.24 Generating of Random Samples from a Standard Normal Distribution

FIGURE 21.25 Generation of Random Samples from a Weibull Distribution

FIGURE 21.26 Inverse Function for a Binomial Process

FIGURE 21.27 Various Forms of the Inverse Student Distribution Function in Excel

FIGURE 21.28 Confidence Interval for the Mean Based on Sample Data

FIGURE 21.29 Confidence Interval for the Mean Using CONFIDENCE Functions

FIGURE 21.30 Confidence Interval for the Standard Deviation Based on Sample Data

FIGURE 21.31 Confidence Interval for the Slope (Beta) of a Regression Line by Explicit Calculation

FIGURE 21.32 Confidence Interval for the Slope (Beta) of a Regression Line Using LINEST

FIGURE 21.33 Using LINEST to Perform a Multiple Regression and an Associated Forecast

FIGURE 21.34 Use of the LOGEST Function

FIGURE 21.35 Comparison of LOGEST and LINEST Applied to the Logarithm of the Data

FIGURE 21.36 Use of TREND and GROWTH Functions and Comparison with Explicit Calculations

FIGURE 21.37 Use of FORECAST.LINEAR Function and Comparison with Explicit Calculations

FIGURE 21.38 Example of the Use of the FORECAST.ETS Suite

FIGURE 21.39 The Result of Using the Forecast Sheet Feature

Chapter 22

FIGURE 22.1 Use of ISTEXT to Create an In-formula Comment

FIGURE 22.2 Use of ISBLANK to Create Forecasts that Update as Reported Data is Input

FIGURE 22.3 Use of ISBLANK to Detect Inconsistent Data Entries

FIGURE 22.4 Use of the NA() Function

FIGURE 22.5 Overview of INFO and CELL Functions

FIGURE 22.6 Use of CELL Function to Create Updating Labels Referring to the Location of the Data

FIGURE 22.7 Use of CELL to Show the Recalculation Mode

FIGURE 22.8 Use of Release Information to Create Backward Compatible Functions

FIGURE 22.9 Integrated Example of the Use of the INFO, CELL, SHEETS and SHEET Functions

Chapter 23

FIGURE 23.1 Use of Date Calculations and DAYS Function in Task Durations and Resource Estimation

FIGURE 23.2 Using DAYS to Calculate Length of Stay of Hotel Guests

FIGURE 23.3 Using EOMONTH to Create a Time Axis

FIGURE 23.4 Using EDATE to Create a Time Axis

FIGURE 23.5 Example of YEAR and MONTH Function

FIGURE 23.6 Various Ways to Calculate the Quarter of a Given Date

FIGURE 23.7 Creating Reports and Models from Time-based Data Sets

FIGURE 23.8 Examples of the Use of the WEEKDAY Function

FIGURE 23.9 Calculating the Date of the Last Friday of the Month for a Given Date

FIGURE 23.10 Examples of the DATEDIF Function

Chapter 24

FIGURE 24.1 Examples of the CONCAT, TEXTJOIN and Related Functions

FIGURE 24.2 The Second Step of the Convert Text to Columns Wizard

FIGURE 24.3 Using the VALUE and NUMBERVALUE Functions

FIGURE 24.4 Using the LEFT and RIGHT Functions

FIGURE 24.5 Using the MID Function to Extract Part of a Text Field

FIGURE 24.6 Comparing LEFT, RIGHT, MID and LEN

FIGURE 24.7 Using the SEARCH and MID Functions Combined

FIGURE 24.8 Comparing the FIND and SEARCH Functions

FIGURE 24.9 Using the UPPER and LOWER Functions

FIGURE 24.10 Example of the PROPER Function

FIGURE 24.11 Use of the EXACT Function

FIGURE 24.12 Comparing REPLACE with SUBSTITUTE

FIGURE 24.13 Example of the REPT Function

FIGURE 24.14 Example Uses of the CLEAN Function I

FIGURE 24.15 Example Uses of the CLEAN Function II

FIGURE 24.16 Using the TRIM Function

FIGURE 24.17 Updating Model Labels and Graph Titles Using TEXT to Format the Numerical Fields

FIGURE 24.18 Two Data Sets that Can Be Linked by Creating a Common Key

Chapter 25

FIGURE 25.1 Single Cell and Array Formulae use of the ROW and COLUMN Functions

FIGURE 25.2 Example of the ROWS and COLUMNS Function

FIGURE 25.3 Use of ADDRESS and Comparison with CELL

FIGURE 25.4 Finding the Address of Corresponding Cells in Another Worksheet

FIGURE 25.5 Use of the CHOOSE Function to Select the Relevant Scenario Data

FIGURE 25.6 Use of the CHOOSE, INDEX and OFFSET Functions to Select Scenario Data

FIGURE 25.7 Use of Scenarios for Multiple Model Variables

FIGURE 25.8 Use of Scenarios for Non-contiguous Data Sets

FIGURE 25.9 Scenario Approaches to Creating Graphs

FIGURE 25.10 Use of OFFSET to Reverse Time-series Data

FIGURE 25.11 Use of INDEX to Reverse Time-series Data

FIGURE 25.12 Using ROW to Create an Embedded Indexation Field

FIGURE 25.13 Using Lookup Functions to Transpose Data

FIGURE 25.14 Various Methods to Shift Cash Flows over Time

FIGURE 25.15 Using INDEX in a Triangle-type Depreciation Calculation

FIGURE 25.16 Using MATCH to Find the Time at Which Revenues Reach a Target

FIGURE 25.17 Using MATCH to Select Data from Non-contiguous Scenarios

FIGURE 25.18 Using Text Functions and MATCH to Find Data in Currency Database

FIGURE 25.19 Combining INDEX and MATCH Processes

FIGURE 25.20 Using VLOOKUP to Find the Relevant Exchange Rate

FIGURE 25.21 Limitations to Data Structures When Using VLOOKUP

FIGURE 25.22 Error-prone Nature of Hard-coded Column Numbers When Using VLOOKUP

FIGURE 25.23 Using MATCH to Create a Flexible Column Number Within VLOOKUP

FIGURE 25.24 Using INDEX-MATCH in Place of VLOOKUP

FIGURE 25.25 Computation Inefficiency of VLOOKUP When Multiple Items are Looked Up Requiring the Same Underlying Key

FIGURE 25.26 Dependency Tracing when VLOOKUP Functions are Used

FIGURE 25.27 Precedents Tracing when VLOOKUP Functions are Used

FIGURE 25.28 Greater Transparency and Computational Efficiency of an INDEX/MATCH Approach

FIGURE 25.29 Use of HLOOKUP to Select Values from a Table

FIGURE 25.30 Potential Errors when a Row is Inserted Within the HLOOKUP Range

FIGURE 25.31 Using the Vector Form of the LOOKUP Function as an Alternative to INDEX-MATCH

FIGURE 25.32 Finding Closest Matching Values Using Lookup and Array Formula

FIGURE 25.33 Using OFFSET to Sum Between User-defined Cells

FIGURE 25.34 Using OFFSET to Sum Between Calculated Cells

FIGURE 25.35 Using OFFSET to Sum Rows Above

FIGURE 25.36 Creation of a Correlation Matrix Using a Single Formula

FIGURE 25.37 Translation Between Two Fixed Languages

FIGURE 25.38 Translation Between Any Two Languages

FIGURE 25.39 Basic Application of the INDIRECT Function

FIGURE 25.40 Combining INDIRECT with ADDRESS or CELL Functions

FIGURE 25.41 Direct and Indirect Referencing of Data on Another Worksheet

FIGURE 25.42 Use of a Text Argument as a Range Within the INDIRECT Function

FIGURE 25.43 Using INDIRECT to Incorporate Data from a Specified Sheet

FIGURE 25.44 Sequential Drop-downs Using INDIRECT

FIGURE 25.45 Comparison and Use of HYPERLINK Function or Insert/Hyperlink Menu

Chapter 26

FIGURE 26.1 Multiple Row Field Identifiers – To Be Avoided When Using Databases and Tables

FIGURE 26.2 Single Row Field Identifiers – To Be Preferred When Using Databases and Tables

FIGURE 26.3 Part of an Initial Data Set Before Correcting for Spelling Mistakes or Blanks

FIGURE 26.4 Data Filtered to Show Only Incorrect or Blank Entries Within the Country Field

FIGURE 26.5 Functions Applied to a Filtered Data Set

FIGURE 26.6 Functions Applied to a Filtered Data Set with a Hidden Row

FIGURE 26.7 Applying Remove Duplicates to Copied Data

FIGURE 26.8 Unique Items Resulting from Application of Remove Duplicates

FIGURE 26.9 Application of Remove Duplicates to Identify All Unique Combinations

FIGURE 26.10 Unique Combinations Resulting from Application of Remove Duplicates

FIGURE 26.11 Filtering and Selecting of Rows Which Are Desired to be Deleted

FIGURE 26.12 Result of Deleting Specified Rows

FIGURE 26.13 Addition of a New Field to Capture Specific or Complex Criteria

FIGURE 26.14 Defining the Data Range for a Table

FIGURE 26.15 Addition of a New Column to a Table

FIGURE 26.16 Modification of Formulae Using the Header Names Generated Within the Table

FIGURE 26.17 Example of the Implementation of An Advanced Filter

FIGURE 26.18 Database and Parameters Required to Use Database Functions

FIGURE 26.19 Implementation of Selected Database Functions

FIGURE 26.20 Criteria Range Extended by a Column to Implement a Between Query

FIGURE 26.21 Database Functions Do Not Treat Calculated Blanks as True Blanks

FIGURE 26.22 Foundation Structure of a PivotTable

FIGURE 26.23 Creation of PivotTable Row–column and Reporting Structure

FIGURE 26.24 Basic Use of a Filter

FIGURE 26.25 Representation of Report Based on a Copied PivotTable

FIGURE 26.26 Example of Results of Drill-down of An Item in a PivotTable

FIGURE 26.27 Use of a Single Slicer

FIGURE 26.28 Inclusion of a Second Slicer

FIGURE 26.29 Interactions Between the Slicers

FIGURE 26.30 Use of a TimeLine Slicer

FIGURE 26.31 Data Set with a Comment Field

FIGURE 26.32 Using Slicers to Exclude Specific Commented Items

FIGURE 26.33 Use of the GETPIVOTDATA Function

FIGURE 26.34 Example of a Pivot Chart and Alignment of Filters with Its PivotTable

FIGURE 26.35 Data Sets Used for PowerView Example

FIGURE 26.36 Adding a New Workbook Connection I

FIGURE 26.37 Completed List of Connections

FIGURE 26.38 Defining Relationships Between the Tables

FIGURE 26.39 PivotTable Report Based on the DataModel

Chapter 27

FIGURE 27.1 Copying a Formula Without Altering Preset Formatting

FIGURE 27.2 Invoking Function Parameters Within the Formula Bar Using Ctrl+Shift+A

FIGURE 27.3 Building a Formula by Invoking the List of Named Ranges

FIGURE 27.4 Selecting the Current Region of a Cell (Cell D13)

FIGURE 27.5 Use of Short-cuts to Trace Precedents (of cell I8)

FIGURE 27.6 Finding the Last Cell in the Used Range

Chapter 28

FIGURE 28.1 Invoking the Developer Tab

FIGURE 28.2 The Developer Tab

FIGURE 28.3 Core Elements of the Visual Basic Editor

FIGURE 28.4 Recording A Copy/Paste Operation

FIGURE 28.5 Simple Example of Using Values from Excel in VBA

FIGURE 28.6 Results of Running Some Simple Code, with the Message Box Displayed

FIGURE 28.7 Creating Named Ranges in Excel

FIGURE 28.8 Result of Running a Simple Macro to Assign Values from VBA into Excel

FIGURE 28.9 Results of Assignment Operation Instead of Copy/Paste

FIGURE 28.10 Simple Example of a User-defined Function

FIGURE 28.11 Displaying a Message when a Workbook is Opened

Chapter 29

FIGURE 29.1 Highlighting the Row and Column of a Chosen Cell

Chapter 30

FIGURE 30.1 Example of Code for a

For…Next

Loop

Chapter 31

FIGURE 31.1 Results of Passing Arguments

ByRef

and

ByVal

Chapter 32

FIGURE 32.1 Using

CurrentRegion

to Detect the Size of a Range

FIGURE 32.2 Taking User Input About the Location of a Cell or Data Point

FIGURE 32.3 Finding the Full Range of any Single Contiguous Data Set

FIGURE 32.4 Reversing Data and Placing the Results Next to the Original Data

FIGURE 32.5 A Selected Data Set Before the Delete Blank Rows Code is Run

FIGURE 32.6 The Data Set After the Delete Blank Rows Code Has Run

FIGURE 32.7 Deleting All Blank Rows in the Used Range: Before

FIGURE 32.8 Deleting All Blank Rows in the Used Range: After

FIGURE 32.9 Original Data Set Before Application of the Procedure

FIGURE 32.10 Results of Running a Set of Queries of Different Structures

FIGURE 32.11 Generic Model Worksheet Structure

FIGURE 32.12 Example Data Set

FIGURE 32.13 Final Consolidated Data

Chapter 33

FIGURE 33.1 Using VBA's Val to Create a User-defined Function

FIGURE 33.2 Using VBA's StrReverse to Create a User-defined Function

FIGURE 33.3 Using VBA's Split to Create a User-defined Array Function

FIGURE 33.4 Using VBA's Split to Create a User-defined Function to Extract Part of a String

FIGURE 33.5 Wrapper Function to Use the Latest Excel Function Version

FIGURE 33.6 Example of a Consolidated Error Check Using a User-defined Function

FIGURE 33.7 A User-defined Function to Replace Calculation Ranges

FIGURE 33.8 Example of Application.Caller with User-defined Array Functions

FIGURE 33.9 Individual User-defined Functions

FIGURE 33.10 An Array Function Valid Only in the Row Form

FIGURE 33.11 Adapted Array Function that Can Be Entered in Row or Column Form

FIGURE 33.12 Creating a User-defined Functions for Triangle-type Calculations

FIGURE 33.13 Examples of User-defined Functions with Worksheet Names as Inputs

FIGURE 33.14 Functions with Worksheet Names as Inputs and Additional Calculation Capability

FIGURE 33.15 Array Function for the Probability-weighted Moments of a Data Set

FIGURE 33.16 Rank Correlation Using a User-defined Function

FIGURE 33.17 Semi-deviation Using a User-defined Function

Guide

Cover

Table of Contents

Begin Reading

Pages

C1

ii

iii

iv

v

xxv

xxvi

xxvii

xxix

1

3

4

5

6

7

8

9

10

11

12

13

15

16

17

18

19

20

21

23

25

26

27

28

29

30

31

32

33

34

35

37

38

39

40

41

42

43

44

45

47

48

49

50

51

52

53

54

55

56

57

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

143

144

145

146

147

148

149

150

151

153

155

156

157

158

159

160

161

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

199

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

243

244

245

246

247

248

249

250

251

252

253

254

255

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

387

388

389

390

391

392

393

394

395

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

413

414

415

416

417

418

419

420

421

422

423

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

E1

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers' professional and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation and financial instrument analysis, as well as much more.

For a list of available titles, visit our Web site at www.WileyFinance.com.

Principles of Financial Modelling

Model Design and Best Practices using Excel and VBA

MICHAEL REES

This edition first published 2018

© 2018 John Wiley & Sons, Ltd

Registered office

John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ, United Kingdom

For details of our global editorial offices, for customer services and for information about how to apply for permission to reuse the copyright material in this book please see our website at www.wiley.com.

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, except as permitted by the UK Copyright, Designs and Patents Act 1988, without the prior permission of the publisher.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Designations used by companies to distinguish their products are often claimed as trademarks. All brand names and product names used in this book are trade names, service marks, trademarks or registered trademarks of their respective owners. The publisher is not associated with any product or vendor mentioned in this book.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. It is sold on the understanding that the publisher is not engaged in rendering professional services and neither the publisher nor the author shall be liable for damages arising herefrom. If professional advice or other expert assistance is required, the services of a competent professional should be sought.

Library of Congress Cataloging-in-Publication Data is Available:

ISBN 978-1-118-90401-5 (hardback)        ISBN 978-1-118-90400-8 (ePub)

ISBN 978-1-118-90394-0 (ePDF)

Cover Design: Wiley

Cover Images: © AmbientShoot/Shutterstock;

© whiteMocca/Shutterstock

“To my mother, father and the Godsall and Rees families”

Preface

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 error-free 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.

This text builds on some key principles that were also a core aspect of the author's earlier work Financial Modelling in Practice: A Concise Guide for Intermediate and Advanced Level (John Wiley & Sons, 2008), especially that of using sensitivity thought processes as a model design tool. However, the discussion here is more extensive and detailed, reflecting the author's enhanced view of these topics that has been gained through the decade since the publication of the earlier work. Indeed, this text is approximately three times the length of that of the corresponding elements of the earlier work (i.e. of Chapters 1, 2 and 6 in that work). Note that, unlike the earlier work, this text does not aim to treat specific applications in depth (such as financial statements, valuation, options and real options). Further, the topic of risk, uncertainty and simulation modelling is covered only briefly, since the author's Business Risk Modelling in Practice: Using Excel, VBA and @RISK (John Wiley & Sons, 2015) provides a detailed treatment of this topic.

The website associated with this text contains approximately 235 Excel files (screen-clips of most of which are shown in the text). These are an integral part of this work, and it will generally be necessary to refer to these files in order to gain the maximum benefit from reading this text.

About the Author

Michael Rees has a Doctorate in Mathematical Modelling and Numerical Algorithms and a B.A. with First Class Honours in Mathematics, both from Oxford University. He has an MBA with Distinction from INSEAD in France. In addition, he studied for the Wilmott Certificate of Quantitative Finance, where he graduated in first place for coursework and received the Wilmott Award for the highest final exam mark.

Since 2002, he has worked as an independent expert in quantitative decision support, financial modelling, economic, risk and valuation modelling, providing training, model-building and advisory services to a wide range of corporations, consulting firms, private equity businesses and training companies.

Prior to becoming independent, Michael was employed at J.P. Morgan, where he conducted valuation and research work, and prior to that he was a Partner with strategy consultants Mercer Management Consulting, both in the U.K. and in Germany. His earlier career was spent at Braxton Associates (a boutique strategy consulting firm that became part of Deloitte and Touche), where he worked both in the UK and as a founding member of the start-up team in Germany.

Michael is a dual UK/Canadian citizen. He is fluent in French and German, and has wide experience of working internationally and with clients from diverse cultural backgrounds. In additional to this text, he is the author of Financial Modelling in Practice: A Concise Guide to Intermediate and Advanced Level (John Wiley & Sons, 2008), Business Risk and Simulation Modelling in Practice (John Wiley & Sons, 2015), a contributing author to The Strategic CFO: Creating Value in a Dynamic Market Environment (Springer, 2012) and has also contributed articles to the Wilmott Magazine.

About the Website

This book is accompanied by a companion website which can be accessed at www.principlesoffinancialmodelling.com (Password hint: The last word in Chapter 5).

The website includes:

237 PFM models (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.

PART OneIntroduction to Modelling, Core Themes and Best Practices

CHAPTER 1Models of Models

INTRODUCTION

This chapter provides an overview of financial modelling, including its objectives, stages and processes. The discussion sets the context and frameworks that are used in much of the subsequent text.

CONTEXT AND OBJECTIVES

A model is a numerical or mathematical representation of a real-life situation. A financial model is one which relates to business and finance contexts. The typical objectives of financial modelling include to support decisions relating to business plans and forecasts, to the design, evaluation and selection of projects, to resource allocation and portfolio optimisation, to value corporations, assets, contracts and financial instruments, and to support financing decisions.

In fact, there is no generally accepted (standardised) definition of financial modelling. For some, it is a highly pragmatic set of activities, essentially consisting of the building of Excel worksheets. For others, it is a mainly conceptual activity, whose focus is on the use of mathematical equations to express the relationships between the variables in a system, and for which the platform (e.g. Excel) that is used to solve such equations is not of relevance. In this text, we aim to integrate theory and practice as much as possible.

THE STAGES OF MODELLING

The modelling process can be considered as consisting of several stages, as shown in Figure 1.1.

FIGURE 1.1 A Generic Framework for Stages of the Modelling Process

The key characteristics of each stage include:

Specification: This involves describing the real-life situation, either qualitatively or as a set of equations. In any case, at this stage one should also consider the overall objectives and decision-making needs, and capture the core elements of the behaviour of the real-world situation. One should also address issues relating to the desired scope of model validity, the level of accuracy required and the trade-offs that are acceptable to avoid excessive complexity whilst providing an adequate basis for decision support.

Implementation: This is the process to translate the specification into numerical values, by conducting calculations based on assumed input values. For the purposes of this text, the calculations are assumed to be in Excel, perhaps also using additional compatible functionality (such as VBA macros, Excel add-ins, optimisation algorithms, links to external databases and so on).

Decision support: A model should appropriately support the decision. However, as a simplification of the real-life situation, a model by itself is almost never sufficient. A key challenge in building and using models to greatest effect is to ensure that the process and outputs provide a value-added decision-support guide (not least by providing insight, reducing biases or correcting invalid assumptions that may be inherent in less-rigorous decision processes), whilst recognising the limitations of the model and the modelling process.

Note that in many practical cases, no explicit specification step is conducted; rather, knowledge of a situation is used to build an Excel workbook directly. Since Excel does not calculate incorrectly, such a model can never truly be “(externally) validated”: the model specification is the model itself (i.e. as captured within the formulae used in Excel). Although such “self-validation” is in principle a significant weakness of these pragmatic approaches, the use of a highly formalised specification stage is often not practical (especially if one is working under tight deadlines, or one believes that the situation is generally well-understood). Some of the techniques discussed in this text (such as sensitivity-driven model design and the following of other best practices) are particularly important to support robust modelling processes, even where little or no documented specification has taken place or is practically possible.

BACKWARD THINKING AND FORWARD CALCULATION PROCESSES

The modelling process is essentially two-directional (see Figure 1.2):

FIGURE 1.2 Modelling as a Combination of a Backward Thought Process and a Forward Calculation Process

A “backward thought process”, in which one considers a variable of interest (the model output) and defines its underlying, or causal, factors. This is a qualitative process, corresponding to reading

Figure 1.2

from left to right. For example, cash flow may be represented as being determined from revenue and cost, each of which may be determined by their own causal factors (e.g. revenue is determined by price and volume). As a qualitative process, at this stage, the precise the nature of the relationships may not yet be made clear: only that the relationships exist.

A “forward-calculation process”, in which one which starts with the assumed values of the final set of causal factors (the “model inputs”) and builds the required calculations to determine the values of the intermediate variables and final outputs. This is a numerical process corresponding to reading

Figure 1.2

from right to left. It involves defining the nature of the relationships sufficiently precisely that they can be implemented in quantitative formulae. That is, inputs are used to calculate the intermediate variables, which are used to calculate the outputs. For example, revenue would be calculated (from an assumed price and volume), and cost (based on fixed and variable costs and volume), with the cash flow as the final output.

Note that the process is likely to contain several iterations: items that may initially be numerical inputs may be chosen to be replaced by calculations (which are determined from new numerical inputs), thus creating a model with more input variables and detail. For example, rather than being a single figure, volume could be split by product group. In principle, one may continue the process indefinitely (i.e. repeatedly replacing hard-coded numerical inputs with intermediate calculations). Of course, the potential process of creating more and more detail must stop at some point:

For the simple reason of practicality.

To ensure accuracy. Although the creation of more detail would lead one to expect to have a more accurate model, this is not always the case: a detailed model will require more information to calibrate correctly (for example, to estimate the values of all the inputs). Further, the capturing of the relationships between these inputs will become progressively more complex as more detail is added.

The “optimal” level of detail at which a model should be built is not a trivial question, but is discussed further in Chapter 4.

It may be of interest to note that this framework is slightly simplified (albeit covering the large majority of cases in typical Excel contexts):

In some applications (notably sequential optimisation of a time series, and decision trees), the calculations are required to be conducted both forward and backward, as the optimal behaviour at an earlier time depends on considering all the future consequences of each potential decision.

In econometrics, some equations may be of an equilibrium nature, i.e. they contain the same variable(s) on both sides of an equation(s). In such cases, the logic flow is not directional, and will potentially give rise to circular references in the implemented models.

CHAPTER 2Using Models in Decision Support

INTRODUCTION

This chapter summarises the main benefits and challenges of using models in decision support. Where significant amounts of money are at stake, or the choice of the most appropriate decision option is important for some other reason, it is often taken as a given that the building of a model would be useful. However, it is important to understand the specific sources of benefits, and the challenges and potential weaknesses of modelling processes. Doing so will help to support a more robust basis for decision-making, and reduce the likelihood that the outputs are misinterpreted, misused, or assumed to apply to a context for which the model was not designed.

BENEFITS OF USING MODELS

This section highlights the key benefits potentially achievable by the use of models.

Providing Numerical Information

A model calculates the possible values of variables that are considered important in the context of the decision at hand. Of course, this information is often of paramount importance, especially when committing resources, budgeting and so on.

Nevertheless, the calculation of the numerical values of key variables is not the only reason to build models; the modelling process often has an important exploratory and insight-generating aspect (see later in this section). In fact, many insights can often be generated early in the overall process, whereas numerical values tend to be of most use later on.

Capturing Influencing Factors and Relationships

The process of building a model should force a consideration of which factors influence the situation, including which are most important. Whilst such reflections may be of an intuitive or qualitative nature (at the early stages), much insight can be gained through the use of a quantitative process. The quantification of the relationships requires one to consider the nature of the relationships in a very precise way (e.g. whether a change in one would impact another and by how much, whether such a change is linear or non-linear, whether other variables are also affected, or whether there are (partially) common causal factors between variables, and so on).

Generating Insight and Forming Hypotheses

The modelling process should highlight areas where one's knowledge is incomplete, what further actions could be taken to improve this, as well as what data is needed. This can be valuable in its own right. In fact, a model is effectively an explicit record of the assumptions and of the (hypothesised) relationships between items (which may change as further knowledge is developed). The process therefore provides a structured approach to develop a better understanding. It often uncovers many assumptions that are being made implicitly (and which may be imprecisely understood or incorrect), as well as identifying the assumptions that are required and appropriate. As such, both the qualitative and the quantitative aspects of the process should provide new insights and identify issues for further exploration.

The overlooking or underestimation of these exploratory aspects is one of the main inefficiencies in many modelling processes, which are often delegated to junior staff who are competent in “doing the numbers”, but who may not have the experience, or lack sufficient project exposure, authority, or the credibility to identify and report many of the key insights, especially those that may challenge current assumptions. Thus, many possible insights are either lost or are simply never generated in the first place. Where a model produces results that are not readily explained intuitively, there are two generic cases:

It is over-simplified, highly inaccurate or wrong in some important way. For example, key variables may have been left out, dependencies not correctly captured, or the assumptions used for the values of variables may be wrong or poorly estimated.

It is essentially correct, but provides results which are not intuitive. In such situations, the modelling process can be used to adapt, explore and generate new insights, so that ultimately both the intuition and the model's outputs become aligned. This can be a value-added process, particularly if it highlights areas where one's initial intuition may be lacking.

In this context, the following well-known quotes come to mind:

“Plans are useless, but planning is everything” (Eisenhower).

“Every model is wrong, some are useful” (Box).

“Perfection is the enemy of the good” (Voltaire).

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

When conducted rigorously, the modelling process distinguishes factors which are controllable from those which are not. It may also highlight that some items are partially controllable, but require further actions that may not (currently) be reflected in the planning nor in the model (e.g. the introduction of risk mitigation actions). Ultimately, controllable items correspond to potential decisions that should be taken in an optimal way, and non-controllable items are those which are risky or subject to uncertainty. The use of sensitivity, scenario and risk techniques can also provide insight into the extent of possible exposure if a decision were to proceed as planned, lead to modifications to the project or decision design, and allow one to find an optimal decision or project structure.

Improving Working Processes, Enhanced Communications and Precise Data Requirements

A model provides a structured framework to take information from subject matter specialists or experts. It can help to define precisely the information requirements, which improves the effectiveness of the research and collection process to obtain such information. The overall process and results should also help to improve communications, due to the insights and transparency generated, as well as creating a clear structure for common working and co-ordination.

CHALLENGES IN USING MODELS

This section highlights the key challenges faced when using models in decision support.

The Nature of Model Error

Models are, by nature, simplifications of (and approximations to) the real-world. Errors can be introduced at each stage (as presented in Figure 1.1):