Financial Modeling with Crystal Ball and Excel

John Charnes

• Publisher: Wiley
• Year: 2011
• Print ISBN:
  9780471779728
• eBook ISBN:
  9781118161135
• Edition: 1
• Topic: Betriebswirtschaft
• Subtopic: Investitionen & Wertpapiere

Praise for
Financial Modeling with Crystal Ball(r) and Excel(r)

"Professor Charnes's book drives clarity into applied Monte Carlo analysis using examples and tools relevant to real-world finance. The book will prove useful for analysts of all levels and as a supplement to academic courses in multiple disciplines."
-Mark Odermann, Senior Financial Analyst, Microsoft

"Think you really know financial modeling? This is a must-have for power Excel users. Professor Charnes shows how to make more realistic models that result in fewer surprises. Every analyst needs this credibility booster."
-James Franklin, CEO, Decisioneering, Inc.

"This book packs a first-year MBA's worth of financial and business modeling education into a few dozen easy-to-understand examples. Crystal Ball software does the housekeeping, so readers can concentrate on the business decision. A careful reader who works the examples on a computer will master the best general-purpose technology available for working with uncertainty."
-Aaron Brown, Executive Director, Morgan Stanley, author of The Poker Face of Wall Street

"Using Crystal Ball and Excel, John Charnes takes you step by step, demonstrating a conceptual framework that turns static Excel data and financial models into true risk models. I am astonished by the clarity of the text and the hands-on, step-by-step examples using Crystal Ball and Excel; Professor Charnes is a masterful teacher, and this is an absolute gem of a book for the new generation of analyst."
-Brian Watt, Chief Operating Officer, GECC, Inc.

"Financial Modeling with Crystal Ball and Excel is a comprehensive, well-written guide to one of the most useful analysis tools available to professional risk managers and quantitative analysts. This is a must-have book for anyone using Crystal Ball, and anyone wanting an overview of basic risk management concepts."
-Paul Dietz, Manager, Quantitative Analysis, Westar Energy

"John Charnes presents an insightful exploration of techniques for analysis and understanding of risk and uncertainty in business cases. By application of real options theory and Monte Carlo simulation to planning, doors are opened to analysis of what used to be impossible, such as modeling the value today of future project choices."
-Bruce Wallace, Nortel

CHAPTER 1

Introduction

Life is stochastic. Although proponents of determinism might state otherwise, anyone who works in business or finance today knows quite well that future events are highly unpredictable. We often proceed by planning for the worst outcome while hoping for the best, but most of us are painfully aware from experience that there are many risks and uncertainties associated with any business endeavor.

Many analysts start creating financial models of risky situations with a base case constructed by making their best guess at the most likely value for each of the important inputs and building a spreadsheet model to calculate the output values that interest them. Then they account for uncertainty by thinking of how each input in turn might deviate from the best guess and letting the spreadsheet calculate the consequences for the outputs. Such a “what-if” analysis provides insight into the sensitivity of the outputs to one-at-a-time changes in the inputs.

Another common procedure is to calculate three scenarios: best case, worst case and most likely. This is done by inserting the best possible, worst possible, and most likely values for each key input, then calculating the best-case outputs when each input is at its best possible value; the worst-case outputs when each input is at its worst possible value; and using the base case as the most likely scenario. Scenario analysis shows the ranges of possibilities for the outputs, but gives no idea of the likelihood of output values falling between the extremes.

What-if and scenario analysis are good ways to get started, but there are more sophisticated techniques for analyzing and managing risk and uncertainty. This book is designed to help you use the software programs Crystal Ball and Excel to develop financial models for risk analysis. The spreadsheet program Excel has dramatically changed financial analysis in the past 30 years, and Crystal Ball extends the capability of Excel by allowing you to add stochastic assumptions to your spreadsheets. Adding stochastic assumptions provides a clearer picture of the possibilities for each of the outputs of interest. Reading this book and following the examples will help you use Crystal Ball to enhance your risk analysis capabilities.

