CHAPTER 15 Introduction to Simulation Modeling

REAL APPLICATIONS OF SIMULATION WITH @RISK This chapter introduces Palisade’s @RISK add-in for simulation modeling in Excel. @ RISK is not only for academic use. Palisade has trained numerous well-known compa- nies in the use of @RISK, and its website chronicles how many of these companies have used @RISK in their businesses. Here are a few of these applications.

• Merck, the multinational pharmaceutical company, recognizes the importance of value-at-risk in its risk management programs. (Value-at-risk, defined in the next chap- ter, is nearly the worst that can happen.) Merck is also aware that exchange rate volatil- ity is one of the largest components of its value-at-risk. @RISK provides the flexibility to fit and evaluate alternative distributions of currency rates. The company must man- age currency exposures in both the balance sheet and in future revenues. Evidently, simulating currency risks on the balance sheet is relatively straightforward. However, simulating hedged cash flow currency risk presents challenges because of accounting standards for derivative investments. It requires a model that can project economic and accounting hedge performance through time. This involves many uncertain variables, including option time decay and the volatility of option price components. @RISK has the power to handle this complexity, and for this reason, @RISK is Merck’s analytic tool of choice.

• Benjamin Waisbren uses @RISK in all his negotiations. Waisbren is president of LSC Film Corporation, a company that provides the funding for major film productions, such as V for Vendetta, Blood Diamond, and 300. He recently used @RISK to complete a $200 million deal with Sony, giving his team, LStar Capital, a stake in nearly all mov- ies produced by Sony. He estimates that because of @RISK modeling, closing costs on this deal were about $20 million lower than on similar deals. Since then, he has used @RISK to perform statistical analysis on risks in the motion picture business. This has led to surprisingly accurate predictions of how much a film will make on its opening day or weekend, as well as the amount film production will cost in any given number of months. Trained as a lawyer, Waisbren urges other law firms to train their employees in @RISK, arguing that this can save them huge amounts in complex negotiations.

Ti np

ix el

s/ E+

/G et

ty Im

ag es

09953_ch15_ptg01_717-778.indd 717 04/03/19 4:04 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

7 1 8     C h a p t e r 1 5     I n t r o d u c t i o n t o S i m u l a t i o n M o d e l i n g

• Amway, the global direct sales giant with annual sales over $10 billion and manufac- turer of more than 450 different products in 18 plants in the U.S. and Southeast Asia, must make many detailed capacity planning decisions on a regular basis. In the past, a team from the IE (industrial engineering) team would gather data and use traditional modeling tools to evaluate various scenarios. This was time-consuming, and the results were often less than accurate by the time they were available. In 2014, faced with a planned expansion of five new manufacturing sites, the IE team sought a better solution process and turned to @RISK. They developed a simulation tool called Long Range Capacity Planning (LRCP) that could quickly evaluate thousands of what-if scenar- ios, accounting for the vast number of uncertainties faced by Amway’s manufacturing teams. This tool allows plant managers to change variables such as demand, output rates, new products, and run sizes for a selected plant. Then it can simulate up to 20 scenarios and provide real-time results on which configurations work best.

• Deloitte, the global consulting firm, has used @RISK to help its cell captive insurance clients. This growing form of insurance occurs when a host insurer, the “cell captive,” allows other companies, the “cell owners,” to piggyback on the host’s insurer’s license, so that the cell owners don’t have to deal with the costs and regulations of buying their own licenses. The cell owners can then perform some of the functions on behalf of the host insurer to make their own profits. However, the host insurer takes on significant risks in such an arrangement, and it is exposed to huge financial risks if disastrous events occur. For this reason, the cell owners are required to capitalize the cell at the outset, providing funds for the host insurer in case of a disaster. The big question is how much capital is required, and this is where @RISK enters the picture. It can be used to simulate many possible scenarios over a future time period such as a year, and its results can predict how bad things could be. In this case, Deloitte uses the 99.5th percentile as the relevant value, the amount of exposure faced by the host insurer in a “one-in-200-event.”

