Custom Excel Programming

No matter how good your model, it's still You Play, You Pay.  And Rowland's disclaimer echoed a comment an influential racing veterinarian made to me: "Never invest in something that eats while you sleep."     

Developed using the Six Sigma features of @RISK,
software for risk analysis using Monte Carlo simulation

Palisade’s Six Sigma Calculator allows you to create a function that models the performance of a process with uncertain elements. It allows you to include uncertainty around design factors through the use of probability distributions. It was built by Palisade Custom Development using the @RISK Developer’s Kit (RDK) to perform a Monte Carlo simulation so the following process capability metrics can be calculated: Cpk, Cpk Upper, Cpk Lower, Sigma Level, DPM, Cp, Ppk, Pp.

The RDK is Palisade’s widely-used risk analysis programming toolkit. It uses the features and functions of @RISK for Excel - the industry-leading risk analysis tool for spreadsheets. The RDK allows you to build Monte Carlo simulation models in your own applications using Windows and .NET programming languages, such as C, C#, C++, Visual Basic, or Visual Basic .NET. Examples of programs written in Windows and .NET programming languages are provided.

Palisade Custom Development services are used to build tailored applications for individual client needs using @RISK and other technology.

» Six Sigma Calculator
» More about using @RISK for Six Sigma
» More about using @RISK
» Palisade Custom Development

I have recently spoken to several clients whom have all came to the same conclusion about the risk analysis solution they think is most appropriate. They don’t want to do it, and I have no problem with that!

Of course that’s not precisely true. The benefits of Monte Carlo techniques in risk analysis are quite well understood and there is plenty of buy-in from businesses in the Australasian region. The trouble these businesses face (particularly in the realm of project cost estimation) is that the specific process of quantifying their risks for stochastic analysis and the ensuing simulation is not well understood and the means to ameliorate this appears to be beyond their reach. The modelling and simulation components of the project risk management process are not given adequate resources to be performed well, and certainly not to the extent that they provide the most useful information.

It is the case that many companies do not employ dedicated quantitative analysts. This means they have to rely upon some (maybe one) person in the team who has a non-zero quantity of experience and possibly training with risk simulation software to create a valid and credible stochastic model. This person is also not likely to be given enough time to do said task, thus the model inevitably suffers. It is my experience that most models – and all project cost estimation models – can be improved or actually need to be fixed.

So the corporate mind is willing, but the flesh is weak. How can this be addressed? No amount of additional training will suddenly allow you to overcome your time and resource constraints. Perhaps you can’t get the budget for training anyway or don’t want to master risk analysis software when it’s not really core to your role? The solution is one that I personally endorse (and provide!) as a risk analysis consultant – custom Excel programming.

VBA for Excel is a fairly simple language to learn, yet very powerful tool for automating repetitive or sometimes complex spreadsheet tasks. A customised solution involves writing VBA code to perform the tasks we’d rather not do ourselves in the risk analysis model. The “we” here refers to companies that find themselves in the situations previously described whereby they are incapable of creating and operating these models, not necessarily though any fault of their own. In my next blog I’ll examine some modelling problems/requirements and how they might be dealt with effectively using customisation.

Rishi Prabhakar
Trainer/Consultant

The use of custom Excel VBA programming and @RISK APIs allows the automated analysis of historical data and construction of sophisticated risk models. Here, we present an application in the energy sector as an example.

Palisade Corporation developed an add-in that automates the construction of a risk analysis model to assess the Value-At-Risk  (VaR) of a portfolio of gas future contracts.  This application uses a Principal Component Analysis (PCA) to describe the variability of historical correlated forward price curves; this analysis allows the creation of a @RISK Monte Carlo simulation model to generate forward price curves and compare them against the current positions of the portfolio.

PCA is a statistical technique which can identify the main independent components (sources of risk or information) in data (In this example, historical prices of natural gas forward contracts.). There will generally be as many components as there are forward contracts in the analysis. Therefore, if we are analyzing monthly contracts up to 36 months forward, the analysis would reveal 36 components. In data which is highly correlated (such as natural gas forward prices), typically only 2 or 3 components are significant, accounting for nearly all the variation or “movement” in the data set. For the forward price curves of natural gas, the first principal component generally corresponds to a parallel shift in prices, while subsequent principal components correspond to relative price changes (i.e. a change in calendar spreads).

Using a VBA macro, historical data is analyzed using PCA. The macro constructs an Excel model and @RISK runs a simulation to generate forward price curves so the risk profile of the portfolio can be assessed. The figure below presents a result that shows the predicted performance a of sample portfolio where the VaR (@ 5%) is shown:



Valuing Natural Gas Storage Using Seasonal Principal Component Analysis,  Carlos Blanco, Ph.D., Financial Engineering Associates, 2002.

If you are interested in the implementation of this type of model, @RISK can be of great help. You can concentrate on the quality of the model and input data and let @RISK deal with the simulation and generation of reports.

» More about Palisade Custom Development

Dr. Javier Ordóñez
Director of Custom Development

@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

Palisade Corporation now offers custom development services. Our consulting team can help you to automate your risk and decision analysis models so they can be easily used by everyone in your company, or even outside of it. 

We offer different options that include Excel add-ins, Windows, and Web based applications. Our consultants can help you to design, program and deploy these applications. A typical application might connect an Excel spreadsheet to your company’s database, extract data, then adjust it to probability distributions so they can be used in dynamic risk or optimization models. The structure of reports can be also customized and published as PDFs, or to the Web.

Palisade Custom Development can incorporate Monte Carlo simulation, probability distributions, distribution fitting, graphs, reports, and many other features of @RISK into any Windows-based application. In addition, we can integrate genetic algorithm optimization from RISKOptimizer or Evolver. This allows you to apply powerful, proven analytics to applications outside Excel. Applications can be run in a desktop, network, or Web environment.

You may wish to customize your @RISK or DecisionTools Suite spreadsheet models, restricting access to model components for some users or automating reports and other aspects of your analysis. Using the DecisionTools built-in Excel Developer Kit (XDK) and custom Excel VBA programming language, Palisade can help you build powerful, easy-to-use risk models for one user or for an entire work group.

We are currently working on a new website where you will find more information and project samples.  Upcoming posts will discuss examples of custom Excel VBA programming.

» More about Palisade Custom Development

Dr. Javier Ordóñez
Director of Custom Development