The Expert distribution is a distribution for quantifying expert estimates, for example in risk management, better and more meaningfully. Experts here are subject matter experts: people who know certain professional relationships well from their daily work and are therefore asked to estimate them, for example how long a delivery may take or how often the IT is attacked. As a rule, they are not statisticians. The Expert distribution is tailor-made to be parameterised by such experts: it works with parameters that such specialists can easily picture. This puts it in one group with the PERT distribution, the triangular distribution and the uniform distribution. In practice, the PERT distribution and the triangular distribution are the most commonly used distributions in this area.
These parameters are bounds and quantiles. Below we show how it is defined. We then use an example to see where the PERT distribution reaches its limits and how the Expert distribution offers a way out. A second example shows what else it can do. Finally, we use it in Excel with Risk Kit.
What is an Expert distribution?
The PERT distribution has three parameters: the minimum, the most likely value, also called the mode, and the maximum. The Expert distribution has not three but six parameters:
- The lower bound is the counterpart of the minimum. In Risk Kit it is called Lb.
- The upper bound is the counterpart of the maximum. In Risk Kit it is called Ub.
- The median is the 50% quantile, that is, exactly the middle of the distribution.
- Q1 is a quantile to the left of the median.
- Q2 is a quantile to the right of the median.
- The probability p determines which quantiles Q1 and Q2 are. In Risk Kit this parameter is called P.
If we set p = 5%, for example, Q1 is the 5% quantile and Q2 is the 95% quantile. A quantile is a value that the result stays below with a given probability. So the result falls below the 5% quantile in only 5% of cases and below the 95% quantile in 95% of cases. Expressed as a formula, where P(X ≤ Q1) stands for the probability that the result X is at most Q1:
P(X ≤ Q1) = p, P(X ≤ Median) = 0.5, P(X ≤ Q2) = 1 − p
If the bounds are given, all values of the distribution lie between them:
Lb ≤ X ≤ Ub
The probability p must lie between 0 and 0.5, as the note in the input dialog also says (see Figure 5). Both bounds are optional. We show what that means further below.
When does the PERT distribution reach its limits?
Let us take an example where the PERT distribution reaches its limits. The uncertainty we want to model is delivery times, for example for a delivery from China. Transport takes at least 25 days. The most likely value is 35 days, and the maximum is 60 days.
In Figure 1 these figures are in the spreadsheet on the left: the minimum of 25 in cell D10, the most likely value of 35 in D11 and the maximum of 60 in D12. For the delivery time in cell D15, we chose the PERT distribution from the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface). In the input dialog, Min, Mode and Max refer to cells D10, D11 and D12. On the right of the dialog, the density of the distribution is drawn. The horizontal axis shows the delivery time in days, the vertical axis the density: the higher the curve, the more often delivery times occur in that range. The curve starts at 25 days, peaks at 35 days and runs out at 60 days. This is the classic PERT distribution. On the far right, a table lists statistics of the distribution, including the expected value, the median and various quantiles.
Figure 1: The classic PERT distribution for the delivery time with a minimum of 25, a most likely value of 35 and a maximum of 60 days from cells D10 to D12 (German interface).
But there is a chance that the Suez Canal will be closed. This happens only rarely, but then delivery takes up to 120 days. This value is in cell D13. If we raise the maximum of the PERT distribution from 60 to 120 days by pointing Max to D13, the following happens.
In Figure 2 the curve now reaches 120 days. This length is correct, because we entered the value 120 ourselves. The problem is the thickness of the tail. The PERT distribution has rather broad shoulders: its probability is spread widely over the range between the peak and the maximum instead of running out in a thin, long tail. If we push the maximum far to the right, a lot of probability therefore moves along with it. We now see delivery times of more than 60 days in very many cases, sometimes even more than 100 days. The table on the right shows this too: the median is now 44.62 days, the 95% quantile 76.92 days and the 99.9% quantile 102.17 days. Extreme delivery times are therefore assumed with too high a probability. As a model, this is very unrealistic.
Figure 2: The same PERT distribution with a maximum of 120 days from cell D13. The tail is very thick, so extreme delivery times are assumed too often (German interface).
In practice, this often leads to the distribution being cut short artificially so that it does not reach into unrealistic regions. But this also excludes the long tails that are particularly critical for the delivery. So you are left with a choice between two evils.
What way out does the Expert distribution offer?
The Expert distribution offers a way out here. To show this, we add it as an overlay to the PERT distribution, that is, as a second curve in the same chart. We find it directly in the group of popular distributions.
First we use the Expert distribution to rebuild the PERT distribution. We set P to 5%. Then Q1 is the 5% quantile and Q2 the 95% quantile. We read the values we need to match the PERT distribution well from the table on the right: the 5% quantile of the PERT distribution is 27.97, the median 44.62 (entered in the dialog rounded to 44.6) and the 95% quantile 76.92. The lower bound Lb refers to the minimum in D10, the upper bound Ub to the 120 days in D13.
Figure 3 shows both distributions in one chart. The orange area is the PERT distribution, the blue line the Expert distribution. The dashed vertical lines mark the 5% quantile, the median and the 95% quantile. In the table, these three rows are printed in bold. The blue line lies essentially exactly on top of the orange area. So we can reproduce the PERT distribution very closely with the Expert distribution.
Figure 3: The Expert distribution (blue line) rebuilds the PERT distribution (orange area) when it takes over its 5% quantile, median and 95% quantile (German interface).
Using the degrees of freedom
Beyond that, we can now make use of the degrees of freedom of the Expert distribution. We reinterpret the maximum of 60 days: it is a fairly safe value that the delivery time stays below. It is a bad case, but not the worst case. So we enter 60 as the 95% quantile, and Q2 now refers to cell D12. This alone changes the distribution considerably. Then we lower the median to 35 days, the value that was given as the most likely value before.
Figure 4 shows the result. The blue line of the Expert distribution starts at the minimum of 25 days, rises steeply to a high peak and then falls quickly. It still stretches out to 120 days, but the region above 60 days is now reached with a probability of only 5%. That is much less often than with the PERT distribution, whose orange area extends far to the right. The Expert distribution does not have such broad shoulders, it is much slimmer. The markers labelled in blue in the chart belong to the Expert distribution, those labelled in orange to the PERT distribution. The table on the right still shows the statistics of the PERT distribution.
Figure 4: With a median of 35 and 60 days as the 95% quantile, the Expert distribution becomes much slimmer. Only 5% of cases lie above 60 days (German interface).
Even so, the Expert distribution has only parameters that can be interpreted. They are all quantiles, that is, values an expert can easily picture: the minimum as the 0% quantile, the maximum as the 100% quantile, the 5% and the 95% quantile as the good and the bad case, and the median as the middle of the distribution.
In addition, the median is easier to understand than the mode. As the “most likely value”, the mode sounds intuitive, but in everyday life it is less familiar than the middle, which half of the cases fall below and the other half exceed. With this, we have solved the problem of the broad tails of the PERT distribution and can represent the delivery times sensibly with the help of the Expert distribution.
How do you model a quantity without a known maximum?
The Expert distribution offers even more options than we have seen in this first example. In the input dialog, some parameters are printed in bold and some in regular type. The bold ones are required parameters, the regular ones are optional. The optional ones are the lower bound Lb and the upper bound Ub, that is, the minimum and the maximum. We can leave them out if we want to. This widens the range of applications of the Expert distribution considerably.
Take as an example the attack frequency in a cyber risk, that is, how often I am attacked. There is a lower bound: fewer than zero attacks are impossible. With a probability of 5%, I am attacked fewer than 20 times. The middle of the distribution is around 35 attacks, and I am 95% sure that I am attacked fewer than 65 times. This could, for example, be information I received from my IT department. It recorded the attack frequency this year and last year.
But what is the upper bound for the attack frequency? Can I name a maximum? That is not so easy. It is always possible that the data from last year or the year before are no longer representative of what will happen in the future. I only have to get on the radar of professional hackers, and all bets are off. Then I am attacked much more often than I can infer from my data. That is why it is important that I can leave the upper bound open.
If we modelled the attack frequency with a PERT distribution or with a triangular distribution, we would always have to assume that there is an upper bound, that is, a maximum attack frequency, and that we know it. Both assumptions are completely unrealistic.
Figure 5 shows the Expert distribution for this example. We set it up in cell F15 of the same worksheet. The labels on the left still belong to the delivery time example. The input dialog contains P = 5%, Lb = 0, Q1 = 20, Median = 35 and Q2 = 65. The field Ub stays empty, and next to it it says “any”: the upper bound is open. The chart shows that the distribution then develops a very long tail. Almost the entire area lies on the left, and the dashed line marks the 95% quantile at 65. Even so, the horizontal axis extends to 650. So I am 95% sure that I am attacked at most 65 times, but the number can also go well beyond that. The table on the right shows how far: 92 attacks are not exceeded with a probability of 99%, 107 with 99.5% and 150 even with 99.9%. The maximum is given as ∞, that is, infinite.
The formula bar at the top shows the finished formula in cell F15: =Expert_variate(5%;0;20;35;65). The arguments are in the same order as in the input dialog: P, Lb, Q1, Median and Q2, separated by semicolons as in the German version of Excel. The open upper bound Ub is simply left out at the end.
Figure 5: Attack frequency with an open upper bound. The Expert distribution develops a very long tail, and the maximum in the table is infinite (German interface).
The whole region on the right up to 650 is reached with an even smaller probability. In the chart, Risk Kit always shows the range up to an event of 1 in 1 million. This means that in a simulation with 1 million runs, the attack frequency would actually reach the order of 650.
Open on one side or on both sides
The Expert distribution thus opens up a completely new group of situations for expert estimates: situations in which a minimum and a maximum are not necessarily known and may not even be sensible to assume. We can leave both bounds open.
For the attack frequency, for example, we could specify a maximum of 120 attacks and leave out the minimum. Then the distribution is open to the left and can also take negative values. Or we go so far as to say that neither exists, no minimum and no maximum. Then the distribution is open on the left and on the right. So we can describe every single case precisely: closed on both sides, closed on one side and open on both sides.
Can you derive the parameters of the Expert distribution from data?
The Expert distribution goes one step further, though. Because its parameters are all quantiles, I can also derive them from data. In the attack frequency example, the lower bound of zero follows from general considerations, and the values 20, 35 and 65 come from the data the IT department recorded.
If I think that these data are no longer representative, I can overwrite them with expert estimates in a controlled way. I can then state which values are better suited today to assess the situation, because the circumstances have changed, than what we observed last year and the year before.
Feedback on contradictory inputs
If I make extreme assumptions in these expert estimates, the system will, if in doubt, tell me that there is an inconsistency. The parameters then contradict each other to some extent. An example: the 5% quantile is at 20 attacks, and I say that the median, that is, the 50% quantile, is at 22 attacks. Then I get the error message that the median must lie between 23.2 and 56.1 to be consistent with 20 as the 5% quantile and 65 as the 95% quantile.
Figure 6 again shows the dialog for cell F15. The field Median contains 22. To the right below the fields, where the current value of the function is otherwise shown, it now says “Es muss 23,17…” (“It must be 23.17…”). Pointing the mouse at it shows the complete message: “Es muss 23,1720445835834 < Median < 56,1020843590558 sein”, meaning that the median must lie between these two values. In this state, the chart and the table on the right do not belong to the inputs, and the curve is only lightly hatched.
Figure 6: A median of 22 does not fit 20 as the 5% quantile and 65 as the 95% quantile. Risk Kit reports that the median must lie between 23.2 and 56.1 (German interface).
So I can respond directly in order to obtain better expert estimates.
When is an Expert distribution a good fit?
We can sum up: the Expert distribution broadens the horizon of expert estimates, both qualitatively and quantitatively. It is tailor-made to be parameterised by experts. The examples point above all to these cases:
- The quantity can, in rare cases, take very extreme values, like the delivery time when the Suez Canal is closed. The PERT distribution would assume such a long tail far too often, or you would have to cut it off. With the Expert distribution we can shape the course of the distribution.
- A minimum or a maximum is not known or not sensible to assume, as with the attack frequency. With a PERT or triangular distribution we would have to assume an upper bound and know it. The Expert distribution, by contrast, can be open on one side or on both sides.
- There are data from which quantiles can be derived. With them we can put the situation into context, see exactly in which range the data lay, and overwrite them with expert estimates in a controlled way if needed.
- The parameters should be easy to understand. An expert can easily picture bounds, the median and quantiles for a good and a bad case.
In addition, the system gives us feedback when we have entered contradictory values.
How do you simulate an Expert 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. The Expert distribution itself is available in Risk Kit and is also part of the Enterprise Risk Explorer. The idea behind it does not depend on the tool: describing an uncertain quantity through bounds and quantiles that an expert can easily picture.
We stay with the delivery time example. In our spreadsheet, the figures are in column D: the minimum of 25 in cell D10, the most likely value of 35 in D11, the maximum of 60 in D12 and the 120 days with the Suez Canal closed in D13. The delivery time is to be created in cell D15. We proceed in five steps, as described above:
- Select cell D15, open the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface) and choose the PERT distribution. In the input dialog, Min refers to D10, Mode to D11 and Max to D12, later to D13 (Figures 1 and 2).
- At the bottom left of the dialog, click the arrow next to the small distribution button and choose “Add overlay” (“Overlay hinzufügen”). In the group of popular distributions, choose the Expert distribution. The fields P, Lb, Q1, Median, Q2 and Ub now appear below the fields of the PERT distribution (Figure 3).
- Enter the parameters of the Expert distribution: P is 5%, Lb refers to D10, Q1 is 27.97, the median 35, Q2 refers to D12 and Ub to D13 (Figure 4).
- Using the same arrow, choose “Remove Pert_variate” (“Entferne Pert_variate”). Now only the Expert distribution is left in the dialog.
- Clicking “Insert” makes Risk Kit write the function Expert_variate with these parameters into cell D15.
Figure 7 shows the dialog after step 4, just before inserting. The title bar and the description below it still name the PERT distribution, but the fields, the chart and the table already belong to the Expert distribution. Because it is now the only distribution in the dialog, Risk Kit draws it as an orange area. The table on the right shows what we have set: the minimum is 25 days, the 5% quantile 27.97, the median 35, the 95% quantile 60 and the maximum 120 days. So only 5% of cases lie above 60 days.
Figure 7: The input dialog for cell D15 just before inserting. The PERT distribution has been removed, and the Expert distribution with the parameters from Figure 4 remains (German interface).
More on this: Our article “Expert and Poly Distributions” presents the Expert distribution together with the Poly distributions, which adapt to expert estimates and data analyses alike.
More distributions
For an overview of all distributions, see our guide “When Do I Use Which Distribution?”.
- When do I use a Bernoulli distribution in a Monte Carlo simulation?Definition, formula and examples: when the Bernoulli distribution fits and what role it plays as a switch in a simulation.
- 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.
- 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.