An example of a Monte Carlo simulation risk analysis model for cost modeling

Technical difficulty: 2

Techniques used: Monte Carlo simulation in Excel

ModelRisk functions used: VoseCombined,VoseDiscrete,VoseOgive

Model description

One of the most common applications, perhaps the most common, of risk modeling is to add uncertainty to discounted cashflow models. The example model presented here is typical of this kind of analysis.

The problem: You are evaluating a new company making fuel cells for hospital power plants. Currently there are no competitors. The figure below shows the NPV calculation for the project's 10-year life. This calculation is static, and no uncertainty is included; all input values are considered as most likely. The example model contains three sheets – a description of the problem, a static model shown below that you can add uncertainty to, and a solution with all the uncertainties included.

The NPV, discounted at 10% shows a negative figure of -$37,134.

Let's see how different uncertainties can affect our NPV result. The list of uncertainties is shown below:

(a, b, c) notation means a distribution with min = a, most likely = b, and max = c.

1. Product development cost have been estimated by F Gibbons to be (70000, 80000, 120000) spread over 2004 to 2006 in the ratio 5:2:1. However P Gumbel estimates the product development costs are (70000, 100000, 140000) in the same ratio over the same period. Capital expenses and overheads are assumed to be well defined and are not subject to change.
2. The tax rate is fixed at 46% unless the Conservatives get in at the next election in two years (20% chance) when the rate would drop to (32%, 35%, 46%).
3. Market volume is expected to grow each year by (10%, 20%, 40%) beginning in three years at (2500, 3000, 5000) up to a maximum of 20,000 units. The cost per unit in the first year of sales is estimated at (22.75, 23.25, 24.5) and the sales price per unit is estimated at (45, 58, 65). Both the cost and sales price per unit are subject to inflation from then on at a rate starting at (3%, 4%, 6%) and varying yearly in a similar fashion to historic rates.
4. You expect one competitor to emerge as soon as the market volume reaches 3,500 units in the previous year. A second would appear at 8,500 units. Your competitors' shares of the market would grow linearly until you all have equal market share after three years.

The solution sheet looks like this:

There are three points in the model that need special attention:

1. Cell C41 uses a VoseCombined function to combine the experts’ opinions and return values from "Gibbons" and "Gumbel" with equal probabilities. Since we assumed both experts have equal weights, we assign 50% to each of them. If one of them was more experienced or trusted we would have assign different weights to their opinions.
2. A common mistake here is to multiply their opinions by the weights and then take the sum: = Gibbons*50%+Gumbel*50%. This would result in the decrease of the spread in the final outcome and underestimation of the risk arising from that particular risk factor, explained in detail in the Wiki topic about Incorporating Differences in Expert Opinion. Note that we could also use the VoseDiscrete function.
3. A VoseOgive function was used to model the inflation, taken historic inflation rates.

If we run a simulation and graph the output cell, we will get the following distribution of NPV:


As we can see from the chart above, there is only 21% probability that the project will have a negative NPV.

NPV calculations performed in a risk analysis spreadsheet model are usually presented as a distribution of NPVs because the cashflows selected in the NPV calculations are their distributions rather than their expected values. Theoretically, this is however incorrect. Since a NPV is the net present value, it can have no uncertainty. The NPV is the amount of money that the company values the project at today. The problem is that we have double counted the risk of the project by first discounting at the risk-adjusted discounted rate r and then showing the NPV as a distribution (i.e. it is uncertain). The actual NPV to quote in a report would be the expected value (mean) of the NPV distribution, which in this case equals $ 57k.

The mean NPV value can only determined if one runs a simulation, but there is another good reason for doing the simulation – namely, that we can see what is driving the uncertainty. The Tornado plot provides this:

The sales price is the greatest uncertainty driver, followed by market growth, and the market volume. If the level of risk is too great to make the investment now, these are the variables one would research more thoroughly to get narrower estimates and thus obtain the probability of a positive NPV closer to 0 or 1 which would clarify whether to make the investment.