Risk Analysis Consultants

Customised Solutions Using @RISK and VBA for Excel

Thursday, July 8, 2010 by DMUU Training Team

If you missed Palisade trainer Rishi Prabhakar's webcast "Customised Solutions Using @RISK and VBA for Excel," you can still view it in our archive.

The hour-long presentation explores the use of VBA for Microsoft Excel to control @RISK functionality, to simplify the process of risk analysis for resource-strapped businesses. Rishi explains the advantages (and limitations) of macro control for modelling and running simulations.

Simple examples are worked through to show the XDK (@RISK’s automation library) in action, from generic examples to a cost estimation model. This addresses elements of model construction, various simulation settings and finally reporting. The emphasis is on exposing the viewer to the various possibilities the XDK lends to the user rather than an in-depth VBA for Excel coding session.

Rishi Prabhakar holds a BSc in Mathematics from the University of Technology, Sydney Australia. Rishi has experience in the resources, infrastructure and primary industries, telecommunications, scientific research, banking and finance with an emphasis on operational risk.

With technical skills in the areas of modelling, simulation, statistical analysis, cost estimation, time series forecasting, customised solutions utilising VBA for Excel, and extreme value theory, Rishi has provided training and consulting services in risk and decision analysis for Palisade’s Asia Pacific office since 2005.

» Customised Solutions Using @RISK and VBA for Excel
» Webcast archive

Value-Based Management Compensation

Wednesday, June 9, 2010 by Holly Bailey

Full disclosure: I am, like so many of my friends, an investor––a small-time one--and recently, I have joined in the public outrage about bankers' bonuses and executive compensation in general. Compensation is one of the hot buttons in the debate over financial reform. I keep wondering why compensation practices are what they are and how they could be adjusted to calm turmoil on Wall Street.

Enter Marwaan Karame, and his version of risk analysis.

Karame heads the New York consultancy Economic Value Advisors, which coaches major corporations on Value Based Management. Value--long-term versus right-now profit--is the foundation of the firm's philosophy. Its central principle is that any activity a business undertakes should increase the wealth of its shareholders--in the case of a privately held company, the number of shareholders may equal 1.

Karame has developed what he calls Value Based Compensation, and the goals of this are to align the self-interest of management with the self-interest of shareholders. He believes the shareholders, the company, come first. And this means a lot of decision-making under uncertainty. But Marwaan has a method for his management-shareholders balancing act, and it involves performance targets, statistical analysis, and risk assessment (in this case, managing probabilities of performance). His strategy involves maintaining a reserve of bonus funds and timing the payout of these rewards.

The point at which Monte Carlo simulation and Monte Carlo software come in is the point at which variance between performance targets and the level and timing of reward converge. He shows his his client how to click into Monte Carlo in the Excel spreadsheet and use the software to locate the tipping point between wealth for management and wealth for shareholders.

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.

(Data) Cleanliness Is Next To Godliness

Monday, June 7, 2010 by Steve Hunt

I’m pleased to welcome Palisade Six Sigma Partner Edward Biernat of Consulting with Impact as featured guest blogger. As well as running a successful consultancy, Ed is a noted Six Sigma educator and author.

--Steve Hunt

(Data) Cleanliness Is Next To Godliness

I recently had dinner with Eric Alden, a Master Black Belt for Xerox corporation. Eric had just gotten back from the American Society for Quality’s (ASQ) headquarters in Milwaukee where he was one of 200 Master Black Belts worldwide that generated the questions for the upcoming ASQ Master Black Belt certification examination (more on that in an upcoming post). Eric had also recently completed a mini-course for the local ASQ chapter on data integrity. We shared some war stories and came up with some common threads regarding data integrity.

1. Just because it is a number doesn’t mean it is worth anything. People get enamored with tons of data from process instrumentation, shop floor collection sources or Excel spreadsheets. There seems to be a false security with this pile of data, and managers often look to the Black Belt to ‘sort it out’, because with all that data, the answer is in there somewhere. Many a belt has crashed on the rocky reefs of bad data, often after tons of time and effort (and credibility) were wasted generating false answers.

2. GIGO. The Garbage In – Garbage Out philosophy of computing applies especially to existing corporate databases. Here a few recent examples of GIGO.

a. A belt wanted to analyze the specific timing of events in shop floor process and had tons of data from the process instrumentation that had times down to the fraction of a second. After lengthy analysis, they found a significant difference between two shifts and forced the lesser shift to adopt the sequence of the more uniform shift. After introducing costly production problems and actually hurting the overall process, the sensors were found to be faulty and the overall process subject to human manipulation to generate the ‘pretty charts’ that everyone expected.

b. Office areas are not immune. Something as simple as a checksheet to gather data to analyze when a particular computer error occurred can be in question, especially when the clerk fills in the times at the end of the shift from memory rather than logging the event as it occurs.

3. Good data in bad spreadsheets. Even if you get good data, having an inexperienced person setting up the spreadsheet can cause problems. It is analogous to a person using a word processing software and making a table using spaces and tabs. It looks like a great table until you have to manipulate it. Then it falls apart. Problems like merged cells, subtotals, random formula inserted in cells, etc. can make a Belt weep and cause significant errors in the resulting analyses.

