As someone pointed out in a comment to my previous post, simply using RAND() in Excel gives you independent random variables.   That’s perfectly OK for situations where the risks are not closely related to each other.  For instance, the if you are trying to project the cost of bandwidth, the uncertainty in the growth of your traffic is likely to be independent from the uncertainty around the price per TB of data.

But in many cases, risk is correlated and ignoring that correlation or relying on historical correlations can be very misleading.  Exhibit A here are most of the mortgage backed securities.  The assumptions made in rating and pricing these assumed that defaults by individual home owners have a relatively low correlation (as they did historically).  Of course if you wind up with a bursting housing bubble then the actual default probablities are highly correlated, so that outcomes in which lots of people default are much more likely than they have ever been.

So how does one generate correlated random variables in Excel?  The easiest approach is to have a main random variable and then create other random variables that are correlated with that variable using a simple formula.  Let r1 and r2 be two random variables.  Then r3 = c * r1 + sqrt(1 - c^2) * r2 is a new random variable so that correl(r1, r3) is c, i.e. c is the coefficient of correlation between the two variables.  You can construct additional variables correlated with r1 in the same fashion.  These variables will also be correlated with each (not surprisingly).

If one wants to be more precise (which may be overkill for most business models), then one needs to start out by setting up a correlation matrix between all the random variables.   The correlation matrix then needs to be run through a Cholesky Decomposition – generally using a fast Excel plug in, such as this one, but you could roll your own pretty easily in VBA.  If C is your correlation matrix and AAT is the Cholesky decomposition, then you get your correlated random variables from Au where u is a vector of independent radom variables.