@RISK model converter

Crystal Ball model converter


 

@RISK is a Monte Carlo Excel add-in from Palisade Corporation.

ModelRisk includes a tool to allow you to translate models with @RISK functions 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 the active workbook only. We 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 not necessary for @RISK to be running or be installed in order to perform the conversion.

Now click the Convert button . ModelRisk will search your model for all @RISK functions 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 @RISK functions and shows the original and converted formulae together with a comment on whether ModelRisk was able to find a suitable formula.

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 @RISK converter does not currently convert any @RISK VBA functions.

Not all @RISK spreadsheet functions have an exact equivalent in ModelRisk. For example, the RiskCompound function in @RISK is equivalent in purpose to VoseAggregateMC or VoseAggregateDeduct in ModelRisk, depending on the options selected in RiskCompound, but ModelRisk uses distribution objects to define the variables to be summed, whilst @RISK does not. The conversion report identifies where there is no direct conversion possible.

@RISK has some functions that calculate properties of distributions. For example:

=RiskTheoMean(RiskNormal(100,10))

will return the mean of the Normal(100,10) distribution.

The equivalent in ModelRisk would be:

=VoseMean(VoseNormalObject(100,10))

Both formulae will return the same value of 100. Note that ModelRisk uses a distribution object function to define the normal distribution and distinguish it from functions that take random samples from the distribution. The converter is not able to convert such formulae because, for example, an @RISK model might have:

A1: =RiskNormal(100,10)

A2: =RiskTheoMean(A1)

A3: =RiskOutput()+A1^2

The formula in A1 serves two purposes: to define a Normal distribution, and to sample from it. A functionally equivalent model in ModelRisk would be:

A1: =VoseNormalObject(100,10)

A2: =VoseMean(A1)

A3: =VoseOutput()+VoseSimulate(A1)^2

RiskMakeInput, RiskCollect

@RISK has two functions that will collect generated values for sensitivity analysis:

1. RiskCollect()

This is imbedded within a distribution sampling function, for example:

=RiskNormal(100,10,RiskCollect())

ModelRisk converts this function to VoseNormal(100,10) and ignores the RiskCollect() part because it is incompatible with how ModelRisk specifies input variables for sensitivity analysis. If you wish to make the cell a ModelRisk input for sensitivity analysis, add a VoseInput function as follows:

=VoseInput(“Name”)+VoseNormal(100,10)

If you had a formula with two or more RiskCollect functions, we recommend you separate out the formula. So for example, change:

A3: =RiskNormal(100,10,RiskCollect()+RiskGamma(2,3,RiskCollect())

To:

A1: =VoseInput()+VoseNormal(100,10)

A2: =VoseInput()+VoseGamma(2,3)

A3: =VoseInput()+A1+A2

This has the benefit of making it clear what exactly the output sensitivity is to each distribution, which may not be apparent in the @RISK formulation above.

2. RiskMakeInput

This is wrapped around a formula, for example:

=RiskMakeInput(3+RiskNormal(100,10)+RiskGamma(2,3))

ModelRisk removes the RiskMakeInput function and replaces it with a VoseInput function, so the above formula would appear as:

=VoseInput()+3+VoseNormal(100,10)+VoseGamma(2,3)

RiskTheo statistical functions

@RISK has a number of statistical functions reporting probabilities, etc for @RISK distribution sampling functions. ModelRisk does not allow this, because it uses distribution objects to query properties. For example, if cell A1 contains the formula ‘=RiskGamma(2,3)’, in @RISK you can write:

=RiskTheoMin(A1)       which returns 0, the minimum value that the Gamma distribution may take.

To do the same in ModelRisk we define the Gamma distribution as an object:

A1:       =VoseGammaObject(2,3)

Then =VoseMin(A1) returns the same value of 0.

Note that some properties of a distribution are infinite (in which case ModelRisk returns “+Infinity”) or undefined (in which case ModelRisk statistical functions return “Undefined”). Tested versions of @RISK return #VALUE!.

The following table lists @RISK’s RiskTheo functions in alphabetical order and their ModelRisk equivalents, assuming that cell A1 contains an @RISK distribution sampling function or the equivalent ModelRisk distribution object function:
 

RiskTheoKurtosis(A1) VoseKurtosis(A1)

RiskTheoMax(A1)  

VoseMax(A1)
RiskTheoMean(A1) VoseMean(A1)
RiskTheoMin(A1) VoseMin(A1)
RiskTheoMode(A1) No equivalent, since this is often undefined
RiskTheoPercentile(A1,P) VoseSimulate(A1,P)
RiskTheoPercentileD(A1,Q) VoseSimulate(A1,1-Q)
RiskTheoPtoX(A1,P) VoseSimulate(A1,P)
RiskTheoQtoX(A1,Q) VoseSimulate(A1,1-Q)
RiskTheoRange(A1) No equivalent
RiskTheoSkewness(A1) VoseSkewness(A1)
RiskTheoStdDev(A1) VoseStdev(A1)
RiskTheoTarget(A1,x) VoseProb(x,A1,1)
RiskTheoTargetD(A1,x) 1-VoseProb(x,A1,1)
RiskTheoVariance(A1) VoseVariance(A1)
RiskTheoXtoP(A1,P) VoseProb(x,A1,1)
RiskTheoXtoQ(A1,Q) 1-VoseProb(x,A1,1)

 

Note that there is considerable redundancy among the @RISK functions, which may cause some confusion:

RiskTheoPercentile(A1,P) = RiskTheoPtoX(A1,P)

RiskTheoPercentileD(A1,Q) = RiskTheoQtoX(A1,Q)

RiskTheoTarget(A1,x) = RiskTheoXtoP(A1,P)

RiskTheoTargetD(A1,x) = RiskTheoXtoQ(A1,Q)

Difference in modelling correlation

@RISK 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. @RISK uses RiskCorrmat, RiskIndepC, and RiskDepC functions to produce correlations between variables. In contrast, ModelRisk simulates from copulas and connects the copula values directly to the appropriate distributions using the optional U parameter. 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. 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.

The converter does not currently convert the multiple incidence feature of RiskCorrmat.

Discrepancies between calculated values

@RISK and ModelRisk use different numerical methods for estimating properties of distribution like its moments (mean, variance, etc). ModelRisk uses known equations for calculating moments where they exist (i.e. where there is a known formula using the distribution parameters), and it appears that @RISK does the same. However, where distributions are truncated there do not generally exist any exact equations for the moments, and @RISK and ModelRisk results may differ significantly. ModelRisk uses advanced adaptive numerical integration for continuous distributions, returning calculations with very high accuracy, and summation techniques for discrete distributions. In this spreadsheet you can test a few @RISK and ModelRisk estimates of a truncated distribution’s mean where a formula also exists. ModelRisk does not use these special case formulae, but does use the same numerical methods for all distributions, so this should provide you with a neutral test of the accuracy of the approach of each product.

Different approach to error generation

The parameters of many distributions are restricted to lie within certain ranges. For example, a Normal distribution is defined by its mean and standard deviation, and the standard deviation cannot be negative. In versions we have tested, @RISK and ModelRisk take a different approach:

=RiskNormal(100,-10) returns #VALUE!

= VoseNormal(100,-10) returns Error: sigma must be >=0

If you deliberately use the #VALUE! as part of your model logic, you may get different results with ModelRisk.  For example, using Excel’s ISERROR or ISERR functions will generate FALSE for the ModelRisk error message, but TRUE for @RISK’s #VALUE!

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
-->