4. Useless manipulation. Often a big issue is that management wants data sliced a certain way for no good reason. This sometimes leads to the proliferation of additional spreadsheets or databases that needlessly add to complexity. (Note: If you have an ERP system like Oracle or SAP, USE IT! They are designed to house data and protect its integrity. Plus the data entry screens typically allow for better and more accurate entry. Few things are more wasteful than entering everything in the ERP system then re-entering it into a spreadsheet to appease a manager’s inability to adapt and change.)

What are some tactics for resolving these issues?

1. On a macro level, start ensuring that the data that your company is collecting is sound data as part of the preparation for a Six Sigma launch, or a part of plain old good business. Bad data slows down or stops a Six Sigma project dead in its tracks, changing it from getting something done to fixing the data.

a. Know catalog your data databases, including the extra ones (Excel, Access) that are usually relied upon but undocumented.

b. Prioritize the data sources by synchronizing them with your Six Sigma launch sequencing.

c. Sample the data to insure its usefulness. If it is bad, fix it. This will give teams better data to start off with and will allow time for that data to accumulate for analysis.

2. For specific projects, conduct a Measurement System Analysis (MSA) on you data sources (This tool is often used in the Measure phase of the DMAIC model). We often think of MSA’s when it comes to physical measurements. It is just as critical in the ‘softer’ data.

a. Pull the correct sample size. In StatTools, under Statistical Inference there is a Sample Size Selection tool that can be used to pull the correct amount of data needed for the analysis.

b. Pull your data randomly and follow the trail to the actual entry point. That may mean watching how individuals enter data, probing for special circumstances, etc.

c. In your analysis, look for random factors such as vacation fill-ins. Both Eric and I both had several experiences where one person was filling in for someone who is out sick or on vacation and, usually do to inadequate training, varies from the expected process.

3. Pivot Tables are our friends. Start today upgrading the skill sets of the people that do the actual data entry and first level analysis. Train them in how to use tools like Picot Tables that slice the data but leave the actual spreadsheet intact. The fewer merged cells, etc. that we fight with, the better.

4. Managers – Trust your Belt. If they say the data is bad, it probably is. No matter how much you want an answer today, you may not be able to get one. The good news is that some processes can be modeled using @RISK to begin improvement that is directionally correct while waiting for the data to compile. Then the better data can be used to either update or replace the early model.

5. Go hunting. Find extraneous datasets and merge them / kill them. The fewer that are out there, the more likely you will be able to ensure the integrity of those that remain.

Remember that data analysis is a funnel. Tons of data leads to bunches of information which then can help us make some decisions. Throwing bad data into the system is similar to throwing bad tomatoes into the food distribution system. The end results can be pretty messy and difficult to clean up.

Also, don’t miss Ed Biernat’s free live webcast DMAIC and Using a Non-Intuition Approach, Thursday, 11AM Eastern Time.

https://palisade.webex.com/palisade/onstage/g.php?d=719996370&t=a

BIO:

Edward Biernat is the president of Consulting With Impact, Ltd., a training, coaching, and consultancy located in Canandaigua, NY that he founded in 1998.

Another take on the BP Oil Spill

Friday, May 28, 2010 by Steve Hunt

We are pleased to introduce you to consultant and trainer Sandi Claudell, today’s featured guest blogger. Sandi is CEO of MindSpring Coaching, and has been a valued Palisade Six Sigma Partner for quite some time. She is a Six Sigma Master Black Belt (Motorola), and is a Lean Master (Toyota Motors - Japan) among other notable achievements.

--Steve Hunt

Part 1: The Platform Disaster

Much has been said about the disastrous BP oil spill in New Orleans. If we use the theory of probability and reliability then have too many different companies responsible for a very complex construction and operation added to the chance of failure.

There is probably a cultural issue at work where each entity wanted to give the other what they wanted to hear rather than the truth. (For historic and recent examples: NASA Challenger and recent Toyota Prius problems). When we lose sight of quality and reliability of parts, construction, maintenance, testing under ALL conditions rather than the obvious few, etc. then we run high risks of failure. When you build 100+ wells and avoided disasters . . . perhaps people fool themselves into thinking there never WILL be a disaster. They don’t look at a model that demonstrates the longer you go without such an event (given the input factors of how each element can and will fail) the closer you come to the event we all want to avoid.

They may or may not have used an integrated Systems Design . . . not simply an engineering system but the system on how individuals work together, communicate with each other, act as a conforming unit or a more self-directed autonomous unit looking for and generating solutions outside the box. A team that is innovative and willing to look at all the possibilities and create a breakthrough design that was / is more mistake proof.

If they had used DFSS (Design for Six Sigma) then their designs would be more robust taking into consideration all the necessary safety precautions for human life as well as immediate response to a potential failure. As part of DFSS we use a statistical tool call Design of Experiments (Strategy of Formulations, Central Composites, etc.) where we can try very complex interactions (factors) with minimal effort / cost and maximum statistical accuracy. DoE creates prediction equations that allow us to model and ask questions of what would happen under different conditions. More importantly we can look at many different quality metrics (responses, outcomes, etc.) with the same experimental trial. If we replicate the test then we can even forecast what elements cause variation (very hard to detect in highly complex systems without the use of statistics).

If they had used an FMEA (Failure Mode Effect Analysis . . . a tool used in Six Sigma) then they could have anticipated failures and put error proofing devices in place to detect and/or respond to potential faults BEFORE it is irreversible. If we add a Monte Carlo simulation to potential working conditions then the model forecasts probability plots and identifies key factors that will be critical to success or failure.

