RESEARCH ARTICLES | RISK + CRYSTAL BALL + ANALYTICS

Excel Simulation Show-Down Part 3: Correlating Distributions

Escel Simulation Showdown Part 3: Correlating DistributionsModeling in Excel or with any other tool for that matter is defined as the visual and/or mathematical representation of a set of relationships. Correlation is about defining the strength of a relationship. Between a model and correlation analysis, we are able to come much closer in replicating the true behavior and potential outcomes of the problem / question we are analyzing. Correlation is the bread and butter of any serious analyst seeking to analyze risk or gain insight into the future.

Given that correlation has such a big impact on the answers and analysis we are conducting, it therefore makes a lot of sense to cover how to apply correlation in the various simulation tools. Correlation is also a key tenement of time series forecasting…but that is another story.

In this article, we are going to build a simple correlated returns model using our usual suspects (Oracle Crystal Ball, Palisade @RISK , Vose ModelRisk and RiskSolver). The objective of the correlated returns model is to take into account the relationship (correlation) of how the selected asset classes move together. Does asset B go up or down when asset A goes up – and by how much? At the end of the day, correlating variables ensures your model will behave correctly and within the realm of the possible.

  • 19 August 2011
  • Author: Eric Torkia
  • Number of views: 2414
  • Comments: 0

 

One of the cool things about professional Monte-Carlo Simulation tools is that they offer the ability to fit data. Fitting enables a modeler to condensate large data sets into representative distributions by estimating the parameters and shape of the data as well as suggest which distributions (using these estimated parameters) replicates the data set best.

Fitting data is a delicate and very math intensive process, especially when you get into larger data sets. As usual, the presence of automation has made us drop our guard on the seriousness of the process and the implications of a poorly executed fitting process/decision. The other consequence of automating distribution fitting is that the importance of sound judgment when validating and selecting fit recommendations (using the Goodness-of-fit statistics) is forsaken for blind trust in the results of a fitting tool.

Now that I have given you the caveat emptor regarding fitting, we are going to see how each tools offers the support for modelers to make the right decisions. For this reason, we have created a series of videos showing comparing how each tool is used to fit historical data to a model / spreadsheet. Our focus will be on :

The goal of this comparison is to see how each tool handles this critical modeling feature.  We have not concerned ourselves with the relative precision of fitting engines because that would lead us down a rabbit hole very quickly – particularly when you want to be empirically fair.

  • 15 May 2011
  • Author: Eric Torkia
  • Number of views: 2522
  • Comments: 0

Is there a winner in this battle between Crystal Ball and ModelRisk? To quote that way-too-often-quoted reply: It depends. Some users will value certain technical capabilities over others. Some users will value user-friendliness over accuracy. If there is to be a group deployment of a MCA spreadsheet package, usability may trump technical capabilities overall. Does it matter if one package has more distributions to choose from if there are only three that are of interest for your particular class of stochastic problems? Would it matter what kind of correlation enforcement method is used if, as in many manufactured assemblies, there is practically no correlation between separate components? Probably not. But if they do (as in financial and insurance applications), there will be a clear winner.

Correlation behavior in ModelRisk is enforced with the use of copulas. Copulas offer more flexibility in accurately simulating real data scatter-plot patterns than do single-value correlation coefficients. While this advantage is clear for financial and insurance applications, its implementation in an MCA spreadsheet simulator can make the difference between universal adoption and rejection by a majority of the intended user group. Let us now use ModelRisk (MR) to enforce the correlation behavior between Duke Basketball offense scores and their opponents' scores, based on the '09/'10 historical data.

Correlation behavior in ModelRisk is enforced with the use of copulas. Copulas offer more flexibility in accurately simulating real data scatter-plot patterns than do single-value correlation coefficients. While this advantage is clear for financial and insurance applications, its implementation in an MCA spreadsheet simulator can make the difference between universal adoption and rejection by a majority of the intended user group. Let us now use ModelRisk (MR) to enforce the correlation behavior between Duke Basketball offense scores and their opponents' scores, based on the '09/'10 historical data.

RSS
123