Wehrspohn Risk Management

How do you assess the risk of business interruptions from a pandemic with a Monte Carlo simulation?

Case study

By Dr. Uwe Wehrspohn.

An epidemic can bring a business to a standstill when too many employees are ill at the same time. Using a model in Excel, we show how to simulate the spread of an epidemic in a business with 500 people, including superspreader events, how to derive the probability and duration of business interruptions from it and how to compare measures such as a ban on large events, testing and quarantine, vaccination and fewer contacts.

The Corona pandemic kept all of us very busy for several years. For companies, every epidemic raises a very concrete question: how likely is it that so many employees fall ill at the same time that operations have to be interrupted, and how long does such an interruption last?

Below we show a model with which this personnel risk can be assessed methodically. It includes complications we saw during the pandemic: superspreader events, which accelerated the spread massively, and countermeasures such as vaccination and quarantine. The assessment takes place in five steps:

  1. We determine the factors that influence the risk of temporary closures caused by a mass infection. These are the ingredients of the model.
  2. We determine the order of magnitude of these factors, because the extent to which the individual things happen matters.
  3. We describe the relationships between the factors that drive the dynamics of the whole process. This is the recipe.
  4. We evaluate the model with a Monte Carlo simulation. This is, so to speak, baking the bread.
  5. We analyse the results. Because an epidemic can be influenced in many ways, we run a series of Monte Carlo simulations for this and compare them in a scenario analysis.

Which factors determine the risk of a business interruption from an epidemic?

We include eight factors in the model:

  1. The size of the business matters because an infection that has appeared can also die out again, above all in a small group. In a very large group such as the 80 million people in Germany, on the other hand, chance hardly plays a role any more. The law of large numbers takes over, and the epidemic develops as if on rails. To still see random effects there, you would have to divide the population into small local groups. Instead, we look at a medium-sized business.
  2. The number of sick people from which the business has to be interrupted determines, the other way round, how long the business can be kept running even though some employees are ill. We need it to assess the probability and duration of an interruption.
  3. The number of infected people at the start plays a central role. If it is very small, these infected people are considerably more likely to recover before they have infected enough others to set an epidemic in motion at all.
  4. The number of daily contacts each employee has determines how many people who are not yet infected an infected person can infect. The more contacts there are, the more infections there are.
  5. The degree of contagion states how likely someone who is not yet ill becomes infected on contact with a sick person.
  6. The duration of the illness states how long someone is ill and contagious before recovering. The longer this lasts, the more other people they can infect.
  7. The frequency of superspreader events matters because on such days people come into contact with far more others than on a normal day. Examples are a staff meeting, an après-ski party, a carnival party, a wedding or church services at Christmas time. Such events were clearly noticeable in the Corona pandemic and attracted a lot of attention.
  8. The speed with which infected people go into quarantine is already a measure, but it clearly influences the course of the infection. The faster infected people are detected and put into quarantine, the lower the risk of infection they pose.

What assumptions does the model use?

Figure 1 shows the orders of magnitude of the factors we assume in the example.

Figure 1: The orders of magnitude of the factors in the example. “Ws” stands for Wahrscheinlichkeit (probability), “SE” for superspreader event (German slide).

We look at a business with 500 people, essentially one site. If a larger company has further sites that are far away, so that infection between them is not easily possible, these are considered separately.

We assume that a business interruption becomes necessary from 150 infected people, because production can then no longer be maintained. This number is chosen arbitrarily. Where it lies depends on the individual company, and in the model it can simply be overwritten.

Initially, 2 infected people come into the company, and the infection spreads from them. Each person has 10 contacts a day.

A lot of information on the course of infection of a particular disease comes from the Robert Koch Institute. On its website it has published epidemiological profiles of infectious diseases, for example on SARS-CoV-2 and COVID-19, with information such as the routes of transmission, the reproduction number and the incubation period. From them you can obtain parameters that are reasonably realistic. Which ones these are depends on the type of epidemic.

The infection rate applies to a contact between an infected and a susceptible person. A susceptible person is someone who is not yet ill and has not yet recovered. We therefore assume that someone who has come through the infection is immune for the time being. For each contact between an infected and a susceptible person, the probability of infection is 3%.

The recovery rate is the probability with which an infected person recovers on a given day. A recovery rate of 10% corresponds to an average duration of illness of 10 days. In individual cases the illness is shorter or longer, which is why we represent it as a probability.

The quarantine rate is the probability with which an infected person is recognised as such and sent into quarantine. When the epidemic begins, no measures have been taken yet, so the quarantine rate is 0%. Setting it to a higher value would already be a measure against the epidemic. Analogous to the Robert Koch Institute, the Paul Ehrlich Institute has published data on the effectiveness of the tests for COVID-19.

