When doing risk analysis simulation modeling, it’s critical to represent the fact that many variables are related. Very rarely are all variables completely independent of one another. For example, when interest rates are low, housing starts tend to go up. This relationship is represented by a correlation coefficient, a number between -1 and 1. -1 means the two variables are perfectly negatively correlated; that is, when interest rates go up 1 unit, housing starts go down 1 unit. A coefficient of 1 is exactly the opposite, and 0 means there is no relationship between the variables at all. Typical coefficients are between these extremes. This correlation between input variables can and must be captured in your financial risk analysis models. Even if you are using expert opinion, estimating these relationships is better than ignoring them completely.
@RISK provides an easy way to define correlation coefficients. Through the use of a slider bar, you can dynamically update coefficients between variables as well as scatter plots representing those variables. This helps you visualize and more easily define these relationships.
Presented at the Palisade Risk & Decision Analysis Conference New York City Chris Albright, author of the book VBA for Modelers, presented a number of examples of how to automate @RISK, RISKOptimizer, and StatTools in Excel using Excel's VBA and Palisade's built-in object-oriented Excel Developer Kit. These examples include production applications, scheduling applications, World Series simulation, and more. All examples include macros written by Dr. Albright, so you'll need to enable macros when you open them.
In his blog yesterday for Smart Data Collective, Dean Abbott, makes a worthy, commonsense observation: no matter how accurate a predictive model is, it is of no use to the enterprise unless it is presented in such a way that all the decision makers understand what factors and techniques went into the analysis and why.
The reason that the 'best understood' model is more effective than the 'best' model is that when the people with authority over a particular decision are presented with a statistical analysis that is beyond their ken, they may or may not pretend to understand it. But in any event, they are not likely to buy into the results if they can't retell the story the model describes.
Take for instance, a Monte Carlo simulation that focuses on credit risk analysis for a particular loan. Everyone in the line of authority will be held responsible for real world outcome of what the Monte Carlo software describes in the Excel spreadsheet. And if you are one of these decision makers, how can you take responsibility for something you may not quite understand?
The problem of acceptance of a predictive model presents the analyst with a tough question: Do I present the model that I know is true and statistically accurate? Or do I present a ruder, cruder analysis that presents a story that can be immediately understood?
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.
@RISK risk analysis software using Monte Carlo simulation is used for a wide variety of applications. In this model, we have an example of a general usage to address Operational Risk.
In many circumstances one wishes to calculate the aggregate impact of many possible yes/no type events. For example, it is often important to answer questions such as "What is the loss amount that will not be exceeded in 95% of cases?" @RISK simulation can be used to answer such questions.
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.
Sure enough, there it was, a comparative rating of pollsters. This will give people like me, who tend to believe any poll just because it's covered in the news, a way to assess the poll reliability. FiveThirtyEight is the brainchild of Nate Silver, and 538 is the number of members of the Electoral College. Silver's primary business is Baseball Prospectus, which is also fueled by Monte Carlo simulation and other risk analysis techniques, but FiveThirtyEight has done well enough for the New York Times to want incorporate it in its online coverage during the coming elections.
Silver's grasp of statistical analysis becomes immediately evident when you go to his page on the pollsters, and he's more than happy to discuss the statistical methods he uses to rate the pollsters--regression analysis of raw data, Monte Carlo software in an Excel spreadsheet, weighting of poll performance data, and so forth. His take on these matters may be of practical interest to any of you who use these techniques in financial risk analysis.
Elections are all about decision making under uncertainty, especially voter decisions under uncertainty, and according to Nate Silver, only polls taken within 21 days of an election are reasonably reliable. So when the national campaigns are ramping up in October, keep one eye on the polls and one on FiveThirtyEight.
You can quickly generate a graph of any simulated output relative to a given input using the tornado graphs in @RISK risk analysis software. Just drag a bar from the tornado graph onto a blank space, and a scatter plot of that input relative to the simulated output appears. This is a great communication tool when you’re trying to assess the most important risk variables in your risk analysis model and want to “zoom in” on a particular input to see what’s going on. It can really help understand relationships in your modeling.
In @RISK (risk analysis software using Monte Carlo simulation), you can easily overlay two graphs directly from any graph window. Just click on a button to identify the variables to overlay, and you’re done. Alternatively, you can drag thumbnails of different simulated results onto the same graph to create overlays. These overlays in the risk analysis models are useful for comparing different strategies, showing trends over time, and more.
@RISK, Palisade's risk analysis software using Monte Carlo simulation, has many features that help you create powerful models for decision making under uncertainty.
This model demonstrates the use of the alternate, percentile parameter formulation. In this case we assume that we have decided to use a Normal distribution to represent the arrival time of someone at work. The use of traditional parameters would require knowledge of the standard deviation of the arrival time, which may be hard to estimate. The use of the alternative parameter formulation allows data to be estimated by others in a more natural way. In the first case, the traditional parameters are used (mean and standard deviation). In the second case, the mean is still used, and the P90 is used in place of the standard deviation, i.e. the time before which the person arrives in 90% of cases. In the second case, the P10 and the P90 is used in place of the standard deviation i.e. the time before which the person arrives in 10% of cases, and in 90% of cases respectively.
@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.
Just after I posted my last blog questioning a recent Investopedia column in the San Francisco Chronicle, I had a congenial note from the author of that column, David Harper. His column compared Monte Carlo Simulation with two other methods of calculating Value-at-Risk, and I was concerned that its view of risk and risk analysis techniques was overly simplified. David was surprised to discover that column had just appeared because he wrote it five years ago!
The five-year lag explains a lot--Monte Carlo simulation was not nearly so widely adopted or carried about by so many software tools as it is today--and I should have suspected the article was a vintage piece before I started carping.
So I happily retract my concerns to introduce to you David Harper, CPA and certified Financial Risk Manager. In response to my comment about the attitudes and techniques that led to last year's collapse of the financial markets, David says that, now that the black swan has flown, "the crisis should implicate both HistoricalSim VaR and parametric VaR (at least multivariate normal!) and point toward Monte Carlo Sim. I've been thinking for a while that all of this [I think he means lack of accuracy in specifying risk] should really boost Monte Carlo."
Investment commentary is only one of David's activities. He is the founder of Bionic Turtle, a business devoted to e-learning about financial risk and preparation for the certification exam for financial risk managers. This is a worthy enterprise--I was relieved to discover that there are hoops financial risk managers have to got through to be called that--and for anyone who would like to know more about quantitative techniques for risk analysis, its website is worth prowling.
The UK finally seems to be heading into summer after an unusually long and cold winter. However, despite the amount of rain that falls on our 'green and pleasant land' (to the extent of major flooding on occasions), one of the anomalies of the UK weather system is that any prolonged warm period seems to be accompanied by the underlying threat of a hosepipe ban.
This is in stark contrast to many regions around the world that, despite seeing far less precipitation, manage a robust water supply. Abu Dhabi for example has no rain for several months of the year, and relies almost completely on desalinated seawater for its potable water requirements. The desalination process is challenging in terms of operation, costs, and environmental impact. Whilst over-production capacity is expensive, at the other end of the scale it is essential that Abu Dhabi has sufficient water production capacity to support the Abu Dhabi government development plan (Abu Dhabi Plan 2030).
This plan means that the Abu Dhabi Water & Electricity Company (ADWEC) is required by the Regulation and Supervision Bureau (RSB), the regulatory body of Abu Dhabi, to use a risk-based methodology to assess the water demand and required capacity. As a result ADWEC uses @RISK risk analysis software to help it to forecast as accurately as possible the demand for water and electricity across the Emirate in order to plan for the optimum expansion as well as the efficient and effective use of water production plants.
@RISK enables ADWEC to model all feasible uncertainties in the variables that determine the quantity of water required over specific timescales, such as per capita water consumption rates and the rate of population growth. The variables input into the @RISK risk simulation software model are based on the water demand categories such as domestic, agricultural and industrial. Factors with inherent uncertainties that affect the demand forecast outcome and must be modelled include: seasonal variation, distillers' unplanned outages, water losses, population growth rates and demand for housing.
By undertaking risk analysis of the variables involved in assessing demand and supply, ADWEC minimises the potential for water production capacity to be over or under deployed. As a result of using @RISK to assist with its forecasting, planning and management strategies, ADWEC has been able to consistently meet with almost complete accuracy the Abu Dhabi Emirate water demand forecasts.
Modeling Uncertain Number of Events, Each with Uncertain Parameters @RISK (risk analysis software using Monte Carlo simulation) is widely used in insurance and reinsurance for premium pricing and loss reserves modeling. A 2006 survey identified @RISK as the third most widely-used software by actuaries, after Microsoft Office and in-house actuarial tools.
@RISK's RiskCompound function allows for the sampling of frequency-severity distributions. This is often required in insurance modelling, as well as in some operations management situations. For example, to determine the total insurance claims payout, one must account for the uncertainty in both the total number of claims (frequency) and the dollar amount of each claim made (severity).
A powerful feature of the function is that the argument that corresponds to the severity may be a reference to a cell containing a formula (rather than just a single distribution function).
For example, one could use the function in the form RiskCompound(RiskPoisson(5), A10). The Poisson distribution would describe the frequency (occurrence) of events (e.g. an individual sample may determine that three events occur), and cell A10 would contain a formula that is separately evaluated for each of these three events (before returning the sum of these three as the sampled value of RiskCompound).
A simple example could be A10 = RiskLognorm(10000,1000)/(1.1^RiskWeibull(2,1)), with the Weibull distribution representing the time to settlement of an insurance claim, which is used to discount the basic claim value sampled from the Lognormal distribution of severity. For example, once a claim is filed for a nominal amount, the actual payment may be delayed due to court actions or disputes, which may reduce the cost of the claim to the insurer.
Value-at-Risk is a calculation that predicts a worst case scenario in which the maximum loss for a specific investment would be realized. Recently the San Francisco Chronicle investment blog Investopedia, ran a short series posts on VAR. One of the more intriguing of these demonstrated three ways of calculating Value-at-Risk for a single stock investment for more than one time period.
The three methods were historical simulation, variance-covariance, and Monte Carlo simulation. What was intriguing about the comparison of methods was the observation that best choice among these methods was the variance-covariance method because it was easy. The downside of using the historical method was the need to crunch data and the downside of getting out your Monte Carlo software--no mention of using historical data to inform your model--was that the Monte Carlo method was "complex."
Does that mean that risk is simple enough to require only simple statistical analysis? And doesn't this kind of thinking encourage financial planners to take a direct but drastically reduced view of the possible outcomes of an investment? And isn't this the same turn of mind that led to the collapse of the financial markets only a year or so ago?
Variance-covariance assumes volatility only in terms of standard deviation, and volatility doesn't come in one flavor or standard deviation. Neither does risk.
Working in dynamic groups is a fact of modern life. You may work with different colleagues on different projects, and may work with others in remote offices as well. When you use risk modeling software, being able to share information with others is critical to team projects.
@RISK offers a number of ways to facilitate sharing and collaboration of risk analysis models. This three-part series will highlight popular sharing methods.
Did you know that you can remove @RISK functions from @RISK spreadsheet models in order to share them with others? This enables those without @RISK installed to be able to read and edit the same models. It’s great for sharing and collaborating across various workgroups. Furthermore, changes that non-@RISK users make will be preserved when you go to restore the @RISK functions back into the model.
@RISK risk modeling software is used for a wide variety of applications in financial risk analysis forecasting, investments, and banking. This model is one example of how @RISK can help in risk analysis decision making.
Models of the prices of assets (stocks, property, commodities) very often assume a random walk over time, in which the periodic price changes are random, and in the simplest models are independent of each other. The future price level of the asset may result in some contract or payoff becoming valuable, such as in the case of financial market options. In these cases, the value of the contract (contingent payment or option) is calculated as the average discounted value of the future payoff. In the special case of European options on a traded underlying asset, the value calculated from the simulation may be compared with mathematical formulas that analytically provide the valuation, such as the Black-Scholes equation. In many more complex cases, the pertinent analytic formulas may be unknown or very complex to derive, and one may wish to rely on simulation techniques. This particular model compares the average simulated payoff for European Call and Put options with the Black-Scholes valuation.
@RISK risk modeling software is used for a wide variety of applications in financial risk analysis forecasting, investments, and banking. Below is an application of a discounted cash flow analysis.
Discounted cash flow (DCF) calculations are a frequent example of the use of @RISK. In the example model, the sources of risk are the revenue growth rate and the variable costs as a percentage of sales. After taking into account the assumed investment, and applying a discount factor, the DCF is derived. Following the simulation, the average (mean) of the DCF is known as the net present value (NPV).
In this example, the results show that the average DCF is positive (about 40), whereas the probability of a negative DCF is about 15%. The decision as to whether to proceed or not with this project will therefore depend on the risk perspective or tolerance of the decision-maker.
This example has also been extended to calculate the distribution of bonus payments on the assumption that a bonus is paid whenever the net DCF is larger than a fixed amount (such as 50). It also uses some of the @RISK Statistics functions RiskMean, RiskTarget, and RiskTargetD to work out the average net DCF, the probability that the net DCF is negative and the probability that a bonus is paid.
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.
A recent U.S. Court of Appeals case is timely not only because it involves corporate liability for ocean pollution when everybody in this country is morbidly tracking the BP spill in the Gulf but because it is a case in which the judge highlights and corrects some common misconceptions about Monte Carlo simulation.
In a consolidated case involving hazardous waste dumping in the Houston Ship Channel, the codefendants, Tenneco and Occidental, acknowledged liability for the pollution cleanup, but they appealed a lower court's decision partly on the basis of the court's method of allocating costs. The court had called an environmental engineer as expert witness and statistical analyst. The engineer used Monte Carlo software and court-established inputs for his model. The defendants challenged the court's inputs in the risk analysis model, and the Circuit Court decision rebutted their objections in clear terms.
Writing for the Fifth Circuit Court of Appeals, Judge Patrick Higginbotham said, "Monte Carlo measures the probability of various outcomes, within the bounds of input variables; to calculate Occidental's waste volume,. . . Instead of simply averaging the input values, Monte Carlo analysis uses randomly-generated data points to increase accuracy, and then looks to the results that those data points generate. The methodology is particularly useful when reaching an exact numerical result is impossible or infeasible and the data provide a known range—a minimum and a maximum, for example—but leave the exact answer uncertain."
Responding to the charge that this method of statistical analysis is unreliable and untestable, Higginbotham responded,". . .the cited cases at most stand for the proposition that Monte Carlo analysis is unreliable when injected with faulty inputs, but nothing more. . . . Monte Carlo simulation is not inherently untestable. . . . If anything, Monte Carlo provides greater certainty than the basic alternatives: using one of the three data or using the arithmetic average of all three."
Countering the challenge that the model results were "equivocal" the judge continued, " The Monte Carlo analysis—though it produced a statistical range of likely outcomes and not one determinative answer—supports choosing one result over another, and certainly assisted the district court in its decisionmaking."
The decisions-by-the-numbers guys certainly had their day in court. The free advertising wasn't bad either.
Chris Albright, author of the book 
The UK finally seems to be heading into summer after an unusually long and cold winter. However, despite the amount of rain that falls on our 'green and pleasant land' (to the extent of major flooding on occasions), one of the anomalies of the UK weather system is that any prolonged warm period seems to be accompanied by the underlying threat of a hosepipe ban.
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.
Subscribe to RSS blog feed.
Subscribe to the Palisade
Become a fan of the Palisade 
Follow Palisade on Twitter.
