Wehrspohn Risk Management

How do you take correlations into account in a Monte Carlo simulation?

Article

By Dr. Uwe Wehrspohn.

Risk factors are often related, for example development and test costs or marketing costs and sales, and such dependencies change the result of a Monte Carlo simulation. Using a simple profit and loss calculation in Excel, we show how to build correlations into the model with the normal copula, how they change the range of the profit and how to estimate distributions and correlations from data.

In practice, risk factors are often related to each other. When the development of a product runs into problems, the test costs often rise as well, and when we do more marketing, we usually sell more too. If a Monte Carlo simulation treats all risk factors as independent of each other, such relationships are left out.

Using a simple profit and loss calculation in Excel, we therefore show in two steps how to take dependencies into account. First, we build correlations that we already know into the model with the normal copula and compare the profit with and without correlations. Then we estimate the distributions of the risk factors and the correlations between them from data. In the end, this estimation delivers exactly the building blocks from the first step: a correlation matrix, the distributions and the copula that connects the two.

In short: Risk Kit is an add-in for Microsoft Excel that lets you build and analyse Monte Carlo simulations directly in the spreadsheet. The principle of connecting correlations with arbitrary distributions through a copula is not tied to any particular tool, though.

As an example, we take a simple profit and loss calculation with four cost components: development costs, test costs, marketing costs and manufacturing costs. Each cost component follows a triangular distribution, which is defined by three values: the minimum, the most likely case and the maximum. On the revenue side is the number of products sold. It also follows a triangular distribution and is multiplied by a fixed unit price.

Figure 1 shows the model in Excel. Row 3 holds the name of the distribution, “Triangular”, and rows 4 to 6 hold the minimum, the most likely case and the maximum. For the development costs in column C, these are 1,300,000, 1,500,000 and 2,300,000 euros, and for the number of products sold in column G, 430,000, 500,000 and 590,000 units. The unit price of 21 euros is in cell J2. In row 8, “Unabhängige Faktoren” (independent factors), the model draws a random number for each risk factor from its triangular distribution. From these, it calculates the costs in cell I8 as the sum of the four cost components, the revenues in cell J8 as the number of products sold times the unit price and the profit in cell K8 as the difference between revenues and costs. In the simulation run shown in the figure, these are costs of 10,731,681 euros, revenues of 11,130,074 euros and a profit of 398,393 euros.

Figure 1: The model. The five risk factors follow triangular distributions with minimum, most likely case and maximum in rows 4 to 6. Row 8 shows a simulation run with independent factors, and costs, revenues and profit are shown to the right (German interface).

If we simply use its own triangular distribution for each risk factor, the risk factors are independent of each other. The development costs and the test costs, for example, then do not influence each other, although that would be quite conceivable. If there are problems in development, tests have to be repeated as well, and the test costs rise. If, on the other hand, development goes very smoothly, you may only have to test once and reach a positive result straight away. Then both costs are low. So it makes sense to make these two risk factors dependent on each other.

It is similar with the marketing costs and the number of products sold. So far, they are independent in the model: even if we do a great deal of marketing, it has no effect on the number of products sold. Yet that is usually exactly why we do marketing. Good marketing does affect the number of products sold, so there is a correlation between the two factors.

A correlation describes how closely two quantities are related. With a positive correlation, high values of one risk factor coincide more often with high values of the other, and low values with low ones. With a negative correlation, high values of one tend to meet low values of the other.

We would therefore like to correlate the development costs with the test costs and the marketing costs with the number of products sold. For the other combinations, it is not so clear whether that would be a good idea. If we do a lot of marketing, the manufacturing costs usually do not change. Conversely, it has no effect whatsoever on the marketing if the manufacturing costs turn out higher than planned. How much marketing we do is a completely separate question. The same applies to the development costs and the marketing or to the test costs and the marketing. Based on business experience, there is no recognisable relationship here for the time being.

We record the correlations between all risk factors in a correlation matrix. This is a table in which each risk factor appears once as a row and once as a column. Where a row and a column cross, we find the correlation between the two risk factors. Figure 2 shows the correlation matrix of the example in rows 12 to 16, in the same columns C to G as the distributions in Figure 1.

