Quantitative Risk Analysis for Projects on Excel

A risk register is a project risk management tool. It is used to identify potential risks in a project or an organization, sometimes to comply with regulations, but mainly to be aware of potential problems that can derail expected results. The risk register includes all the information about each identified risk, such as the nature of that risk, the level of risk, to whom it belongs and what are the mitigation measures established to respond to it. It acts as a “repository of all identified risks and includes additional information on the nature of the risk, reference and owner, mitigation measures”.

We know that qualitative risk heat maps are very poor tools for conducting consistent risk analysis. Here we justify why we do not use them as an adequate project risk management methodology:

We saw in the previous lesson why heat maps or risk matrices are a very poor tool to assess the risks of a project. The obvious alternative to a qualitative risk analysis, then, is a quantitative risk analysis.

Some think that sophisticated software and advanced statistical knowledge are required to successfully perform a quantitative risk analysis of a project. We are going to demonstrate in this video that doing this is not complicated at all and that it can be done completely with just Excel.

The most popular tool for doing quantitative risk analysis is called a Monte Carlo simulation. We are going to explain this tool with an example in Excel

Now open the file "Quantitative Project Risk Model". This file comes in two versions "unsolved" or "resolved". Choose the one that best suits you based on your best learning style. Some prefer to solve the exercise as the instructions are given, so they can open the "unsolved" version. Others prefer to continue the exercise with the workbook already "solved".

Remember that, in any case, these are workbooks loaded with macros, so the Excel security level must be set to at least "Medium" or "Low", so that Excel supports opening macros once the workbook to open.

A Building company has defined the following list of 20 risks in a specific Building project:

We go to the first risk, "Unknown site conditions".

For example, the first two risks in our risk register respond to this type of “single binary event” risk. Either they happen or they don't happen. There is no middle ground.

Unique binary events are relatively easy to quantify in a risk register. Its expected value, for example, is calculated simply by multiplying the probability of occurrence by the average magnitude of its impact.

The fourth risk of "Natural Disasters" is similar to the previous one. By admitting that it is a risk that could occur more than once during the life of the project, you should also use a function that allows more than binary frequencies. Assuming that the disaster expert claims that there could be, on average, 2 or 3 natural disaster events during the duration of the project, we insert a Poisson distribution and a 2.5 in their only lambda parameter. Note that the value of this parameter does not necessarily have to be an integer. In this case, it is just the average between 2 and 3. With the cursor on G5 the frequency graph for this risk would look like this.

Before proceeding to run the Monte Carlo simulation, there is something to understand from this particular workbook regarding the columns where hazard frequencies and severities are stored: columns G and L respectively.

In statistics, a histogram is a graphic representation of a variable in the form of bars, where the area of ​​each bar is proportional to the frequency of the represented values. They are used to obtain a general "first view", or panorama, of the distribution of the population, or of the sample, in this case of the simulated numbers, with respect to a characteristic, quantitative and continuous (the magnitude of the risk). In this way, it offers an integral vision allowing to observe a preference, or trend, on the part of the simulated data because it is located towards a certain region of values ​​within the spectrum of possible values ​​(whether infinite or not) that the evaluated characteristic can acquire.

The best way to understand the impact of individual risks, or risk categories, on overall risk results is through tornado charts. Otherwise, tornado charts help prioritize which risks are important and which are not so important in quantitative prioritization.

Let us go back to Outputs tab and place your cursor on Total Risk, cell D2. Then click again on the Tornado icon. Remember that the risk with the greatest impact on the total risk is “Availability of Construction Materials” with a coefficient of 0.59 and the category with the greatest impact is Logistics Risk with a coefficient of 0.68. Click on the Tornado Chart icon to generate it:

We hope we have dismantled the myth that performing quantitative risk analysis on projects is complicated, esoteric, difficult, and only reserved for advanced professionals in statistics, mathematics, or actuarial science.

It is possible to build an introductory quantitative risk management model for a project using simple software on top of Excel, with suitable macro-coded applications.

On the other hand, we hope that we have also justified why we do not recommend qualitative risk analysis given the enormous limitations and weaknesses of this so-called “methodology”.

If you have not done so yet, I suggest you navigate to www.dtsimulator.com where you can download a free version of this Excel software that allows you to perform this type of Monte Carlo simulations without any complications. As we have shown, this is just a workbook loaded with powerful Excel macros.

Let us summarize the steps to perform a quantitative risk analysis for a project:

Monte Carlo simulation users usually have a common question: how many iterations to run a simulation? As almost anything in life, it all depends! In this case, it depends on several factors such as the complexity of the model (number of variables and their relationships, correlations, shapes of the distributions, etc.), the availability of time and the precision required on the answers (technically known as the convergence of your model).