Anyone who wants to build a Monte Carlo model for the first time often faces the question of how to get from a problem to a model that can be calculated. A Monte Carlo simulation calculates a model very many times, each time with newly drawn random numbers for the uncertain quantities, and analyses the results of all runs statistically.
We show this with three examples that all follow the same school class. First we ask when the bus for the school trip can leave if it has to wait for all 26 pupils. Then the class is buried by an avalanche, and we ask how many helicopters the rescue needs. Finally the pupils insure each other against the cost of their treatment. Each example shows a different idea of model building: the maximum of many uncertain times, a simulation in two stages and the balancing of many risks within a group.
When can the bus leave?
The class of 26 pupils wants to meet at the school at 8 o’clock in the morning to travel to the ski lodge. Every pupil plans to be there at 8 o’clock. Experience shows, however, that you do not leave at 8 o’clock but considerably later. This is because the trip can only begin once the last pupil is on board. If one pupil is missing, everyone has to wait.
This is exactly what makes the example representative of many applications at work. A project that consists of a series of tasks is only finished when all tasks are finished. A car that leaves the production line is only finished when all parts have been fitted. In a supply chain, a product cannot be built if something is missing. And a complex calculation distributed across many servers takes as long as the longest partial calculation. If there are queues and other random effects, the question of how long it takes can no longer be answered at a glance.
Each pupil is a risk factor
In short: The three models are built with Risk Kit, 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: a Monte Carlo model is built on the same principles in any simulation tool.
To represent the school trip as a Monte Carlo simulation, we list each pupil in Excel as a risk factor, that is, as an uncertain quantity that affects the result. Figure 1 shows the worksheet. Column B holds the expected arrival, which is 8 o’clock for everyone. Each of them is punctual on their own. Their arrival is still uncertain, though, and column C states how large this uncertainty is in minutes. For simplicity it is 10 minutes for everyone. The sheet calculates times of day as decimal numbers in hours.
Figure 1: The school trip model in Excel. Each pupil has an expected arrival in column B and an uncertainty in minutes in column C. Column D holds the simulated arrival as a decimal number in hours, and cell D32 the departure (German interface).
For a Monte Carlo simulation, we have to assume a distribution for each risk factor, that is, describe which values it can take and how likely they are. We assume that each pupil’s arrival time is normally distributed, with an expected value of 8 o’clock and a standard deviation of 10 minutes. Because the sheet calculates in hours, we divide the 10 minutes by 60. Cell D4 holds the following for Leo:
=Normal_variate(B4;C4/60)+OutputName(A4)
Normal_variate draws a new arrival time from the normal distribution with every calculation. OutputName gives the risk factor the pupil’s name from column A, so that we can assign it in plain language later on. The same formula stands in the row of every pupil.
More on this: Our article “When do I use a normal distribution in a Monte Carlo simulation?” explains how the normal distribution is described by its expected value and standard deviation and when it fits.
The actual departure time is simply the maximum of the arrival times of all pupils. When the last one jumps on the bus, the doors can close and we can leave. Cell D32 therefore holds:
=+MAX(D4:D29)+OutputName(D31)
This cell, named “Abfahrt” (departure), is the output cell of the model, that is, the result we want to analyse. In the simulation run shown in Figure 1, the arrivals lie between 7.7 and 8.4, and the departure is 8.4, the latest arrival. For comparison, cell B32 holds the maximum of the expected values. Since all expected values are 8, it is 8 as well. Naively, we would therefore expect the bus to leave at 8 o’clock.
Why does the bus leave later than expected?
To analyse the departure, we calculate a few statistics of the output cell: the mean, the earliest and the latest departure, the median and the quantiles at 10, 25, 75 and 90%. A quantile is the value that the result does not exceed with a given probability. Risk Kit provides the functions Mean, Minimum, Maximum, MedianSim and Perc for this. In addition, the function Plot draws a histogram of the departure times.
Now we start the simulation with 10,000 simulation runs. Figure 2 shows the results. The statistics are in column G as decimal numbers and next to them, in column H, as times of day. The mean of 8.3 corresponds to 8:20.
Figure 2: The results after 10,000 simulation runs when all pupils have an uncertainty of 10 minutes. On the left are the statistics of the departure as decimal numbers and as times of day, on the right the histogram of the departure times (German interface).
On average, then, we only leave at 8:20. Because all 26 pupils have to be on board and we can only start once the last one has arrived, the departure is not at the maximum of the expected values but later. The probability that someone is late is simply too high. Even the earliest departure in the 10,000 simulation runs was only at 8:06. In not a single run did the bus leave on time at 8 o’clock.
This shows that in this situation you cannot calculate with expected values. That is only possible under very specific conditions, in particular when a relationship is additive and linear. Here, however, it is about the maximum and not, say, the sum of the arrival times. That is why the expected value of the actual departure time is systematically larger than the maximum of the expected values.
At 8:19, the median is close to the mean. In the worst run, the bus only left at 8:50, almost an hour late. The histogram still looks somewhat symmetrical, but it is not: it falls off more steeply at the bottom, and at the top it runs out considerably further.
We can transfer this finding directly to project management. We must always plan for individual activities to be late, and from the outset we need a buffer that absorbs this delay at the end.
Which pupils delay the departure?
So far, all pupils behave in the same way. Often, however, the pupils in a class are not all equally reliable. Most are very reliable and inconspicuous, and a few have much more uncertainty. They may be on time, but they may also be very clearly late. We simply enter this: in column C, Emilia gets an uncertainty of 15 minutes, Matteo of 20 minutes, Marie of 25 minutes and Anton of 30 minutes. Do we find these needles in the haystack of the class, so that we can talk to them beforehand?
To see the effect, all we have to do is run the simulation once more. Figure 3 shows the new results.
Figure 3: The results after Emilia, Matteo, Marie and Anton have been given an uncertainty of 15, 20, 25 and 30 minutes in column C. The minimum stays at 8:06, while the mean, the 90% quantile and the maximum rise (German interface).
The mean has risen from 8:20 to 8:29, while the minimum is unchanged at 8:06. In the early departures, the four hardly make themselves felt. In the runs that led to the minimum, they were evidently on time as well. For the large delays, things look different. The 90% quantile has jumped by 20 minutes, from 8:27 to 8:47, and the maximum is no longer at 8:50 but at 10:11.
The sensitivity analysis finds the needles
Can we now identify the four? This is what the sensitivity analysis is for. It shows how strongly the result reacts to the individual risk factors. To use it, we mark the arrival times as input cells and select the pupils and the departure in the dialog of the analysis. The pupils’ names appear there because we assigned them with OutputName. Otherwise there would be cell references such as D4 instead of Leo.
Figure 4 shows the linear sensitivities. They are nothing other than correlations, so they measure how closely a pupil’s arrival is related to the departure. Anton, Marie, Matteo and Emilia stand out at once. Their bars are clearly longer and neatly graded. From Oskar onwards, there are only small fluctuations.
Figure 4: The linear sensitivities of the departure to the 26 pupils. The bars of Anton, Marie, Matteo and Emilia clearly stand out (German interface).
Figure 5 shows the absolute deviations. They give the effect of each pupil in hours, as a deviation from the mean departure. Anton has a massive upward effect, and so do Marie, Matteo and Emilia. The other pupils have hardly any effect.
Figure 5: The absolute deviations show the effect of each pupil on the departure in hours, measured as a deviation from the mean. The bars of Anton, Marie, Matteo and Emilia reach far to the right (German interface).
The different lengths of the short bars are random effects caused by drawing random numbers. The long bars, on the other hand, are systematic effects. The four problem candidates can therefore be recognised at once, and even in the right order: Anton with 30 minutes of uncertainty has the greatest influence, followed by Marie with 25, Matteo with 20 and Emilia with 15 minutes. The bars also show the direction. If Anton arrives very early, everyone tends to leave earlier. If he arrives late, the departure can shift considerably later. With Emilia, the accelerating effect is barely noticeable any more.
Figure 6 shows how a pupil acts across their entire range. The horizontal axis shows the quantile of the individual pupil, that is, how early or late they arrive compared with their own distribution. The vertical axis shows how far the departure then deviates from its mean. Each line belongs to one pupil. The line that rises highest on the right is Anton’s. Like his bar in Figure 5, it reaches just over 0.4.
Figure 6: The effect of the pupils across their entire range. The line that rises highest on the right belongs to Anton. The legend shows only part of the names (German interface).
If Anton arrives very early, this speeds up the whole departure, up to about his 70% quantile. Up to the 80% quantile, he slowly blends in with the crowd. If he arrives even later within his own distribution, he holds everyone up. Anton therefore has a chance of about 20% of delaying the departure. For the other conspicuous pupils this is less pronounced, because their distributions are not as wide. 70 to 80, even up to 90% of the time, these risk factors have no negative effects. They only play out their negative potential in a few cases, but then all the more drastically.
This settles which pupils the teacher should talk to beforehand. It is also very practical at work. Anyone who can see in advance where relatively large problems may arise with bad luck, in a project, a supply chain or a server control system, can take preventive action there.
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”.
How many helicopters does the rescue need?
In the second part, the class has arrived at its ski camp and is buried by an avalanche. This part is shown in the second video of the series.
The rescue is demanding, because at least five helicopters are needed to take up the whole class. In addition, the weather can change quickly, and depending on the weather, the probability of success of each individual helicopter flight changes considerably. How many helicopters have to be deployed so that we rescue all pupils with a very high probability, for example with 95%?
This situation is typical of a service level agreement, that is, an agreement on a level of performance. The focus is always on the probability with which a service can be provided, for example that a computer system is available with a probability of 95%. There are comparable considerations for the police, ambulances or the fire brigade when every location in a city or catchment area has to be reached within 15 minutes.
A simulation in two stages
The model is divided into three parts: the input data, the actual simulation in two steps and the evaluation. The input data state which weather occurs with which probability. Fog and snowfall, fog only, snowfall only and sun occur with a probability of 10, 25, 15 and 50%. Each weather condition corresponds to a success rate of a single flight, from 30% in fog and snowfall to 90% in sun. Next to them on the right is the service level agreement: we need five successful missions to rescue the whole class and want to achieve them with a confidence level of 95%. Figure 7 shows the model before the simulation.
Figure 7: The rescue model before the simulation. At the top are the input data with the weather and the service level agreement, below them the first simulation step with the success rate and the second with the individual helicopters. The evaluation only gets values after the simulation (German interface).
In the first stage, it is determined which weather prevails. This gives the success rate in cell B11. It follows a multinomial distribution:
=Multinomial_variate(C5:C8;B5:B8)
The multinomial distribution takes exactly the information from the table: the success rates in C5 to C8 as possible values and their probabilities in B5 to B8. It is a generalisation of the binomial distribution, which applies to events with only two possible outcomes. Here there are four possible outcomes. In the run in Figure 7, fog and snowfall have been drawn, so the success rate is 30%.
In the second stage, we look at how the helicopters behave in this weather. We have up to 25 helicopters to choose from, and each one is successful with the drawn success rate. All helicopters in a run therefore fly in the same weather. Column B holds a 1 for each flight if it was successful and a 0 if not. Column C, highlighted in yellow, adds up the successful flights from the top. It is the actual result column of the simulation: for each number of helicopters, it shows how many flights were successful. In Figure 7, the first flight was successful, and after that only the twelfth and the fourteenth.
Our key statistic is the probability of success: how likely is it that at least five helicopters reach their goal? It is in column D for each number of helicopters. The function Probability calculates it as the probability that the value in column C is greater than 4, that is, at least 5. To the right is the distribution of the successful flights with the mean, minimum, quantiles, median and maximum.
What does a high service level cost?
After the simulation with 10,000 runs, the column with the probability of success is coloured, as Figure 8 shows. Red stands for a probability of success of 0, and via orange and yellow the column turns green as the probability of success approaches 1.
Figure 8: The results of the rescue. The probability of success in column D only reaches 95% with 16 helicopters and 99% with 25 helicopters. Next to it is the distribution of the successful flights for each number of helicopters (German interface).
With one to four helicopters, the probability of rescuing everyone is 0, because we need five successful missions. It can only work at all from five helicopters upwards. If we deploy exactly this minimum, the probability of success is about one third, exactly 34.83%. For 95%, we have to go far up in number and deploy at least 16 helicopters. The probability of success is then 95.46%, and with a probability of 5% we would still lose one or more pupils. For 99%, we have to enlarge the helicopter fleet to 25 machines flying at the same time.
This is a very typical effect of a service level defined by a conservative quantile: we need enormous overcapacity. For 95%, that is 11 helicopters more than the minimum needed for five successful flights, so more than three times as many in total. We cannot really be economical, but potentially have to waste a great many resources. This also shows the risks that arise when you economise on rescue services, the police or ambulances. In the fortunate case, everything still works. But when push comes to shove, there are not enough resources.
The other statistics show this as well. On average, we already reach five successful missions with seven helicopters, where the mean is 5.2. But the minimum is 0 up to 19 helicopters. Among the 10,000 runs there was therefore at least one in which all 19 flights failed. And even with 25 helicopters there was a run in which only a single one succeeded. At the maximum, on the other hand, all flights are always successful. With luck, that can happen too.
With this result you can negotiate how high the service level should be. Lives are not always lost straight away if it is lowered a little. Often, perhaps, a computer system is just unavailable for a day, and you could massively reduce the number of servers to cut costs. In other cases you want to go very high. Above all, however, we must not have any illusions: a confidence level always causes costs and effort.
What does a joint insurance achieve?
In the third part, the rescue is followed by medical treatment, and the question arises who bears the costs. This part is shown in the third video of the series.
There are two models to choose from. In the first, the parents pay the costs of their own child and want to be 90% sure that they can afford the treatment. How much money do they have to set aside for this, and which residual risks remain? The second model is a joint provision. Here the parents want to be 100% sure that their child’s treatment will be paid in full, so that there is no quarrel between them. Is it cheaper to make provision individually or jointly?
This grassroots insurance is a mutual insurance within a small circle of people. The portfolio consists of the individual pupils and the treatment costs that each of them causes. Above all, we see the effect of diversification here. Diversification is a free reduction of the joint risk that comes from risks mixing. You get a risk reduction as a gift, without having to pay anything for it. We also see that many risks, here the 26 pupils, can be covered with a single measure.
The insurance model
Figure 9 shows the model. We describe each pupil’s treatment costs by the expected value of the expenses in column B and their standard deviation, the uncertainty, in column C. For Leo, the expected expenses are 25 euros or monetary units, and the uncertainty is also 25. All pupils up to row 25 are described in this way. From row 26 onwards there follows a very small group of high-risk pupils with expected expenses of 50 and an uncertainty of 50 as well. The last of them, Sophia in row 30, lies just below the section shown.
Figure 9: The model of the grassroots insurance. Leo and the other low-risk pupils have expected expenses and an uncertainty of 25, the high-risk pupils from row 26 onwards of 50. At the top is the formula of Leo’s realised expenses in cell D5 (German interface).
This is characteristic of insurance, because not everyone has the same risk profile. The high-risk pupils may have skied particularly riskily, may have been at a dangerous spot on the slope when the avalanche came, or may be more vulnerable for physical reasons. The reason hardly matters. All that matters is that the costs need not be the same for all policyholders.
We simulate the realised expenses in column D with a lognormal distribution. It is steep on the left and skewed to the right. Mostly, therefore, the costs stay low, but very high costs can arise with a very small probability. With the variant LogNormal2_variate, whose formula is shown at the top of Figure 9, the 25 and 25 are the expected value and the standard deviation of the lognormal distribution itself and not of the normal distribution hidden inside it. These are very intuitive parameters.
More on this: Our article “When do I use a lognormal distribution in a Monte Carlo simulation?” explains what the lognormal distribution looks like, when it fits and how to parameterise it in Excel.
From here on, Leo represents the low-risk pupils and Sophia the high-risk ones. Their realised expenses are marked as output cells so that we can calculate statistics for them. The third case is the insured pupil. His insurance premium is nothing other than the average of the costs incurred in total. We add up the total costs across all pupils and divide them per head. Cell D32 holds the following, where SUMME is the German name of Excel’s SUM function:
=+SUMME(D5:D30)/26+OutputName(H4)
Unlike with normal health insurance, the premium is therefore not paid in advance; instead, the real costs are distributed afterwards. The costs remain random, but they are random in a different way than if you were on your own. In each run, column E, “Besser als Durchschnitt?” (better than average?), shows a 1 if Leo’s expenses are at most as high as those of the insured pupil, and a 0 otherwise. The mean of this cell across all runs is the probability that Leo is better off than the insured pupil. The same applies to Sophia.
How does the insurance change the costs?
After 10,000 simulation runs, a chart shows the histograms of the three distributions. At first they are pressed together very strongly at the left edge, and at the right edge we see the long tails of individual high costs. The plotted range runs from the 0% quantile, that is, the minimum, to the 100% quantile, the maximum. If we set the upper end to 90%, we cut off the tails. The distributions then move clearly towards the middle, and we can compare much better what they look like. Figure 10 shows the result.
Figure 10: The histograms of the insured pupil (orange), the high-risk pupil (blue) and the low-risk pupil (yellow), cut off at the top at 90%. On the right, Max. % is set to 90 (German interface).
The blue bars belong to the high-risk pupil. Costs close to zero have only a very small probability for him; then the distribution rises and falls off to the right over a long stretch. The yellow bars of the low-risk pupil are pulled much further to the left. He has small costs with a much higher probability, but his distribution then also fades out quickly. The orange bars belong to the insured pupil. Very small costs are very rare for him. In principle, his distribution only starts at about 15, is highest between 20 and 40 and then falls off quickly.
This is a very classic effect of insurance: it removes the situations with extremely low costs and likewise those with extremely high costs. You practically never have no costs at all, but also practically never extremely high ones, and in all cases you move within a moderate range. Behind this is diversification. In a portfolio of risks, one risk factor swings upwards and another downwards, so that they partly offset each other. This offsetting effect stabilises the distribution close to its mean.
Who benefits from the insurance?
Figure 11 shows the statistics for the insured pupil in column H, the low-risk pupil in column I and the high-risk pupil in column J.
Figure 11: The statistics for the insured, the low-risk and the high-risk pupil after 10,000 simulation runs. At the top is the formula of the average costs of the insured pupil in cell H5 (German interface).
Row 5 holds the average costs. An insured pupil has average costs of 30, a low-risk pupil only 25. On average, the low-risk pupil is therefore worse off if he takes part in the insurance. This point is discussed again and again in the United States: very high-risk patients drive up the costs of insurance, and low-risk people who take good care of themselves have to pay for it. A real insurance would also have administrative costs. The high-risk pupil, by contrast, has average costs of 50 on his own and is considerably better off in terms of the expected value if he takes part.
Row 6 shows the probability with which a pupil is better off than the insured pupil. The low-risk pupil is better off with a probability of 73%, the high-risk pupil with 41%. These are considerable probabilities of being worse off by taking part. For the insured pupil himself it shows 0%, because he cannot be better than himself.
With individual provision, the parents wanted to be able to cover the costs with a probability of 90%. The 90% quantile of the costs in row 7 is 38 for the insured pupil. The distribution of his costs is therefore very narrow. For the low-risk pupil it is already 50. Although he is cheaper than the insurance on average, at the 90% quantile considerably more would have to be set aside. For the high-risk pupil it is as much as 105, almost three times as much as for the insured pupil. At the confidence level of 90%, both are therefore better off with the insurance.
Row 8 reverses the question: which quantile of the insured pupil do the amounts of 38, 50 and 105 correspond to? For 38 it is 90% by construction. For the insured pupil, 50 would already be the 99.53% quantile. With this amount he can therefore cover all costs much more safely than the low-risk pupil. The insured pupil does not get anywhere near 105 in the 10,000 runs, which is why it shows 100%.
Row 9 holds the “maximum” costs. The quotation marks are there because the 99.99% quantile is meant. For the insured pupil it is 71, for the low-risk pupil 309 and for the high-risk pupil as much as 687. The quantiles are calculated by the function Perc. The reverse question, which quantile an amount corresponds to, is answered by the function Probability. Row 10 thus shows that for the low-risk pupil the 71 correspond to just the 95% quantile, exactly 95.36%. For the high-risk pupil it is not even 80% but 79.13%.
Here we see a very typical result of risk management: the higher you choose the confidence level, the more measures pay off. If the costs fall from 309 or from almost 700 to 71, the insurance has paid off massively. With a single measure, averaging the costs, we covered the whole class. This made it possible to cover 100% of the costs without the costs getting out of hand. The conservative target often demanded of insurance in practice can therefore be achieved here without ruining yourself.
What do we learn from this for building models?
Each of the three examples shows a basic idea that you need again and again when building a Monte Carlo model:
- You can only calculate with expected values if a relationship is additive and linear. If the result depends on the maximum of many uncertain times, as with the departure of the bus, it is systematically later, and we need a buffer.
- Often a few uncertain risk factors cause the large deviations, just as Anton, Marie, Matteo and Emilia caused the late departures. A sensitivity analysis finds them, so that you can take preventive action there.
- A simulation can run in several stages. In the rescue, the weather is drawn first and then the success of the individual flights.
- A service level defined by a conservative quantile needs enormous overcapacity. A confidence level never comes without costs and effort.
- If you pool many risks in a portfolio, they partly offset each other. This diversification costs nothing, makes the distribution considerably narrower and pays off all the more, the higher the confidence level.
The three examples are very basic, archetypal models for applications of Monte Carlo simulation. In practice they can be generalised and adapted to your own use case.