Project risk Simulation with crystal Ball

At this point, we will deal with one of the more quantitative methods of dealing with risk as it applies to the task of simulating budgets and cash flows. The essence of risk analysis is to make estimates or assumptions about the probability distributions associated with key parameters and variables and to use analytic decision models or Monte Carlo simulation models based on these distributions to evaluate the desirability of certain managerial decisions. Real‐world problems are usually large enough that the use of analytic models is very difficult and time consuming. With modern computer software, simulation is not difficult. PMBOK describes the simulation process in Chapter 11 on Risk.

A mathematical model of the situation is constructed, and a simulation is run to determine the outcomes of key performance measures under various scenarios. The model is run (or replicated) repeatedly, starting from a different point each time based on
random choices of values from the probability distributions of the input variables. Outputs of the model are used to construct statistical distributions of items of interest to decision makers, such as costs, profits, completion dates, or return on investment. These distributions are the risk profiles of the outcomes associated with a decision. Risk profiles can be considered by the manager when considering a decision, along with many other factors such as strategic concerns, behavioral issues, fit with the organization, cost and scheduling issues, and so on.

In the following section, using an example we have examined earlier in Chapter 1, we illustrate how Oracle Crystal Ball® 11.1.2.2 (CB), a widely used Excel® Add‐In that is available with this book, can be used to improve the PM’s understanding of the risks
associated with managing projects. Reconsider the Ceramic Sciences example we solved in Chapter 1 in the section devoted to finding the discounted cash flows associated with a project. Setting this problem up in Excel® is straightforward, and the earlier analytic solution is shown here for convenience as Table 4-5. We found that the project cleared the barrier of a 13 percent
hurdle rate for acceptance. The net cash flow over the project’s life is just under $400,000, and discounted at the hurdle rate plus 2 percent annual inflation, the net present value of the cash flow is about $18,000.

Assume that the expenditures in this example are fixed by contract with an outside vendor so that there is no uncertainty about the outflows; there is, of course, uncertainty about the inflows. Suppose that the estimated inflows are as shown in Table 4-6 and include a minimum (pessimistic) estimate, a most likely estimate, and a maximum (optimistic) estimate. (In Chapter 5, “Scheduling the Project,” we will deal in more detail with the methods and meaning of making such estimates. Shortly, we will deal with the importance of ensuring the honesty of such estimates.) Both the beta and the triangular statistical distributions are well suited for modeling variables with these three parameters. In earlier versions of CB the beta distribution was complicated and not par­ticularly intuitive to use, so the triangular distribution was used as a reasonably good approximation of the beta. Use of a new beta distribution, labeled “BetaPERT” by CB in its Distribution Gallery, has been simplified in version CB 11.1.2.2; we will use it in this example, and the simulations run elsewhere in this book.

The hurdle rate of return is typically fixed by the firm, so the only remaining variable is the rate of inflation that is included in finding the discount factor. We have assumed a 2 percent rate of inflation with a normal distribution, plus or minus 1 percent (i.e., 1 per­cent represents ± 3 standard deviations).

It is important to point out that approaches in which only the most likely estimate of each variable is used are equivalent to assuming that the input data are known with certainty. The major benefit of simulation is that it allows all possible values for each variable to be considered. Just as the distribution of possible values for a variable is a better reflection of reality than the single “most likely” value, the distribution of out­comes developed by simulation is a better forecast of an uncertain future reality than is a forecast of a single outcome. In general, precise forecasts will be “precisely wrong.” Using CB to run a Monte Carlo simulation requires us to define two types of cells in the Excel® spreadsheet.[2] The cells that contain variables or parameters that we make assumptions about are defined as assumption cells. For the Ceramic Sciences case, these are the cells in Table 4-5, column B and cell B16, the cash inflows and the rate of infla­tion, respectively. As noted above, we assume that the rate of inflation is normally dis­tributed with a mean of 2 percent and a standard deviation of .33 percent. Likewise, we assume that yearly inflows can be modeled with a BetaPERT (or a triangular) distribution.

The cells that contain the outcomes (or results) we are interested in forecasting are called forecast cells. In Ceramic’s case we want to predict the NPV of the project. Hence, cell D14 in Table 4-5 is defined as a forecast cell. Each forecast cell typically contains a formula that is dependent on one or more of the assumption cells. Simulations may have many assumption and forecast cells, but they must have at least one of each. Before pro­ceeding, open Crystal Ball® and make an Excel® spreadsheet copy of Table 4-5.