15-1 Introduction A simulation model is a computer model that imitates a real-life situation. It is like other mathematical models, but it explicitly incorporates uncertainty in one or more input vari- ables. When you run a simulation, you allow these random input variables to take on var- ious values, and you keep track of any resulting output variables of interest. In this way, you are able to see how the outputs vary as a function of the varying inputs.

The fundamental advantage of a simulation model is that it provides an entire distri- bution of results, not simply a single bottom-line result. As an example, suppose an auto- mobile manufacturer is planning to develop and market a new model car. The company is ultimately interested in the net present value (NPV) of the cash flows from this car over the next 10 years. However, there are many uncertainties surrounding this car, including the yearly customer demands for it, the cost of developing it, and others. The company could develop a spreadsheet model for the 10-year NPV, using its best guesses for these uncertain quantities. It could then report the NPV based on these best guesses. However, this analysis would be incomplete and probably misleading because there is no guarantee that the NPV based on best-guess inputs is representative of the NPV that will actually occur. It is much better to treat the uncertainty explicitly with a simulation model. This involves entering probability distributions for the uncertain quantities and seeing how the NPV varies as the uncertain quantities vary.

Each different set of values for the uncertain quantities is a scenario. Simulation allows the company to generate many scenarios, each leading to a particular NPV. In the end, it sees a whole distribution of NPVs, not a single best guess. The company can see what the NPV will be on average, and it can also see worst-case and best-case results.

09953_ch15_ptg01_717-778.indd 718 04/03/19 4:04 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

15-1 Introduction    7 1 9

These approaches are summarized in Figures 15.1 and 15.2. Figure 15.1 indicates that the deterministic (nonsimulation) approach, using best guesses for the uncertain inputs, is generally not the appropriate method. It leads to the “flaw of averages,” as we will discuss later in the chapter. The problem is that the outputs from the deterministic model are often not representative of the true outputs. The appropriate method is shown in Figure 15.2. Here the uncertainty is modeled explicitly with random inputs, and the end result is a probability distribution for each of the important outputs.

Best guesses for uncertain inputs

Deterministic (nonsimulation) model

Best guesses for important outputs

Usually not correct: the “flaw of averages”

Figure 15.1 Inappropriate Deterministic Model

Figure 15.2 Appropriate Simulation Model Probability distributions for

uncertain inputs Simulation model Probability distributions for important outputs

Simulation models are also useful for determining how sensitive a system is to changes in operating conditions. For example, the operations of a supermarket could be simulated. Once the simulation model has been developed, it could then be run (with suit- able modifications) to ask a number of what-if questions. For example, if the supermarket experiences a 20% increase in business, what will happen to the average time customers must wait for service?

A huge benefit of computer simulation is that it enables managers to answer these types of what-if questions without actually changing (or building) a physical system. For example, the supermarket might want to experiment with the number of open registers to see the effect on customer waiting times. The only way it can physically experiment with more registers than it currently owns is to purchase more equipment. Then if it determines that this equipment is not a good investment—customer waiting times do not decrease appreciably—the company is stuck with expensive equipment it doesn’t need. Computer simulation is a much less expensive alternative. It provides the company with an electronic replica of what would happen if the new equipment were purchased. Then, if the simula- tion indicates that the new equipment is worth the cost, the company can be confident that purchasing it is the right decision. Otherwise, it can abandon the idea of the new equip- ment before the equipment has been purchased.

Spreadsheet simulation modeling is similar to the other modeling applications in this book. You begin with input variables and then relate these with appropriate Excel® for- mulas to produce output variables of interest. The main difference is that simulation uses random numbers to drive the process. These random numbers are generated with special functions that we will discuss in detail. Each time the spreadsheet recalculates, all the ran- dom numbers change. This provides the ability to model the logical process once and then use Excel’s recalculation to generate many different scenarios. By collecting the data from these scenarios, you can see the most likely values of the outputs and the best-case and worst-case values of the outputs.

