Business Risk and Simulation Modelling in Practice

MICHAEL REES is an independent consultant and trainer for financial modelling. He works for a wide range of clients, including major corporations, private equity firms, fund managers, strategy consultants and risk management consultants.

• Preface xviiAbout the Author xxiiiAbout the Website xxvPart I An Introduction to Risk Assessment – Its Uses, Processes, Approaches, Benefits and ChallengesChapter 1 The Context and Uses of Risk Assessment 31.1 Risk Assessment Examples 31.1.1 Everyday Examples of Risk Management 41.1.2 Prominent Risk Management Failures 51.2 General Challenges in Decision-Making Processes 71.2.1 Balancing Intuition with Rationality 71.2.2 The Presence of Biases 91.3 Key Drivers of the Need for Formalised Risk Assessment in Business Contexts 141.3.1 Complexity 141.3.2 Scale 151.3.3 Authority and Responsibility to Identify and Execute Risk-Response Measures 161.3.4 Corporate Governance Guidelines 161.3.5 General Organisational Effectiveness and the Creation of Competitive Advantage 181.3.6 Quantification Requirements 181.3.7 Reflecting Risk Tolerances in Decisions and in Business Design 191.4 The Objectives and Uses of General Risk Assessment 191.4.1 Adapt and Improve the Design and Structure of Plans and Projects 201.4.2 Achieve Optimal Risk Mitigation within Revised Plans 201.4.3 Evaluate Projects, Set Targets and Reflect Risk Tolerances in Decision-Making 211.4.4 Manage Projects Effectively 211.4.5 Construct, Select and Optimise Business and Project Portfolios 221.4.6 Support the Creation of Strategic Options and Corporate Planning 25Chapter 2 Key Stages of the General Risk Assessment Process 292.1 Overview of the Process Stages 292.2 Process Iterations 302.3 Risk Identification 322.3.1 The Importance of a Robust Risk Identification Step 322.3.2 Bringing Structure into the Process 322.3.3 Distinguishing Variability from Decision Risks 342.3.4 Distinguishing Business Issues from Risks 342.3.5 Risk Identification in Quantitative Approaches: Additional Considerations 352.4 Risk Mapping 352.4.1 Key Objectives 352.4.2 Challenges 352.5 Risk Prioritisation and Its Potential Criteria 362.5.1 Inclusion/Exclusion 362.5.2 Communications Focus 372.5.3 Commonality and Comparison 382.5.4 Modelling Reasons 392.5.5 General Size of Risks, Their Impact and Likelihood 392.5.6 Influence: Mitigation and Response Measures, and Management Actions 402.5.7 Optimising Resource Deployment and Implementation Constraints 412.6 Risk Response: Mitigation and Exploitation 422.6.1 Reduction 422.6.2 Exploitation 422.6.3 Transfer 422.6.4 Research and Information Gathering 432.6.5 Diversification 432.7 Project Management and Monitoring 44Chapter 3 Approaches to Risk Assessment and Quantification 453.1 Informal or Intuitive Approaches 463.2 Risk Registers without Aggregation 463.2.1 Qualitative Approaches 463.2.2 Quantitative Approaches 483.3 Risk Register with Aggregation (Quantitative) 503.3.1 The Benefits of Aggregation 503.3.2 Aggregation of Static Values 513.3.3 Aggregation of Risk-Driven Occurrences and Their Impacts 523.3.4 Requirements and Differences to Non-Aggregation Approaches 543.4 Full Risk Modelling 563.4.1 Quantitative Aggregate Risk Registers as a First Step to Full Models 56Chapter 4 Full Integrated Risk Modelling: Decision-Support Benefits 594.1 Key Characteristics of Full Models 594.2 Overview of the Benefits of Full Risk Modelling 614.3 Creating More Accurate and Realistic Models 624.3.1 Reality is Uncertain: Models Should Reflect This 624.3.2 Structured Process to Include All Relevant Factors 634.3.3 Unambiguous Approach to Capturing Event Risks 634.3.4 Inclusion of Risk Mitigation and Response Factors 664.3.5 Simultaneous Occurrence of Uncertainties and Risks 664.3.6 Assessing Outcomes in Non-Linear Situations 674.3.7 Reflecting Operational Flexibility and Real Options 674.3.8 Assessing Outcomes with Other Complex Dependencies 714.3.9 Capturing Correlations, Partial Dependencies and Common Causalities 734.4 Using the Range of Possible Outcomes to Enhance Decision-Making 744.4.1 Avoiding “The Trap of the Most Likely” or Structural Biases 764.4.2 Finding the Likelihood of Achieving a Base Case 784.4.3 Economic Evaluation and Reflecting Risk Tolerances 824.4.4 Setting Contingencies, Targets and Objectives 834.5 Supporting Transparent Assumptions and Reducing Biases 844.5.1 Using Base Cases that are Separate to Risk Distributions 854.5.2 General Reduction in Biases 854.5.3 Reinforcing Shared Accountability 854.6 Facilitating Group Work and Communication 864.6.1 A Framework for Rigorous and Precise Work 864.6.2 Reconcile Some Conflicting Views 86Chapter 5 Organisational Challenges Relating to Risk Modelling 875.1 “We Are Doing It Already” 875.1.1 “Our ERM Department Deals with Those Issues” 885.1.2 “Everybody Should Just Do Their Job Anyway!” 885.1.3 “We Have Risk Registers for All Major Projects” 895.1.4 “We Run Sensitivities and Scenarios: Why Do More?” 895.2 “We Already Tried It, and It Showed Unrealistic Results” 895.2.1 “All Cases Were Profitable” 905.2.2 “The Range of Outcomes Was Too Narrow” 905.3 “The Models Will Not Be Useful!” 915.3.1 “We Should Avoid Complicated Black Boxes!” 915.3.2 “All Models Are Wrong, Especially Risk Models!” 915.3.3 “Can You Prove that It Even Works?” 925.3.4 “Why Bother to Plan Things that Might Not Even Happen?” 935.4 Working Effectively with Enhanced Processes and Procedures 935.4.1 Selecting the Right Projects, Approach and Decision Stage 935.4.2 Managing Participant Expectations 955.4.3 Standardisation of Processes and Models 955.5 Management Processes, Culture and Change Management 965.5.1 Integration with Decision Processes 965.5.2 Ensuring Alignment of Risk Assessment and Modelling Processes 975.5.3 Implement from the Bottom Up or the Top Down? 985.5.4 Encouraging Issues to Be Escalated: Don’t Shoot the Messenger! 995.5.5 Sharing Accountability for Poor Decisions 995.5.6 Ensuring Alignment with Incentives and Incentive Systems 1005.5.7 Allocation and Ownership of Contingency Budgets 1015.5.8 Developing Risk Cultures and Other Change Management Challenges 102Part II The Design of Risk Models – Principles, Processes and Methodology Chapter 6 Principles of Simulation Methods 1076.1 Core Aspects of Simulation: A Descriptive Example 1076.1.1 The Combinatorial Effects of Multiple Inputs and Distribution of Outputs 1076.1.2 Using Simulation to Sample Many Diverse Scenarios 1106.2 Simulation as a Risk Modelling Tool 1126.2.1 Distributions of Input Values and Their Role 1136.2.2 The Effect of Dependencies between Inputs 1146.2.3 Key Questions Addressable using Risk-Based Simulation 1146.2.4 Random Numbers and the Required Number of Recalculations or Iterations 1156.3 Sensitivity and Scenario Analysis: Relationship to Simulation 1166.3.1 Sensitivity Analysis 1166.3.2 Scenario Analysis 1196.3.3 Simulation using DataTables 1216.3.4 GoalSeek 1216.4 Optimisation Analysis and Modelling: Relationship to Simulation 1226.4.1 Uncertainty versus Choice 1226.4.2 Optimisation in the Presence of Risk and Uncertainty 1296.4.3 Modelling Aspects of Optimisation Situations 1316.5 Analytic and Other Numerical Methods 1336.5.1 Analytic Methods and Closed-Form Solutions 1336.5.2 Combining Simulation Methods with Exact Solutions 1356.6 The Applicability of Simulation Methods 135Chapter 7 Core Principles of Risk Model Design 1377.1 Model Planning and Communication 1387.1.1 Decision-Support Role 1387.1.2 Planning the Approach and Communicating the Output 1387.1.3 Using Switches to Control the Cases and Scenarios 1397.1.4 Showing the Effect of Decisions versus Those of Uncertainties 1407.1.5 Keeping It Simple, but not Simplistic: New Insights versus Modelling Errors 1447.2 Sensitivity-Driven Thinking as a Model Design Tool 1467.2.1 Enhancing Sensitivity Processes for Risk Modelling 1507.2.2 Creating Dynamic Formulae 1517.2.3 Example: Time Shifting for Partial Periods 1537.3 Risk Mapping and Process Alignment 1547.3.1 The Nature of Risks and Their Impacts 1557.3.2 Creating Alignment between Modelling and the General Risk Assessment Process 1567.3.3 Results Interpretation within the Context of Process Stages 1577.4 General Dependency Relationships 1587.4.1 Example: Commonality of Drivers of Variability 1597.4.2 Example: Scenario-Driven Variability 1607.4.3 Example: Category-Driven Variability 1627.4.4 Example: Fading Impacts 1687.4.5 Example: Partial Impact Aggregation by Category in a Risk Register 1707.4.6 Example: More Complex Impacts within a Category 1717.5 Working with Existing Models 1737.5.1 Ensuring an Appropriate Risk Identification and Mapping 1737.5.2 Existing Models using Manual Processes or Embedded Procedures 1747.5.3 Controlling a Model Switch with a Macro at the Start and End of a Simulation 1757.5.4 Automatically Removing Data Filters at the Start of a Simulation 1767.5.5 Models with DataTables 178Chapter 8 Measuring Risk using Statistics of Distributions 1818.1 Defining Risk More Precisely 1818.1.1 General Definition 1818.1.2 Context-Specific Risk Measurement 1818.1.3 Distinguishing Risk, Variability and Uncertainty 1828.1.4 The Use of Statistical Measures 1838.2 Random Processes and Their Visual Representation 1848.2.1 Density and Cumulative Forms 1848.2.2 Discrete, Continuous and Compound Processes 1868.3 Percentiles 1878.3.1 Ascending and Descending Percentiles 1888.3.2 Inversion and Random Sampling 1898.4 Measures of the Central Point 1908.4.1 Mode 1908.4.2 Mean or Average 1918.4.3 Median 1938.4.4 Comparisons of Mode, Mean and Median 1938.5 Measures of Range 1948.5.1 Worst and Best Cases, and Difference between Percentiles 1948.5.2 Standard Deviation 1958.6 Skewness and Non-Symmetry 1998.6.1 The Effect and Importance of Non-Symmetry 2018.6.2 Sources of Non-Symmetry 2028.7 Other Measures of Risk 2038.7.1 Kurtosis 2048.7.2 Semi-Deviation 2058.7.3 Tail Losses, Expected Tail Losses and Value-at-Risk 2068.8 Measuring Dependencies 2078.8.1 Joint Occurrence 2078.8.2 Correlation Coefficients 2098.8.3 Correlation Matrices 2108.8.4 Scatter Plots (X–Y Charts) 2128.8.5 Classical and Bespoke Tornado Diagrams 212Chapter 9 The Selection of Distributions for Use in Risk Models 2159.1 Descriptions of Individual Distributions 2159.1.1 The Uniform Continuous Distribution 2169.1.2 The Bernoulli Distribution 2189.1.3 The Binomial Distribution 2199.1.4 The Triangular Distribution 2209.1.5 The Normal Distribution 2229.1.6 The Lognormal Distribution 2269.1.7 The Beta and Beta General Distributions 2329.1.8 The PERT Distribution 2349.1.9 The Poisson Distribution 2369.1.10 The Geometric Distribution 2389.1.11 The Negative Binomial Distribution 2409.1.12 The Exponential Distribution 2419.1.13 The Weibull Distribution 2429.1.14 The Gamma Distribution 2429.1.15 The General Discrete Distribution 2449.1.16 The Integer Uniform Distribution 2459.1.17 The Hypergeometric Distribution 2459.1.18 The Pareto Distribution 2469.1.19 The Extreme Value Distributions 2469.1.20 The Logistic Distribution 2509.1.21 The Log-Logistic Distribution 2519.1.22 The Student (t), Chi-Squared and F-Distributions 2529.2 A Framework for Distribution Selection and Use 2569.2.1 Scientific and Conceptual Approaches 2579.2.2 Data-Driven Approaches 2589.2.3 Industry Standards 2599.2.4 Pragmatic Approaches: Distributions, Parameters and Expert Input 2599.3 Approximation of Distributions with Each Other 2639.3.1 Modelling Choices 2639.3.2 Distribution Comparison and Parameter Matching 2659.3.3 Some Potential Pitfalls Associated with Distribution Approximations 267Chapter 10 Creating Samples from Distributions 27310.1 Readily Available Inverse Functions 27410.1.1 Functions Provided Directly in Excel 27410.1.2 Functions Whose Formulae Can Easily Be Created 27610.2 Functions Requiring Lookup and Search Methods 27710.2.1 Lookup Tables 27710.2.2 Search Methods 27810.3 Comparing Calculated Samples with Those in @RISK 27910.4 Creating User-Defined Inverse Functions 28010.4.1 Normal Distribution 28110.4.2 Beta and Beta General Distributions 28210.4.3 Binomial Distribution 28310.4.4 Lognormal Distribution 28310.4.5 Bernoulli Distribution 28410.4.6 Triangular Distribution 28410.4.7 PERT Distribution 28410.4.8 Geometric Distribution 28510.4.9 Weibull Distribution 28510.4.10 Weibull Distribution with Percentile Inputs 28510.4.11 Poisson Distribution 28510.4.12 General Discrete Distribution 28710.5 Other Generalisations 28710.5.1 Iterative Methods using Specific Numerical Techniques 28710.5.2 Creating an Add-In 289Chapter 11 Modelling Dependencies between Sources of Risk 29111.1 Parameter Dependency and Partial Causality 29111.1.1 Example: Conditional Probabilities 29311.1.2 Example: Common Risk Drivers 29311.1.3 Example: Category Risk Drivers 29411.1.4 Example: Phased Projects 29411.1.5 Example: Economic Scenarios for the Price of a Base Commodity 29511.1.6 Example: Prices of a Derivative Product 29611.1.7 Example: Prices of Several Derivative Products 29711.1.8 Example: Oil Price and Rig Cost 29711.1.9 Example: Competitors and Market Share 29811.1.10 Example: Resampling or Data-Structure-Driven Dependence 29911.1.11 Implied Correlations within Parameter Dependency Relationships 30211.2 Dependencies between Sampling Processes 30211.2.1 Correlated Sampling 30311.2.2 Copulas 30411.2.3 Comparison and Selection of Parameter-Dependency and Sampling Relationships 30611.2.4 Creating Correlated Samples in Excel using Cholesky Factorisation 30911.2.5 Working with Valid Correlation Matrices 31311.2.6 Correlation of Time Series 31511.3 Dependencies within Time Series 31611.3.1 Geometric Brownian Motion 31711.3.2 Mean-Reversion Models 31911.3.3 Moving Average Models 32111.3.4 Autoregressive Models 32111.3.5 Co-Directional (Integrated) Processes 32311.3.6 Random State Switching and Markov Chains 323Part III Getting Started with Simulation in Practice Chapter 12 Using Excel/VBA for Simulation Modelling 32712.1 Description of Example Model and Uncertainty Ranges 32712.2 Creating and Running a Simulation: Core Steps 32812.2.1 Using Random Values 32812.2.2 Using a Macro to Perform Repeated Recalculations and Store the Results 33012.2.3 Working with the VBE and Inserting a VBA Code Module 33012.2.4 Automating Model Recalculation 33112.2.5 Creating a Loop to Recalculate Many Times 33112.2.6 Adding Comments, Indentation and Line Breaks 33212.2.7 Defining Outputs, Storing Results, Named Ranges and Assignment Statements 33312.2.8 Running the Simulation 33412.3 Basic Results Analysis 33512.3.1 Building Key Statistical Measures and Graphs of the Results 33512.3.2 Clearing Previous Results 33612.3.3 Modularising the Code 33812.3.4 Timing and Progress Monitoring 33912.4 Other Simple Features 33912.4.1 Taking Inputs from the User at Run Time 33912.4.2 Storing Multiple Outputs 34012.5 Generalising the Core Capabilities 34012.5.1 Using Selected VBA Best Practices 34012.5.2 Improving Speed 34112.5.3 Creating User-Defined Functions 34212.6 Optimising Model Structure and Layout 34312.6.1 Simulation Control Sheet 34312.6.2 Output Links Sheet 34412.6.3 Results Sheets 34412.6.4 Use of Analysis Sheets 34612.6.5 Multiple Simulations 34812.7 Bringing it All Together: Examples Using the Simulation Template 35012.7.1 Model 1: Aggregation of a Risk Register using Bernoulli and PERT Distributions 35112.7.2 Model 2: Cost Estimation using Lognormal Distributions 35212.7.3 Model 3: Cost Estimation using Weibull Percentile Parameters 35212.7.4 Model 4: Cost Estimation using Correlated Distributions 35312.7.5 Model 5: Valuing Operational Flexibility 35312.8 Further Possible uses of VBA 35412.8.1 Creating Percentile Parameters 35412.8.2 Distribution Samples as User-Defined Functions 35412.8.3 Probability Samples as User-Defined Array Functions 35512.8.4 Correlated Probability Samples as User-Defined Array Functions 35612.8.5 Assigning Values from VBA into Excel 35812.8.6 Controlling the Random Number Sequence 35912.8.7 Sequencing and Freezing Distribution Samples 36312.8.8 Practical Challenges in using Arrays and Assignment Operations 36412.8.9 Bespoke Random Number Algorithms 36412.8.10 Other Aspects 364Chapter 13 Using @RISK for Simulation Modelling 36513.1 Description of Example Model and Uncertainty Ranges 36513.2 Creating and Running a Simulation: Core Steps and Basic Icons 36613.2.1 Using Distributions to Create Random Samples 36813.2.2 Reviewing the Effect of Random Samples 36913.2.3 Adding an Output 37013.2.4 Running the Simulation 37013.2.5 Viewing the Results 37013.2.6 Results Storage 37313.2.7 Multiple Simulations 37313.2.8 Results Statistics Functions 37413.3 Simulation Control: An Introduction 37713.3.1 Simulation Settings: An Overview 37713.3.2 Static View 37713.3.3 Random Number Generator and Sampling Methods 37913.3.4 Comparison of Excel and @RISK Samples 38113.3.5 Number of Iterations 38213.3.6 Repeating a Simulation and Fixing the Seed 38213.3.7 Simulation Speed 38313.4 Further Core Features 38413.4.1 Alternate Parameters 38413.4.2 Input Statistics Functions 38413.4.3 Creating Dependencies and Correlations 38513.4.4 Scatter Plots and Tornado Graphs 38513.4.5 Special Applications of Distributions 39513.4.6 Additional Graphical Outputs and Analysis Tools 40013.4.7 Model Auditing and Sense Checking 40513.5 Working with Macros and the @RISK Macro Language 40513.5.1 Using Macros with @RISK 40513.5.2 The @RISK Macro Language or Developer Kit: An Introduction 40713.5.3 Using the XDK to Analyse Random Number Generator and Sampling Methods 40913.5.4 Using the XDK to Generate Reports of Simulation Data 41713.6 Additional In-Built Applications and Features: An Introduction 41713.6.1 Optimisation 41913.6.2 Fitting Distributions and Time Series to Data 42013.6.3 MS Project Integration 42113.6.4 Other Features 42113.7 Benefits of @RISK over Excel/VBA Approaches: A Brief Summary 421Index 425