Perhaps they did indeed use a Monte Carlo using Crystal Ball. It is a good product but if they used Palisade’s @RISK and added some of the other tools provided by Palisade such as RISK Optimizer, Neural Tools, etc. then they could have analyzed the system in other dimensions besides a simple Monte Carlo, thus uncovering weaknesses BEFORE designing and/or building the platform and well.

Part 2: Capping the well head

In Lean there is a whole discipline called “Error Proofing Devices”. As part of the design effort we need to create first and foremost safety and other devices that prevent the error from occurring in the first place. If that line of defense fails then there should be devices built into the process designed to cap the well if your error proofing fails. If that line of defense fails then there should be a disaster response plan created and practiced and tested to ensure that the spill is repaired immediately.

Part 3: Treating the resulting spill

Again, Design of Experiments could test different materials, chemicals and methods to find the right combination to contain or otherwise manage the resulting oil spill. Trying one chemical only may be the age old definition of madness . . . trying the same thing over and over again expecting different results. Again, a robust design of experiments could aid in the process of finding a solution that is most effective and with multiple tests on the same samples ensure that is it the most safe for the environment and the population most directly in the path of the oil spill. These tests are ideally run years before such a spill however, doing something now is better than simply standing by and watching it happen.

Last but not least:

Management (Executives down to line managers) should have coaches. Coaches who can speak to the culture, the systems design, the tools and methods used in Lean Six Sigma and who can verify data analysis and help with the accurate interpretation of the data. These coaches should be independent . . . not a full time employee of the corporation as they are more likely to speak the truth and highlight risks as well as opportunities.

Now BP and all the other entities may have done some of what I mentioned above. But I would assume they must have left out one or more of the listed items or we wouldn’t be looking at the oil traveling into the wetlands around New Orleans right now. Hindsight is always brilliant but we can learn from our mistakes. We can create better cultures, systems, error proofing devices, Experimental Designs etc.

BIO:

Sandi Claudell is CEO of MindSpring Coaching. She is a Master Black Belt in Six Sigma, a Lean Master and has worked as a consultant for many companies to initiate worldwide improvements. For more information or to contact Sandi please visit http://www.mindspringcoaching.com/.

Statistical Gizmos and the UK Election

Thursday, May 20, 2010 by Holly Bailey

The recent elections in the United Kingdom provided a really fun opportunity to see how extensively statistical decision evaluation and predictive modeling have penetrated popular culture. The British press outdid themselves with online graphical gizmos that allowed readers to set the terms for outcome scenarios and let those spin out in true operations management style.

While The BBC offered an election seat calculator that really only translated voting percentages to number of Parliament seats won, the Guardian put up a Three-Way Swingometer. With about 8, depending on which you count, parties in the fray, the Swingometer allowed readers to twiddle a dial to anticipate the effect of hypothetical party-shifting and election results.

Next, Nate Silver, the election forecasting guru behind the FiveThirtyEight.com website, produced what he calls the Advanced Swingometer to offset the statistical disarray introduced by the original version's assumption of a uniform rate of "swing." He backed this up with a demonstration of how elegant the statistical analysis behind his model was.

The Times came forward with a predictive map based on the predictions of gamblers in UK's lively betting shop scene. Who know where those risk assessments came from.

None of the online descriptions of the methods behind the gizmos were very detailed. There were no mentions of named statistical analysis procedures, and this turns out to have been a good thing--because none of the gambits proved up to foreseeing the muddle that resulted from the actual voting. If you wanted to try to come to a clear view of that, you will need to consult the decision tree posted by the BBC.

Oops! Didn’t see that coming!

Wednesday, May 12, 2010 by Steve Hunt

We are pleased to introduce you to consultant and trainer David Roy, our first guest blogger in my blog. Dave comes to us from SSPI, Six Sigma Professionals, Inc., and taught Jack Welch and his entire staff their Six Sigma Green Belt training. David’s blog will be the first in a series, and this initial entry also has a quick survey at the end for your input on structuring DFSS training.

--Steve Hunt

Oops! Didn’t see that coming!

How often do we hear these words after we have made a change to product, service or process?

We frequently solve one problem only to discover a new problem; or the solution we selected didn’t really resolve the problem.

There are many reasons for these surprises. Problem Solving sometimes addresses the symptoms and not the root cause. Useful solutions often have compromising harmful effects that we did not consider.

You may now be thinking; “Wow, if everything we do is going to turn out bad let’s not change anything.” The reality is that change is inevitable. Whether driven by rising customer expectations, innovative new technologies or even variation in inputs over time; change will occur.

Managing the design and implementation of these changes requires a more formal methodology than the prominent “Launch and Learn” method.

The sophistication of the methodology will vary depending on the magnitude of the risks associated with the change. If we are problem solving for variation in a standard process and trying to regain control simple tools such as Cause and Effect diagram and Failure Mode Effects Analysis and Standard Work may be all that is required.

When we start to explore reducing variation or introducing new technologies or process then we need to bring on a Design For Six Sigma (DFSS) methodology which incorporates elements such as Change Management, Robust Design, Reliability, Modeling & Simulation and Piloting & Prototyping.

Over the next 4 blogs we will cover the four phases of a DFSS project under the framework of I-dentify, C-onceptualize, O-ptimize, and V-erify or ICOV for short.