Finally, the superspreader event consists of two inputs. With a probability of 10%, a superspreader event takes place on a given day, so roughly once every two weeks. On that day the contacts rise from the normal 10 to 100.

How does the epidemic spread in the model?

In short: Risk Kit is an add-in for Microsoft Excel that lets you build and evaluate Monte Carlo simulations directly in the spreadsheet. What we show here applies in general, though: such an epidemic model can be built and evaluated on the same principle in any simulation tool.

Figure 2 shows the model in Excel on the sheet “Pandemie-Modell” (pandemic model). Row 4 contains the factors with their values from Figure 1, and the blue area below it contains their relationships. The epidemic develops over days 1 to 100, which are numbered in column A. That is a relatively short period, but in this time the epidemic essentially does everything in its power.

Figure 2: The model on the sheet “Pandemie-Modell”. Row 4 at the top contains the factors, below it there is one row for each day. On day 7 in row 15 a superspreader event takes place with 100 contacts and 91 new infections (German interface).

For each quantity in the table we distinguish the start day and the following days. On every following day the value is calculated from the values of the previous day.

  • Column B contains the susceptible people (“Anfällige”), that is, the people who can still be infected. On the first day these are the 500 people minus the 2 initially infected, so 498. Cell B9 contains the formula =B4-C4 for this. On every following day it is the susceptible people of the previous day minus the newly infected of the previous day.
  • Column C contains the free infected (“Freie Infizierte”). These are the undetected infected who are not in quarantine and can therefore infect susceptible people. On the first day these are the 2 initially infected. On every following day the newly infected of the previous day are added to the free infected of the previous day. Those from this group who recovered on the previous day and the admissions to quarantine on the current day are subtracted.
  • Column D contains the infected in quarantine (“Infizierte in Quarantäne”). On the first day nobody is in quarantine yet. On every following day those admitted on that day are added to the infected in quarantine of the previous day, and those who recovered in quarantine are subtracted.
  • Column E contains the recovered (“Genesene”). On the first day nobody has recovered yet. On every following day those who recovered on the previous day are added to those recovered so far, from both groups, the undetected infected and the infected in quarantine.

Contacts and superspreader events

Column F contains the contacts (“Kontakte”) that everyone in the company has on that day, except the people in quarantine. It takes one of two values: 10 on a normal day or 100 at a superspreader event. Which case occurs is decided for each day by a Bernoulli experiment. This is a random experiment with exactly two outcomes, similar to a coin toss. The formula in cell F9 reads as follows, where WENN is the German name of Excel’s IF function:

=+WENN(Bernoulli_variate($I$4);$J$4;$D$4)

With the superspreader probability of 10% from cell I4, the Risk Kit function Bernoulli_variate returns a 1. The formula then returns the 100 contacts from cell J4. With a probability of 90% it returns a 0, and it is a normal day with the 10 contacts from cell D4. By chance there can be several superspreader events in close succession, in Figure 2 on days 2 and 3. After that things are quiet for a while until the next one comes on day 7.

More on this: Our article “When do I use a Bernoulli distribution in a Monte Carlo simulation?” explains how this distribution describes events with exactly two outcomes, such as whether or not a superspreader event takes place on a given day.

Recovery and quarantine

The model decides who recovers on the same principle. For each free infected person it tosses a coin, so to speak, which shows a 1 with the recovery rate of 10%. This produces the newly recovered in column H. Exactly the same applies to the infected in quarantine in column I.

The admissions to quarantine in column J are zero on the first day. From the second day on they result from the previously undetected infected whom a test successfully finds, and from the quarantine rate. Every infected person we send into quarantine increases the admissions by one. Finally, the total infected in column K are the sum of the free infected and the infected in quarantine.

How do you simulate the infections?

The newly infected in column G are the most complicated quantity in the model, because they depend on a great many factors. Figure 3 shows the formula in cell G9. The coloured frames mark the cells it accesses:

=NeuInfektionen($B$4;B9;C9;D9;E9;$F$4;F9)

Figure 3: The formula for the newly infected in cell G9. The coloured frames mark the cells that go into the function NeuInfektionen, German for new infections (German interface).

The function queries the total number of people (B4), the infection rate for an encounter between an infected and a susceptible person (F4), the number of susceptible people (B9), of free infected (C9), of infected in quarantine (D9) and of recovered (E9), as well as the contacts each free infected person has on that day (F9). The infected in quarantine do not take part in the spread of the infection. We still need them to calculate how many susceptible people are left.