Figure 2: The correlation matrix in rows 12 to 16. Below it are row 18 for the dependent factors, which is still empty, and the cell “Plot” in row 21 (German interface).

The diagonal shows 100% throughout, because each factor is fully correlated with itself. For the development costs and the test costs, we have assumed a correlation of 60%. It appears twice in the matrix: in the row of the development costs under the test costs and in the row of the test costs under the development costs. Because correlation is a symmetric relationship, the matrix must be symmetric. We therefore enter the value only once, and the second cell takes it from the first.

We have assumed the same correlation of 60% for the marketing costs and the number of products sold. All other correlations are set to 0%, because we do not assume a business relationship there.

What is a copula?

To get the correlations into the model, Risk Kit has the function NormalCopulaWithDistributions. Figure 3 shows where to find it: on the Risk Kit tab under “Weitere Funktionen” (more functions) in the submenu “Mehrdimensionale Verteilungen” (multidimensional distributions). The tooltip next to the menu entry describes it as “Normale Copula mit Randverteilungen” (normal copula with marginal distributions).

Figure 3: The function NormalCopulaWithDistributions under “Weitere Funktionen” (more functions) and “Mehrdimensionale Verteilungen” (multidimensional distributions) on the Risk Kit tab (German interface).

Multidimensional distributions return not just one number but a whole list of numbers, one for each risk factor. In such distributions, two things can be separated: the dependencies between the risk factors and the distributions of the individual risk factors, which are also called marginal distributions. The part that describes only the dependencies is called the copula. It is combined with the distributions of the individual risk factors.

This is exactly what the function NormalCopulaWithDistributions does. The normal copula describes the dependencies through correlations. Each risk factor keeps its own distribution, in our example the triangular distribution with its parameters. With the copula, correlations can therefore also be taken into account between risk factors that follow arbitrary distributions. Strictly speaking, the correlations in the matrix apply to normally distributed random numbers from which the copula derives the values of the risk factors. If you calculate the correlation directly from the simulated costs and unit sales, it may therefore differ slightly from the 60%.

More on this: Our case study “How do you assess credit risk from receivables with a Monte Carlo simulation?” also uses the normal copula, there to correlate the defaults of many receivables with each other through the economy.

How do you build the correlations into the model?

Figure 4 shows the dialog of the function. It has three mandatory parameters, which are printed in bold:

  • CorrelationMatrix is the correlation matrix of our risk factors, that is, C12:G16.
  • Distributions are the distributions, here the names “Triangular” from the row “Verteilung” (distribution), that is, C3:G3.
  • Parameters is the matrix of the parameters used by the individual triangular distributions, that is, minimum, most likely case and maximum in C4:G6, with one column per risk factor.

Figure 4: The dialog of the function NormalCopulaWithDistributions with the correlation matrix, the distributions, the parameters and the optional entries RowVector and Identifier (German interface).

In addition, there are two optional parameters. For RowVector we enter “wahr” (true), because the five random numbers should sit next to each other in one row, just like the risk factors in the table. As the identifier we enter a 1. It makes Risk Kit keep the correlation matrix and the parameters in memory instead of reading them anew in every simulation run. This makes the simulation faster and does not change the result as long as the parameters are constant.

However, the parameters themselves can also be made random, for example the minimum and maximum of the triangular distribution in a preceding random experiment. That is a generalisation of the model. Then Risk Kit has to read the data anew in every run, and we leave out the identifier.

Because the function returns five random numbers at once, we insert it as an array formula. An ordinary formula returns one number, an array formula a whole cell range. In the dialog, we use the small arrow next to “Einfügen” (insert) to choose the entry “Einfügen als Matrixformel” (insert as array formula). Cells C18 to G18, the row “Abhängige Faktoren” (dependent factors), then contain:

{=NormalCopulaWithDistributions(C12:G16,C3:G3,C4:G6,WAHR,1)}

Note: The video shows an older version of Risk Kit. In current versions the function has a further optional argument, IsRowWise, between the argument for row output and the identifier. If you rebuild the formula today, leave this argument empty and put the identifier in sixth place, that is =NormalCopulaWithDistributions(C12:G16,C3:G3,C4:G6,TRUE,,1).