We will give a high level look at the steps within these phases and the tools used to reduce the risk of the change and un-intended consequences.

On another note, if you are able, we’d like to ask for your guidance by completing a short marketing survey to help SSPI structure our training in a way that is most useful to our community. This 8 question survey should take less than 5 minutes, and is anonymous. Your opinions are greatly appreciated.

http://www.surveymonkey.com/s.aspx?sm=2aQk8QF1eLB5MFQJC1pUXA_3d_3d.

BIO:

David Roy is an integral part of the Six Sigma community. He taught GE’s Jack Welch and entire staff Six Sigma, as well as served as Senior Vice President of Textron Six Sigma. He is a Certified GE Master Black Belt, was instrumental in developing GE’s DMADV (DFSS) methodology, and has taught 3 waves of DFSS Black Belts. Dave’s experience includes Product and Transactional so his examples are of interest to all. David holds an BS in Mechanical Engineering from The University of New Hampshire. He is also the co-author “Services Design for Six Sigma – A Roadmap for Excellence”

Robust Risk Analysis for the Time/Expertise Poor – Part 2

Thursday, April 15, 2010 by DMUU Training Team

In my last blog I introduced the idea of a customised risk analysis solution to problems commonly faced in project risk management, especially cost estimation. Of course this idea is not uniquely applicable to project costs, but this paradigm is the simplest to explore, and that’s what I’m about to do.

Picture a risk register in a worksheet that has been created at a macro level to encapsulate most (all?) of the risks your projects may face. For any given project only a subset of these will be relevant – what is the best way to get these risks into a risk model on the next worksheet? By pressing a button of course! It is almost trivial to write code that picks up all selected risks and places them and the relevant data fields in the model worksheet. Sure beats manually copying and pasting individual line items and the transcribing errors that follow.

The next problem is utilising the workshopped parameters (likelihood of event, three-point estimates for severity etc.) in a logical way to be referenced by appropriate @RISK functions. Once a model structure has been agreed upon a macro button can place @RISK distributions where they ought to go, either logically due to the paradigm (using RiskBinomial, for example) or via a drop-down selection for dollar impact (RiskPert or RiskGamma, say). My clients have been especially thankful when I limit their choice of distribution and provide a simple flow-chart to follow to make this very decision. Reducing the propensity for arguments in risk workshops is worth its weight in gold; if we can assume that reducing this risk ‘weighs’ plenty!

Similarly one or two instances of the simulation settings are likely to satisfy all requirements, so these too can be activated by macro buttons. In this way a user can’t run a ‘poor’ simulation thus creating spurious results. The simulation output that is required can be placed into a report template attached to the model template and generated using yet another simply-labelled macro button. In this way there will be consistent reporting across the organisation allowing decision makers to become familiar and comfortable with simulation results they might otherwise ignore or be unaware of.

A risk model created by this process may not be the theoretically optimal one, but it will be valid and in context with its intended use. It will certainly be easy to use! The results will be consistent and should satisfy management’s desires as well as regulatory requirements.
The project cost estimation is but one example, and the above possibilities are far from the only ones imaginable. Additional complexity or alternate needs would be just as easily met simply with different code essentially without any practical limits. You don’t need to be an expert in Monte Carlo techniques and software to run robust, credible risk analyses. All you need is a risk analysis consultant who macro-controls the cumbersome and probabilistic elements, some appropriate simulation options and reporting procedures. Ask for me by name!

» Robust Risk Analysis for the Time/Expertise Poor – Part 1

Rishi Prabhakar
Trainer/Consultant

Robust Risk Analysis for the Time/Expertise Poor – Part 1

Tuesday, April 13, 2010 by DMUU Training Team

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

Profitability Projections in a Manufacturing Environment of High Uncertainty

Monday, April 12, 2010 by Steve Hunt

The other night, I had the opportunity to watch a free webcast titled “Use of @RISK for Probabilistic Decision Analysis of a Manufacturing Forecast in an Environment of High Uncertainty”. This presentation was extremely timely, since many companies are struggling to survive in these challenging economic times. Dr. Jose Briones did an excellent job discussing and illustrating how profitability projections in a manufacturing environment are directly tied to how the sales forecast fits with the capability of the operation, and how different manufacturing capacities and productions rates impact the output of the plant and the allocation of the fixed cost of production.

In the example he presents, a company is trying to decide how best to balance the sales of certain families of products to maximize revenue, maintain a diverse product line, and properly price each individual product based on the impact to the manufacturing schedule and fixed cost allocation.

He spends an appropriate amount of time discussing different input distributions such as the Triangular, Normal, Pert and Gamma distributions as well as sharing his recommendations on when to use them. He also shares his expertise on fixed cost allocation by product and the dangers in using the common method of dividing the fixed cost by the total production, and recommends doing so by allocating the fixed costs based on the projected run time of each product family. Lastly, he spends some time discussing the interpretation of the results, which I feel does a great job wrapping up the information presented in the webcast.

Dr. Jose A. Briones is currently the Director of Operations for SpyroTek Performance Solutions, a diversified supplier of specialty materials, BPM software and innovation consulting services. Dr. Briones has a PhD in Chemical Engineering from Clemson University and is a graduate of the Business Administration Program of Wharton Business School. If you have any questions about the webcast, you can contact Jose at Brioneja@SpyroTek.com or through Jameson Romeo-Hall at Palisade Corporation.

Cost-Benefit Analysis in the Land of Buzz