Risk Kit functions in Visual Basic

NeuInfektionen is a function in Visual Basic, and Figure 4 shows its code. What is special about it is that you can use the Risk Kit functions in Visual Basic. To do so, in the Visual Basic editor you activate the Risk Kit add-in under “Extras” and “Verweise” (Tools and References) and create a variable of type RiskKitComServer, here named ran. Through it, the Risk Kit functions are accessible in the code, for example ran.Hypergeometric_variate and ran.Compound.

Figure 4: The function NeuInfektionen in Visual Basic. Through the variable ran, the Risk Kit functions Hypergeometric_variate and Compound are available in the code (German interface).

If there are no susceptible people left, nobody can be infected any more, and the function returns zero. Otherwise it keeps a counter for the infected, which at the start contains the free infected and the infected in quarantine. It then checks in a loop, for each free infected person, how many people that person infects.

For each infected person, the function first counts how many susceptible people are left: the total number of people minus the counter for the infected and minus the recovered. It is important that the running counter is used here. If, for example, there are 10 free infected on a day, two or more of them could infect the same susceptible person. The function has to keep track of this. Every infection therefore raises the counter for the infected and lowers the number of remaining susceptible people, like a pair of scales. Once all susceptible people are infected, the epidemic has run its course, and the function stops the loop.

The hypergeometric distribution: drawing as in the lottery

An infected person meets not only susceptible people but also recovered people, whom they can no longer infect, and people who are already infected. How many susceptible people they actually have contact with on that day is drawn by the function with Hypergeometric_variate as a random number from the hypergeometric distribution. That sounds complicated, but it is nothing other than the distribution of the lottery.

In the German lottery “6 aus 49” (6 out of 49) there are 6 winning balls. We do not know which ones they are, but we know they are there. In the model this corresponds to the following:

  • The winning balls are the susceptible people we want to draw.
  • The 49 balls we draw from are the people without the infected in quarantine. You cannot meet anyone who is in quarantine.
  • The number of draws corresponds to the contacts a person has on that day.

It is then determined at random how many winners are among those drawn, that is, how many susceptible people the infected person actually meets.

How many contacts lead to an infection?

How many of these susceptible people the infected person actually infects is determined by the Risk Kit function Compound. For each contact with a susceptible person it draws a random number from a Bernoulli distribution, which returns a 1, that is an infection, with the infection rate of 3%. It adds up the results, and the newly infected are added to the counter for the infected.

Once the loop has run through all free infected, the new infections of the day follow as the increase: the counter for the infected minus the free infected and the infected in quarantine at the start of the round. The function is the same on every day. With this, the dynamics of the epidemic are fully described.

How does an epidemic run its course in a business?

How the individual quantities develop over time is shown in Figure 5 for a single random course. The blue line is the susceptible, who become fewer and fewer over time. The grey line is the recovered, who start at zero and keep increasing. The orange line is the total infected on a given day, and the yellow line the newly infected of that day.

Figure 5: A single random course of the epidemic over 100 days. The spikes in the yellow line of the newly infected are caused by superspreader events (German interface).

Among the newly infected there is a spike every now and then. These are superspreader events. The large spike around day 7 is the superspreader event from Figure 2, on which the number of newly infected jumps from 13 to 91. Later superspreader events remain only small spikes, because by then only a few susceptible people are still available, and at the end they no longer play any role at all.

Each recalculation produces a new random course. In one of them there are very effective superspreader events with 149 and 197 newly infected on a single day. As a result, the total number of infected rises drastically to 222 and on the next day to 399. Here, too, the epidemic is over very quickly: a short rise is followed by a fall in the number of infected.

The whole process runs towards a saturation point at which the number of recovered stops rising. Interestingly, this does not necessarily mean that everyone in the company has caught the disease. In this course only 495 people were infected in total. After that there were no more infected people who could have infected others. In Figure 5, too, the grey line of the recovered stays below 500 at the end.

When the epidemic dies out by itself

In some cases the epidemic dies out. This happens when the free infected, who are the only ones able to infect others, recover faster than they infect others. Figure 6 shows such a course. For a long time there was no superspreader event, the contacts are 10 every day, and there were some quick recoveries. On day 4 only one free infected person is left, from day 5 none.

Figure 6: A course in which the epidemic dies out. From day 5 there are no free infected any more. Cell C28, highlighted in yellow, with the free infected after 20 days is an output cell of the simulation (German interface).

Anyone who is still ill then gradually recovers until there are only recovered and susceptible people. Their sum is always exactly 500; in Figure 6 these are 497 susceptible and 3 recovered.

