Risk Analysis Models

New measures were published by the UK Government in November 2009 to protect communities at risk from ‘flash’ flooding when drains get overwhelmed by sudden downpours.  The Flood & Water Management Bill going before Parliament will give new powers to local authorities to manage surface water.  Environment Secretary Hilary Benn said, “We’ll see more severe weather as climate change takes hold, with heavier rainfall and potential flooding. The weather in the last couple of weeks has shown that this risk is very real.” 

Recent events in Cumbria in the UK suggest climate change really should be at the top of the agenda, and this new bill will certainly go some way to managing the risk associated with it.

Risk analysis has its place in determining the risk associated with climate change, and Palisade's risk modeling software has helped numerous organizations develop reports on likely outcomes. Cambridge University’s Judge Business School recently used @RISK to provide key input to the Stern Review on the Economics of Climate Change. Released in 2006, this report undertaken by the British government by Lord Stern discusses the effect of climate change and global warming on the world economy and is the largest and most widely referenced report of its kind.

They researched issues such as the impacts of the sea level rising and increases in temperature making land infertile or unfarmable, and balanced these against the costs of various options available to tackle global warming.  

At one end of the scale, doing nothing costs nothing, but the environmental consequences will be high. However, activity that reduces the severity of the impacts may itself be very expensive. The aim of the @RISK model was to enable people to make informed decisions on the optimum way to deal with climate change (ie how much to cut back on damaging activity and what methods to use).

@RISK risk simulation software also helped researchers tackle a key problem associated with investigating climate change, namely that the different effects of the various factors which influence it are themselves, undetermined. For example, the historical evidence does not pin down exactly how much global temperatures will increase if CO2 emissions double. @RISK enabled researchers to quantify this uncertainty in order that they have a measurement of the accuracy of their findings.

Risk analysis models should go some way to determining outcomes, and potentially help us prepare for the tragic events that have unfolded in recent days in the UK.

Craig Ferri
EMEA Managing Director of Risk & Decision Analysis

Wayne Winston is the newest blogging personality at the Huffington Post! His first post, “The Importance of Schedule Strength in Sports,” appeared yesterday. Wayne will focus on the interface between math and sports, with detailed explanations of statistical analysis and spreadsheet modeling, including @RISK risk analysis models. You can find a link to the Wayne Winston blog from the newly-launched HuffPost Sports.

Wayne is the John and Esther Reese Professor of Decision Sciences at Indiana University’s nationally ranked Kelly School of Business. He has won over 30 teaching awards, and written over 20 journal articles and 15 books.  Wayne has consulted for many organizations including the Dallas Mavericks, USA Diving, Cisco, Microsoft, US Army, Eli Lilly, Diamond Consulting, Tellabs and Medtronics. He has also developed online spreadsheet modeling and mathematics courses for Harvard Business School Publishing. And, Wayne is a two time Jeopardy! champion!

Wayne’s latest book, Mathletics, provides an introduction to the use of math by baseball, football, and basketball teams. He has also authored several books published by Palisade, including Financial Models Using Simulation and Optimization I, Financial Models Using Simulation and Optimization II: Investment Valuation, Options Pricing, Real Options & Product Pricing Models, and Decision Making Under Uncertainty with RISKOptimizer.


DMUU Training Team

@RISK allows you to use historical data to fit data to a probability distribution. The process is very simple: first select the range where the data is located, and then select the Distribution Fitting button. @RISK will guide through the fitting process where you can select a variety of statistical tests such as Chi-Square, Anderson-Darling, Kolmogorov Smirnov, and the Root-Mean Squared Error. View a short tutorial about Distribution Fitting in risk analysis models below.



While the Distribution Fitting functionally is very useful, in some real life cases we need to fit hundreds of distributions, or create filters for certain date ranges or conditions. If we are to do this manually, the fitting process can be overwhelming. A batch fitting function streamlines the process in your risk modeling software.

Palisade’s Custom Development Team  has been helping many of our customers automate this process using the @RISK Developers Kit. With a custom batch fitting add-in, we are able to extract information from external databases and organize data so that the fitting process can be done automatically. The resulting distributions can be dropped with ease into risk analysis models.

If you are interested in this type of consulting support for risk analysis models, please let us know. Feel free to contact your Palisade sales representative.





>> View @RISK tutorials

Javier Ordóñez, Ph.D
Director of Custom Solutions