Throughout the book, I use the word stochastic as a synonym for random or probabilistic, and as an antonym for deterministic. The majority of spreadsheet models in use today are deterministic, but every spreadsheet user knows at some level that there is a degree of uncertainty about each of the inputs to his or her models. Crystal Ball enables you to use a systematic approach to account for uncertainty in your spreadsheet models.

The first six chapters of this book demonstrate how to use Crystal Ball. The remainder of the text provides examples of using Crystal Ball models to help solve problems in corporate finance, investments, and financial risk management. The appendices provide technical details about what goes on under the hood of the Crystal Ball engine.

This chapter is an overview of financial modeling and risk analysis. Some example applications are listed below where these tools provide insights that might not otherwise come to light, and you get a glimpse of how straightforward it is to assess financial risk using Crystal Ball and Excel. For a simple model that is already built and ready to run, we will interpret the output and analyze the model’s sensitivity to changes in its inputs. The chapter concludes with a discussion of the benefits and limitations of risk analysis with Crystal Ball and Excel.

FINANCIAL MODELING

For the purposes of this book, financial modeling is the construction and use of a spreadsheet depiction of a company’s or an individual’s past, present, or future business operations. To learn more about deterministic financial modeling, see Proctor (2004), Sengupta (2004), or Koller, Goedhart, and Wessels (2005). For each situation where we wish to use a stochastic model, we begin with a deterministic Excel model, then add stochastic assumptions with Crystal Ball to generate stochastic forecasts. By analyzing the stochastic forecasts statistically, we can make inferences about the riskiness of the business operations described by the model. The risk analysis process became much easier and more widely available with the introduction of Crystal Ball to the marketplace in 1987.

RISK ANALYSIS

The first recorded instances of risk analysis are the practices of the Asipu people of the Tigris-Euphrates valley about 3200 B.C. (Covello and Mumpower 1985). The Asipu would serve as consultants for difficult decisions such as a proposed marriage arrangement, or the location of a suitable building site. They would list the alternative actions under consideration and collect data on the likely outcomes of each alternative. The priest-like Asipu would interpret signs from the gods, then compare the alternatives systematically. Upon completion of their analysis, they would etch a final report to the client on a clay tablet, complete with a recommendation of the most favorable alternative (Oppenheim 1977).

According to the Oxford English Dictionary (Brown 2002), the term risk analysis means the “systematic investigation and forecasting of risks in business and commerce.” The word risk comes through French, Latin, and Italian from the Greek word rhiza, in reference to sailors navigating among cliffs. Note that although some authorities believe that risk is derived from the Arabian word rizq, meaning “subsistence” it is difficult to explain how this meaning developed into that of “danger” (Klein 1967). If you bought this book to help you analyze business problems, I will bet that you have no trouble seeing the connection between the risks of managing a business and the perils of navigating a sailing vessel around cliffs and barely submerged rocks that can damage the hull and sink the ship.

Imagine an ancient Greek mariner piloting a ship as it approaches a cliff or point of rocks in uncharted waters. Another sailor is on lookout in the crow’s nest at the top of the mast to give the earliest possible warning about how far down into the water an outcrop from the cliff might be. A navigator nearby with sextant and compass is keeping track of where the ship has been and the direction in which it’s headed. His lookout warns him at the first sign of trouble ahead, but it is up to the pilot to decide how wide to take the turn around the cliff. Cutting the corner too close can save time but might sink the ship. Veering far from the edge is safer, but adds costly travel time.

In navigating a strait between two cliffs, the pilot’s decision is even more difficult. Being too far from one cliff can mean being too close to the opposing cliff. The pilot must weigh the risks, use judgment and instinct to carefully choose a course, and then hope for the best as vessel and crew proceed through the strait.

It is the pilot’s job to take all of the available information into account and decide how best to sail the ship in uncharted waters. The pilot wants a clear analysis of all the dangers and opportunities that lie ahead, in order to decide whether the potential time savings of the ship’s chosen course outweigh the disastrous consequences of hull damage. Even though the ship may have been through many different straits in the past, the pilot needs a systematic investigation and forecasting of the risks associated with the planned course through each new strait encountered during the voyage.