How likely is a business interruption if you do nothing?

To evaluate the model, we define several statistics on the sheet “Pandemie-Modell”:

  • First, we measure the probability with which the epidemic dies out. To do so, we mark the free infected after 20 days in cell C28 as an output cell of the simulation. The function Probability measures the probability with which this value is less than or equal to zero. If there are no free infected left after 20 days, the epidemic dies out.
  • Second, we count the days of a business interruption. This is where the threshold comes into play: we count the days on which the total infected reach at least 150. The probability that there are more than zero such days is the probability of a business interruption.
  • Third, for the duration of a business interruption, if one occurs, we calculate the 10% quantile, the mean duration and the 90% quantile. With the function Plot we also display the distribution of the duration as a histogram on the sheet “Ergebnisse” (results).
  • Fourth, we look at the maximum number of infected, that is, how many people are ill at the same time at most. In one random course this is about 234, in another 350.
  • Finally, we want to see how the total infected develop across all simulation runs, that is, the orange line from Figure 5 for all runs together. This course is a stochastic process, a random development over time. We define it with the function ProcessDefinition from the days in column A and the total infected in column K. The function PlotProcess displays it on the sheet “Ergebnisse” as a family of curves with the 1%, 10%, 90%, 95% and 99% quantiles.

More on this: How to simulate stochastic processes and display them with ProcessDefinition and PlotProcess is shown in our case study “How do you simulate price movements with a Monte Carlo simulation?” using the oil price and an interest rate as examples.

Because the model and the results are on two sheets, the simulation has to cover the entire workbook and not just the active worksheet. Since the simulation is very time-consuming, we only calculate a small number of simulation runs. Each run produces a new random course of the epidemic over the 100 days.

The result without measures

Figure 7 shows the sheet “Ergebnisse” after the simulation. At the top are the scenario “Wir werden unvorbereitet getroffen” (we are caught unprepared), that is, without any countermeasure, and its parameters, with the results below.

Figure 7: The results when we are caught unprepared. At the top the probabilities (WS, short for Wahrscheinlichkeit) and the duration of a business interruption, on the left the infected over time with the quantiles (dark blue) and the mean (light blue), on the right the distribution of the maximum number of infected (German interface).

The probability that the epidemic dies out again within 20 days is 6.0%. A business interruption, on the other hand, is very likely and occurs with a probability of 93.2%. Its duration is 10 days at the 10% quantile, 12 days on average and 15 days at the 90% quantile.

On the left, the course starts almost at zero, namely at the 2 initially infected. It rises, reaches a maximum after a few weeks, falls again and fades out. After three months the epidemic is over in practically all simulation runs, so looking at 100 days is clearly sufficient. The top line, the 99% quantile, reaches around 350 infected.

On the right, the maximum number of infected lies roughly between 130 and 420 if the epidemic does not die out. That is an enormous range, and really a lot of people are infected at the same time. If the epidemic dies out, the number stays close to the 2 initially infected. That is the single bar at the far left. The third chart further down the sheet shows the duration of a business interruption. It is 0 days if there is none and otherwise between 1 and 18 days.

What measures are there, and how do you calculate the scenarios?

So far we have simply let the epidemic run, and it really ran its course. The question is what can be done about it. There are many levers we can pull. Figure 8 shows them on the sheet “Parameter”, summarised as six scenarios S1 to S6.

Figure 8: The sheet “Parameter”. At the top in the blue area the active scenario, below it in the yellow area the six scenarios, at the bottom left the quantiles for the family of curves (German interface).

S1 is the scenario without measures. The other five scenarios change individual parameters:

  • S2 “Keine Großveranstaltungen” (no large events): we ban large events so that there are as few superspreader events as possible, that is, no staff meetings, no carnival parties and no Christmas church services. The superspreader probability falls from 10% to 0%, and all other values remain the same.
  • S3 “Testen und isolieren” (test and isolate): with all other values unchanged, we set a quarantine rate of 20%. So each day we find 20% of the undetected infected and send them into quarantine, so that they no longer infect anyone. The superspreader probability is back at 10%, because we consider quarantine as an isolated measure.
  • S4 “Impfen” (vaccinate): the infection rate falls a little, from 3% to 2%. According to the Robert Koch Institute, the main effect of vaccination in the Corona pandemic was not necessarily that people were infected less often. That happened only to a small extent. The essential effect was that the courses of illness were no longer as severe and that people did not die of it. Our model does not consider severe courses and deaths. That is why the infection rate with vaccination is only one percentage point lower than without.
  • S5 “Kontakte reduzieren” (reduce contacts): we lower the daily contacts from 10 to 5. Everything else remains the same, including the superspreader events.
  • S6 “Kombination aus S2-S5” (combination of S2-S5): we take all four measures at the same time. So we ban large events, test and isolate, vaccinate and reduce contacts.

