Monte Carlo Software Excel

Abbott suggests a compromise: streamline your plot by masking (Abbott says "removing") fields that contribute to the robustness of the analysis but involve statistical twists and turns that are distracting to decision makers who may not be fascinated with technique and just want to see how the story turns out. This, he explains, allows you to work from a model both you and the decision makers can believe in.

Your thoughts? 

With the New York State primaries coming up September 14 and the general election on November 2, I predict that as soon as summer turns the corner into September, we'll start hearing lots and lots about polls that predict election outcomes.  To find out if there was any early discussion of polls, polling, and outcomes, I returned to my favorite election forecast site from the 2008 presidential elections, FiveThirtyEight: Politics Done Right.

@RISK, risk analysis software using Monte Carlo simulation, has many powerful features that help you create powerful models for decision making under uncertainty.

For example, you can use the RiskTheo function in @RISK to determine the parameters of a discrete distribution based on a continuous one. In this example, the RiskTheo functions of @RISK work out the P10, P50, and P90 percentiles of a continuous distribution (in this case the LogNormal), and the Mean and Standard Deviation of a RiskDiscrete distribution which has these X-values and some assumed probabilities (or weights). It then uses Excel's Solver to work out the probabilities required so that the discrete distribution based on these percentiles and probabilities would have the same mean and standard deviation as the continuous distribution.

» Download the example: CtsToDiscrete.xls
» See "Uses of the RiskTheo functions in
   @RISK to match distributions"
 » See "DMAIC Failure Rate using RISKTheo" for a
    Six Sigma application of the RISKTheo function

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

As a small--very small--shareholder, discovering that there is such a tipping point and that Karame knows how to locate it is reassuring.  Makes me feel there's someone on my side.   

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

Today there is a heightened appetite for risk management due to global economic circumstances. But risk management has always been an intrinsic aspect of business to a higher or lesser degree. However, in the current technology-led business environment, the use of software to effectively manage risk makes logical sense. It provides a level of sophistication that the traditional processes simply cannot offer. Let me explain why.

Risk management essentially involves three stages – identification, quantification, and the on-going management of risks. In reality, these stages are not completely distinct from each other, with each stage influencing and informing the others. For example, an initial quantification of risks may lead to the conclusion that some of the identified risks are in fact not serious enough to warrant further consideration, or that the original description of the risk was not sufficiently precise for meaningful risk management measures to be put in place.

Each of these stages can benefit from the use of supporting risk modeling software. For instance, Microsoft Excel can be used to create a risk register, i.e. a database that records the risks identified, the assessment of the likelihood and impact of each of these risks, the mitigating actions that have been planned, and the assignment of responsibilities for these actions. However, there are many other software tools available, each designed for a specific purpose and focus. To illustrate, enterprise-wide risk management software focuses on the creation of integrated and holistic risk management systems, whereas Monte Carlo simulation and decision tree software place their emphasis on enhancing the quantitative analysis of risks.

The selection of the appropriate risk analysis software should involve very careful thought. The right decision can lead to a very effective implementation, whereas the wrong decision may result in a large amount of wasted investment.

There are some key considerations to bear in mind when selecting the risk modeling software. Choosing software based on how many staff will genuinely be required for the day-to-day risk management process is crucial. It is easy to select software based on the ideal situation that there will be a wide staff involvement in the risk management process. In reality, this may not be possible, potentially resulting in a cumbersome and inflexible solution being chosen over a more stand-alone and flexible application.

Similarly, knowing the level of risk quantification required is important. In fact, best practice risk management now involves the use of quantitative techniques, often using Monte Carlo simulation. When correctly conducted, the process of quantifying risks is rigorous and structured, can expose hidden or biased assumptions, as well as provide a more solid rationale upon which to base the major decisions.

Finally, determining the extent of on-going risk management needed for your business can assist with software selection. 

Needless to say, any software application will be most successful when used by appropriately trained and motivated staff, and when used as a supporting tool within an overall risk management process. Software is not a replacement for process.

Craig Ferri
EMEA Managing Director of Risk & Decision Analysis

It’s clear that the financial crisis has exposed a number of failings in the practice of risk management. In my last post I talked about the relevance risk analysis and the disciplines of ‘quantitative risk management’ (QRM) and ‘decision making under uncertainty’ (DMU) are to all sizes of organisations, be it large or small. 

However, how accessible are these disciplines to the average size business across the globe today?

