1st Edition

Practical Spreadsheet Risk Modeling for Management

    284 Pages 190 B/W Illustrations
    by Chapman & Hall

    284 Pages 190 B/W Illustrations
    by Chapman & Hall

    Risk analytics is developing rapidly, and analysts in the field need material that is theoretically sound as well as practical and straightforward. A one-stop resource for quantitative risk analysis, Practical Spreadsheet Risk Modeling for Management dispenses with the use of complex mathematics, concentrating on how powerful techniques and methods can be used correctly within a spreadsheet-based environment.


    • Covers important topics for modern risk analysis, such as frequency-severity modeling and modeling of expert opinion
    • Keeps mathematics to a minimum while covering fairly advanced topics through the use of powerful software tools
    • Contains an unusually diverse selection of topics, including explicit treatment of frequency-severity modeling, copulas, parameter and model uncertainty, volatility modeling in time series, Markov chains, Bayesian modeling, stochastic dominance, and extended treatment of modeling expert opinion
    • End-of-chapter exercises span eight application areas illustrating the broad application of risk analysis tools with the use of data from real-world examples and case studies

    This book is written for anyone interested in conducting applied risk analysis in business, engineering, environmental planning, public policy, medicine, or virtually any field amenable to spreadsheet modeling. The authors provide practical case studies along with detailed instruction and illustration of the features of ModelRisk®, the most advanced risk modeling spreadsheet software currently available. If you intend to use spreadsheets for decision-supporting analysis, rather than merely as placeholders for numbers, then this is the resource for you.

    Conceptual Maps and Models
    Introductory Case: Mobile Phone Service
    First Steps: Visualization
    Retirement Planning Example
    Good Practices with Spreadsheet Model Construction
    Errors in Spreadsheet Modeling
    Conclusion: Best Practices

    Basic Monte Carlo Simulation in Spreadsheets

    Introductory Case: Retirement Planning
    Risk and Uncertainty
    Scenario Manager
    Monte Carlo Simulation
    Monte Carlo Simulation Using ModelRisk
    Monte Carlo Simulation for Retirement Planning
    Discrete Event Simulation

    Modeling with Objects

    Introductory Case: An Insurance Problem
    Frequency and Severity
    Using Objects in the Insurance Model
    Modeling Frequency/Severity without Using Objects
    Modeling Deductibles
    Using Objects without Simulation
    Multiple Severity/Frequency Distributions
    Uncertainty and Variability

    Selecting Distributions

    First Introductory Case: Valuation of a Public Company—Using Expert Opinion
    Modeling Expert Opinion in the Valuation Model
    Second Introductory Case: Value at Risk—Fitting
    Distributions to Data
    Distribution Fitting for VaR, Parameter Uncertainty, and Model Uncertainty
    Commonly Used Discrete Distributions
    Commonly Used Continuous Distributions
    A Decision Guide for Selecting Distributions
    Bayesian Estimation

    Modeling Relationships

    First Example: Drug Development
    Second Example: Collateralized Debt Obligations
    Multiple Correlations
    Third Example: How Correlated Are Home Prices?—Copulas
    Empirical Copulas
    Fourth Example: Advertising Effectiveness
    Regression Modeling
    Simulation within Regression Models
    Multiple Regression Models
    The Envelope Method

    Time Series Models

    Introductory Case: September 11 and Air Travel
    The Need for Time Series Analysis: A Tale of Two Series
    Analyzing the Air Traffic Data
    Second Example: Stock Prices
    Types of Time Series Models
    Third Example: Oil Prices
    Fourth Example: Home Prices and Multivariate Time Series.
    Markov Chains

    Optimization and Decision Making

    Introductory Case: Airline Seat Pricing
    A Simulation Model of the Airline Pricing Problem
    A Simulation Table to Explore Pricing Strategies
    An Optimization Solution to the Airline Pricing Problem
    Optimization with Simulation
    Optimization with Multiple Decision Variables
    Adding Requirements
    Presenting Results for Decision Making
    Stochastic Dominance

    Appendix A: Monte Carlo Simulation Software

    A Brief Tour of Four Monte Carlo Packages


    Dale Lehman is Professor of Economics and Director of the MBA Program at Alaska Pacific University. He also teaches courses at Danube University and the Vienna University of Technology. He has held positions at a dozen universities and for several telecommunications companies. He holds a B.A. in Economics from SUNY at Stony Brook and M.A. and Ph.D. degrees from the University of Rochester. He has authored numerous articles and two books on topics related to microeconomic theory, decision making under uncertainty, and public policy, particularly concerning telecommunications and natural resources.

    Huybert Groenendaal is a managing partner and senior risk analysis consultant at EpiX Analytics. As a consultant, he helps clients using risk analysis modeling techniques in a broad range of industries. He has extensive experience in risk modeling in business development, financial valuation, and R&D portfolio evaluation within the pharmaceutical and medical device industries, but also works regularly in a variety of other fields, including investment management, health and epidemiology, and inventory management. He also teaches a number of risk analysis training classes, gives guest lectures at a number of universities, and is adjunct professor at Colorado State University. He holds a M.Sc. and Ph.D. from Wageningen University and an MBA in Finance from the Wharton School of Business.

    Greg Nolder is VP of Applied Analytics at Denali Alaskan Federal Credit Union. The mission of the Applied Analytics Department is to promote and improve the application of analytical techniques for measuring and managing risks at Denali Alaskan as well as the greater credit union industry. Along with Huybert, Greg is also an instructor of risk analysis courses for Statistics.com. Prior to Denali Alaskan he has had a varied career including work with EpiX Analytics as a risk analysis consultant for clients from numerous industries, sales engineer, application engineer, test engineer, and air traffic controller. Greg received a M.S. in Operations Research from Southern Methodist University as well as a B.S. in Electrical Engineering and a B.S. in Aviation Technology, both from Purdue University.