Friday, April 9, 2010 by Holly Bailey

For the past couple of years, I've been following the advance of cloud computing into the marketplace. Recently, as the Cloud has begun to--I can't say materialize as that might confer some notion of definable substance, which in this line of business is to be avoided at all costs--become a presence, information officers have been increasingly interested in matters of costs and benefits. Those who are considering migrating their current computing operations to the Cloud would like to make risk assessments that weigh CAPEX--capital expense--against OPEX--operating expense--and for that they will need help calculating the TCO--the Total Cost of Ownership. To forecast the TCO, they will need to get out the Monte Carlo software to predict their potential flow of data out through the Cloud, and depending on a company's familiarity with risk analysis, this "could = hire a consultant who understands the meaning of all this."

Recently, to help clarify matters, a Computerworld blogger declared, "The fact that people are so interested in cloud TCO indicates that the general value proposition of cloud computing has been accepted and absorbed." The need for this incisive commentary he blames on the fact that "there's been an amazing amount of FUD"--Fear, Uncertainty, and Doubt--"strewn about on the topic of cloud TCO."

My problem with this discussion, as you've probably gathered, is not the efforts of smart people to grapple with the opportunities and operations management issues raised by Internet-based computing. It's the FUD that folks in computing seem to experience when it comes to clear, plain labels. They flee into the land of buzz in order to assure TO--Total Ownership--of the terms.

For starters, take the term Cloud for Internet. It all gets just a little too. . . .well, vaporous. It makes me feel like the grandmother of a man being ceremoniously installed as a dean at Cornell University a while back. Having survived into her nineties and through the morning's pomp and circumstance, she asked her grandson what exactly he would be doing in this new job, and as he started to explain, she looked as if something tasted bad. Finally she broke in. "Honey," she said, "if you can't say it in one sentence, it has got to be illegal."

The Paradox of Knowledge

Wednesday, April 7, 2010 by DMUU Training Team

Modeling from empirical data takes observed information and attempts to replicate that information in a set of calculations. There are a number of relationships to account for when incorporating those data in a model. These relationships include dependencies and/or correlations. Correlations are often omitted for a variety of reasons, which can lead to critical errors in your results. Some knowledge of the situation leads to a more credible representation of the relationships in the data. Added knowledge, perhaps from subject matter experts, or other sources, aids the refinement of the conclusions one can draw from the data. Whether the correlations are direct or aggregate, involving simple mathematics or greater complexities, ultimately the model is likely to be used in some form of analysis for projecting future outcomes. The knowledge brought to the model and the analysis with embedded correlations improves knowledge about inherent uncertainty in a given problem.

Correlation is a principal relational element which describes relationships between variables in datasets. There may be general tendencies and patterns which drive the input risks to move together or differently from each other. It is these relationships between variables which need to be expressed in a model to bolster its usefulness, which is accomplished with correlation. It is important to remember there may be observed correlation between variables but it is not necessarily a causal relationship; it may be only a general tendency of paired behavior.

One significant aspect to note: positive correlations appear to increase uncertainty. Wait, you say, how is that possible? Knowledge is supposed to reduce uncertainty. Doesn’t knowing counteract unknowing? Think about it for a moment. In effect, the correlations included in the model reduce the uncertainty about reality while increasing the range of predicted values, adding uncertainty. What may seem illogical on the outset really is quite logical. If two (or more) risks are positively correlated, their aggregation will produce a larger range as a consequence of Monte Carlo sampling. In fact, failing to account for correlations that really are there reduces the validity of the analysis.

Correlations are easily incorporated in models set up for Monte Carlo simulation. MCS, as a technique, generates many ‘random’ samples allowing the modeler to study a variety of scenarios and their impact on decisions. A correlation matrix defines the sampling relationship between any pair of input variables in the model. Using a tool such as Palisade’s @RISK facilitates matrix construction. Once the correlations are in place, running the MCS will produce results and scenarios that are more credible. We want decisions to be based on the best information available and the correlations lend a hand to the knowledge we already incorporate into the process.

Thompson Terry
Senior Training Consultant

Making Optimal Choices, or Just Making Choices? Part 4

Tuesday, March 30, 2010 by DMUU Training Team

It has taken four entries but I’ll finish this blog stream now with a discussion on optimisation optimisation. That’s not a typo. It’s an art form that is analogous to elegant modelling, as opposed to ‘just’ modelling. The tipping competition model not only opened my eyes to the world of optimisation but also that not all models are created equally, even if they are numerically equivalent.

A few rounds into the season we were allowed to buy and sell riders, which was great if you’d bought some duds at the start! But from a modelling point of view the complexity had increased exponentially. There was now a time component to the model structure as well as different prices for the riders based on their performance to date. My first attempt to model this was quite cumbersome with dozens of 0/1 decision cells to indicate buying riders at the start of the season and then the buying and selling of riders after four rounds. While mathematically correct I wasn’t doing Evolver any favours by having such complex dependencies between so many decision cells. The optimisation was taking far too long to converge, so much so that the final solution when the optimisation was stopped after what I considered to be a reasonable length of time was greatly impacted by the initial solution.