To illustrate the process of defining an assumption cell, consider cell B5, the cash inflow estimate for 20X2. We can see from Table 4-6 that the minimum expected cash inflow is $35,000, the most likely cash flow is $50,000, and the maximum is $60,000.

If you have not already done so, load the Excel program on your computer. If prompted, click on “Use Crystal Ball” and then open the spreadsheet you made of Table A in Chapter 1 (also shown in Table 4-5). Given the information in Table 4-6, the pro­cess of defining the assumption cells and entering the pessimistic and optimistic data is straightforward and involves six steps:[3]

1. Click on cell B5 to select it as the relevant assumption cell.
2. Select the Crystal Ball tab in Excel and from the Crystal Ball ribbon, select “Define Assumption” at the very left of the Crystal Ball ribbon. CB’s Distribution Gallery is now displayed as shown in Figure 4-5.

3. CB allows you to choose from a wide variety of probability distributions. Click on the BetaPERT box and then click “OK” button to select it.
4. CB’s BetaPERT Distribution dialog box is displayed as in Figure 4-6. It may have numbers in the boxes but ignore them. If it does not otherwise appear exactly like Figure 4-6, click “Parameters” on the menu at the top of the BetaPERT Distribution dialog box, and then select “Minimum, Most Likely, Maximum” at the top of the drop’down menu.
5. In the Name: textbox at the top of the dialog box enter a descriptive label, for exam­ple, Cash Inflow 20X2. Then, enter the pessimistic, most likely, and optimistic esti­mates from Table 4-6 in the appropriate cells below the distribution.
6. Click on the “Enter” button and then on the “OK” button.

Now repeat steps 1 through 6 for the remaining cash inflow assumption cells (cells B6:B12), using the information in Table 4-6.

When finished with the cash inflow cells, an assumption cell for the inflation rates in cell B16 needs to be defined. For this cell, select the Normal distribution. We decided earlier to use a 2 percent inflation rate, plus or minus 1 percent. Recall that the normal distribution is belbshaped and that the mean of the distribution is its center point. Also recall that the mean, plus or minus three standard deviations, includes 99+ percent of the data. The Normal Distribution dialog box, Figure 4-7, calls for the distribution’s mean and its standard deviation. The mean will be 0.02 (2 percent) for all cells. The standard deviation will be .0033 (one’third of the ± percent range). (Note that Figure 4-7 displaysonly the first two decimal places of the standard deviation. The actual  standard deviation of .0033 is used by the program, but if you wish to see the .0033 you may click on “Preferences” at the top of the box. Then click on “Chart,” “Axis,” “Cell format,” “Number,” and change the “2” to “4.” “OK” your way back to the input data sheet.) As you enter these data, the distribution will show a mean of 2 percent and a range from 1 percent to 3 percent. Thus in cell B16 in the Name: textbox enter Inflation Rate. Then enter .02 in the Mean textbox and .0033 in the Std. Dev. textbox.

The process of defining a forecast cell involves four steps.

1. Click on the cell D14 to select it as containing an outcome that interests us.
2. Select the menu option Define Forecast from the Crystal Ball ribbon at the top of the screen.
3. CB’s Define Forecast dialog box is now displayed. In the Name: textbox, enter a descriptive name such as Net Present Value of Project. Then enter a descriptive label such as Dollars in the Units: textbox.
4. Click OK. There is only one Forecast cell in this example, but in other situations there may be several. Use the same four steps to define each of them.

When you have completed all entries, what was Table 4-5 is now changed and appears as Table 4-7.

Table 4-7 Three-Point Estimate of Cash Flows and Inflation Rate for Ceramic Sciences, Inc. All Assumption and Forecast Cells Defined

We are ready to simulate. CB randomly selects a value for each assumption cell based on the probability distributions which we specified and then calculates the net present value of the cell values selected. By repeating this process many times, we can get a sense of the distribution of possible outcomes, or in this case the distribution of NPVs for this project.

Approximately in the center of the CB ribbon, you will see the Run Preferences. Below it is the “Trials” box. It should be set to “1000.” If not, simply enter 1000 in the box. To run the simulation, click the green Start Arrow in the CB ribbon.