Equally, you could form any mixed forms or play through what happens if we reduce contacts even more, test even more effectively or use a vaccine with which the infection rate is below 2%. To do so, you only adjust the parameters, and the evaluation works exactly as below.

We evaluate the six scenarios in a batch simulation. It runs via a Visual Basic script that can be found on the Risk Kit toolbar under “Beispiele” (examples) in the example “Batch Simulation”. The script copies each data set from the yellow area in turn into the active parameter area at the top, evaluates it and copies the results into a new worksheet. Afterwards the workbook contains six new sheets, named after the abbreviations S1 to S6.

What do the measures achieve?

In the first scenario without measures, the epidemic again really takes off. Because every simulation calculates with new random numbers, the values differ somewhat from Figure 7: a business interruption now occurs with a probability of 94.8%, the epidemic dies out with a probability of 4.4%, and the maximum number of infected reaches over 400. An interruption lasts 9 days at the 10% quantile, 13 days on average and 15 days at the 90% quantile. We compare the other scenarios with this result.

No large events

Figure 9 shows the result for S2, in which the only thing we do is ban large events. The range of how many infected occur at the same time at most becomes considerably smaller. The histogram on the right now only reaches a little over 200 instead of over 400.

Figure 9: Scenario S2 without large events. The superspreader probability is 0%, and the 99% quantile stays well below 200 infected (German interface).

The curves no longer run as high either. The highest curve, the 99% quantile, stays well below 200; in the first scenario it was around 350. The business interruptions become somewhat shorter: the longest previously lasted 17 or 18 days, now somewhat less, and on average they last 6 instead of 13 days. Above all, however, a business interruption now occurs with a probability of only 60.0%. The probability that the epidemic dies out rises somewhat, to 7.6%.

Testing and isolating

Figure 10 shows the third scenario. Superspreader events still occur, but now with a quarantine rate of 20%. In terms of the mean, the course is much flatter and much more drawn out.

Figure 10: Scenario S3 with testing and quarantine. The mean number of infected runs much flatter than without measures (German interface).

A great deal can be achieved with the quarantine rate. If it were 100%, we would recognise and isolate every infected person immediately. The two initially infected would be filtered out on the first day and recover after some time, and the epidemic would be over straight away.

A low quarantine rate of 20% is what you might have if the tests are not always applied consistently and not everyone tests themselves as they should, but only some people do. Even then, the probability of a business interruption falls to 32.0%, and the epidemic dies out with a probability of 44.0%. The business interruptions also become somewhat shorter; here they last at most 14 days.

Vaccination and fewer contacts

Vaccination, which merely lowers the infection rate from 3% to 2%, has a relatively small effect. The curves look very similar to those without measures. However, the 99% quantile falls from around 350 to less than 300, so the curve does become flatter, and the maximum number of infected is no greater than 370. A business interruption occurs with a probability of 78.8%. Long interruptions are hardly shorter than without measures: the 90% quantile is 14 instead of 15 days.

Fewer contacts work in a similar way to vaccination. The superspreader events still occur, and they are what matters. Reducing the daily contacts to 5 has only a small effect: the probability of a business interruption falls to 80.4%. The duration hardly changes; here, too, the 90% quantile is 14 days.

All measures together

In the last scenario we take all measures at the same time, and now there is a resounding overall effect, shown in Figure 11. The epidemic dies out within 20 days with a probability of 96.0%, so almost certainly. There is no longer any business interruption; it did not occur in a single simulation run. At most nine people were ill at the same time.

Figure 11: Scenario S6 with all measures together. The quantiles of the infected stay between 0 and 5, and the maximum number of infected lies between 2 and 9 (German interface).

What does the comparison of the scenarios show?

Individually, testing and isolating and the ban on large events lower the probability of a business interruption the most, to 32.0% and 60.0%. Vaccination and fewer contacts are considerably weaker at 78.8% and 80.4%. Only all measures together prevent a business interruption completely.

This example shows how an epidemic and its effect on a business can be analysed. You can weigh the individual influencing factors against each other in terms of their effectiveness. You can evaluate combinations of measures and see what happens overall when you do several things together. And you can set targets for improving individual measures, for example raising the quarantine rate from 20% to higher values or lowering the infection rate of 2% even further through better vaccines.

The model is one of the examples that ship with Risk Kit. You will find it on the Risk Kit toolbar under “Beispiele” (examples).