In a previous blog, I presented a very simple way to allocate contingencies to uncertain cost elements in the project risk management process. However, that methodology works well when there are not risk events that affect a cost element or a group of cost elements.
A risk event is described by two elements: the probability of occurrence and the conditional impact to the project given its occurrence. For example, we have a risk that describes the possibility of a new regulation. If it occurs, it will increment the cost of group of cost elements by a minimum of 10%, most likely 15%, and a maximum 20%. If the risk does not occur, no impact will be observed. Using a Discrete and a PERT distribution, we can model such risk such as:



When sampling from this distribution approximately only 20% of the time will generate a multiplier with a minimum of 1.1, most likely 1.5 and a maximum of 1.2; in 80% of instances the multiplier will be 1. That means that only 20% of the time the risk will increment the cost of selected cost elements by the multiplier previously described as show in the figure below:



In addition to risk events in our cost risk analysis models, we often use distributions that describe cost uncertainties. These distributions model ranges are mostly in a different order of magnitude. Therefore, the variance will also be in a even greater order of magnitude. For example, the cost of Item 3 modeled using a 3-point estimate (i.e., min 100,000, ML 120,000, and max 150,000) has a variance of   87,698,412.70), while the variance of the risk event is 0.0036. 

If we are to distribute the contingency using the % of contribution of the variance method, the risk event that we just modeled will be ignored even though we know that such risk event has an impact that we cannot dismiss. Given this practical scenario, the method of variance contribution will not work appropriately.

As an alternative, we can use a tornado diagram that results from @RISK’s sensitivity analysis. Here we can use the regression coefficients to understand what risk events or uncertainties are affecting the total cost in a more drastic way. In the case that you also incorporated events that represent an opportunity to reduce cost, you will observe that the coefficient is negative; in your allocation calculations you should not consider negative coefficients.

In the figure below you can observe the Regression Tornado. Here risk events and uncertainties are represented in a scale that goes from 0 to +/-1:



Knowing the regression coefficient of each input that affects the total cost in a negative way, we can construct a table and obtain a normalized percent that can be used to distribute contingency. If for example, we have a contingency of $100,000, it can be distributed to each input proportionally to the regression coefficient as shown below.



Some risk management experts do not distribute the entire amount of the calculated contingency. It is common practice to distribute only a percentage of it (i.e., 70%). The remaining amount will be used as a reserve that will handle unidentified risks.

Javier Ordóñez, Ph.D
Director of Custom Solutions

In a previous entry to this blog I discussed how to assess the contingency required in a cost risk analysis study. The next step is to allocate the calculated contingency to uncertain cost elements that drive the variation in the total cost of the project. In this way, the contingency can be better managed and controlled throughout the life of a project.

While reviewing literature on this topic, I found a practical way to do this. This methodology uses the percentage contribution of each uncertain variable (usually 3 point estimate distributions) to the variance of the resulting distribution of the total cost.

To apply this method, we need to report the variance of each input distribution and the variance of the end result. In case that input distributions are independent from each other, we can just add up individual variances to estimate the variance of the total. However, this is hardly the case since correlation between input variables is expected in cost models.

@RISK allows reporting statistics from an input distribution without running a simulation as well as statistics that describe an output. These functions are from part of the @RISK functions library: Statistic Functions> Theoretical and Statistic Functions>Simulation Results, respectively. These functions can be accessed using the fx icon from the @RISK toolbar. 

To report the variance of input distributions we can use the RiskTheoVariance and for the output RiskVariance. The construction of the allocation model is shown below.



In the project risk management model above, it can be observed that the % Contribution to the Variance of the Total Cost is calculated as a proportion of the input variance to the total variance. Once these percentages are determined we can use them to allocate the management contingency to each cost element. It can be also observed that the engineering allowance is also calculated, and the decision maker now has criteria to manage and control contingencies.

Javier Ordóñez, Ph.D
Director of Custom Solutions

Thermageddon, the title of a recent book by Greenpeace founder Robert Hunter,has morphed into pervasive net-speak for climate change doomsday, and recently The Register website ("Biting the hand that feeds IT") presented a study on a trend that might help delay Thermageddon a bit longer: the music download.  A trio of scientists from Lawrence Livermore Laboratory and Carnegie Mellon University used Monte Carlo software to analyze the energy impacts of various modes of music distribution.  