The distribution of NPVs resulting from simulating this project 1,000 times is dis­played in Figure 4-8. Statistical information on the distribution is shown in Figure 4-9. The NPVs had a mean of $14,817 and a standard deviation of $8,095. We can use this information to make probabilistic inferences about the NPV of the project such as what is the probability the project will have a positive NPV, what is the probability the pro­ject’s NPV will exceed $10,000, or what is the probability the project’s NPV will be between $5,000 and $10,000?

CB provides considerable information about the forecast cell in addition to the fre­quency chart including percentile information, summary statistics, a cumulative chart, and a reverse cumulative chart. For example, to see the summary statistics for a forecast cell, click on the view menu item at the top of the Frequency Chart dialog box (see Figure 4-8), and then check Statistics from the drop down menu. The Statistics view for the frequency chart (Figure 4-8) is illustrated in Figure 4-9.

Figure 4-9 contains some interesting information. There are, however, several ques­tions that are more easily answered by using Figure 4-8, the distribution of the simulation’s outcomes. What is the likelihood that this project will achieve an NPV at least $10,000 above the hurdle rate including inflation? It is easy to answer this question. Note that there are black triangles at either end of the baseline of the distribution in Figure 4-8. By placing the cursor on the triangle on the left end of the simulation distribution baseline and sliding it to the $10,000 mark, the probability of a $10,000 or greater outcome can be read in the “Certainty” box. One can also find the same answer by deleting the “-Infinity” in the box in the lower left corner of the Frequency View and entering $10,000 in it.

Even in this simple example, the power of including uncertainty in project budgets should be obvious. Because a manager is always uncertain about the amount of uncer­tainty, it is also possible to examine various levels of uncertainty quite easily using CB. We could, for instance, alter the degree to which the inflow estimates are uncertain by expanding or contracting the degree to which optimistic and pessimistic estimates vary around the most likely estimate. We could increase or decrease the level of inflation. Simulation runs made with these changes provide us with the ability to examine just how sensitive the outcomes (forecasts) are to possible errors in the input data. This allows us to focus on the important risks and to ignore those that have little effect on our decisions. While the focus of our discussion in this chapter is on budgeting, in the next chapter on scheduling we take the simulation analysis a step further and develop a model that incorporates uncertainty about both costs and the time to complete project activities.

Considering Disaster

In our consideration of risk management in the Ceramic Sciences example, we based our analysis on an “expected value” approach to risk. The expected cost of a risk is the esti­mated cost of the risk if it does occur times the probability that it will occur. How should we consider an event that may have an extraordinarily high cost if it occurs, but has a very low probability of occurring? Examples come readily to mind: the World Trade Center destruction of 9/11, Hurricane Katrina, the BP oil spill in the Gulf of Mexico, the Hurricane Sandy floods of 2012 (a supposedly 500-year event!). The probability of such events occurring is so low that their expected value may be much less than some com­paratively minor misfortunes with a far higher probability of happening.

If you are operating a business that uses a “just in time” input inventory system, how do you feel about a major fire at the plant of your sole supplier of a critical input to your product? The supplier reports that their plant has never had a major fire, and has the latest in fire prevention equipment. Does that mean that a major plant fire is impossible? Might some other disaster close the plant—a strike, an al Qaeda bomb? Insurance comes immediately to mind, but getting a monetary pay-back is of little use when you are con­cerned with the loss of your customer base or the death of your firm.

In an excellent book, The Resilient Enterprise, Yossi Sheffi (Sheffi, 2005) deals with the risk management of many different types of disasters. He details the methods that creative businesses have used to cope with disasters that struck their facilities, supply chains, customers, and threatened the future of their firms. The subject is more complex than we can deal with in these pages, but we strongly recommend the book, a “good read,” to use a reviewer’s cliche.

We are now ready to consider the scheduling problem. Because durations, like costs, are uncertain, we will continue our discussion of the matter, adding some powerful but reasonably simple techniques for dealing with the uncertainty surrounding both project schedule and cost.

Source: Meredith Jack R., Mantel Jr. Samuel J., Shafer Scott M., Sutton Margaret M. (2017), Project Management in Practice, John Wiley & Sons, Inc. 3th Edition.