Now this of course isn’t usually the way things work with Evolver; as a sophisticated genetic algorithm optimiser the global optimal solution should be found regardless of the initial conditions. However the time taken to get to such a solution can be extended greatly if optimising an unnecessarily convoluted model. After initially blaming the software I realised I could simplify the model by turning two decisions (“buy” and “sell”) into one (“change status”). This immediately removed one third of the decision variables and straight away the optimisation converged quickly to a global optimal solution regardless of the initial feasible solution. Evolver can only work with the model you give it!

The act of optimising the optimisation can be a subtly tricky one, but is necessary if you are to have confidence in your optimised solutions and thus the decisions made. Building a model that ‘works’ is only the first piece to the puzzle. If the solutions aren’t stable then can you really be sure you’re producing the best answer? No. And if the model is being used to decide which of the multi-million dollar projects you will proceed with (or some other equally critical decision) I’m sure you’d want to have some certainty around the answer provided by Evolver. Of course if you’d like some help with an optimisation model from experienced risk analysis consultants feel free to contact the consulting team at Palisade!

» Making Optimal Choices, Part 1
» Making Optimal Choices, Part 2
» Making Optimal Choices, Part 3

Rishi Prabhakar
Trainer/Consultant

Making Optimal Choices, or Just Making Choices? Part 3

Friday, March 26, 2010 by DMUU Training Team

Part 2 of this blog ended with me very quickly stating that the MotoGP tipping comp optimiser was identical in structure to a portfolio optimisation problem, where the portfolio could contain stock or other assets, or even projects. Let’s look at this in a little more detail as I’m sure you’re reading this to find how to optimise your own decisions rather than wondering how I went in the tipping competition!

In my model there was a fixed budget (though less could be spent if desired) to spend on riders, with the aim of maximising their total points haul. In the real world you may have a total budget of say $100m to invest in a range of projects perhaps many hundreds of millions of dollars in total value each of which have certain expected returns. At its simplest this decision evaluation will find the most (expected) profitable portfolio of the projects. This is an inclusion/exclusion grouping model, but it is very simple to optimise assets with a continuous level e.g. the amount of money invested in various shares etc. Another real example I have seen when working with an investment company here in Australia was a model whose goal was simply to find the portfolio mix that came closest to the total allowable spend without exceeding it.

Further realism can be included by using constraints should there be the need. A resource constraint may mean there has to be a limit to the number of projects that can be run simultaneously. There may also be a minimum number of projects determined by management as a mitigation strategy. Such constraints are very simple to employ using Evolver and add value to the decision analysis without the need to provide specific risk analysis/Monte Carlo simulation information for the model.

A slightly more sophisticated method of turning an optimisation into a useful portfolio risk management tool where uncertainty hasn’t been specifically modelled is to estimate the possible downside of each asset and include it in the calculation of the portfolio’s ‘score’. The Evolver software comes standard with over twenty example spreadsheets for your educational pleasure, of which “Portfolio Mix.xls” gives one method for doing just this.
In the next (and final) instalment of the Making Optimal Choices blog I will explore the idea that not all optimisations no matter how mathematically correct will produce the same results in good time, and that elegant modelling should always be the goal prior to firing up Evolver.

And so you know, I came second in the competition. Next year I’m hoping to go one better!

» Making Optimal Choices, Part 1
» Making Optimal Choices, Part 2

Rishi Prabhakar
Trainer/Consultant

New Approaches to Risk and Decision Analysis

Wednesday, March 17, 2010 by DMUU Training Team

Risk analysis and decision-making tools are relevant to most organisations, in most industries around the world. This is demonstrated by the speaker line-up at this year's European User Conference, an event at which we believe it is important to bring together customers from a wide range of market sectors.

We are holding 'New Approaches to Risk and Decision Analysis' at the Institute of Directors in central London on 14th and 15th April 2010. As with previous years, the programme aims to provide everyone attending with practical advice to enhance the decision-making capabilities of their organisation. Customer presentations, which offer insight into a wide variety of business applications of risk and decision analysis, include:

CapGemini: Faldo's folly or Monty's Carlo – The Ryder Cup and Monte Carlo simulation
DTU Transport: New approaches to transport project assessment; reference scenario forecasting and quantitative risk analysis
Georg-August University Research: Benefits from weather derivatives in agriculture: a portfolio optimisation using RISKOptimizer
Graz University of Technology: Calculation of construction costs for building projects – application of the Monte Carlo method
Halcrow: Risk-based water distribution rehabilitation planning – impact modelling and estimation
Pricewaterhouse Coopers: PricewaterhouseCoopers and Palisade: an overview
Noven: Use of Monte Carlo simulations for risk management in pharmaceuticals
SLR Consulting: Risk sharing in waste management projects - @RISK and sensitivity analysis
Statoil: Put more science into cost risk analysis
Unilever: Succeeding in DecisionTools Suite 5 rollout – Unilever's story

We will also look at the recently-launched language versions of @RISK and DecisionTools Suite, which are now available in French, German, Spanish, Portuguese and Japanese. Software training sessions will provide delegates with practical knowledge to ensure they can optimise their use of the tools and implement business best practise and methodologies.

With over 100 delegates from around the world attending, the event is also a good opportunity to network and knowledge-share with risk professionals from around the world.

» Complete programme schedule, more information on each presentation,
and registration details

Making Optimal Choices, or Just Making Choices? Part 1

Tuesday, March 16, 2010 by DMUU Training Team

