Wehrspohn Risk Management

Custom distributions in a Monte Carlo simulation: the Flex distribution

By Dr. Uwe Wehrspohn.

With the Flex distribution you define a custom distribution: by values and the probabilities that the result is less than or equal to these values, which means you can draw its shape essentially freely. We explain its definition, density and distribution function, when to use it, how to simulate it in Excel with Risk Kit and how to use it to transfer simulation results into another model with almost no loss of information.

When we want to model uncertainties in a Monte Carlo simulation, it is sometimes difficult to find a standard distribution that fits the shape of this uncertainty. Standard distributions typically have relatively fixed shapes. If an uncertainty has a different shape, we have a problem.

That is why it is very helpful to have a distribution at hand that is easy to handle and at the same time extremely flexible. For this purpose, Risk Kit has the Flex distribution, a custom distribution. We can essentially draw it completely freely by hand on a sheet of paper. Below we show how it is defined, when it fits and how to simulate it in Excel with Risk Kit. Then we use it to transfer a simulation result into another model.

What is a Flex distribution?

For the Flex distribution you always specify values and, for each value, the probability that the result is less than or equal to this value. These are the two parameters of the distribution: the values and the corresponding probabilities. So each row with a value a and a probability p means:

P(X ≤ a) = p

This assignment of values to probabilities is called the distribution function. In older Risk Kit versions and in our guide “When Do I Use Which Distribution?”, this distribution is called ContCustomCDF.

The values can be positive or negative numbers, it makes no difference. The Flex distribution can therefore represent risks as well as opportunities, or whatever positive and negative numbers are meant to stand for in a model.

An example

Figure 1 shows an example. Column B contains the values in rows 6 to 12, and column C next to it the probabilities. Column D says in words what each row means. Here the minimum is −1,000, and the corresponding probability is 0%. In everyday language this means: in 0% of cases the result is less than −1,000, that is, never. −1,000 is the minimum. In 5% of cases the result is less than −150, in 25% of cases less than 200, and so on. Row 12 holds the maximum: in 100% of cases the result is less than 3,000.

Figure 1: Parameters of a Flex distribution. The values are in B6 to B12, the probabilities in C6 to C12, and column D says in words what each row means (German interface).

In between, the probabilities can take any values. The only condition is that they start at 0%, end at 100% and always increase. For the values, the only requirement is that they too are sorted in ascending order.

What do the density and the distribution function of a Flex distribution look like?

The density: uniform section by section

Between the specified points, the distribution is uniform section by section. From one value to the next we always have a uniform distribution that contains exactly as much probability mass as the difference between the two percentages. In our example, 25% − 5% = 20% of the probability therefore lies between −150 and 200, spread evenly over this section. This is how the Flex distribution is defined.

Figure 2 shows the density that belongs to our table. Risk Kit displays it as soon as you enter the parameters. The horizontal axis shows the possible results from −1,000 to 3,000, the vertical axis the density. The density consists of flat blocks, one for each section between two values. A block is higher the more probability lies on its section and the shorter the section is. The highest block is between 850 and 1,250: there, 25% of the probability lies on a short stretch. Between 200 and 850 there is also 25%, but on a longer stretch, so this block is lower.

To the right of the density are statistics of the distribution. At 5% it shows −150, at 25% it shows 200, at the median 850 and at the maximum 3,000. These are exactly the values from our table.

Figure 2: Density of the Flex distribution from Figure 1. Between any two values the density is constant, and the statistics of the distribution are on the right (German interface).

The distribution function: straight lines from point to point

Figure 3 shows the distribution function as Risk Kit displays it in the spreadsheet when you move the mouse over cell B14. That is where the Flex distribution is. We show further below how to insert it into the cell.

The distribution function has a very simple shape. It is the linear connection of the points we entered: value and probability, next value and next probability, always joined by a straight line. The horizontal axis again shows the values, the vertical axis the probability from 0 to 1, that is, from 0% to 100%. The kinks in the curve lie exactly at the points from the table, for example at −150 and 0.05, at 200 and 0.25 or at 850 and 0.5. Where the curve rises steeply, the block of the density in Figure 2 is high. For two neighbouring rows with the values a and b and the probabilities p and q, the following applies to the distribution function F in between:

F(x) = p + (q − p) · (x − a) / (b − a) for a ≤ x ≤ b

In this way we can draw any curve we like. We will only need rows 19 to 22 of the sheet at the end of this article.

