The phrase “Garbage in, garbage out” is often used to express concern about the validity of a model or its input parameters. Whilst one should pay attention to populate model inputs with sensible (perhaps “best guess”) values, what is less appreciated, is that the output case of a model will generally not correspond to the input case. Thus a model whose inputs are at their most likely values will not generally show the most likely values for the calculations. This fact is a key reason to conduct advanced sensitivity analysis and risk modelling on a model. [Read more…] about Garbage In, Garbage Out. Most Likely Values in …What Comes Out?
Archives for 2012
In principle it is easy to integrate an Excel macro (VBA) into an @RISK simulation. For example, most typically, once the distributions have been sampled, a macro needs to be run at each iteration (with the sampled values fixed during the macro run). However, there are some subtleties to bear in mind.
Many @RISK models for cost budgets and portfolios of projects are often structured as datasets, and the Excel Data/Filter options are used to provide a drop-down menu so that sub categories of items can be shown. During a recent consulting engagement, I discovered that the @RISK simulation only calculated correctly if the filters are removed before the simulation is run. [Read more…] about Using @RISK with Filters Active
In this blog, I post a few ideas that sometimes help when trying to speed up an @RISK simulation. In fact, most of the simulation speed is determined by the structure, size and logic of your Excel model. Some of it is through the simulation settings as well. Here are a few ideas that you could try … [Read more…] about Speeding up an @RISK Simulation
When working with a database or dataset in Excel, often one wants to query the data set for values that are within a specific range. For example, what is the total amount outstanding for invoices that are between 30 and 60 days overdue? Sometimes this is implemented as two separate queries and the difference taken between them. However, it can in fact be implemented as a single query.
To implement as a single query when using the SUMIFS function, one simply tests the same criteria range twice within the same function evaluation i.e. summing the amounts overdue by testing within the “overdue range” when the criteria >30 is met, and then (as an additional argument to the same function) testing again within the “overdue range” whether the criteria <=60 is met. [Read more…] about Implementing a Between Query with SUMIFS or DSUM
Tornado charts are an appealing visual display for the results of a Monte Carlo simulation model. Nevertheless, they are difficult to correctly interpret and communicate. In particular, the idea that a tornado chart shows you which risk to prioritise for mitigation is potentially flawed. [Read more…] about Tornado or Not Tornado: What was the Question?
It turns out that the Contact Us form on the web-site has not been working for some time due to a mistake by the web administrators. Whereas the email@example.com e-mail address has worked fine, if you have tried to contact me through the contact form in recent times, then there is a good chance that the message did not get through (100% certain if you expected a reply from me, which you did not get). [Read more…] about Contact Errors – My Apologies
In many practical situations, the regression and correlation coefficients generated in @RISK are more or less similar to each other. However, in other cases they are quite different, and in this case one may wish to understand why that is the case. [Read more…] about Tornado Graphs in @RISK: Regression versus Correlation Coefficients
The issue of whether to the MC or LH sampling method in @RISK often causes a lot of discussion. These days, it is a topic that I tend to de-emphasise when running training courses for several reasons. [Read more…] about Monte Carlo versus Latin Hypercube Sampling in @RISK
It is often thought that a large portfolio of assets is well-diversified, in the sense that its standard deviation is proportionally smaller than that of the individual assets. This can lead to the often implicitly assumed, but incorrect, assumption that risk can be reduced to any desired level by having a sufficiently large portfolio. This blog shows that the potential diversification effect is often much less than is often appreciated. [Read more…] about Is Diversification Through Large Portfolios a Myth?
Once again, apologies to readers, due to my busy schedule!! Last week saw me running a 5 day course on financial modelling for one of the largest banks in Indonesia, covering the full range of topics from best practices through to risk modelling, taking a look at financial statements, valuation, lookup functions, risk modelling and optimisation (amongst other topics) along the way! I’ve finally got around to thinking about organising a public course on financial modelling for oil and gas, including risk modelling, decision-making and optimisation etc. This is planned for May 22-25th in London. Read here for more.