The curly brackets show that this is an array formula. All risk factors still follow their triangular distribution. Now, however, the development costs and the test costs are 60% correlated through the normal copula, and so are the marketing costs and the number of products sold. From these random numbers, we calculate the costs and the revenues as in row 8 and obtain a random profit with correlations in cell K18.

How do correlations change the result?

To see the impact of the correlations, we compare the profit without correlations with the profit with correlations. The two random numbers from a single run lie somewhere within their range and cannot be compared meaningfully. We have to compare the two distributions, so we draw them in a common chart with the Plot function. The function is in cell C21, the orange cell “Plot” in Figure 2, and takes the two profit cells K8 and K18 as input:

=+Plot((K8,K18))

Then we run the simulation. Once the simulation has finished, Risk Kit sets the two profit distributions against each other in a common chart, which Figure 5 shows. The orange bars belong to the profit without correlations, the blue bars to the profit with correlations.

Figure 5: The profit without correlations (orange) and with correlations of 60% (blue). Both distributions have essentially the same mean, and the blue one is considerably narrower (German interface).

Both distributions have essentially the same mean. This is a very characteristic result: as a rule, correlations do not change the mean but only the range. Whether this holds depends on the model, though. The copula leaves the distribution of each individual risk factor, and therefore its mean, unchanged. In our model the risk factors add up: the profit is the number of products sold times the fixed unit price minus the sum of the four cost components. The mean of such a sum is always the sum of the individual means, no matter how the risk factors are correlated. If the risk factors interact differently, for example in project management, the mean need not stay the same.

We see immediately, however, that the range of the two distributions differs. The profit with correlations is spread much less than the profit without correlations: the blue bars are higher in the middle and tail off faster towards the edges.

Why does the distribution become narrower?

There are two dependencies in the model. The first is between the development costs and the test costs. Both are costs, and high test costs now coincide with high development costs more often than before. So we would actually expect a larger range. The test costs, however, vary only very little: between the minimum of 200,000 euros and the maximum of 320,000 euros there are only 120,000 euros. That is why this effect is only small.

With the marketing costs, it is exactly the other way round. They have the largest range of all cost components: between 4.5 million and 7 million euros there is a spread of 2.5 million euros. The number of products sold also varies a lot. 160,000 units between minimum and maximum times 21 euros give the revenues a range of over 3 million euros.

What matters now is that marketing costs and products sold have opposite signs: one is a cost, the other is revenue. When the marketing costs rise, we assume that the revenues from the products sold also tend to rise. Higher marketing costs are then partly offset by more products sold, and lower marketing costs tend to go with fewer products sold. This compensation effect between costs and revenues makes the range of the profit considerably narrower. Because both quantities vary a lot, it far outweighs the first effect, and that is exactly what we see in Figure 5. The effect makes good business sense, and it was the reason why we wanted to look at this correlation in the first place.

The cross-check with a negative correlation

We can also check this in the opposite direction. To do so, we set the correlation between the marketing costs and the number of products sold from plus 60% to minus 60% and run the simulation again. Now the relationship is reversed: high marketing costs tend to meet a small number of products sold. The costs rise while the revenues fall at the same time, and vice versa. The profit therefore reaches further to both sides, and the distribution becomes wider.

Figure 6 shows the result. Now the profit with correlations (blue) is wider than the profit without correlations (orange). On one side we see additional losses with higher marketing costs and lower revenues. On the other side there are higher profits with low marketing costs and a correspondingly large number of products sold.

Figure 6: The same comparison with a correlation of minus 60% between marketing costs and products sold. Now the profit with correlations (blue) is wider than without (orange) (German interface).

So we can read the plausibility of the assumptions directly from the result, in one direction as well as the other. This makes it possible to check and validate the results and the modelling well.

How do you estimate distributions and correlations from data?

So far, we have used correlations that we already knew. In practice, correlations are often estimated by experts, especially when there is no data basis for the dependencies between the risk factors. That is the case when your own company has no experience at all with something and there is therefore no documentation about it either. Where data are available, however, Risk Kit can estimate both the distributions of the individual risk factors and the dependencies between them. The second video shows this case.

