Using Data Analytics to Visualize Correlations

In a recent article in “BewertungsPraktiker”, Andreas Covi and Christoph Hofstetter examine the modelling of correlated in random variables within Monte-Carlo-simulations and their implementation with Visual Basic for Application in Excel.  In business valuation Monte-Carlo-simulations are used to estimate the effects of risk in the parameters by means of a large iteration of randomized experiments.  Where these parameters involve correlations – interrelations – between different random variables, these relations need to be considered and require an appropriate simulation design.  The authors examine on a high level a couple of probability distributions, mainly continuous distributions which are most relevant in a valuation setting.  They then proceed delving into the question how to model correlated random variables by looking at bivariate normal distributions, the Cholesky-decomposition, and specific joint probability distribution, the copula.  Before they proceed to discuss a concrete application example, they provide some example VBA code that can be used to implement correlated random variables directly in Excel, without relying on more specialized statistical applications.

The article by Covi and Hofstetter was published in the May 2021 issue of “BewertungsPraktiker” on pages 42 through 47.