Financial Simulation Modeling in Excel

PRODUCING SETS OF CORRELATED NORMAL RANDOM NUMBERS USING MATRIX MATHEMATICS

The previous example allowed us to see the effects of a single type of correlation between entities and to understand the effects on simulation results that correlation exerts. However, the example was limited in scope because the correlation coefficient was provided, and there was only a single correlation coefficient assumed. When financial simulations become more developed, they may require more robust methods of generating correlated normal random numbers.

Using matrix mathematics is an efficient means for creating multiple correlated normal random numbers. In the next Model Builder, we will examine historical data on fictitious companies, perform calculations to understand how those companies are correlated, and then set up a system for generating correlated random numbers for eventual use in a broader financial simulation.

MODEL BUILDER 3.3: Advanced Correlation Concepts through the Lens of Corporate Performance

1. Create a new workbook and save it as MB_3.3_User.xls.
2. For this Model Builder we will need a large set of raw data. Open MB_3.3_Complete.xls from the website, copy the range B3:G505, and paste the copied range in your workbook's first sheet in the same range (B3:G505). We need only one sheet for this Model Builder, so you can delete the other sheets and name the sheet you are working in “MB 3.3” to tie with the complete version.
3. The values that we copied over could represent the observable ...