The latest version of @RISK (risk analysis software, Monte Carlo software for Excel), has a new graphing tool that will let you create a scatter plots using any two outputs of your risk model. 

For example, if you construct an integrated schedule and cost risk model you can easily evaluate the distributions of the duration of the project and its cost. However, you might  also be interested to know the probability of meeting a target duration and cost at the same time.

The duration and the cost of the project can be defined as outputs of the Monte Carlo simulation model. Once the simulation is run, you can assess the total cost and duration at the 85th percentile as seen in the figures below. Here you can see that the duration at this percentile is 1500 days and the cost is $11.35 Millions.


Next, you can create a scatter plot with these two outputs and locate the delimiters at the target values for both variables as shown below. Here you can see that there is only a 2.9% chance that those two targets will be exceeded and there is a 72.8% that both targets will be met. You can also evaluate the probability that only one target is met.

I hope that this new tool can help you to understand in a better way the interaction between a two outputs of your @RISK risk analysis model.

Dr. Javier Ordóñez
Director of Custom Development

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

An earlier blog on Best Practice Principles in Excel Modelling generated quite some interest, as well as demand for more details on some of the points made, especially those concerning the use of named ranges risk asssessment models in Microsoft Excel. In the earlier posting, I had simply stated that (in my opinion): “Named ranges should be used highly selectively but not excessively”. Here I will expand a little more; the topic itself can be a subject of quite animated discussion within the risk analysis modelling community, with a wide set of opinions expressed. The points I make below are therefore simply my view of the topic.

In my view, named ranges are indispensible in some types of modelling situations. The most frequent of these in my experience are:

• When writing VBA code (macros) that refer to ranges in the workbook (as such code almost always would do at some point), the use if names provides a much more robust way of creating flexible code, rather than referring to the range using cell references.
• For general Excel modelling, it can be useful to name a small set of key ranges, so that the F5 key or the name box can be used to rapidly navigate around the model.
• Where the model process is not required as a process to experiment with or modify a model, but is purely required to implement a known situation which will never be changed. However, much Excel modelling involves the process of experimenting with different approaches, and the use of named ranges in such cases can create extra complexity.

Some disadvantages of using named ranges include: that their use too early on in the risk analysis modelling process can create cumbersome structures, that it can be easy to create models with far too many names that then become poorly labelled, and the possibility to inadvertently create links between models. The management of names (such as their deletion and their scope) has traditionally been cumbersome in Excel. It is important to note that Excel’s 2007 Name Manager has radically reduced some of these disadvantages (this change being one of the most important improvements made to Excel when moving from Excel 2003 to 2007, in my opinion).

This set of points is by no means complete; a deeper discussion of modelling in Excel, including robust and readily understandable risk analysis models or option valuation and price forecasting, is contained within my book Financial Modelling in Practice.

Dr. Michael Rees
Director of Training and Consulting

The topic of skewness of an uncertain variable is perhaps one of the most fundamental in risk assessment modelling. When it is believed that a (continuous) process is symmetric, the choice of distributions to use to represent that process is generally of less consequence than when the uncertainty is asymmetric. For example, a symmetric Triangular, PERT, and Normal distribution (with appropriately selected parameters e.g. so that the means and standard deviations for each are the same) will be broadly similar; of course there are some differences, but they are generally at the margin and of little significance in many practical risk analysis modelling situations for general business purposes (though such differences can still be important in cases where extremely accurate models are required).

Here, I briefly mention some sources of skewness that arise in real-life processes, or in the associated modelling of risk:

• Multiplicative processes.  A process in which random variables are multiplied will create a skewed output, tending to a Lognormal distribution when many such independent variables are multiplies, and often approximated by such a distribution in any case. Such process arise in cost budgeting (e.g. the total cost estimate as the product of an uncertain volume, unit cost, and perhaps a duration), in asset price forecasting (% changes to asset values over several periods work in a multiplicative sense), and in oil reservoir modelling (uncertain reserve estimation volume estimate being the product of uncertain spatial dimensions and some additional other factors, i.e. for exploration and production).
• Compound processes with event risks. When taking a pragmatic modelling approach in cost budgeting (e.g. using a Triangular distribution), one often simply assumes that the cost distribution of an item is asymmetric; that is we assume that (for unspecified reasons) the costs are more likely to be over the base estimate than below it.  Often part of the underlying reason is the presence of event risks in the situation, where the occurrence of a specific event creates an additional (perhaps uncertain) set of costs in addition to a (perhaps uncertain and symmetric) base cost.
• Parameter estimation for small sample sizes. When estimating a probability from a set of observations (for example 5 occurrences of an event during 100 periods, or trials), one sometimes takes the “maximum likelihood” approach (i.e. assume 5%) or otherwise assumes that there is a distribution of possible probabilities (such as a Beta distribution). Either way, for small sample sizes, the distribution of the uncertainty of the true probability is not symmetric. Examples of this were given in the earlier blog about the difficulties in estimating the probability of low probability events.

