In a Monte Carlo simulation, a Bernoulli distribution works like a railway switch. It describes a random experiment with exactly two possible outcomes: an event occurs or it does not. Depending on the outcome of the experiment, the simulation takes one path or the other.
Such experiments are everywhere: in a coin toss, in an exam, in medicine, in production or in risk management. Below we show how the distribution is defined, what it is used for and when it fits. Then we simulate it step by step in Excel with Risk Kit.
What is a Bernoulli distribution?
A Bernoulli random variable takes two values, success or failure. As numerical values we usually use 1 for success and 0 for failure. Multiple occurrences do not exist.
The distribution has a single parameter, the success probability p. With this probability the random variable takes the value 1, with the complementary probability the value 0:
P(X = 1) = p and P(X = 0) = 1 − p
The term success is meant purely technically. It denotes the event we are interested in, even if it is an unwanted one, such as a defective part or a machine failure.
The classic example: a coin toss
The classic example of a Bernoulli distribution is a coin toss. The coin has two sides, heads and tails. We assign success to one of these sides and failure to the other. Success occurs with a probability of 50%, and so does failure. Figure 1 shows this as a bar chart: the blue bar stands for “Kein Erfolg” (no success), the orange one for “Erfolg” (success), and both reach 50%.
Figure 1: Bernoulli distribution with a success probability of 50%, the fair coin toss (German labels)
Any success probability
The Bernoulli distribution is much more general, though. It is not limited to fair experiments in which the probability of occurrence is 50%. The probability of occurrence can be anything.
Take the board game Ludo, for example. At the start of the game my piece waits at home, and I have to roll a six to enter the game. The die has six sides, but I only care about the six. The other five sides are a failure. So we have a Bernoulli experiment with a success probability of one sixth. Success occurs with a probability of about 17%, no success with about 83%. In Figure 2, the orange bar for success has shrunk accordingly to about 17%, and the blue bar for “Kein Erfolg” has grown to about 83%.
Figure 2: Bernoulli distribution with a success probability of one sixth (German labels)
With general success probabilities there are many applications for the Bernoulli distribution:
- A student passes an exam or not.
- A patient survives an operation or not.
- A light bulb stays intact or it burns out.
- A manufactured part works or it is defective.
What role does the Bernoulli distribution play in a simulation?
In a simulation, the Bernoulli distribution usually acts as a trigger. If the event occurs, a new path opens up that the simulation follows.
In Ludo this is easy to see. If I manage to roll a six, I enter the game and can move around the board. This path stays hidden from me as long as I do not roll a six.
The Bernoulli distribution therefore decides which part of the model comes into play in a simulation run at all.
How do you model the occurrence of a risk?
An important field of application for this trigger function is risk analysis. The Bernoulli distribution can represent whether a risk occurs. If the risk does not occur, nothing happens. If the risk occurs, a new path opens up, and we have to determine how large the damage caused by the risk is. So further random experiments follow that determine the size of this damage.
Overall, simulating a risk is therefore a two-stage process. At the beginning, a Bernoulli distribution decides whether the risk occurs. Further steps then determine how the risk plays out.
When does a Bernoulli distribution fit?
A Bernoulli distribution fits whenever we are only interested in whether an event occurs or not, and when it can occur at most once in the period under consideration. This applies to all the examples above: an exam is passed or not, a part is defective or not.
Sometimes the Bernoulli distribution also fits because the period under consideration is so short that a second occurrence is ruled out on factual grounds. A machine may fail every now and then. But if I limit the period to one week and the machine fails, it may take me so long to repair it and bring it back into production that it cannot fail a second time in that week. In this case I have a Bernoulli experiment: in this week the machine fails or it stays intact. The period is then part of the model. If you later extend it or split it into shorter sections, you thereby also change how often the event can occur in total.
If an event can occur several times in the period under consideration, you need a different distribution. More on this at the end of this article.
How do you simulate a Bernoulli distribution in Excel?
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. Whether you simulate with Risk Kit, with Python or with any other tool: the Bernoulli distribution has the same parameter everywhere and delivers the same results.
We stay with the dice example. In our spreadsheet, the success probability is in cell B8. It contains the formula =1/6, and the cell shows a rounded 17%. The Bernoulli random variable is to be created in cell D8. We proceed in five steps:
- Select cell D8, open the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface) and choose the Bernoulli distribution.
- In the input dialog, enter the only parameter P, the success probability. We enter a reference to cell B8. To the right of the field, Risk Kit shows the value the reference points to: 0.166666667, that is, one sixth (Figure 3).
- Clicking “Insert” makes Risk Kit write the function =Bernoulli_variate(B8) into cell D8.
- Mark cell D8 as an output and name it “Bernoulli-Verteilung”. Risk Kit adds OutputName("Bernoulli-Verteilung") to the formula and highlights the cell in yellow (Figure 4).
- Run the simulation.
In Figure 3, cell B8 on the left shows 17%, and the formula bar at the top shows its formula =1/6. The title bar of the dialog, “[D8] = Bernoulli_variate(...)”, shows that the function is inserted into cell D8. The orange wedge points there too. The number below the field P is a single sample draw.
Figure 3: Cell B8 contains the success probability =1/6. In the input dialog for cell D8, the parameter P refers to B8, and the value 0.166666667 is shown next to it (German interface).
Figure 4: Cell D8 is marked as an output. The formula bar shows the complete formula =Bernoulli_variate(B8)+OutputName("Bernoulli-Verteilung") (German interface).
After the simulation, Risk Kit shows the result for the output “Bernoulli-Verteilung” (Figure 5). On the left is the histogram of the simulated values. It has only two bars: one at 0 that reaches about 0.83, and one at 1 that reaches about 0.17. The distribution therefore takes exactly two values, 0 with a probability of about 83% and 1 with about 17%. On the right are the statistics of the simulated values. The mean is 0.17, which is the success probability.
Figure 5: Simulation result. On the left the histogram with the values 0 and 1, on the right the statistics, including a mean of 0.17 and a standard deviation of 0.37 (German interface).
Where does the Bernoulli distribution get its name?
The Bernoulli distribution is named after the Swiss mathematician Jacob Bernoulli. He showed that if a random experiment is repeated thousands of times, the proportion of successes gets closer and closer to the success probability. This is exactly what we see in Figure 5: the bar at 1 reaches about 0.17, and the mean is also 0.17, that is, one sixth.
Which distributions can be built from it?
In a Monte Carlo simulation, the Bernoulli distribution is a building block. If it were a Lego brick, it would be the smallest brick there is. Other distributions can be built from the Bernoulli distribution, for example the binomial and the Poisson distribution. They can also represent events that occur several times in one period.
More distributions
For an overview of all distributions, see our guide “When Do I Use Which Distribution?”.
- When do I use a binomial distribution in a Monte Carlo simulation?Definition, formula and examples: how the binomial distribution describes the number of successes in n independent trials and how to calculate it in Excel.
- When do I use a Poisson distribution in a Monte Carlo simulation?Definition, formula and examples: how the Poisson distribution describes the frequency of independent events and when it fits.
- When do I use a normal distribution in a Monte Carlo simulation?Definition, formula and examples: when the normal distribution fits, where its limits lie and how to truncate it.
- When do I use a triangular distribution in a Monte Carlo simulation?Definition, formula and example: when the triangular distribution fits, why the most likely value is not the expected value and where its limits lie.
- Quantifying expert estimates in a Monte Carlo simulation: the Expert distributionQuantifying expert estimates better: how the Expert distribution captures estimates through bounds and quantiles, even without a fixed minimum or maximum.
- When do I use a PERT distribution in a Monte Carlo simulation?Definition, formula and example: how the PERT distribution describes a bounded duration with minimum, most likely value and maximum, and where its limits lie.
- When do I use a lognormal distribution in a Monte Carlo simulation?Definition, properties and limits: when the lognormal distribution correctly describes proportional growth processes and how to parametrise it in Excel.
- Custom distributions in a Monte Carlo simulation: the Flex distributionCustom distributions: how the Flex distribution is built from values and probabilities and when it fits, from expert estimates to transferring simulation results.