This estimation is called calibration. Our example has the same risk factors as before: development, test, marketing and manufacturing costs for a product on the cost side and the number of products sold on the revenue side. Each row of the data contains one observation, that is, costs and sales that occurred together.

To determine the distributions and the correlations, we choose “Normale Copula kalibrieren” (calibrate normal copula) under “Kalibrieren” (calibrate) on the Risk Kit toolbar. This calibration determines the distributions of the individual risk factors as well as the dependencies between them. Figure 7 shows the data on the left and the “Kalibrierung” (calibration) dialog on the right. We select the data, here B1:F5001, and tick the box stating that the first row contains the names of the data series, because that is where the names of the risk factors are.

Figure 7: On the left the data in columns B to F, on the right the “Kalibrierung” (calibration) dialog with the input data B1:F5001. All distributions are selected under “Verfügbare Verteilungen” (available distributions) (German interface).

Then we select the distributions that we consider relevant for these data. If you are not sure which ones are best suited, you can simply tick “Alles auswählen” (select all), as in Figure 7. During the calibration, Risk Kit sorts out the distributions that are out of the question in any case, calibrates the remaining ones and shows their goodness of fit, that is, how well the estimated distribution fits the data.

A click on “Kalibrieren” (calibrate) starts the calibration for each individual risk factor and for the joint distribution. This takes a while. First, Risk Kit reports for each risk factor the distributions that are out of the question, for example the beta distribution, because the data do not lie in the interval from 0 to 1.

How do you tell which distribution fits best?

Then Risk Kit shows the result of the calibration. Figure 8 shows the dialog, here with the test costs selected. The upper table lists each risk factor with its name from the column heading of the data, with the chosen distribution and the estimated parameters. As the starting value, Risk Kit always takes the distribution that fits the data best. For all risk factors except the test costs, that is the triangular distribution. For the test costs, it is the trapezoidal distribution, which is very similar to the triangular distribution. It has four parameters, which is why the column “Parameter 4” is filled for it as well.

Figure 8: The result of the calibration with the test costs selected. At the top are the chosen distributions and their parameters, below them all distributions for the test costs with their goodness-of-fit measures, sorted by the Kolmogorov-Smirnov distance, and on the right the data and the trapezoidal distribution. The dialog is already cut off on the left in the video (German interface).

On the right we see the data that went into the estimation as a histogram, here 5,000 data records per risk factor, and over it the calibrated distribution that is currently selected.

For the risk factor selected at the top, the lower table shows all distributions that come into question, with their goodness of fit. This is expressed through various measures, for example the Kolmogorov-Smirnov distance, the Anderson-Darling statistic, the Akaike information criterion (AIC) and two further information criteria. For all of them, the smaller the value, the better the distribution fits. Clicking a column heading sorts the table by that criterion. The order usually changes only slightly, and by and large the criteria agree. For the development costs, the triangular distribution is at the top under all criteria.

Triangle or trapezoid for the test costs?

For the test costs, the Kolmogorov-Smirnov distance, by which Risk Kit sorts by default, puts the trapezoidal distribution at the top with 0.0108 and the triangular distribution in second place with 0.0120. By the information criteria it is the other way round: in the columns AIC, AIC Finite Correction and AIC Bayes Correction in Figure 8, the triangular distribution has the smaller value.

We can change the selected distribution in the upper table and choose the triangular distribution for the test costs. With the small checkboxes in the lower table, several distributions can be put into the chart together and compared.

Figure 9 shows the density of the test costs with both distributions. The triangular distribution (blue) and the trapezoidal distribution (red) look essentially the same. They differ only in a tiny corner at the top of the peak, where the trapezoid is flattened. We can therefore well justify a uniform triangular distribution for all risk factors.

Figure 9: The density of the test costs with the triangular distribution (blue) and the trapezoidal distribution (red). The two differ only at the peak (German interface).

More on this: Our article “When do I use a triangular distribution in a Monte Carlo simulation?” explains how the triangular distribution works with minimum, most likely value and maximum and where its limits lie.

Quantiles, QQ plot and PP plot