Figure 3: Distribution function of the Flex distribution, as Risk Kit shows it when you hover over cell B14. The curve connects the points from B6 to C12 with straight lines (German interface).

How do you represent rare extreme values?

This freedom is very helpful in practice when you have risks that stay in a moderate range most of the time and take extreme values in very rare cases. In our example we are between −1,000 and 3,000. That is more or less moderate. But what if, with the last 5% of probability, the result could also be as high as 25,000?

We can represent that in just the same way. In Figure 4, cell B12 now contains 25,000 instead of 3,000. As before, the distribution function rises steeply to 95% up to 2,500. After that it runs almost flat and only reaches 100% at 25,000. This gives the distribution an extremely long tail on the right, that is, an end that stretches out far. You can also see this in the statistics on the right: at 99% it now shows 20,500, at the maximum 25,000.

Figure 4: The same distribution with 25,000 instead of 3,000 as the maximum. The last 5% of probability is spread over the range from 2,500 to 25,000, which creates a long tail on the right (German interface).

Such a shape would be very difficult to represent with a standard distribution. With the Flex distribution I can enter any value in cell B12, 5,000 or even a million. So we really are free to shape the distribution.

When does a Flex distribution fit?

We have just seen one case: risks that mostly stay in a moderate range and rarely take extreme values. Beyond that, there are three applications in which the Flex distribution is particularly helpful.

Expert estimates

The parameters can be determined very well with expert estimates. Experts understand these figures very well. Everyone understands what a minimum is anyway, and the same goes for the maximum. And if you say, “Give me the value below which we are in 5% of cases”, the expert who has to make this assessment knows exactly what to do.

Small samples

A second very good field of application is small samples. Perhaps we have a few observations, but so few that nothing can reasonably be calibrated with them. Then we can take over the corner points of this sample directly. We see the minimum, we see the maximum, and we see what share of the observations lies below a given value. We enter these points as values and probabilities.

The distribution is also adaptive: when new observations are added, they change the distribution function accordingly. This directly changes what is taken over from the sample into the simulation. The effect the new observations have on the distribution is completely transparent.

Sample and experts combined

A third application combines both. We have a sample and revise the result once more with expert estimates. So we ask: Is this really a sensible minimum? Is this really a sensible maximum? Or can values also lie outside the range we have already seen, so that we would have to assume smaller minima and larger maxima? In this way we can check, communicate and correct every single value of this distribution.

This is the great strength of the Flex distribution: it can be communicated exceptionally well. We show a further, less obvious application further below, namely transferring simulation results into another context.

How do you simulate a Flex 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 Flex distribution is built into Risk Kit. But the idea behind it can be used anywhere: you define a distribution by values and probabilities and connect the points of the distribution function with straight lines.

We stay with our example from Figure 1. The values are in cells B6 to B12, the probabilities in cells C6 to C12. The Flex random variable is to be created in cell B14. We proceed in six steps:

  1. Select cell B14, open the button for popular distributions on the Risk Kit toolbar (“Beliebt” in the German interface) and choose the Flex distribution.
  2. The input dialog has two parameters, Values and Prob. They correspond exactly to the values and the probabilities. For Values we select the cell range of the values, B6:B12, and for Prob the cell range of the probabilities, C6:C12.
  3. In the “Name” field we can also give the distribution a name. We call it Flex-Verteilung.
  4. Clicking “Insert” makes Risk Kit write the function into cell B14. The formula bar then shows =Flex_variate(B6:B12;C6:C12)+OutputName("Flex-Verteilung"). The name from step 3 is in OutputName.
  5. Mark cell B14 as an output with the Output button on the Risk Kit toolbar (“Ausgabe” in the German interface). The name Flex-Verteilung is already filled in in the dialog. After clicking “Update”, Risk Kit highlights the cell in yellow.
  6. Run the simulation.

Figure 5 shows the input dialog after step 2. In the sheet on the left, cells C6 to C12 are highlighted in grey because we have just selected them for Prob. In the dialog, the Values field contains the reference B6:B12 and the Prob field the reference C6:C12. To the right of the fields, Risk Kit shows the numbers the references point to. The description at the top of the dialog sums up the definition: a continuous distribution with a piecewise linear distribution function. Piecewise linear means that the distribution function consists of straight sections, as in Figure 3. On the far right we can already see the density from Figure 2, with the statistics of the distribution next to it. The orange wedge points to cell B14, into which the function is written.