If you are running a business (or are an analyst helping to run a business), you are often in situations conceptually similar to those facing the pilot of a sailing vessel in uncharted waters. You know where your business has been, and you are always on the lookout for dangers and opportunities on the horizon. You operate in an environment fraught with uncertainty. You know that future circumstances can affect you and your business greatly, and you want to be prepared for what might happen. In many situations, you need to weigh the favorable and unfavorable consequences of some decision and then choose a course of action. Similar to a ship pilot, it is your job to decide how best to navigate the straits of your business environment. What do you do?

Fortunately, mathematicians such as Simon LaPlace and Blaise Pascal developed the fundamental underpinnings of risk analysis in the seventeenth century by devising the mathematical methods now used in probability theory (Ore 1960). From these precepts came the science of statistics. “What?” you ask, “I studied probability and statistics in college and hated every minute of them. I thought I was done with that stuff. How can it help me?”

In short, probability and statistics help you weigh the potential rewards and punishments associated with the decisions you face. This book shows you how to use Crystal Ball to add probabilistic assumptions and statistical forecasts to spreadsheet models of a wide variety of financial problems. In the end, you still must make decisions based on your best judgment and instincts, but judicious use of the methods of probability and statistics that we go through in this book will help you in several ways.

The modeling process described here enables you to investigate many different possibilities, hone your intuition, and use state-of-the-art software tools that are extremely beneficial for managing risk in dynamic business environments. The risk analysis process forces you to think through the possible consequences of your decisions. This helps you gain comfort that the course of action you select is the best one to take based on the information available at the time you make the decision. Risk analysis is the quantification of the consequences of uncertainty in a situation of interest, and Crystal Ball is the tool for carrying it out.

MONTE CARLO SIMULATION

Risk analysis using Crystal Ball relies on developing a mathematical model in Excel that represents a situation of interest. After you develop the deterministic model, you replace point estimates with probability distribution assumptions and forecast the distribution of the output. The forecasted output distribution is used to assess the riskiness of the situation.

For simple models, the output distribution can be found mathematically to give an analytic solution. For example, consider the simple cost equation

(Total Cost) = $100 + $15 × (Quantity Produced),

where (Quantity Produced) is modelled as a normal probability distribution with mean, μ = 50, and standard deviation, σ = 10, and we want to know the probability that (Total Cost) is greater than $900. We don’t need Crystal Ball for this situation because we can easily obtain an analytic solution.

A result in probability theory holds that if a random variable X follows the normal distribution with mean, μ, and standard deviation, σ, then the random variable Y = a + bX will also be normally distributed with mean, a + bμ, and standard deviation, bσ. Therefore, we can easily determine that (Total Cost) is normally distributed with mean, 100 + (15 × 50) = $850, and standard deviation, 15 × 10 = $150. Using a table of cumulative probabilities for the standard normal distribution, or using the Excel function...

Table of contents

• Cover
• Contents
• Title
• Copyright
• Dedication
• Preface
• Acknowledgments
• About the Author
• Chapter 1: Introduction
• Chapter 2: Analyzing Crystal Ball Forecasts
• Chapter 3: Building a Crystal Ball Model
• Chapter 4: Selecting Crystal Ball Assumptions
• Chapter 5: Using Decision Variables
• Chapter 6: Selecting Run Preferences
• Chapter 7: Net Present Value and Internal Rate of Return
• Chapter 8: Modeling Financial Statements
• Chapter 9: Portfolio Models
• Chapter 10: Value at Risk
• Chapter 11: Simulating Financial Time Series
• Chapter 12: Financial Options
• Chapter 13: Real Options
• Appendix A: Crystal Ball’s Probability Distributions
• Appendix B: Generating Assumption Values
• Appendix C: Variance Reduction Techniques
• Appendix D: About the Download
• Glossary
• References
• Index