Monte Carlo with Microsoft Excel

The project sponsor comes to you and ask:

–What’s the probability of my project finishing before April under one million euros?

If you need to answer professionally, you can use the so called Monte Carlo method. You only need to open an Excel file, model activity risks on cost and schedule, and produce two charts:

Using this first chart, you could explain:

–There is simply no chance of finishing before the first of March. Considering risks, the most likely date for delivery of an acceptable product is March 31st. Even that date is not particularly bankable. You probably don’t want to publish any delivery date before April 15. At least with a later date, you’ll have a better than fifty-fifty chance of making it. If you want a date you’ll have virtually zero chance of missing, you’ll have to go all the way out to the first of June.

Using this second chart, you could talk about cost:

–Budget at completion should be  1.1M€ in order to include a contingency reserve of 200k€. We got to this amount by subtracting 50% percentile (1.1M€) minus 0% percentile (900k€).

When he asks you how these useful graphs have been produced, you can answer:

–We’ve used a quantitative risk analysis technique called  Monte Carlo method.

–I assume you are using some expensive tool for that? I did not authorize such a cost!

–Easy, we’re just using native Microsoft Excel.

The RAND function

If you model properly the project activity risks, Monte Carlo technique will help elaborate a response on overall project uncertainty. This technique is based on the computers capability nowadays to generate random numbers between 0 and 1. In Microsoft Excel you can use the RAND function to get a random number between 0 and 1 with the same probability, that is, following a uniform distribution U(0,1).

Using these numbers, you can simulate a lot of possible results for activity durations and costs, which you can sum up to compute the total project duration and cost, in order to represent uncertainty ranges with probability graphs.

Using Monte Carlo to calculate number pi

The simplest application of Monte Carlo doesn’t have to do with projects, but with the calculation of number π. Let’s imagine a circular sector of radius 1 inside a square of side 1:

In the figure above, you can see, for instance, that point (0.4,0.3) falls inside the circular sector since 0.4²+0.3²=0.25<=1, whereas the point (0.8,0.8) falls outside because 0.8²+0.8²=1.28>1.

What is the probability of any point (x,y) of the square falls inside the circular sector? If you divide favorable by possible outcomes, you get a probability of π/4.

• Favorable outcomes = circular sector area = π/4.
• Possible outcomes = square area = 1.

If you are proficient in statistics, you will know how to obtain this result algebraically. You model X and Y as uniform distribution variables U(0,1) and then you calculate that the probability of the compound variable (X²+Y²) being less or equal 1 is just π/4.

Monte Carlo is a numeric method to get these results. Using Monte Carlo we don’t get any algebraic expressions, but numbers, the more accurate the higher the number of simulations.

Let’s see now how to use Monte Carlo to calculate π. You can download herethe Excel file I’ve used.

First you generate enough random points (x,y) with RAND() function, then you count the amount of them meeting the condition x²+y²<=1, divide this number by the total and multiply by 4, and you will get a number close to π.

Using 10,000 simulations, I’ve got a result 99,68% precise:

Using 20,000 simulations, I’ve got a result 99,89% precise:

And finally, using 30,000 simulations, I’ve got a result 99,97% precise:

Using Monte Carlo to estimate project durations and costs

Dr. Mario Vanhoucke in his book The Data-Driven Project Manager, propose the following cases to model statistically the activities of any project:

• If the activity has no risk, we can trust single point estimate coming from expert judgment. You can model the activity with the most likely estimate.
• If the activity has low risk –estimation is quite precise except for unexpected variations due to random factors– you can use a symmetric beta or triangular.
• If the activity has a known risk –causes of variances are well known and the project management team know the activity will be earlier or later if causes happen– then you can use a asymmetric beta or triangular.
• If the activity has an unknown risk –the project management don’t have the proper knowledge to produce a reliable estimate– you can set a range of possible values and model the activity as a uniform distribution.
• If the activity is a black swan –beyond the expected situation, very low probability, very high impact– you can manage them as assumptions. With regard to Monte Carlo analysis, you can model them as no risk activities.

The book uses a case study of a tennis stadium construction project. See the following table with activity duration estimates.

To consider risk, activities are modeled with three points: optimistic, pessimistic and most likely. In order to choose the type of statistical distribution, we have to consider the qualitative risk analysis for the activity:

• symmetric triangular or beta if activity has a low probability
• asymmetric triangular or beta if activity has a medium probability
• uniform if activity has a high probability

Impact could be modeled according to the distance between optimistic and pessimistic points.

Using the table data, applying the central limit theorem, you can estimate the project duration as a normal distribution of mean 53 weeks and standard deviation of 1.83 weeks.

It is very convenient to mode the project as a normal distribution centered in week 53 (μ=53), with a standard deviation of 1.83 (σ=1.83).

Since this is a normal function, we know that there is a 68% probability for the interval (μ-σ,μ+σ), a 95% probability for the interval (μ-2σ,μ+2σ) and a 99% probability for the interval (μ-3σ,μ+3σ). So, for our project, we could easily say:

We can finish between weeks 51 and 55 with a probability of 68%

We can finish between weeks 49 and 57 with a probability of 95%

We can finish between weeks 47 and 58 with a probability of 99%

We could also say, for instance, using Excel formula NORMDIST(50,53,1.83,1) = 5%:

The probability of the project being earlier than 50 weeks is just a 5%.

The issue is that, in project management, is not quite applicable the central limit theorem, since variance of sum is not always the sum of variances.

Using Monte Carlo simulations, you will get a more precise probability distribution. As you can see below, it does not look much like a bell curve:

In the lower graph you can see that percentiles 22%, 33% and 93% corresponds, respectively, to weeks 48, 50 y 60. So you can say this:

There is a probability of 71% (=93%–22%) for the project to finish between weeks 48 and 60, and a 33% of finishing before 50 weeks.

If you click this link you can download the Excel file I’ve created. I encourage you to use it and share it freely. I thank you in advance for any comments, please.

This article is also available in Spanish