With the need to make more informed decisions more pressing by the day, thankfully QRM and DMU and now far more accessible than ever before.  Traditionally systems tended to be expensive, enterprise-based applications targeted at large companies who were prepared to spend considerable time, money and human resources.  The result was an all-singing, all-dancing product which often ended up underused due to confusion on the part of the very employees who were supposed to make it work.

Steady increases in computer processing have given the desktops of today as much power as the high-end servers of a few years ago, meaning that risk analysis and management is now an achievable goal for businesses of all sizes.  Palisades @RISK and Decision Tools Suite software are such desktop risk and decision analysis tools – working within Microsoft Excel and therefore being accessible to a large number of users.

‘Monte Carlo Simulation’, a technique originally conceived by scientists working to develop the atomic bomb as part of the Manhattan Project, is an inherent part of @RISK, a cornerstone of the Suite.  It enables users to introduce uncertainty into their previously static spreadsheets, which lets them look at things in a probabilistic, rather than a deterministic way.  In layman’s terms, this means that rather than companies and individuals making decisions based on estimates or best guesses, they can see all the potential outcomes to a venture – and how likely these scenarios are to occur.

For many companies this significantly improves the decision-making process.  Firstly it requires a change in the methodology of employees responsible for assessing risks and opportunities and secondly for the first time employees have a tool which allows them to communicate their recommendations to management or colleagues in a transparent and standardised way.  Equally, being able to look at scheduling risk in a probabilistic and quantitative sense allows for the allocation of labour and resources in a way which minimises slack and wastage whilst maximizing ROI.

So, it would seem that the new ‘risk management’ language that is starting to develop in the workplace and being taught to a new generation of managers on MBA courses should be welcomed.  With the accessibility of the technology available to assist them, we need to make sure that organisations do more than just pay lip service to QRM and DMU if they are to reap the rewards.

In my next blog I’ll be giving you the my top ten tips to adopting a health approach to risk, that will help businesses of all sizes maximize their risk management programmes.

Craig Ferri
EMEA Managing Director of Risk & Decision Analysis

I'll defer to Mr. Miller, whose blog is loaded with interesting details.   

When modeling risk events, it is common that several events could affect the same cost element of a project. During the simulation, two or more risk events can occur at the same time. The question becomes how to calculate the total impact. This type of modeling technique is very common and often needed in project risk analysis, contingency and mitigation studies, reserve estimation and production forecasting.

A common practice is to aggregate the total impact. However, this simplistic approach might not be correct since it ignores the compounding effect of multiple occurrences. For example, if we have two risk events occurring and their respective impacts are a 20% and 40% cost increase, an additive model will calculate the total impact as 20%+40%=60%. On the other hand, a multiplicative (compound) model will calculate the total impact as 1.2 x 1.4 = 1.68; here the total impact is 68%. In other words, the impact of these risk occurrences will be greater than the summation of the individual impacts, which in many cases makes sense.

Using the table below we will show how you can use @RISK (risk analysis software using Monte Carlo simulation in Excel spreadsheets) to can calculate the distribution of the total impact that can affect the cost of a project activity. You can see that in this example we also model the opportunity of a cost reduction.





To model the % cost increase and reduction we use a Triangular distribution. Here we say that the maximum impact is the value in column F (the user can use other distributions or parameters as needed). With this logic the distribution of the impact of Risk 1 looks like:



and the total impact distribution results in:



The following table contrasts the results obtained using a compound and an additive model:



Something to note in the table above is that in the opportunity side, the maximum cost reduction using the compound model is less conservative than the additive model.

As seen here, coding this type of model is not difficult. My suggestion is that when you have multiple occurrences of risk events you explore better alternatives than the additive model. As always, your comments are more than welcome.

Dr. Javier Ordóñez
Director of Custom Development

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

Clayton Graham is an adjunct professor of Statistics and Economics at DePaul University. He holds senior positions with the Chaos Group, Inc. and Analytical Advantages, LLC where he functions as a management consultant specializing in analytical and graphic econometrics.

He will present a case study at the 2009 the 2009 Palisade Conference: Risk Analysis, Applications, & Training. The conference is set to take place on 21 - 22 October at the Hyatt Regency in Jersey City, 10 minutes by PATH from Manhattan's Financial District.

See the abstract for Clay Graham's case study below, and see the full schedule for the Conference here.

Capitalizing Upon Market Inequities:
A Game Plan for Successful Sports Wagering

