How far can a project deviate from its plan, what does that cost, and which tasks drive the deviation? Below we show, using a project with five milestones and 25 tasks in Excel, how to answer these questions with a Monte Carlo simulation. It calculates the project very many times with randomly drawn durations and evaluates the results statistically.
The starting point is simple expert estimates. For each task, the project manager gives a plan value and an upper bound that he is fairly certain of. From these we build distributions and simulate the plan deviation of the whole project in days and in euros. We then check how accurate the result is and what shape its distribution has, transfer it to the company’s risk aggregation, look for the drivers and finally track the risk as the project progresses.
How is the project structured?
The project consists of five milestones. Milestones 1, 2, 3 and 5 run one after the other. After milestone 1, however, milestone 4 branches off, runs in parallel with milestones 2 and 3 and rejoins the first path before milestone 5 begins. The project is complete when milestone 5 is fully finished.
Each milestone breaks down into five tasks that run in parallel. The project manager has already given a planned duration for each task, for example 81 days for task 1. A milestone is finished when all its tasks are complete. Its duration is therefore the longest duration of its tasks.
Figure 1 shows the project in Excel. Each box is a milestone with its five tasks, and the arrows show the sequence. The column “Plan” holds the planned duration of each task in days, and the row “Gesamt” (total) the planned duration of the milestone. For milestone 1 this is the 98 days of task 2. We will need the columns “90%” and “Ist” (actual) in the next sections. For milestone 1, the actual cells of the tasks are still empty.
Figure 1: The project in Excel with five milestones of five tasks each. At the top is the formula of the planned duration in cell U14, which gives 335 days. The actual cells of the tasks of milestone 1 are still empty (German interface).
The duration of the project follows in the same way. It is the longer duration of the two paths, that is, of milestones 1, 2 and 3 or of milestones 1 and 4, plus the duration of milestone 5. The formula of the planned duration in cell U14 reads:
=+MAX(B19+G19+L10;B19+L28)+Q19
B19, G19, L10, L28 and Q19 are the planned durations of milestones 1 to 5. The path via milestones 2 and 3 takes 98 plus 96 plus 45 days, the path via milestone 4 only 98 plus 22 days. With the 96 days of milestone 5, this gives a planned project duration of 335 days.
Here the cost of the project is proportional to time, at 15,000 euros per day. The project therefore costs 5.025 million euros in total. In Figure 1 this appears on the right as the planned cost.
How do experts estimate the duration of the tasks?
How can we now estimate the risk of a plan deviation? For this, the project manager has already given an indication for each individual task. He has added a maximum duration to the plan value, for task 1 for example 86 days compared with 81 days. It is in the column “90%” in Figure 1.
This maximum should not be understood to mean that the project manager is absolutely certain that the task will not take longer than 86 days. Rather, he is saying: I am fairly certain that it will not take longer. If everything goes wrong, it can take longer after all, but one would not expect it to.
We state “fairly certain” precisely as 90% certain. The 86 days are therefore the 90% quantile of the duration, that is, the value it does not exceed with 90% probability. So for each task we have two figures: the plan value and the 90% quantile.
How does the estimate become a PERT distribution?
In short: Risk Kit is an add-in for Microsoft Excel that lets you build and analyse Monte Carlo simulations directly in the spreadsheet. What we show here applies in general, though: analysing project risks with a Monte Carlo simulation works on the same principle in any simulation tool.
To translate the project manager’s estimate into a representation of uncertainty, we use the PERT distribution. It was developed by the US Navy in the 1950s specifically for project risk management and therefore fits our task exactly.
The classic parameterisation of the PERT distribution consists of the minimum, the most likely value and the maximum. But there is also an alternative parameterisation with two percentiles, that is, two quantiles whose probability is given in percent. Here the minimum is replaced by a lower percentile and the maximum by an upper percentile. The most likely value stays.
More on this: Our article “When do I use a PERT distribution in a Monte Carlo simulation?” explains what the PERT distribution was developed for, what it looks like and when it fits.
To determine the lower percentile, we go back to the context of project risk management. In a project it is generally not possible to undercut the planned duration by any amount, only to a limited extent. We therefore assume that the absolute minimum, that is, the 0% quantile, is 85% of the planned duration. The most likely value, also called the mode, is the project manager’s plan value. The upper percentile is his fairly certain upper bound, the 90% quantile. Risk Kit fits a PERT distribution through these three points, and the maximum follows from them.
Figure 2 shows this for task 1. In the dialog of the function Pert2P_variate, the “2-Perzentil-Parametrisierung” (2-percentile parameterisation) is selected. The lower percentile consists of PMin and QMin: PMin is 0, and QMin is U$7*B14, that is, the 85% from cell U7 times the plan value of 81 from cell B14. This gives a minimum of 68.85 days. Mode is the plan value from cell B14. The upper percentile consists of PMax at 90% and QMax with the upper bound of 86 days from cell C14. The dollar sign before the 7 fixes the row of the 85%, so that the reference is kept when the formula is copied down to the other tasks.
Figure 2: The PERT distribution for task 1 in the 2-percentile parameterisation. The minimum is 85% of the plan value, that is, 68.85 days, the peak is at the plan value of 81 days and the 90% quantile is 86 days (German interface).
At the bottom, the dialog shows the density of the distribution, with its statistics to the right. The higher the area above a duration, the more likely durations in that region are. We find exactly our inputs there: the minimum is 68.85 days, the peak of the density lies at the plan value of 81 days and the 90% quantile is exactly 86 days. Upwards the density runs a little beyond the 86 days, up to a maximum of about 90.6 days.
Marking tasks and milestones as inputs
Cell D14, the actual cell of task 1, then contains the formula:
=Pert2P_variate(0;U$7*B14;B14;90%;C14)+OutputName(A14;A$13)+OutputUnit("Tage")
So that Risk Kit evaluates the cell after the simulation, we mark it as an input and give it a name, a category and a unit. This is what the functions OutputName and OutputUnit at the end of the formula are for. The name is “Aufgabe 1” (task 1) from cell A14, and the category “Meilenstein 1” (milestone 1) from cell A13. Because the following tasks belong to this category too, a dollar sign fixes the row here as well. The unit is days, whereas for the project costs it will be euros. So a model can contain several units.
We copy the formula down to the other tasks and mark them as inputs as well. This defines the actual risk factors of the model, the durations of the 25 tasks.
The duration of a milestone is uncertain too. It results indirectly from its tasks, however, in cell D19 as the maximum of cells D14 to D18. We mark this cell as an input as well, so that we can analyse it later. The name is the milestone and the category is “Gesamt” (total), so that all milestones are collected in this category. The unit is again days:
=+MAX(D14:D18)+OutputName(A13;A19)+OutputUnit("Tage")
How does the plan deviation arise?
Next we determine the result, the plan deviation. Cell W14 holds it in days, labelled “Verzögerung” (delay):
=+MAX(D19+I19+N10;D19+N28)+S19-U14+OutputName(W13;"Planabweichung")+OutputUnit("Tage")
The first part of the formula is the simulated duration of the project. It is again the maximum of the two paths, milestones 1, 2 and 3 or milestones 1 and 4, plus the duration of milestone 5. This time, however, the formula uses the actual values of the milestones instead of the plan values. Since we want to see the deviation from the plan, we subtract the planned duration from cell U14. We mark the cell as an output, with the name “Verzögerung” (delay) from cell W13, the category “Planabweichung” (plan deviation) and the unit days.
We also want to assess the plan deviation as opportunity and risk in euros. To do this, we multiply the delay in cell W16 by the cost per day from cell U10:
=+W14*U10+OutputName(W15;"Planabweichung")+OutputUnit("EUR")
This cell is an output too, with the name “Chance/Risiko” (opportunity/risk) from cell W15, the category “Planabweichung” (plan deviation) and the unit euros. A negative plan deviation is an opportunity: the project finishes earlier and costs less. A positive plan deviation is a risk.
The simulation and its statistics
The model is now ready for simulation. The simulation calculates the model very many times, each time with newly drawn durations. Figure 3 shows a single one of these runs. In it, milestones 1, 2, 3 and 5 take about 393 days together, that is, 58 days longer than planned. This means additional costs of 863,510 euros. The simulated durations have decimals, and the table shows them rounded.
Figure 3: A single run of the simulation. The simulated actual durations are highlighted in green, and on the right are the two outputs, delay and opportunity/risk. At the top is the formula of opportunity/risk in cell W16 (German interface).
After the simulation, all simulated values and their statistics are available in a results dialog. Figure 4 shows it. On the left are the marked outputs and inputs, organised by our categories: under “Ausgabe” (output) the plan deviation with opportunity/risk and delay, under “Eingabe” (input) the milestones in the category “Gesamt” (total) and the tasks of each milestone. On the right are the statistics of the two outputs, from the mean and the standard deviation through the range to the quantiles from the minimum to the maximum.
Figure 4: The results dialog after the simulation. On the left are the marked outputs and inputs by category, on the right the statistics of the plan deviation in euros and in days (German interface).
On average the project takes 42 days longer than planned, which means additional costs of 629,957 euros. The quantiles show the range: the 5% quantile is a delay of 8 days, the 95% quantile one of 85 days. So in 90% of the simulation runs the delay lies between 8 and 85 days. In the most favourable run the project finished 18 days earlier than planned, in the least favourable it took 158 days longer.
How accurate is the result of the simulation?
Every statistic from a simulation is an estimate. With different random numbers, a slightly different value would come out. This random inaccuracy is called the Monte Carlo error. It gets smaller the more simulation runs you do.
If you move the mouse over a statistic in the results dialog, Risk Kit places a confidence interval around it. This is a range in which the true value lies with high probability. Here it runs from the bound at 2.5% to the bound at 97.5% and so covers 95%. In this way the confidence interval shows the Monte Carlo error.
Figure 5 shows this for the 99% quantile of opportunity/risk. The estimate is 1,656,569 euros, and the confidence interval runs from 1,606,494 euros to 1,708,876 euros. The Monte Carlo error here is therefore roughly plus or minus 50,000 euros.
Figure 5: The confidence interval for the 99% quantile of opportunity/risk runs from 1,606,494 euros to 1,708,876 euros. The mouse pointer hides parts of the values at 99.5% and 99.9%, which are shown in Figure 4 (German interface).
This lets us see the Monte Carlo error transparently and decide whether to do more simulation runs to reduce it. Or we find that even fewer runs are enough and get a faster simulation in return.
Why is the plan deviation no longer PERT distributed?
Often you want to transfer the simulated distribution to another context. There are two ways to do this. You can copy the statistics and paste them elsewhere. Or you calibrate the distribution, which means fitting a distribution with a few parameters to the simulated results. To do this, we choose which distributions are shortlisted.
Figure 6 shows the result for opportunity/risk. On the left are the candidates with their Kolmogorov-Smirnov value, a measure of the deviation from the simulated data: the smaller the value, the better the distribution fits. The Poly distribution fits very well at 0.0031, and the Flex distribution, which is also very flexible, almost as well at 0.0032. They are followed by classic distributions such as the PERT distribution at 0.1055, the triangular distribution, the trapezoidal distribution and the uniform distribution.
Figure 6: Calibration to the simulated plan deviation in euros. The Poly distribution (blue) follows the simulated values, while the PERT distribution (green) lies below them in the middle and above them at the sides (German interface).
The PERT distribution is widely used, so we selected it for comparison. It does not fit the result of the simulation well. In the chart, the orange bars are the simulated values, the blue line is the Poly distribution and the green line the PERT distribution. The blue line follows the bars almost exactly. The green line lies clearly below the bars in the middle and above them on both sides.
At first this is surprising, because all the individual risk factors were PERT distributed. The plan deviation that results from the 25 risk factors, however, no longer is. This is a very typical result. Composite risks, which are fed by many uncertainties, have very long and very thin tails at the top and at the bottom. To get far in one direction or the other, everything has to go very well or everything has to go very badly. That happens with a much smaller probability than the PERT distribution assumes.
The PERT distribution is, so to speak, too thick to represent such a composite risk sensibly. You can see this in the statistics on the right of Figure 6. The 1% quantile of the simulated data is −35,417 euros, that of the PERT distribution −161,999 euros. So the PERT distribution considers large opportunities much more likely than they occurred in the simulation. At the upper end it considers large losses more likely: at the 99% quantile, the data show 1,656,569 euros compared with 1,832,081 euros for the PERT distribution.
What we need here are distributions that can adapt slimly: higher in the middle than the PERT distribution and wider at the sides. One example is the Poly distribution. Its tails are very thin but reach out without limit. In the table of Figure 6, its minimum is therefore minus infinity and its maximum infinity.
How does the project risk feed into enterprise risk management?
We can copy the calibrated Poly distribution and transfer it to the risk aggregation in enterprise risk management, that is, to the combination of all the company’s risks into an overall risk. Risk Kit contains the case study “Enterprise Risk Model” for this. Its risk inventory already includes the delay of a major project as risk 10 in the risk field project risk.
The model assesses every risk gross and net. Gross means before measures, net means after measures. For risk 10, the gross side is already estimated with a PERT distribution, but the net side is not yet quantified. That is where we paste the calibrated Poly distribution, and then we carry out the risk aggregation.
More on this: How the Enterprise Risk Model is structured and how it aggregates risks gross and net is shown in our case study “How does risk aggregation work under IDW PS 340 and StaRUG?”.
The case study contains a ready-made cockpit for the analysis. It compares the distribution of the total loss gross and net side by side. The measures have shifted it clearly to the left, towards smaller losses: the gross distribution lies to the right of the net distribution across its whole width.
Figure 7 shows the part of the cockpit with the sensitivities and the risk maps. On the left is a tornado chart, which shows the influencing factors as horizontal bars one below the other, sorted by the strength of their influence on the result. The blue bars belong to the total loss before measures, the orange ones to the total loss after measures. For risk 10, our project risk, the blue bar is long and the orange bar short. Net, it therefore has a much weaker effect on the total loss than gross.
Figure 7: Sensitivities and risk maps in the case study “Enterprise Risk Model”. In the tornado chart, blue stands for the total loss before measures and orange for the total loss after measures. Risk 10 is the project risk (German interface).
The two risk maps, which are quite general scatter plots, also show risk 10. The middle one compares the probability or frequency of occurrence with a risk contribution that is also calculated from a statistic. The right-hand one compares the risk contribution as financial loss with the reputational damage.
Experts do not have to estimate everything at once
With this approach, then, not every risk has to be estimated directly by experts. We can also assess the main risks with an individual model in which expert estimates appear in a different form. The expert then does not have to assess the entire project in one go. It is broken down into 25 very concrete factors that he can measure and where he knows exactly what happens. He can state these much more precisely than if he had to do the aggregation in his head.
In addition, choosing the distribution is evidently not that simple, as the comparison with the PERT distribution has shown. It is better to let the system choose the distribution, as in the calibration here.
Which factors drive the plan deviation?
Back in the project model, we look at what drove the risk. We are primarily interested in the financial impact and ask: which factors had the greatest influence on the plan deviation? To answer this, we calculate sensitivities, that is, how strongly the result responds to the individual input factors. In the results dialog, you select the output and the associated input factors for this.
We start with the milestones, which we had marked as inputs in their own right. Figure 8 shows the result as a tornado chart for opportunity/risk. The type of sensitivity selected is “Absolute bedingte Werte” (absolute conditional values). Each bar then shows, in euros, the range in which the mean plan deviation lies, depending on whether the milestone turns out short or long. The longer the bar, the more strongly the milestone affects the result.
Figure 8: Sensitivities of the plan deviation in euros to the five milestones. Milestone 3 has by far the strongest effect, milestone 4 hardly any (German interface).
Milestone 3 is by far the most important for the project result. Milestone 4, by comparison, is completely insignificant. It was the parallel branch and, so to speak, fell into the gap: with a planned duration of 22 days, it runs alongside milestones 2 and 3, which are planned at 96 and 45 days. It had no potential at all to delay the project and is therefore at the very bottom of the ranking.
More on this: Which kinds of sensitivities there are and which questions they answer is shown in our article “Sensitivities: Finding Problems and Setting Priorities”.
We can see what in particular is the problem in milestone 3 by calculating the sensitivities for its individual tasks. Figure 9 shows the result. Tasks 13 and 11 are the most critical, while tasks 15, 14 and especially 12 are already much less important.
Figure 9: Sensitivities of the plan deviation in euros to the tasks of milestone 3. Tasks 13 and 11 are in the lead (German interface).
With this analysis, the project manager knows before the project has even started that he must pay particular attention to these two tasks. They have the potential to put a real strain on the project. Besides the type used here, there are numerous other kinds of sensitivities.
How do you track the risk as the project progresses?
A project runs over time and changes again and again. So that the project manager can see how it develops, he saves the first analysis he made before the project began. We save all results as the simulation from before milestone 1 to a new worksheet called “M1”.
Risk Kit writes all distributions of the outputs and inputs with all random numbers to this worksheet. This is pure Excel. We can save these random numbers, send them to others, archive them and also load them back into the results dialog. But we only do that after milestone 1 is complete.
After milestone 1: actual values instead of chance
Figure 10 shows the situation when milestone 1 is finished. Otherwise the project is unchanged, and milestones 2 to 5 are as they were before. In milestone 1, however, chance has gone: the actual values have been replaced by concrete numbers and are no longer highlighted in green. Milestone 1 took 112 days in total, 14 days longer than the 98 days originally estimated.
Figure 10: The project after completion of milestone 1. The actual values of milestone 1 are fixed numbers, and it took 112 days instead of 98. The planned duration remains 335 days (German interface).
We can now continue to simulate the remaining plan deviation. It is important that we leave the originally planned 335 days unchanged.
Why the original plan stays in place
In practice, one is often tempted to adjust the plan when delays start to show. If the project has already started late, people then say: we need longer than we originally thought. If you do that, however, the impression arises that the plan is always met and that the plan deviations are much smaller than they really are. This is because you no longer refer to the original plan but to a plan that is updated again and again.
To avoid this impression, we keep the originally planned figure as the benchmark and simulate the changed project situation. We save this result too, in order to archive it. We are now at the start of milestone 2, and the new worksheet is called “M2”.
This gives us two results: “M1” from before the project began and “M2” from now. We load both back into the results dialog and compare the two financial plan deviations side by side.
What does the before and after comparison show?
Before we look at the results, we consider what we expect. Milestone 1 took 14 days longer than planned. 14 days at 15,000 euros are 210,000 euros.
Figure 11 shows both distributions as a histogram, which shows for each range of the plan deviation how often it fell there in the simulation. Orange is the plan deviation before milestone 1, blue the one at the start of milestone 2. In the lower range, on the left of the histogram, there has been a clear shift. In the upper range, by contrast, the two distributions mix better again. On the far right there is still an outlier, but in principle the two distributions come together again at the top.
Figure 11: The plan deviation in euros before milestone 1 (orange) and at the start of milestone 2 (blue). On the left the distribution has clearly shifted, on the right the two come together again (German interface).
This is confirmed by the distribution functions, which state for each amount the probability that the plan deviation does not exceed it. In the lower range, on the opportunity side, the two curves are much further apart than in the upper range, where they come together again.
The statistics confirm this too. Figure 12 shows them side by side. The simulation was run once more after Figure 4, so the values under M1 differ slightly from it.
Figure 12: The statistics of the plan deviation in euros before milestone 1 (M1) and at the start of milestone 2 (M2) side by side (German interface).
The minimum was −210,189 euros before and is now −54,521 euros, a gap of about 160,000 euros. For the mean, 634,446 euros before compare with 776,329 euros now, a gap of about 140,000 euros. At the 99% quantile, the two distributions are only about 100,000 euros apart, at 1,649,329 euros and 1,749,174 euros. All three gaps are smaller than the expected 210,000 euros, and they decrease from the minimum through the mean to the 99% quantile.
Why is the gap smaller than 210,000 euros?
The 210,000 euros measure the delay against the plan of 98 days. The simulation before milestone 1, however, had not assumed 98 days at all. Figure 13 shows the statistics of the milestones from the same simulation as M1: there, milestone 1 took 103 days on average, between 83 and 135 days depending on the run.
Figure 13: The statistics of the five milestones in the simulation before milestone 1. Milestone 1 takes 103 days on average, at least 83 and at most 135 days (German interface).
Because milestone 1 lies on both paths, every additional day in milestone 1 extends the whole project by one day. Compared with the expected 103 days, milestone 1 took only 9 days longer. 9 days at 15,000 euros are 135,000 euros, and the mean has shifted by roughly that much, about 140,000 euros.
That the gap gets smaller towards the top has a second reason: the uncertainty of milestone 1 has gone. Before, the simulation played through durations between 83 and 135 days for it, and now it is fixed at 112 days. This removes the favourable runs with a short milestone 1 at the bottom and the unfavourable runs with a very long one at the top. The distribution becomes narrower, and its standard deviation falls in Figure 12 from 359,863 euros to 325,654 euros. That is why it shifts more at the bottom than at the top.
So the delay is not made up: we have changed nothing about the rest of the project, and the remaining tasks are as uncertain as before. Against the plan, the project is 14 days or 210,000 euros behind. Against the simulation before milestone 1, the gap is smaller because that simulation had already allowed for part of this delay.