Figure 5: Input dialog for cell B14. Values refers to the values in B6:B12, Prob to the probabilities in C6:C12 (German interface).

From Risk Kit 8.5 onwards, after inserting the function you can see the shape of the distribution directly in the spreadsheet by moving the mouse over the cell that contains the function. This is the view in Figure 3. There, the formula bar also shows the complete formula from step 4.

After the simulation we move the mouse over cell B14 again. Figure 6 shows the result. Cell B14 is now highlighted in yellow as an output, and cell B12 now contains 5,000 as the maximum. The curve is the distribution function of the simulated values, and its statistics are on the right. We get back the same distribution function that we put in, with exactly the same shape: straight sections with kinks at the points from the table. The median, the 50% quantile in the table, is 845.94, around 850.

Figure 6: Simulation result for the output in cell B14, here with 5,000 as the maximum. The simulated distribution function has the same shape as the one we entered (German interface).

How do you transfer simulation results into another model?

A less obvious application of the Flex distribution is the situation in which we want to transfer the results of a simulation into another context. In enterprise risk management, for example, we first assess risks individually and then bring them together in a downstream step to aggregate them. The following case shows where conventional methods run into difficulties here.

Risk Kit includes a case study on cyber risks. If we use it to simulate a whole cyber risk inventory, we get a very complex-looking distribution of the total loss with a great many spikes. This is something a standard distribution does not normally have. We want to transfer this distribution into enterprise risk management with as little loss of information as possible.

To do this, we right-click the header of the output at the top of the Risk Kit results dialog and have the simulated distribution calibrated to distributions that we select. We select all popular distributions, including the Flex distribution.

Figure 7 shows the result of the calibration. In the middle, the orange bars are the simulated values, called input data in the window, with their many spikes. The blue line is the fitted Flex distribution. It fits this complicated shape excellently: thanks to its flexibility, it traces the twists and turns of the distribution very closely. On the left is the list of calibrated distributions. The Kolmogorov-Smirnov column measures how far the fitted distribution deviates from the data. The smaller the value, the better the distribution fits. The Flex distribution is at the top with 0.0031. The distributions below it fit considerably worse, for example the normal distribution with 0.0307 and the PERT distribution with 0.1119. Below it, under “Estimated parameters (Flex)” (“Geschätzte Parameter (Flex)” in the German interface), begins the long list of values that the calibration has found.

Figure 7: Calibration of the simulated distribution. The Flex distribution (blue line) follows the spikes of the simulated values (orange) very closely and is in first place in the list on the left (German interface).

With the first button at the bottom left, we copy this distribution as an Excel formula and paste it into our downstream model, here into cell B25. The formula now contains the many parameters we got from the calibration. We mark the cell as an output named Cyber-Risiko and simulate it. We get practically the same distribution as before, with the same spikes and all the details.

The Flex distribution is therefore extremely well suited to creating communication between individual risk management systems. In very many cases, companies have specialised risk models, for example for raw material purchasing, for treasury or for other risk areas that play an important role in the company. The results that these specialised systems have achieved in their simulations can be transferred very well into another context. We calibrate the simulated distribution with the Flex distribution and use it in the other context. Then we have a very small, compact representation of the distribution and can continue working with almost no loss of information.

How do you put the parameters into a single cell?

So far we have entered the values and probabilities as lists in several cells, that is, as vectors. But you can also write each of them into a single cell. This is also how it appears in the formula we pasted into cell B25 from the calibration: there, the parameters are in curly brackets directly inside the function.

To do this, you write the values between curly brackets, separated by semicolons. You must not use a percent sign inside the curly brackets. So you have to write out the percentages as real numbers, for example 0.05 instead of 5%. In our sheet, which uses the German number format with decimal commas, cell B19 contains {-1000;-150;200;850;1250;2500;3500}. These are the values from B6 to B12, here with 3,500 as the maximum. Cell B20 contains the probabilities as {0;0,05;0,25;0,5;0,75;0,95;1}. In cell B22, the function Flex_variate(B19;B20) simply refers to these two compact arrays, and the Flex distribution is correctly parameterised.

This way, even a large number of parameters can be accommodated in a very small space in an Excel sheet, namely in a single cell.

More distributions

For an overview of all distributions, see our guide “When Do I Use Which Distribution?”.