In this chapter we begin by illustrating spreadsheet models that can be developed with built-in Excel functionality. However, because simulation is such an important tool for analyzing real problems, add-ins to Excel have been developed to streamline the process of developing and analyzing simulation models. Therefore, we then introduce @RISK, one of the most popular simulation add-ins. This add-in not only augments the simula- tion capabilities of Excel, but it also enables you to analyze models much more quickly and easily.

Like the other Palisade add-ins, @RISK works only with Excel for Windows, not with Excel for Mac.

09953_ch15_ptg01_717-778.indd 719 04/03/19 4:04 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

7 2 0     C h a p t e r 1 5     I n t r o d u c t i o n t o S i m u l a t i o n M o d e l i n g

The purpose of this chapter is to introduce basic simulation concepts, show how sim- ulation models can be developed in Excel, and demonstrate the capabilities of @RISK. Then in the next chapter, armed with the necessary simulation tools, we will explore a variety of simulation models.

Before proceeding, you might ask whether simulation is really used in the business world. The answer is a resounding “yes.” The chapter opener described an airline example, and many other examples can be found online. For example, if you visit www.palisade .com, you will see descriptions of interesting @RISK applications from companies that regularly use this add-in. Simulation has always been a powerful tool, but until the intro- duction of Excel add-ins such as @RISK, it had limited use for several reasons. It typically required specialized software that was either expensive and difficult to learn, or it required tedious computer programming. Fortunately, in the past two decades, spreadsheet simula- tion, together with Excel add-ins such as @RISK, has put this powerful methodology in the hands of the masses—people like you and the companies you are likely to work for. Many businesses now understand that there is no longer any reason to ignore uncertainty; they can model it directly with spreadsheet simulation.

15-2 Probability Distributions for Input Variables In this section we discuss the building blocks of spreadsheet simulation models: prob- ability distributions for input variables that capture uncertainty. All spreadsheet sim- ulation models are similar to the spreadsheet models from previous chapters. They have a number of cells that contain values of input variables. The other cells then contain formulas that embed the logic of the model and eventually lead to the output variable(s) of interest. The primary difference between the spreadsheet models you have developed so far and simulation models is that at least one of the input variable cells in a simulation model contains random numbers. Each time the spreadsheet recalculates, the random numbers change, and the new random values of the inputs produce new values of the outputs. This is the essence of simulation—it enables you to see how outputs vary as random inputs change.

In spreadsheet simulation models, input cells can contain random numbers. Any output cells then vary as these random inputs change.

Recalculation Key

The easiest way to make a spreadsheet recalculate is to press the F9 key. This is often called the “recalc” key.

Excel Tip

Technically speaking, input cells do not contain random numbers; they contain prob- ability distributions. In general, a probability distribution indicates the possible values of a variable and the probabilities of these values. As a very simple example, you might indi- cate by an appropriate formula (to be described later) that you want a probability distri- bution with possible values 50 and 100, and corresponding probabilities 0.7 and 0.3. If you force the sheet to recalculate repeatedly and watch this input cell, you will see the value 50 about 70% of the time and the value 100 about 30% of the time. No other values besides 50 and 100 will appear.

When you enter a given probability distribution in a random input cell, you are describ- ing the possible values and the probabilities of these values that you believe mirror reality. There are many probability distributions to choose from, and you should always attempt to

09953_ch15_ptg01_717-778.indd 720 04/03/19 4:04 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

15-2 probability Distributions for Input Variables    7 2 1

choose an appropriate distribution for each specific problem. This is not necessarily easy. Therefore, we address it in this section by answering several key questions:

• What types of probability distributions are available, and why do you choose one probability distribution rather than another in any particular simulation model?

• Which probability distributions can you use in simulation models, and how do you invoke them with Excel formulas?

