Monte Carlo Simulation with Crystal Ball
What is the likelihood of reaching a particular goal? What are the critical factors affecting risk? The answer to these and other common "what-if" scenarios can be determined by assigning probability to unknown variables. Excel cannot handle the complexity of probability analysis, so you need a better tool: Crystal Ball. Crystal Ball automates the cumbersome "what-if" process using Monte Carlo simulation, by applying a range of values or a probability distribution to each uncertain variable. The program generates random values from within the defined probability ranges, and then recalculates the model literally hundreds or thousands of times, storing the results of each "what-if" scenario. This time saving process alleviates having to manually enter different scenarios over and over again. Crystal Ball 2000 Standard is an easy-to-use simulation program that helps you analyse the risks and uncertainties associated with your Microsoft Excel spreadsheet models.
Examples - Monte Carlo Simulation with Crystal Ball

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.