Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your model.


 

Risk Solver Engine - Correlating Distributions

Correlating Distributions

Risk Solver Engine provides powerful ways to induce correlations among uncertain variables, even when they are generated by dissimilar probability distributions.  In many modeling situations, both A1 and B1 are uncertain variables, you have no way to directly compute one from the other, yet A1 may statistically depend on B1. For example, mortgage interest rates depend on bond market interest rates (since mortgages are pooled and sold as securities in the bond market), and both rates depend on inflation expectations, but it is quite difficult to specify a formula that relates these variables.

In cases like these, you'll need to specify the statistical dependence between uncertain variables using PSI Property functions, supplied as arguments to PSI Distribution functions. This will induce a correlation between uncertain variables, that would otherwise be considered independent. For example, you can write =PsiNormal (100, 10, PsiCorrIndep("MyCorr")) in cell A1 and write =PsiUniform( 0, 100, PsiCorrDepen(("MyCorr", 0.9)) in cell B1 to specify that B1 depends heavily on A1. "MyCorr" is an arbitrary string name.

The number 0.9 in the example above is a Spearman rank order correlation coefficient. This is a nonparametric measure of correlation that is computed from a rank ordering of the trial values drawn for both variables. It can be used to induce correlations between any two uncertain variables, whether they have the same or different analytic distributions, or even custom distributions.

Meaning of Rank Correlation Coefficients

  • A correlation coefficient of +1 forces the sampled values of the uncertain variables to be exactly positively correlated, meaning that the pth percentile value from one distribution will be sampled on the same trial as the pth percentile value from the other distribution. Coefficients from 0 to +1 will produce varying degrees of positive correlation.
  • A correlation coefficient of -1 forces the sampled values of the uncertain variables to be exactly negatively correlated, meaning that the pth percentile value from one distribution will be sampled on the same trial as the (100-pth) percentile value from the other distribution. Coefficients from 0 to -1 will produce varying degrees of negative correlation.
  • A correlation coefficient of 0 means there is no induced relationship between the variables. In practice, one usually uses coefficients less than +1 or -1, and uses 0 only in a correlation matrix that defines relationships among several variables.

Using a Rank Correlation Matrix for Several Variables

What if you have three, four, or more uncertain variables that should all be correlated with each other? You can create a small table or matrix of rank correlation coefficients in a cell range on the worksheet, and use this cell range in the PSI Property function PsiCorrMatrix.

You pass PsiCorrMatrix (matrix cell range, position) as an argument to the PSI Distribution function, for example =PsiNormal (10,5,PsiCorrMatrix(A1:C3,1)) for the first uncertain variable covered by the correlation matrix. You'd pass PsiCorrMatrix(A1:C3,2) to the PSI Distribution function for the second variable, and PsiCorrMatrix(A1:C3,3) for the third.

< Back to Risk Solver Engine Product Overview