Dr. Michael Rees
Director of Training and Consulting

The recent Wall Street Journal article “Odds-On Imperfection: Monte Carlo Simulation” asserts that Monte Carlo simulation did not predict the market crash, and cites a chorus of critics calling for a fix to the technique. The article equates the technique of Monte Carlo simulation with the models that are using it – two very different things. For instance, the article states, “These models were supposed to help quantify and manage the risks of mortgage-backed securities, credit-default swaps and other complex instruments. But given the events of the past couple of years, it appears that the models often gave big institutions, as well as small investors, a false sense of security.”

This is true – the models for decision making under uncertainty gave a false sense of security. But that’s because the assumptions underlying the risk analysis models were flawed, not because the technique of Monte Carlo simulation was problematic. Monte Carlo simulation is simply a mathematical technique that recalculates many different possible scenarios – but only within boundaries set by the user.  You can’t change the underlying math behind these “what-if” calculations.

The article comes close to making this distinction in one sentence: “Critics emphasize that the problem isn't Monte Carlo itself, but the assumptions that go into it.”  It then goes on to describe efforts by firms to include “fatter tail” distributions that more accurately reflect the probability of extreme events occurring as an effort to improve Monte Carlo simulation.  Tools like @RISK (risk analysis software add-in for Microsoft Excel) allow complete control over the definition of many dozens of distribution types, enabling users to create as fat a tail as they want. While these efforts make sense, it should be made clear that these are changes to underlying model assumptions, not changes to Monte Carlo simulation itself. To equate Monte Carlo simulation as a technique with the probability distributions people decide to use is to equate a carpenter’s choice of nails with his hammer.

Finally, the article cites the need to run tens or hundreds of thousands of scenarios, instead of just 100 or 1000.  This too is user-defined, and tools like @RISK can run as many scenarios as desired.

Randy Heffernan
Vice President

To make his point about environmental risk analysis, Stavins turns to an analogy in baseball: due to randomness over time, the "best" teams win over the full length of a season, while "hot" teams win during the much briefer period of the post-season.  Randomness is what links baseball outcomes to regulation outcomes, and according to Stavins, Monte Carlo software is in much greater use in baseball than in environmental policy making.  In fact, baseball has its own group dedicated to statistical analysis, SABR (Society for American Baseball Research) whose quantitative work is know as "sabermetrics."  You might think sabermetrics would be a discipline unto itself, but Slavins doesn't.  He thinks federal regulators have a lot to learn from SABR.  

At the height of panic--and consternation--over turmoil in the financial sector at the turn of the year, many an accusatory finger was pointed at the risk analysis models the finance industry used to establish the value of various types of debts. Often the particular charge was that the simulations produced by Monte Carlo software lacked not only precision but even the capacity for precision. At the time, I responded in this blog that a risk assessment model is only as reliable as the probabilities it is build on.  

The recent blog positing on best practices in Excel modelling could be thought of as providing a reasonable and robust set of principles for building static Excel models. When building simulation models for risk analysis in Excel (for instance, with @RISK Monte Carlo software), some other points are worthy of consideration:

