Crystal Ball model converter


 

See also: @RISK model converter







Crystal Ball is a Monte Carlo Excel add-in from Oracle Corporation.

ModelRisk includes a tool to allow you to translate models with Crystal Ball entries into the same model using ModelRisk functions. The tool can be accessed through the main ribbon for ModelRisk Standard:

And via the More Tools dropdown list in ModelRisk Professional or Industrial versions:

 

This opens the following dialog box:

If more than one Excel workbook is open, the converter will operate on all active workbooks, so we strongly recommend that you only open one Crystal Ball model at a time. We also recommend that the Rename file option is selected to ensure that you do not overwrite your model. By default this will create a new workbook stored in the same directory as the active workbook with ‘Converted’ appended to the name, but you can enter a different name in the New name field.

We also recommend that you select the Show conversion report when finished option as this will show you whether a complete conversion has been accomplished.

Note: it is necessary for Crystal Ball to be running to perform the conversion.

Now click the Convert button . ModelRisk will search your model for all Crystal Ball entries and replace them with the equivalent ModelRisk functions where possible. It will also automatically save the new converted model with the specified file name. At the end of the conversion, the following window will open provided that the Show conversion report when finished option has been selected:

This lists all of the cells in which there were Crystal Ball entries and shows the ModelRisk formulae together with a comment on whether ModelRisk was able to find a suitable replacement.

If you have also selected the Output the conversion report to Excel option, the same table will appear in a spreadsheet as text (i.e. without the “=” sign for formulae) so that the model does not include extra unrelated equations:

Incomplete conversion issues

The Crystal Ball converter does not currently convert any Crystal Ball VBA functions. It also does not convert ‘categorical’ decision variables.

Not all Crystal Ball entries have an exact equivalent in ModelRisk. For example, certain alternative parameterisations of distributions are not supported within ModelRisk because they are not always solvable. The Crystal Ball Custom distribution is also not supported because it has many different parameter interpretations.

Difference in modelling correlation

Crystal Ball uses rank order correlation with a method developed by Iman and Conover some 30 years ago (Iman and Conover, 1980; Iman and Conover, 1982). Iman and Conover’s technique gives very similar results to using the multivariate Normal copula in ModelRisk. Crystal Ball’s Define Correlation dialog allows the user to produce correlations between the assumption variable in question and more or more others. However if variable A is correlated to Variable B, and Variable B to Variable C, there is an implied range of correlation between A and C.  The implied correlation matrix is calculated behind the scenes and not reported. In contrast, ModelRisk simulates from copulas and connects the copula values directly to the appropriate distributions using the optional U parameter. It requires that a complete correlation matrix be defined for connected variables. If there is any correlation in your model, the converter will create a separate sheet called ModelRiskCorrelation in which it will place the ModelRisk copula functions, and it will connect the copulas to the distributions in your model. It will also correct the correlation matrix is the Crystal Ball entries are not consistent. Note that ModelRisk offers many types of correlation structures (i.e. copulas, which are the more modern approach to modelling correlation), and can estimate correlation structures from data, so you may wish to take the opportunity to update your model with a more appropriate correlation structure.

Converting decision variables

Crystal Ball decision variables are converted to ModelRisk VoseSimTable functions. ‘Categorical’ decision variables are not converted.

Converting forecast variables

Crystal Ball forecast variables are converted to cells marked with ModelRisk VoseOutput functions.

Problems and suggestions

If you come across any problems in converting your models that are not described above, or have a suggestion to improve the converter, please send an email to convert@vosesoftware.com.

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Navigation

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now
-->