Something has troubled me for some time regarding the choices being made in risk land. I train and work with many clients whom have adopted Monte Carlo simulation techniques (via @RISK for Excel) into the day-to-day running of their businesses. By doing so they (hopefully) now have a good understanding of the exposure they are facing be it in project cost estimation, discounted cash flow analysis or, well, anything really. But this is only one facet of risk and decision assessment, specifically dealing with the descriptive statistical output from a simulation. What of the decision evaluation component? Why aren’t more of my customers analysing the decisions they make, or better yet actually optimising them? I have a few ideas why.

If you’re in business you have to make decisions. Big ones, little ones, yes/no, multiple state and continuous value decisions. Decisions that impact other decisions in simple or complex dependency structures. But are you making the best decisions possible? I’m sure important decisions aren’t being made completely randomly (I hope!) but I see many companies who rely completely upon qualitative techniques for their decision making (experience, gut feel, etc.) which of course means optimality is no more than a hoped for outcome rather than something that is actually being worked towards.
Firstly the decision model must be identified and then quantified, and this can be a difficult task. There is a level of modelling aptitude necessary for effective modelling that goes beyond merely knowing Excel and its functions, and into the construction of logical mathematical descriptions of possibly complicated processes. Relevant decisions need to be identified and the impact of those decisions combined into a formula that can be mathematically optimised. A critical component to all this is the knowledge that spreadsheet models can actually be optimised, and that in cases where Excel’s Solver fails there are Palisade products (Evolver and RISKOptimzer) that can perform optimisations under virtually any circumstance.

I too used to focus on Monte Carlo simulation rather than decision evaluation, and this was mainly a product of the clients I was dealing with almost exclusively when I first worked for Palisade. In my next blog I’ll tell you why that changed and also get a little more into the nuts and bolts of optimisation.

Rishi Prabhakar
Trainer/Consultant

Quantitative risk assessment under utilised for infrastructure projects

Friday, March 12, 2010 by DMUU Training Team

Why is it that most of the high profile projects managed by the government in the UK all ultimately become beset by problems? A number of projects jump to mind – the Millennium Dome, Wembley Stadium and currently the NHS IT. All three have been plagued by developmental delays and financial mismanagement.

Recently, yet another worthy, but ambitious project has been announced – the North-South high speed rail line to connect London to Scotland. One wonders if the government undertakes detailed quantitative project risk analysis for its infrastructure initiatives?

A good example to highlight in this context is ENGCOMP, a Saskatchewan-based engineering consulting firm that has worked with the Canadian Department of National Defence (DND) to help define budgets for the fourth phase of construction of its Fleet Maintenance Facility at Canadian Forces Base Esquimalt in Victoria, British Columbia. Using @RISK, a Monte Carlo simulation tool, ENGCOMP helped the DND define and secure budget approval from the Federal Government’s Treasury Board. The consultancy firm was able to estimate the impact of the variability and uncertainties pertaining to risks, costs and scheduling. This assessment enabled it to estimate the project risk budget or the risk reserve and schedule contingency, which were both factored in when defining the total project cost of the infrastructure project.

The fact is, in the world of business, risk is inherent and unavoidable. Whilst one cannot completely control risk, one can certainly help reduce uncertainty, greatly increasing the chances of project success. For instance, a key finding of the project risk analysis conducted by ENGCOMP was that, taking into account all the risk and uncertainties on the project, there is an 85 per cent chance that the Fleet Maintenance Facility project will be completed in January 2014. A fairly positive result for the DND, given the scale and complexity of this project in question.

Craig Ferri
EMEA Managing Director of Risk & Decision Analysis

What Should You Get From a Simulation? Part 3

Tuesday, March 9, 2010 by DMUU Training Team

In the last two blogs I have challenged the idea that simulation results can be boiled down to a single statistic with any positive benefit. The context of a statistic is incredibly important, which is another reason why many statistics and charts/tables should be reported on, not simply one figure. And here’s a compelling reason why.

Consider two competing, similarly-sized projects, of which a company can only pursue one. Now let’s say this company would like to take on the project that has the “least risk”. If they are only familiar with generating the P90 for the total project cost they will be forced to select the project with the lowest P90. But what if the key drivers for exceeding the P90 are easier to mitigate in one project compared to the other? Perhaps the project with the lower P90 also has a higher P95 or P99 – this means the catastrophic failure is actually greater despite a lower P90 and is the mathematical equivalent of “when things go bad, they go really bad”. Not all P90s are created equally! Such an adverse outcome might sink a smaller company where a larger one could wear the loss. The context of the company running the analysis also impacts the context of the analysis itself.

So you can see not only do simulations generate results with which informed decisions can only be made if approached holistically, but if the language used is restrictive this outcome will never be achieved. Risk analyses are a necessary part of business because most of us wish to minimise the chance that something bad will happen, quite simply. Even if a manager tells you they “want the P90” what they are really asking is “tell me about the risk we’re facing”. The answer to this fundamental question is not found in a single figure taken from a simulation, but in a range of charts and tables which require correct interpretation.

More so, Monte Carlo simulation itself is only one piece of the risk and decision assessment pie. Decision modelling and optimisation, predictive modelling and statistical analyses should also form part of the quantitative approach to uncertainty. There is life beyond just risk simulation software, and I intend on exploring that in future blogs.

» Part 1
» Part 2

Rishi Prabhakar
Trainer/Consultant

What Should You Get From a Simulation? Part 2

Wednesday, March 3, 2010 by DMUU Training Team

