ModelRisk needs to be installed in order for the model to work.
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
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.
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.