The fit to the data can also be viewed from other angles. Instead of the density, the dialog can also show the quantiles, that is, the values below which a certain share of the data lies. You then see how the quantiles of the calibrated distribution deviate from the quantiles of the data. In the quantile-quantile plot (QQ plot) in Figure 10, the quantiles of the data on the horizontal axis and the quantiles of the calibrated distribution on the vertical axis are set directly against each other. If the distribution fits, the line lies on the diagonal. Here the fit is one hundred percent.

Figure 10: The quantile-quantile plot of the test costs. The lines of the triangular and the trapezoidal distribution lie on top of each other on the diagonal (German interface).

The probability-probability plot (PP plot) correspondingly sets the probabilities of the data and of the calibrated distribution against each other. There, too, the two distributions are very similar and fit the data very well. If we add a distribution that does not fit so well, such as the Fisk distribution, a small deviation appears straight away.

When several distributions fit well

All in all, the distributions in Risk Kit are very flexible, and as a rule several distributions fit the data very well. Which one is best suited can then be decided by further criteria, for example by the homogeneity of the estimate, as here with a uniform triangular distribution for all risk factors, or by whether the distribution is bounded. Not every distribution fits equally well, though. The density in Figure 11 shows that the Fisk distribution (light blue), which is much further down the list in Figure 8, quite obviously fits the data much worse than the trapezoidal or the triangular distribution.

Figure 11: The density of the test costs with the Fisk distribution (light blue) compared with the triangular distribution (blue) and the trapezoidal distribution (red) (German interface).

Which correlations are in the data?

Once we have decided on a calibration, here the triangular distribution for all risk factors, we can insert it into the Excel sheet and simulate our risk model with it. To do so, we click “Normale Copula einfügen” (insert normal copula) and select a cell. Figure 12 shows the inserted block. At the top are the estimated correlations of the normal copula, which is what we were really after, and below them the calibrated distributions of the risk factors with the distribution type and the parameters.

Figure 12: The inserted block with the estimated correlations, the calibrated triangular distributions with their parameters and the row “Zufallszahlen” (random numbers) (German interface).

In the last row, “Zufallszahlen” (random numbers), the correlations and the distributions are combined, and corresponding random numbers are drawn. It contains the same function as in the first part, this time without an identifier:

{=NormalCopulaWithDistributions(J4:N8,J11:N11,J12:N15,WAHR)}

In Figure 12, Excel highlights the ranges the formula refers to in colour: the correlations in blue, the names of the distributions in red and the parameters in purple.

Let us look at the values in terms of content. Between the development costs and the test costs there is a relatively high correlation of around 60%. This is plausible: problems in development that make the development costs rise usually also lead to rising test costs, because with every new development cycle we have to test again. The two cost types therefore go hand in hand to a certain extent.

Likewise, we see a relatively high correlation of around 60% between the marketing costs and the number of products sold. This also makes sense, because more effort in marketing potentially reaches more customers, and correspondingly more products are sold.

For the other pairs, the estimated correlations are small and can only just be distinguished from zero. We see 2% and 1%, many values are 0%, and between the manufacturing costs and the number of products sold there is minus 2%. There is no notable dependence here. From a business point of view, this is also understandable. That the development costs depend on the manufacturing costs or the marketing costs, for example, cannot readily be seen. A company’s development activities are something different from what happens on the market, how we approach the market and what we actually produce. The same applies accordingly to the other combinations.

In this way, we have calibrated the distributions and determined the correlations between the risk factors automatically from the data.

What does calibration offer compared with expert estimates?

On the one hand, the list of available distributions includes distributions that an expert could also estimate by hand, such as the triangular distribution or the trapezoidal distribution. They are used very often in expert estimates because they can be parameterised very intuitively: the triangular distribution through the minimum, the most likely value and the maximum, the trapezoidal distribution through the minimum, two inner bounds and the maximum. For such distributions, the calibration delivers values of the same kind that experts provide. Where data are available, the calibrated values can therefore also serve as a benchmark for expert estimates.

On the other hand, the list contains distributions such as the Weibull distribution, which are much harder to parameterise with expert estimates. Without a data basis, you will hardly be able to use this kind of distribution. For such distributions, the calibration is, so to speak, the prerequisite for actually being able to fit the full variety of distributions to the data.