Sports wagering brings two separate "markets" together. First is the production market or the game itself.  The second is the wagering or betting market. As a matter of practicality, the wagering market is itself in balance, i.e., bet clearing is covered through the process of adjusting the cost-payout ratio (the line). Betting lines are translated into an expected probability of winning. This resultant probability is frequently inconsistent with the probability of the team actually winning. 

Hence, the opportunity to capitalize upon the dichotomy between the inequities of the production and gaming markets will be detailed and quantified. The presentation will include:

• Fundamentals of gambling lines and odds,
• Identification of key metrics,
• Methods of production modeling baseball and basketball
      (similarities and differences),
• Integration of economics (investment) with production,
• Economics of decision making.

Principal Palisade software utilized includes: StatTools (statistical analysis toolkit for Microsoft Excel), @RISK (risk analysis software using Monte Carlo techniques in Excel) and Evolver (genetic algorithm optimization in Excel). The presentation will have a heavy graphic and visualization emphasis. Theoretical statistics will be tightly tied with pragmatic realities of game modeling and economically based decision making. 

Specific quantification will consist of:

• Probabilities of winning a game,
• Measurement bias of officials,
• Quantification of player performance,
• Expected values of return on investment,
• Sports gambling optimizing algorithm.

Examples of actual results for current and past seasons along with predictions will be provided. 

In short, it’s "Card Counting" for sports!

More information about this project can be seen at Baseballwon.com.

Over the past three years I've been tracking an uptrend in risk analysis in what might seem an unlikely field, wildlife conservation.  But on further thought, this makes perfect, straightforward sense. At-risk animal populations could use some analytical help sorting out the live-or-die questions. 

Kurtosis is a statistical measure of a random process that is often used, but perhaps less widely understood. This blog mentions a couple of key issues and misunderstandings about kurtosis in a risk assessment model.

A high kurtosis figure is sometimes described as being associated with a distribution that has “fat tails”. However, by simply overlaying two Normal distributions with the same mean but different standard deviations (e.g. using @RISK to do so), it is visually clear that the distribution with the larger standard deviation has the “fatter tails”.  However, every Normal distribution has a kurtosis of 3 (sometimes “excess kurtosis” is referred to, whereby any base calculation has three subtracted from it; this is the case when using the Excel KURT function to calculate kurtosis, for example), so the kurtosis figure does not pick up the idea that one of the distributions has more weight in the tail.

In fact, kurtosis is a simultaneous measure of the “peakedness” of a distribution and the extent to which it has “fat tails”; the Normal distribution with the larger standard deviation will have fatter tails, but will also be less peaked, and in terms of how kurtosis is calculated, these effects balance out.  Kurtosis is then a bit like going for a workout, where you are required to push weights in a central direction whilst keeping your elbows up!

Another important aspect of kurtosis that is little appreciated is that the kurtosis of a binomial distribution (e.g. modelling an event risk that may or may not happen with a certain probability) increases as the probability of the event decreases. In this sense, distributions with high kurtosis figures are perhaps most easily understood as ones relating to events of low probability but high impact.

Such topics are very easy to explore with @RISK (risk analysis Monte Carlo software add-in to Excel), where the visual ability to view overlays, combined with the use of the RiskTheo functions to obtain in Excel the numerical values of statistics associated with distributions allows for a powerful environment to rapidly address such issues.

The U.S. Federal Reserve recently released the results of a comprehensive assessment of the financial conditions of the nation's 19 largest banks, which hold two-thirds of American economic assets. This “stress test” was designed to determine the capital buffers required for the banks to withstand losses and maintain lending even in worsening economic conditions. Officially called the Supervisory Capital Assessment Program (SCAP), the test identified the potential losses, resources available to absorb losses, and resulting capital buffer needed.

Monte Carlo simulation was used to determine the potential losses from further defaults on loans. According to Federal Reserve Chairman Ben Bernanke,  “The assessment program was a forward-looking, ‘what-if’ exercise.”

Monte Carlo simulation is one of the most widely used methods of stress testing for capital and operations risk,  according to Investopedia.  It takes into account variables such as interest rates, lending requirements, and unemployment. As any @RISK software user will tell you, this type of sophisticated simulation can be accomplished easily within the Microsoft Excel environment. The result of a Monte Carlo software simulation is a look at a whole range of possible outcomes, including the probabilities they will occur -- a valuable tool when stress testing.


Randy Heffernan
Vice President

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