In later sections we address one additional question: Does the choice of input probabil- ity distribution really matter—that is, are the outputs from the simulation sensitive to this choice?

Basic elements of Spreadsheet Simulation

A spreadsheet simulation model requires three elements: (1) a method for entering random quantities from specified probability distributions in input cells, (2)  the usual types of Excel formulas for relating outputs to inputs, and (3) the ability to make the spreadsheet recalculate many times and capture the resulting outputs for statistical analysis. Excel has some capabilities for performing these steps, but Excel add-ins such as @RISK provide excellent tools for automating the process.

Fundamental Insight

15-2a Types of Probability Distributions Imagine a toolbox that contains the probability distributions you know and understand. As you obtain more experience in simulation modeling, you will naturally add probability distributions to your toolbox that you can then use in future simulation models. We begin by adding a few useful probability distributions to this toolbox. However, before adding any specific distributions, it is useful to provide a brief review of some important general characteristics of probability distributions.1 These include the following distinctions:

• Discrete versus continuous • Symmetric versus skewed • Bounded versus unbounded • Nonnegative versus unrestricted

1 Much of this material was covered in Chapters 2 and 5, but it is reviewed here.

Choosing probability Distributions for Uncertain Inputs

In simulation models, it is important to choose appropriate probability distributions for uncertain inputs. These choices can strongly affect the results. However, there are no “right answers.” You need to choose the probability distributions that best describe the uncertainty, and this is usually not easy. However, the properties discussed in this section provide useful guidelines for making reasonable choices.

Fundamental Insight

09953_ch15_ptg01_717-778.indd 721 04/03/19 4:04 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

7 2 2     C h a p t e r 1 5     I n t r o d u c t i o n t o S i m u l a t i o n M o d e l i n g

Discrete Versus Continuous A probability distribution is discrete if it has a finite number of possible values.2 For example, if you throw two dice and look at the sum of the faces showing, there are only 11 discrete possibilities: the integers 2 through 12. In contrast, a probability distribution is continuous if its possible values are essentially a continuum. An example is the amount of rain that falls during a month in Indiana. It could be any decimal value from 0 to, say, 15 inches.

The graph of a discrete distribution is a series of spikes, as shown in Figure 15.3.3 The height of each spike is the probability of the corresponding value.

2 It is possible for a discrete variable to have a countably infinite number of possible values, such as all the nonnegative integers. However, this is not an important distinction for practical applications. 3 This figure and several later figures are from Palisade’s @RISK add-in.

Figure 15.3 Typical Discrete Probability Distribution

In contrast, a continuous distribution is characterized by a density function, a smooth curve as shown in Figure 15.4. Recall from Chapter 5 that the height of the density func- tion above any value indicates the relative likelihood of that value, and probabilities can be calculated as areas under the curve.

Sometimes it is convenient to treat a discrete probability distribution as continuous, and vice versa. For example, consider a student’s random score on an exam that has 1000 possible points. If the grader scores each exam to the nearest integer, then even though the score is discrete with many possible integer values, it is probably more con- venient to model its distribution as a continuum. Continuous probability distributions are typically more intuitive and easier to work with than discrete distributions when there are many possible values. In contrast, continuous distributions are sometimes dis- cretized for simplicity. In this case, the continuum of possible values is replaced by a few typical values.

Symmetric Versus Skewed A probability distribution can be symmetric or skewed to the left or right. Figures 15.4, 15.5, and 15.6 provide examples of these. You typically choose between a symmetric and skewed distribution on the basis of realism. For example, if you want to model a student’s score on a 100-point exam, you will probably choose a left-skewed distribution. This is because a few poorly prepared students typically “pull down the curve.” On the other hand, if you want to model the time it takes to serve a customer at a bank, you will proba- bly choose a right-skewed distribution. This is because most customers take only a minute or two, but a few customers take a long time. Finally, if you want to model the monthly return on a stock, you might choose a distribution symmetric around zero, reasoning that