• A risk model may need to be built at an appropriate level of detail. A model which is too detailed will be more complex to add risk distributions to and will require more effort to capture the dependencies between the larges number of variables. In many practical cases, key dependencies will simply not be captured, and the result will have an excessively narrow range (for additive type models e.g. cost budgeting) or an excessively wide range (for subtractive models e.g. profit as the difference between uncertain revenues and costs).
• The inclusion or not of event risks. Generically, a static model of a situation in which there are event risks (e.g. something adverse happening in 20% of cases in a reserve estimation model) would not include such a risk as a line item (since the most likely outcome is its non-occurrence), whereas a risk model would.
• The prioritisation of event risks to include may be non trivial, and depend on the decision maker's risk profile (i.e. tolerance and decision-making criteria), as well as the potential total number of event risks under consideration.  For example, in a retirement planning model where a decision is to be based on the P90 outcome (i.e. the worst or best 10% of cases) it would be more important to include an event with an impact of 1 (with 100% probability) than an event with impact of 100 with 1% probability, as this latter event in isolation would have very little effect on the P90 of the output distribution (Were decisions to be made on the P99.5 value, we would have a different situation, of course.).
• The use of DataTables will generally slow down simulation models, as the tables need to be recalculated at every iteration. DataTables may be used when building to model and as an error-checking tool (TopRank may also be used, to check that errors are zero across a range of scenarios), but may need to be removed before running the final simulation model.
• The real challenges in risk modelling boil down to those related to model formulation and decision-making; that is: the selection of variables, capturing the true dynamic of the situation in the model, choice of distributions, capturing dependencies correctly etc. (so come to a training course!)
• Other principles of model implementation (as discussed in the earlier blog) are essentially identical.

Dr. Michael Rees
Director of Training and Consulting

In an article this week in the online publication TheStreet, Taylor Smith takes aim at online calculators for retirement planning.  He says the problem with them is that most of them are based on Monte Carlo software, and then goes on to make some broad and inaccurate statements about the characteristics about Monte Carlo simulation and the ways it skews risk assessment.  

For starters, he quotes an investment "expert," who is, coincidentally, president of concern that produces risk analysis software not driven by Monte Carlo software, to the effect that good financial planning should take into account, more than market returns--including taxes, income, and expenses.  This implies the Monte Carlo simulation is not capable of factoring these elements into its predictions.  Nothing, as the a recent risk analysis model offered by both the Society of Actuaries and the Casualty Society aptly demonstrates, could be farther from the truth.  This model, developed by professors of finance and mathematics at Illinois State University and the University of Illinois Champaign-Urbana, helps pension and insurance planners to to forecast not only projected market returns but the effects such critical economic factors as interest rates, equity price levels, inflation and unemployment rates, and real estate prices.

My point is that a Monte Carlo simulation is what you make it.  It can be very simple and limited to one or two economic factors, or it can be a complex mix of many factors.  If the online retirement calculators are too simplistic to usefully account for reality, their builders have plenty of room to improve them.     

In assigning blame for what they are now calling "the credit crunch," the news media have been pointing vaguely in the direction of risk assessment and the models produced by Monte Carlo simulation.  But with the exception of Joe Nocera's excellent piece focusing on value-at-risk in the New York Times, I had not seen a clear explanation of the factors feeding into the exponential decline of the credit markets until I came across a policy paper from the Association of Chartered Certified Accountants, "Climbing out of the Credit Crunch."

This paper provides an excellent, plain-English account of the interplay of the many factors that brought the current turmoil in the financial sector--a number of these factors the ACCA identifies are psychological and attitudinal.  Its discussion of risk identification and management focuses not on risk analysis models but on the underlying assumptions--a common "garbage in, garbage out" observation--and a passive, unquestioning reliance on these models.  This leads the ACCA to one of what it identifies as a major risk management failure: "a very clear disconnect between incentives to senior staff" and [highly sophisticated] risk management functions."

The point is that risk management is so crucial to financial performance that executives who keep a close, critical eye on the tools and techniques they are using to assess risk should be rewarded for that vigilance. 

Warren Buffet did not have a good year.  This fact about 2008 was detailed last week in a New York Times article and in a letter from Mr. Buffet to stockholders of his Berkshire Hathaway company.  This letter offers plenty of plain-spoken criticism of the risk assessment failures in the financial sector to go around--'beware of geeks bearing formulas." 

But he reserves his harshest criticisms for derivatives--"weapons of mass destruction.".  He not only cites the hazards of using risk analysis models to value these complexly structured investment products, but he points out something I haven't seen mentioned before: derivatives create a "web of mutual dependence" among financial institutions that lingers for years.

About this web of dependence, he says, “Participants seeking to dodge troubles face the same problem as someone seeking to avoid venereal disease,” he wrote. “It’s not just whom you sleep with, but also whom they are sleeping with.”

I recommend Warren Buffet's letter if only for its Olympian view of the exponential decline in the credit markets and his list of the intriguing entertainments he has planned for his shareholders at their annual meeting this May.  His bad year makes for some good reading.