Certain graphical display devices help us to interpret and communicate powerful insights from the immense information produced by the Monte Carlo simulation process. These display devices visually communicate the range of exposure we face and the central tendency of outcomes we care about. They prioritize our attention on the risk factors that can affect us most.
Cash Flow and Cumulative Cash Flow with Probability Bands
To plot the cash flow and cumulative cash flow with their 80th percentile probability bands, we first specify a vector of the desired quantiles.
We then apply the quantiles() function in each year of the cash flow (Figure 4-1) and cumulative cash flow (Figure 4-2) using the sapply() function .
To plot these results , use the following:
The cash flow with probability bands around the 80th percentile prediction interval, representing the range of possible cash flows given all the relevant information we have at hand
The cumulative cash flow with probability bands , useful for determining the potential range of time in which payback on a cash basis can occur
The cash flow graph (Figure 4-1) shows us that there is an 80% probability, given the quality of our information, that we can break even between the third and sixteenth year of operation, and the 50–50 outcome occurs around Year 5. The cumulative cash flow graph (Figure 4-2) is much less encouraging. It shows us there is an 80% probability that we will reach payback between Year 7 and never! The cash flow graph is important for showing when positive cash flow will likely occur, but the cumulative cash flow graph presents an initial insight into the more sobering reality of the economic value of the investment opportunity; that is, when an investment’s resultant cash flows pay back the initial cash outlays. Of course, this result does not yet include the effects of the time value of money, so sobering results can often turn frightening when the discount rate is properly applied.
The Histogram of NPV
The histogram of the NPV is quite easy to set up using the hist() function of R. First, establish the locations of the histogram bins’ breakpoints. Here we begin with 20 bins.
R sets these bin breakpoints automatically, but they are not frequently represented with enough detail. Using this parameter gives us some flexibility to play around with the number of bins and their size.
The histogram of the NPV shows the central tendency of the net value of the investment opportunity
The uncertainty in this problem has clearly produced a situation where the great bulk of the NPV outcomes will most likely fall below $0. In fact, on average they will fall below $0, as the mean NPV = -$5.8 million. Now we need to understand by just how much.
The Cumulative Probability Distribution of NPV
The approach to plotting the cumulative probability distribution is similar to that of the cash flow and cumulative cash flow confidence bands; however, we won’t need to employ the sapply(), but we will need a longer vector of quantile values to get enough detail in the plot. We define the quantile breakpoint values by
which gives a vector of 21 values from 0 to 1 with step sizes of 0.05, as shown here:
Now, using the quantile() function again, we find the cumulative probability of the NPV at the 21 points.
The domain and range of the cumulative probability of the NPV . The 80th percentile prediction interval, indicated in blue, shows there is ∼62% probability that the outcome will be $0 or less.
Now, to plot the NPV cumulative probabilities, as shown in Figure 4-5, we use the following code.
The cumulative probability distribution of the NPV
Therefore, by using both the probability density and the cumulative probability plots, we can observe where the bulk of the results would likely fall, as well as other important probability intervals (e.g., the 80th percentile prediction interval). We also see that the deterministic approach to the problem could have led us into trouble if we had stopped there—an expected loss of $5.8 million is certainly more sobering than the originally determined $8.2 million. Recall, though, that I emphasized that the initial deterministic results were tentative. We only acquire an informative picture of the decisions we make after we consider the effects of uncertainty and risk.
This is not the end of the analysis, though. There very clearly exists some opportunity to do better than less than $0. The question is this: What can we do to manipulate future outcomes to our benefit? Before we can answer that question, though, we need to know what we most likely should manipulate.
The Waterfall Chart of the Pro Forma Present Values
The pro forma waterfall chart shows how the present value of the major pro forma line items contribute cumulatively to the NPV
To create a waterfall chart, we first need to extract the appropriate benefit and cost elements from the pro forma array .
Next, we find the present value of these elements in the same way we found the NPV of the cash flow.
We create a cumulative sum of the present values.
We duplicate the prior vector, but shift all the values forward one space, dropping the last value.
Now, to plot the floating columns of the waterfall chart, we need two vectors, one to capture the high position of each bar, and the other to capture the low position of each bar. We can do this by finding the pairwise maximum of the last two vectors, then by finding the pairwise minimum of the last two vectors.
Finally, we assign these last two vectors to an array.
We then plot the waterfall with the following code by co-opting the box plot .
If we want to improve profit by controlling costs , variable cost should get our first attention, followed by GS&A and CAPEX. However, don’t assume that reducing these items arithmetically necessarily leads to increased profit, as there could be systematic effects that relate, say, capital spent now to operating costs incurred later. In a real analysis, this idea should be explored in a more detailed model for completeness, but for the purposes of this tutorial we won’t consider this further.
The Tornado Sensitivity Chart
For our initial pass of analysis in Chapter 2, the deterministic sensitivity provides us some clues about which variables we should pay attention to, either in the form of manipulating them to our benefit or developing a mitigation plan to prevent some undesirable outcome in the objective function caused by them. However, this also assumes that the variables are under our control, that they are essentially decision variables. During the initial investigation and planning of a business opportunity, this condition of control rarely is the case.
As we’ve already recognized that the variables’ actual future outcomes are most likely uncertain, in Chapter 3 we represented the variables in the model with distributions. Each distribution represents our conception of what the range of those outcomes could be with associated probabilities for intervals across the range. (This implies that a ±x% relative change in any variable might either be highly improbable or just a small fraction of the most likely range of actual behavior.)
What we need is a way to prioritize our attention on the uncertain variables , based on our current information about them and how strongly the likely range of their behavior might affect the average value of the objective function, NPV. So, before we go seeking ways to control variables on the guidance of the deterministic sensitivity analysis, we first need to understand if the probable range of outcomes for any variable is significant enough to matter to make a clear decision of any kind.
Tornado sensitivity analysis works by observing how much the average NPV changes in response to the 80th percentile range of each variable sequentially. We choose a variable and set it to its p10 value, then we record the effect on average NPV. Next we set the same variable to its p90 and record the effect on average NPV. During both of these iterations, we let the other variables run according to their defined distribution. Repeating this process for each variable, we observe how much each variable influences the objective function both by their functional strength and likelihood of occurrence.
We can make this observation easily with a floating bar chart , where each bar is assigned to a particular variable. The width of a bar runs from the low response of the average NPV to the high for the given variable. The bars are ordered such that the variable with the widest bar goes at the top and the narrowest is at the bottom. The declining widths of the bars give the chart its distinctive tornado, or funnel, shape.
To set up the code for this routine, we begin just as we did in the original deterministic sensitivity analysis by duplicating and modifying the base R code file (Risk_Model.R), then giving it a name like Risk_Model_Sensitivity.R . Our new file starts out looking just like the original.
Now we add some additional lines to set up the process that will loop through our variables and create an initialized array to store the values of the NPV on each sensitivity iteration .
Just as before, we assign the appropriate distribution to each uncertain assumption and simulate them.
Next, we collect our simulated assumptions into an array that we iterate through as we sequentially replace each variable’s simulated samples with the p10 and p90 quantile values.
The array d.vals.temp holds the original set of simulated samples. For the run of the sensitivity analysis, we borrow values from this array and place them in a duplicate array.
We also need an array that contains only the p10 and p90 assumption parameters. We extract those from the d.vals array with the following:
The subscript indicator [, -2] in d.vals tells R to remove the second column from d.vals, which is the p50 column .
Now, to summarize again, our R code will loop through our list of borrowed simulated values associated with each variable. On the selection of a specific variable that is being tested in the loop, the simulated samples in the variable’s row will be replaced sequentially with its p10 value from d.vals2, and then its p90 value. Once the code has run its two tests at the sensitivity points on a given variable, the R code will restore the variable’s original samples from the d.vals.temp array to the d.vals.temp2 array and move on to the next variable to repeat the process.
Before we move on, let’s think about the way we set up the deterministic sensitivity analysis. In that case, we stepped across the sensitivity points defined for the low, median (p50), and high values for our variables.
The central value of the sensitivity analysis was always the same because we were running a deterministic model. In stochastic systems, like the one we’re representing in our uncertain model, the central value of the objective function (i.e., the mean) we find in the base uncertainty model will not necessarily be a function of the p50s of the assumptions’ parameters. We therefore can’t just find the center of the sensitivity analysis here by setting all the variables to their p50s in one step.
Another problem also arises from the simulation error we discussed in the last chapter. We likely will not get the exact same mean NPV from the samples simulated in the base uncertainty model as we would get from the samples simulated here in this instance of the sensitivity model, although they will probably be close for a large enough sample set. The way around this conundrum is to run the model twice with the same simulation samples, once to find the mean NPV, then once more to find the sensitivity of that mean NPV to the range in the assumptions. By doing this, we maintain the central value around which its sensitivity behaves. How do we do this? By placing our base model in one big function that has one parameter: the data array that contains the required samples for a given run of the model. We start the function at the point in our base uncertainty model where we extracted the values from our original data parameter array, simulated their samples with the appropriate distribution, and then assigned them to meaningful variable names. Our function won’t need to resimulate the samples for each variable, though. It will simply use the samples we’ve already generated and placed in an indexed array. The following code represents the base model converted to a function that returns the samples for the NPV calculation .
I recommend placing this function code in a separate .R file and then importing it at the beginning of the sensitivity analysis file using the source() function in the same manner as we did with the global assumptions and other functions.
To calculate the base mean NPV, we run the CalcBizSim() once with the values we assigned to the d.vals.temp array.
Then, to find the sensitivity of the base mean NPV to the range in our assumptions, we loop across the secondary array that contains our borrowed samples, replacing each variable’s samples with a vector of the same length containing the values of the p10 and p90 values.
The remainder of the code works just like the deterministic sensitivity analysis that set up the graphical results.
We now obtain a chart (Figure 4-7) very much like our deterministic sensitivity chart, except this one is based on the variation to our mean NPV caused by all the sources of variation and risk (and the bars are colored red). The chart tells us that the top five variables could easily turn a bad situation worse, but they also tell us that if we could harness the drivers behind the variation in each variable, effectively converting each of these uncertainties into a decision, we could create an immensely valuable opportunity (well, immensely so relative to the negative mean NPV we currently see).
The tornado chart displays the sensitivity of the mean NPV to the 80th percentile range of each uncertainty
Although it is beyond the scope of this book, the refinement in our analysis would be to find strategies that would potentially allow us to capture the upper end of each of those upper bars. The cumulative sum of the net value of reaching those goals would show us the marginal contribution of the value of control on those top four variables . After some market and competitive reconnaissance and creative planning, we might be able to find a way legally to deter RoadRunner from coming to market at all at little to no cost to us.
You can see the uninterrupted source code for this procedure in Appendix B.
Closing Comments
- 1.
Thinking about R as a simulation alternative to spreadsheets.
- 2.
Extending R from its common orientation of statistical analysis of empirical data toward an orientation more characterized as probabilistic reasoning.
The framework that we developed represented the mechanical structure for processing information we acquire through the reasoning process, but it by no means represents a complete analysis. We based our analysis on one design structure for engineering, market development, and operations. No one initial business solution can be thorough and comprehensive enough to address all the best (and worst) ways to allocate resources in the pursuit of success.
For our business cases analysis to be truly informative, we must consider multiple alternatives for achieving the same goal, simply because the process of value creation is pathway dependent. Each set of pathways faces opportunity costs that must be thoroughly explored before we can honestly say that we have performed our fiduciary responsibilities . In this tutorial, we considered only one pathway, one decision strategy. To truly learn what the future has to offer, we need to contrast and compare a good handful of thematically different ways we could take advantage of those potential offerings, measuring the risks and benefits of each, and discarding the ones that present the least opportunity to create value. We’ll explore this alternative generation approach in Part 2.
This approach is the scientific process of thinking: We develop hypotheses about how we can create value in a given opportunity context, explore the conditioning effects for what imparts greater utility to those hypotheses, and discard the hypotheses that demonstrate weak explanatory power for creating value. This, in my opinion, is the best kind of process-driven, data-supported decision making.