The following example, shows how to effectively enhance a simple “what-if” spreadsheet model, to a complex quantitive risk analysis model using Monte Carlo simulation. Initial situation A product is to be developed, tested and introduced into the market. To examine the P&L potential of the product, the following model has been constructed: Model As shown above, the expenses required to research, produce, ship and market the product all add up to $6 (million). The expected amount of units sold, 400’000, at $18 a unit will give us a revenue of $7.2 (million). The expected profit will therefore be $1’200’000. Under these assumptions the product shows a profit. What-if scenarios We have seen that if our model follows our expectations, we will achieve a profit of $1’200’000. However, this is a very unstable reading as the following example demonstrates: If we were to assume that the product could not sell 400’000 units but only 300’000 (a difference of 25%), the product would no longer achieve a profit but a loss of $600’000 (a drastic decrease of 150%). This demonstrates how changing only one variable can result in the product never even reaching the developing stage. A classical what-if analysis will help us to understand the bounds of our P&L value by supplying a worst, probable and best case value to our model. After examining this result table, we have not reached a conclusion but merely gathered more information, allowing us to depict the range of profit and loss for our product. We do not know the probability of any given value, nor do we know what exact P&L we can expect. Combination of “what-if” scenarios Of course we assume that not all variables will take on their most negative value at the same time. We imply that maybe one or two variables will produce, for us negative results, whereas we hope that most variables are in the range that we expect. To evaluate each single case we would have to simulate all numbers for all variables, an extremely time and effort consuming procedure. And even if we had all the results, we would still not know the probability of reaching a certain outcome. Monte Carlo simulation Crystal Ball can be used to specify a probability distribution for every input identified as an assumption cell. Then, after running the simulation model, Crystal Ball automatically performs a statistical analysis for every output we have specified as a forecast cell.By using probabilistic assumptions and analysing forecasts statistically, Crystal Ball helps us gain insights that we could not extract from a deterministic model. These insights lead to better decisions in the probabiilistic situations we face in real world applications. Assigning a distribution Let us now concentrate on each variable separately, starting with the testing costs. We can assumed that the research costs will vary between $1.3 (million) and $2.3 (million). We can also assume that each value will emerge with the same probability. Graphically depicted, our assumptions would look like this: As all values are equally likely to appear this distribution is called: Uniform distribution. Uniform distribution is only one of many (usually more complex) distributions that can be chosen for a variable. Looking at the marketing strategy cost, we can assume that our costs will be in the vicinity of $1.5 (million). We also know that our costs are as likely to be above as below this number, but realize that they are more likely to be in this range than far away from our expectations. Graphically our assumptions for our marketing strategy costs would look something like this: This distribution is called the normal distribution and it theoretically describes many natural phenomenas. Running a simulation If we were to assume each of our variable cost (research, production, shipping and wages) were uniformly distributed, and our marketing strategy costs were normally distributed, we could set a minimum and maximum range for each, using our worst and best case examples from before. Let us also assume that the amount of units sold as well as the unit price, are also uniformly distributed between the worst and best case examples. The P&L value, which is a calculated value, we will define as our forecast cell. After defining all variables (assumptions and forecasts) we are ready to perform a simulation. For every simulation, a number will be picked randomly for each assumption cell, from within the range specified and with the distribution specified. From these assumptions, the forecast cell will be calculated and the result graphically plotted, thus producing a graph showing each result achieved in the simulation and the probability of reaching this result. Interpreting the results Once the simulation has started, a window will appear and the user will be presented with a graphic illustration of the results. This chart shows the P&L forecast achieved in the 100’000 trials specified. On the horizontal axis we see the P&L expected. On the vertical axis we see the probability with which a certain P&L is expected. At the bottom of the windows, we are shown that with 100% certainty, the total will be within the minimum range of negative infinity and the maximum range of infinity. If we want to know the probability of achieving a profit we can change the lower bound of our range to 0. From this diagram we can read the that the certainty of achieving a profit (i.e. a value for the P&L between 0 and positive infinity) is 25.25%. It is now not likely that this product will be produced. The next important thing to understand is what impact each assumption had on the forecast result. Quantitive risk management We can now estimate with a certain degree of certainty how much profit our product will achieve and what values are required. However, Crystal Ball also lets us analyse what factors effect our forecast variable and by how much. From this sensitivity chart, we can see that the number of units sold has the greatest effect on our business model and that our production costs will have a relatively small effect on our final P&L.