Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your model.


 

Risk Solver Engine - Certified Distributions

Certified Distributions

Certified Distributions can be prepared and tested using a variety of tools. One of these tools is Risk Solver Engine itself. Once prepared, Certified Distributions should be approved and "published" in a form where they can be made available to end user modelers.

Risk Solver Engine makes it easy for an expert to create an Excel workbook, separate from any end user's simulation model workbook, where Certified Distributions are defined. This may be a standard workbook or an Excel add-in workbook -- the latter provides certain advantages, since it is normally hidden from display and loaded automatically when Excel starts.

Risk Solver Engine provides a property function PsiCertify() that you can use to name and "certify" a distribution as ready for publication.  It also provides a distribution function PsiCertified() that end user modelers can use to access the Certified Distribution using only its name.

Publishing Certified Distributions

To name and certify an analytic distribution, you simply include the PsiCertify() property function in the formula that defines the distribution. For example:

=PsiNormal(0.1, 1, PsiTruncate(-2,2), PsiSeed(3), PsiCertify("MyDist",0.1))

will define a Certified Distribution named "MyDist" based on a Normal distribution with mean 0.1 and standard deviation 1, truncated so that its samples lie in the interval -2 to 2, and generated using a random number seed of 3 (this overrides any random seed specified in the end user's model).

To use this distribution, the end user modeler enters =PsiCertified("MyDist") in his or her own workbook.  When no simulation has been performed, the cell containing the PsiCertified() call will display the default value 0.1.To name and certify a distribution based on trial data in a Stochastic Library, you also use the PsiCertify() property function in the formula defining the distribution. For example:

=PsiSip(A1:A1000, PsiCertify("MyDist",100))

will define a Certified Distribution named "MyDist" whose trials are drawn sequentially from the range A1:A1000, with default value 100 to be displayed when no simulation has been performed.

Using Certified Distributions

When Risk Solver Engine starts up, it scans the open workbooks (including regular workbooks and add-in workbooks) for distributions that include the PsiCertify() function, and builds a list of available distribution names.  In the active workbook that contains the simulation model, Risk Solver Engine matches calls to PsiCertified() to the known Certified Distributions.  If a named distribution is not found, the PsiCertified() call will return the error value #NAME?.

Certified and Coherent Models

The practice of Probability Management can yield the significant benefit of "apples-to-apples" comparison of simulation model results, and valid "roll-ups" of simulation models created by different groups.  But how can you be sure that two or more models can be compared or rolled up?  To help answer this question, Risk Solver Engine can automatically test the properties of a risk analysis model.

A model is said to be Certified if all its uncertain variables are defined by Certified Distributions.  A model is said to be Coherent if all of its Certified Distributions draw their trial data from one consistent source -- either a set of analytic distributions with one common rank correlation matrix, or a set of SIPs that are part of one SLURP.  Such models can be compared and "rolled up" with other models based on the same source of uncertainties.You can test whether a model is Certified or Coherent with just three lines of VBA code:

Dim prob As New Problem
prob.Init ActiveWorkbook
MsgBox prob.Model.IsCertified

To test for a Coherent model, you simply substitute prob.Model.IsCoherent in the last line above.

Next: Stochastic Libraries - SIPs and SLURPs >

< Back to Risk Solver Engine Product Overview