Financial Modeling with Crystal Ball and Excel

John Charnes

• Publisher: Wiley
• Year: 2012
• ISBN:
  9781118240052
• Edition: 2
• Topic: Business
• Subtopic: Investments & Securities
• Index: Business

Updated look at financial modeling and Monte Carlo simulation with software by Oracle Crystal Ball

This revised and updated edition of the bestselling book on financial modeling provides the tools and techniques needed to perform spreadsheet simulation. It answers the essential question of why risk analysis is vital to the decision-making process, for any problem posed in finance and investment. This reliable resource reviews the basics and covers how to define and refine probability distributions in financial modeling, and explores the concepts driving the simulation modeling process. It also discusses simulation controls and analysis of simulation results.

The second edition of Financial Modeling with Crystal Ball and Excel contains instructions, theory, and practical example models to help apply risk analysis to such areas as derivative pricing, cost estimation, portfolio allocation and optimization, credit risk, and cash flow analysis. It includes the resources needed to develop essential skills in the areas of valuation, pricing, hedging, trading, risk management, project evaluation, credit risk, and portfolio management.

• Offers an updated edition of the bestselling book covering the newest version of Oracle Crystal Ball
• Contains valuable insights on Monte Carlo simulation—an essential skill applied by many corporate finance and investment professionals
• Written by John Charnes, the former finance department chair at the University of Kansas and senior vice president of global portfolio strategies at Bank of America, who is currently President and Chief Data Scientist at Syntelli Solutions, Inc. Risk Analytics and Predictive Intelligence Division (Syntelli RAPID)

Engaging and informative, this book is a vital resource designed to help you become more adept at financial modeling and simulation.

CHAPTER 1

Introduction

Life is stochastic. 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 business endeavors. Even engineers who grew accustomed to calculating the precisely correct answer to textbook problems in school now realize that variation plays an important role in real-world problems.

Many analysts begin 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 feeding a spreadsheet model, to calculate the output values that interest them. Often, 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 outputs of interest for each of these scenarios. Such a scenario analysis shows the ranges of possibilities for the outputs, but gives no idea of the likelihood of output values falling between the extremes. Further, the ranges provided by scenario analysis can be misleading because it’s extremely unlikely that all of the inputs will be at their absolute worst (or best) case at the same time.

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 models for risk analysis. The spreadsheet program Excel has dramatically changed financial analysis in the past few decades, 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 should realize that there is some 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 in the following sections, where these tools provide insights that might not otherwise come to light, and you get a glimpse of how easy 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. To learn more about general principles of risk analysis, see Aven (2003), Haimes (2004), or Hertz and Thomas (1984).

1.1 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 (2009), 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.

1.2 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 (2010), 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 outweighs 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’s 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.

1.3 MONTE CARLO SIMULATION

Risk analysis using Crystal Ball relies on developing a mathematical model in Excel that represents the situation of interest. After you develop a 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 determined mathematically to give an analytic solution. For example, consider the simple cost equation

where (Quantity Produced) is modeled 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 distributi...