Using @RISK and Custom Excel VBA Programming to Automate the Creation of Risk Registers

@RISK is a great tool to create cost risk analysis models. An important component in this type of model is the consideration of risk events. A risk event is modeled using its probability of occurrence and its conditional impact. In other words, we need to model first that the risk occurs, and given its occurrence, we have to include the generated impact to the cost of our project.

The occurrence of a risk event can be easily modeled using a Binomial distribution, where n=1 and p= the probability of occurrence. The consequence can be modeled using a continuous distribution like the Uniform distribution. For example, if we have the risk of “Property damage” with a 50% chance of occurrence, and if that happens my project will suffer a 5-10% increase in the total cost, this logic can be constructed using @RISK. The formula is:

=1+(RiskBinomial(1,0.5)*RiskUniform(0.05,0.1))

During the Monte Carlo simulation, the result of the formula will be 1 or a number from 1.05 to 1.1. When the risk does not occur, the total cost of the project will be multiplied by 1, and when the risk occurs there will be an increase of 5% to 10%.

A more efficient alternative is the use of the RiskCompound function available in @RISK versions 5.x; the formula will be:

=RiskCompound(RiskBinomial(1,0.5),RiskUniform(.05,.1),RiskShift(1))


Using custom VBA Excel programming, you can build an interface that will facilitate the selection and definition of risk events. Internally, the formulas shown above can be constructed and written in a risk register form to assess the cost exposure of the project. Below is an example of an interface that uses a probability-impact matrix for the definition of a risk event. 

Palisade Corporation can help you build custom add-ins that will interact with @RISK to create a powerful analysis tool.

We will have some examples of VBA automation in our website soon – we will let you know when they are ready!  Stay tuned…

» More about Palisade Custom Development

Dr. Javier Ordóñez
Director of Custom Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s