Where I left off last time was lamenting the use of Monte Carlo simulation to create a single value (statistic etc.) from a model. It might still not be clear why this is anathema to me, so here goes.

A simulation is not a number. It’s not one possible (future) outcome – that’s a scenario. Monte Carlo simulation is a methodology for understanding one’s exposure to outcomes not situated close to the central tendency of the process/project in question. Note the plural “outcomes”. Risk analysis, when done properly, should let you know essentially all possible outcomes and how likely they are for your model. Output from a simulation can include a plot of means (over time), or P5s, or P95s, or the mean ± one standard deviation or any number of statistics. But that’s not plotting a simulation! Let’s not give a minimalist graph too much credit.

Such statements also perpetuate the idea that simulation is only used for creating means (or other centrally tending statistics) and ignores the wealth of information available. Risk simulation software exists to help you do risk analysis which must include not only several statistics but also sensitivity information. It is all too easy to turn a risk assessment into a hunt for a regularly asked for percentile (such as the P90) and there ends the task. I see this a lot, especially in project cost estimation where the pressure both from management and regulatory bodies is to accurately estimate some large percentile. Once found there is usually scant further risk analysis.

Nothing good ensues. When risk analyses are run “to get ‘the’ number” they become simply another box to tick in a process and ultimately any benefits (perceived or actual) will be forgotten and lost to the ages. The notion of context is also lost. No single number by itself really means anything, or at least shouldn’t mean anything to a decision maker. I have often heard phrases like “the model returned/gave $1.2m” followed by an audience nodding in agreement. Huh? Which statistic are you talking about there, and how about reporting a few other numbers around it to place that $1.2m somewhere meaningful?

In the next installment I will look further into this issue of context and hopefully prove the necessity of an holistic approach to understanding and reporting simulation results.

» Part 1

Rishi Prabhakar
Trainer/Consultant

Pensions – The Ticking Time Bomb

Monday, March 1, 2010 by DMUU Training Team

Both the Conservative Party and the Labour Government have indicated that they will raise the state pensions age of men and women to help reduce the UK’s national debt. In addition, more and more employers in the private sector are closing good pension schemes. The Association of Consulting Actuaries’ (ACA) recent survey on pension trends has revealed that 59% of employers are set to review pensions ahead of 2012 and 24% of employers will consider pension benefit reductions when they have to auto-enroll all employees into a scheme.

With taxes on business and individuals likely to rise over the next few years, it is difficult to see anything other than a deteriorating climate for pension savings unless there is a radical change of approach, says the ACA. It has proposed a standing Pension Commission that will challenge the legal and regulatory hurdles standing in the way of sensible long-term pension designs.

Perhaps, a more in-depth risk analysis may help the ACA make a stronger case to the government. As a related example, in the US, the Society of Actuaries and the Casualty Actuary Society, sponsored a research project with the Illinois State University to develop a model for projecting economic indices such as interest rates, equity price levels, inflation rates, unemployment rates, and real estate price levels. The model was created using Palisade’s @RISK and Microsoft Excel. In fact, @RISK’s built-in probability distribution functions, correlation matrices, and simulation results were essential to the study.

The UK ‘pensions’ landscape is set to undergo tremendous change, which will impact each and every one of us. Using scientific, risk analysis techniques, actuarial industry bodies can develop a strong argument and lobby the government so that informed policy decisions are made that are right for both the financial health of the nation and its citizens.

Craig Ferri
EMEA Managing Director of Risk & Decision Analysis

What Should You Get From a Simulation? Part 1

Thursday, February 25, 2010 by DMUU Training Team

I read an interesting article on the causes of the Global Financial Crisis by John B. Taylor. Although the topic is interesting enough already, especially for a member of a risk analysis-specialising company, something else caught my eye. I have observed in training workshops, onsite consulting and now academic papers a phenomenon regarding probabilistic modelling. Many of those using the methods don’t understand what they should actually be getting from the methodology. There is an intellectual leap from the deterministic to the probabilistic that sometimes does not get made. This limits the usefulness of Monte Carlo simulation, and the value of performing such statistical analyses.

Back to the article which spurred me to write this blog in the first place. Or rather, the graph. Yes a single graph of housing starts vs. time (and its brief description) leapt out at me. One of the lines on the graph was claimed to show model simulations of housing starts using the actual interest rate, compared to the interest rate ‘predicted’ by the Taylor Rule and a third line showing actual data.

So what’s the problem?

The problem is that simulation techniques should not be used to create a single value. The single ‘simulation’ line implies a single modelled/returned value for each time period. This is deterministic modelling. There may be a particular scenario that has been modelled, but it certainly isn’t a simulation that is being represented by that single line. Simulations produce thousands of data, observed values and their associated percentiles as well central moments (mean, variance etc.). Not just one value (sorry Value at Risk – that includes you too) that can be plotted as a single line. I would guess that if a simulation were run as I understand the term then the line in the chart was probably constructed using the simulated means. But I shouldn’t be guessing.

This is far from the only time I’ve seen simulation results reduced to a single entity. I have heard from clients in the past “the simulation gave $X” with little to no context around it, and this is supposed to both mean something to me and to their customers and help to make better decisions under uncertainty…

In the next blog I will explore this idea further and discuss the sorts of results that should be gleaned from a simulation. In particular, why narrowing simulation results down to a single number is counterproductive to healthy business practices.

Rishi Prabhakar
Trainer/Consultant