The heights above a density function are not probabilities, but they still indicate relative likelihoods of the possible values.

09953_ch15_ptg01_717-778.indd 722 04/03/19 4:04 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

15-2 probability Distributions for Input Variables    7 2 3

Figure 15.4 Typical Continuous Probability Distribution

Figure 15.5 Positively Skewed Probability Distribution

Figure 15.6 Negatively Skewed Probability Distribution

the stock return is just as likely to be positive as negative and there is no obvious reason for skewness in either direction.

Bounded Versus Unbounded A probability distribution is bounded if there are values A and B such that no possible value can be less than A or greater than B. The value A is then the minimum possible value,

09953_ch15_ptg01_717-778.indd 723 04/03/19 4:04 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

7 2 4     C h a p t e r 1 5     I n t r o d u c t i o n t o S i m u l a t i o n M o d e l i n g

and the value B is the maximum possible value. The distribution is unbounded if there are no such bounds. Of course, it is possible for a distribution to be bounded in one direction but not the other. As an example, the distribution of scores on a 100-point exam is bounded between 0 and 100. In contrast, the distribution of the amount of damages Mr. Jones submits to his insurance company in a year is bounded on the left by 0, but there is no natural upper bound. Therefore, you might model this amount with a distribution that is bounded by 0 on the left but is unbounded on the right. Alternatively, if you believe that no damage amount larger than $20,000 can occur, you could model this amount with a distribution that is bounded in both directions.

Nonnegative Versus Unrestricted One important special case of bounded distributions is when the only possible values are nonnegative. For example, if you want to model the random cost of manufacturing a new product, you know that this cost must be nonnegative. There are many other such examples. In these cases, you should model the randomness with a probability distribution that is bounded below by 0. This prevents negative values that make no practical sense.

15-2b Common Probability Distributions Now that you know the types of probability distributions available, you can add some common probability distributions to your toolbox. The file Probability Distributions.xlsx was developed to help you learn and explore the distributions discussed in this section, plus others. Each sheet in this file illustrates a particular probability distribution. It describes the general characteristics of the distribu- tion, indicates how you can generate random numbers from the distribution with Excel’s built-in functions, with @RISK functions, or with Albright’s RandGen add-in (freely available at https://kelley.iu.edu/albrightbooks/free_downloads. htm) and it includes histograms of these distributions from simulated data to illustrate their shapes.4

Each of the following distributions is really a family of distributions. Each member of the family is specified by one or more parameters. For example, there is a normal distribution for each possible mean and standard deviation you spec- ify. Therefore, when you try to find an appropriate input probability distribution for a simulation model, you first have to choose an appropriate family, and then you have to select the appropriate parameters for that family.

Uniform Distribution The uniform distribution is the “flat” distribution illustrated in Figure 15.7. It is bounded by a minimum and a maximum, and all values between these two extremes are equally likely. You can think of this as the “I have no idea” distribu- tion. For example, a manager might realize that a building cost is uncertain. If she can state only that, “I know the cost will be between $20,000 and $30,000, but other than this, I have no idea what the cost will be,” then a uniform distribution from $20,000 to $30,000 is a natural choice. However, even though some peo- ple do sometimes use the uniform distribution in such situations, this choice is usually not very realistic. If the manager really thinks about it, she can probably provide more information about the uncertain cost, such as, “The cost is more

Think of the Probability Distributions.xlsx file as a “dictionary” of the most commonly used distributions. Keep it handy for reference.

A family of distributions has a common name, such as “normal.” Each member of the family is specified by one or more numerical parameters.

4 Later sections of this chapter and all the next chapter discuss much of @RISK’s functionality. For this section, the only functionality used is @RISK’s collection of functions, such as RISKNORMAL and RISKTRIANG, for gener- ating random numbers from various probability distributions. You can skim the details of these functions for now and refer back to them as necessary in later sections.

09953_ch15_ptg01_717-778.indd 724 04/03/19 4:04 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experienc