Being an academic, I am always wishing for free tools for my students and for myself to do my work and while searching for them I found a simple Monte Carlo add-on for Excel, built by colleagues from Wabash College. The tool with instructions for installation can be found here.

I wanted to test this tool and compare it (and Monte Carlo simulation in general) with the approach that the pay-off method uses in creating the pay-off distribution. Below I have included a full illustration of what I did with Excel screenshots of “everything”.

I “generated” the Excel sheet by just inventing numbers for a fictitious investment that has an uncertain discount rate, uncertain cost that happens in year zero and with revenues that accrue during five years. For the pay-off method I used three scenarios minimum possible, best guess, and maximum possible. The numbers… came out of the proverbial hat.

Costs and revenues are unrelated here – incidently this, i.e. unrelatedness, is also a requirement, when correctly using the Monte Carlo simulation that is based on randomness. This also means that minimum cost can occur with maximum revenue and vice versa. Screenshot from the Excel sheet is visible below; even more information about the calculation is included in the screenshot.

Below you will see the resulting pay-off distribution that has the top over 148 and min at -69 and max at +501.

Now with the Monte Carlo tool I used the same input information, but one major thing is different. The RAND() function that Excel uses and that is used by the MC simulator can only draw from an interval and hence will not consider the information about the best guess that we have available. Input parameters to the tool are 100000 (one hundred thousand) runs and seed 123 (in case you want to retry this illustration – the results should come out exactly the same, if you input the same seed).

With my oldish laptop it took about 30 seconds to run the simulation, this is quite acceptable for free software. I personally remember way back when running this kind of simulation took two hours. Without using the seed it runs faster.

The setup of the spreadsheet is a bit different as we are not calculating the values for the pay-off distribution for the three scenarios separately, but we are just giving the simulator “orders” to randomly generate the result for cells. For each cell of “PV cost” and “PV revenue” the Excel has been instructed to generate a random number from between the min and the max value. The “PV project” row is just the revenue minus the cost, and “NPV project” is just “PV project” cells summed up. The discount rates had also to be changed and given as integers.

[A related observation: there is also a function called RANDBETWEEN that allows for us to directly specify the interval – this however is not supported by the free simulator software].

Running this sheet (randomization) one hundred thousand times results in a pay-off distribution below – now observe that this is NOT a fuzzy number, but the Monte Carlo simulation hails from a different domain, that of the true randomness – so this is a probability distribution for the NPV.The top is above 307, min value is -8 and max is 598. We also get standard deviation information from the MC tool – standard deviation is 91.

In the two graphs below, I adjusted the scale of the MC simulation result and put the rescaled MC result simple scenario based fuzzy number result one on top of the other – now… these are NOT directly comparable, but if we look at them we can see that for 100000 trials the Monte Carlo seems to be “more to the right” than our other pay-off distribution. This is obviously caused by the difference in the information = the fact that we have the best guess scenario information available skews our triangular distribution to the left. I wonder if I should have compared a four scenario trapezoidal distribution with a “best guess core” on top… it could have been a better comparison. Right now I think I will not do it for this post…

Building the Monte Carlo model took me more time than using the simple way that is the pay-off method – I think as I am not very accustomed in using the add-on it took more than it would if I used it a lot. However, still building the spreadsheets without having to do any “programming” or using more complex formulas is faster. Once the spreadsheet is ready it is very easy to change parameters and run the simulator.

Here, in this case, with the not so sophisticated MC simulator I feel that having to give up the best guess information is such bad news that I would not use it over the simplistic triangular distribution. There are commercial of the shelf software packages available that allow us to input the best guess value and use a “triangular” input into our Monte Carlo simulator. These are most likely far better for the job, however, they cost some money and require learning to use them 😉

For a simple “quick and dirty” analysis I think that this simple experiment with the Monte Carlo simulation did not provide the kind of “bang” as the atom bomb for the construction of which it was originally built to be an aide. If we had not had the best guess scenario information the info provided by the MC would outweigh just “interval information” for sure!

Maybe you have noticed that I have tried to steer clear of any strong opinions on which of the two methods is better – IMO both are good and both have their own uses. The methods themselves are not an issue – the available information and the fit of the information to the method is the issue. Also available time to perform analysis and transparency are issues that one wants to think about.

If you want to try this yourself download here the spreadsheet file used in this illustration. Remember that you cannot run the simulations unless you have the software installed (link to the add-on here).

The golf season is open after a long winter, so this is an opportune time to voice some thoughts of how golf and forecasting (and incidentally valuation) may share some striking similarities:

Teeing off is the moment of truth, the unskilled player feels an itch as he is not exactly sure which club to choose for the distance, or where the ball will land, left or right. His cone of uncertainty, the projection of possible paths is wide and long.

The skilled player feels confident, he knows that the right club for the 146 meter par 3 is the 8-iron and that with his skill he will be able to land “close to the green”, not too much wide on either side. The cone of uncertainty is narrow and not so long.

This is actually a strikingly good analogy for comparing the unskilled and skilled business analyst; the unskilled analyst knows much less about the business or the markets and is less good at estimating the future, based on his knowledge and skill. When “the event” will take place is also less precise. The cone of uncertainty of the unskilled analyst is wide and long, while the skilled analyst knows more and is likely to be better with both, timing and accuracy.

The pro golfer’s and the diletante’s cone of uncertainty (click to enlarge)

BUT just like with golf, even the most skilled make mistakes. Many have seen golf pros shoot to the crowds, even in the majors or make some other unfathomable errors in judgment – the expectation of high accuracy make “off-the-charts” or in this case “off-the-cone” mistakes spectacular (failures). Nobody is surprised if the unskilled player makes a mistake, as it falls within the expectations. ALSO even the golf pro knows that he may end up in the bunker within the “likely area of impact” – the difference with the pro and diletante is that there is only one bunker in the pro’s likely area of impact, but there are many hazards in the diletante’s likely impact area. Also the pro knows how to get out of the bunker.

The know-how, the skill, and the experience of tens of thousands of repeated shots and played courses the pro golfer has is vested in him personally – it is “normative” – exactly like analysis skill that the expert analyst has acquired; the pro’s skill does not help the next guy’s game (unless he gives tips).

Fair enough, usually there are tips to help us! We may have tools that help us a long way – by this I mean that we may have been able to codify the pro’s knowledge in a system or within a method. The big issue is, if we fully understand the context; if the method is the 8-iron for the 146 meter par 3 tee-shot, then is it also the right method for a 460 meter par 5 tee-shot? Most likely it is not; that’s where being a real pro comes in – the selection of the right club for the shot is key in playing good golf. Horses for courses… not one club for all shots, not one rigid method for all situations!

ALSO, sometimes it makes sense to make the decision to make two “sure” shots rather than one really risky one. The expectation is better; call it staged investment if you like, anyway, we surely are in real options territory!

The organizations with the most skilled business analysts are likely to fare better, because they have a narrower cone of uncertainty (ability to analyze markets) and thus a lower level of risk – even in risky markets. We sometimes call this tacit knowledge, and surely it exists, even if it is sometimes hard to put a finger on it. Similarly a pro golfer will do better in a new course than the diletante; experience is key as long as the game rules stay the same.

The “hero golfer’s” cone of uncertainty    (click to enlarge)

Then there is the “hero golfer” who is a blind believer in his skills and choice of club (whatever the actual skill level), he is blind to the cone-of-uncertainty. There is only one outcome, that is the perfect shot, the hole in one. The hero golfer is an analogy of the analyst that gives his expectation of the future as a single number. He oozes certainty that his estimate of the event timing is spot on and there is no width in the cone, indeed his NPV is a hole in one. I don’t have the confidence of the “hero golfer”, so I like to present my expectations of the NPV as a pay-off distribution.

It seems that golf is an endless source of analogies for business, but it is good also for something else; nothing makes a man humble like golf – all mistakes are your own and you cannot blame anyone else for them. Anything that reminds us of our fallibility has got to be good – at the end of the day errare humanum est.

Mikael Collan

The figure is modified from the 11th hole overview map of Salo Golf (SaG) in Salo, Finland

Weather or more precisely the scientific study of the atmosphere – meteorology is an interdisciplinary field that seems to study anything and everything that has to do with the atmosphere. One thing that meteorologist study is the forecasting of tropical storms and hurricanes. Now, the key word “forecasting” brings us to our topic – the pay-off method and the weather. Weather forecasting in the modern sense means the collection of quantitative data about the current state of the atmosphere and the application of scientific methods and technology to predict how the atmosphere will evolve.

This actually does ring a bell, indeed if we look at the previous sentence and just replace the words “weather” and “atmosphere” with the word “markets” we get the following:

“Market(s) forecasting in the modern sense means the collection of quantitative data about the current state of the markets for a and the application of scientific methods and technology to predict how the markets will evolve.”

Now isn’t that kind of interesting… and surely enough the same kind of methods are being used for both, for weather forecasting and forecasting the (financial) markets.There are elaborate mathematical models for the forecasting of weather and the financial markets, run by supercomputers and data and inputs are collected 24/7 and automatically fed into these systems. There is a difference though: atmosphere is not a man made system, but the financial markets are.

Already during my lifetime the reliability of weather forecasts has become vastly better, these days the weather forecasts seem to be mostly right, the inaccuracy having to do with more on the timing of events (when it starts raining) rather than with the events (rain) taking place at all or not. But there are still sometimes days, when rain was forecast, but the skies shed nothing.

We can all have our own opinion about the reliability of the market forecasting (economic) models. My gut is that they are not as good in prediction as the meteorological models. This may be due to the fact that the weather is more predictable than the markets (as it is a natural phenomenon and cannot be affected by humans on the short term). I had a crack at that discussion in an editorial posted here in January – surely a slightly polemical editorial, but with the best of intentions (that pave the way…).

Well enough about that… what else is there, of interest?

I did find something “about the weather” that actually got me a bit excited, and that was finding out that the U.S. National Hurricane Center (NHC) of the National Oceanic and Atmospheric Administration (NOAA)  National Weather Service (NWS) uses what they call “forecast cones” to represent the probable track of the center of a tropical cyclone. [You just gotta love the acronyms]

These forecast cones are obviously of little use if they are presented only as numbers and especially of no use if they are presented without the context – what this means is that the good way to present the data is to visualize it, that is to “draw” the cones and preferably on a map background. Visualized they immediately tell us who should start thinking about finding their way to the basement.

Forecast cone of the tropical storm Dean Aug. 2007 from www.nhc.noaa.gov

The forecast cones are obviously the output from the forecasting (computer) systems that house the complex atmospheric models and they are updated as new information arrives. To be able to understand the accuracy of these predictions the NHS tells us that “Based on forecasts over the previous 5 years, the entire track of the tropical cyclone can be expected to remain within the cone roughly 60-70% of the time.” Link to some more info about the cone from NHC site.

I don’t know about you, but to me the cone looks similar to the “accumulation of the net present value scenarios” graph used in visualizing the intermediate results in connection with the pay-off method. We can also call this the” project NPV cone” – or “the cone of uncertainty for the investment project”. Even the “logic” of the two looks (and should look) the same: the further into the future we go, the wider apart the sides of the cone (minimum and the maximum possible outcomes) are from each other – the width of the cone grows the further into the unknown (further away in time) we try to forecast.

 Graphical presentation of the cumulative net present value, the NPV cone of a project

Now why did I get excited?

The storm forecast cone is easy to intuitively understand and it is being used to report relevant important information by a US government agency to millions of people on-line 24/7. The fact that the cone is an obviously “approved” visualization technique of the output from a mathematical computer driven atmosphere model (that most of us including me would not be able to understand at any detail without considerable study) that allows all of us to understand the output in seconds is just wonderful.

To me this is a corroboration of my thoughts about the benefits of visualizing the accumulation of the net present value (NPV) of a project in the way that the pay-off method likes to do it. It enhances the understandability of the possible “path” of the potential investment, while “honestly” showing the forecasting inaccuracy and that it grows the further into the future we try to forecast.

The moral of the editorial: Use the forecasted NPV cone visually to enhance understandability of the future of the investment and to support decision-making.

Mikael Collan

In the most recent issue ( Jan. 30, 2012) of Time Magazine, Robert Johnson, the executive director of the Institute for New Economic Thinking in New York City, writes about the (crumbled) credibility of economists and economic models. He brings up not only the mistrust and discontent of many students of economics, but also points out the overall demise of the authority of economists everywhere in society.

Johnson brings up four important issues that should be fixed in order to remedy the situation. First, he points out, “economists should resist overstating what they actually know” – referring to the philosopher John Dewey, who called it the quest for certainty in 1929 – in the midst of the great depression, and said that: “Quest for certainty is a dangerous temptress”.

• This is exactly in line with what the pay-off method tries to do, show uncertainty to the decision-maker in a way that the “false sense of certainty” is not conveyed. Showing the pay-off distributions and not only the single number expected or real option values is key in this effort. Also the cumulative net present value graphs of the different scenarios are a tell-tale part in showing how the NPV is accrued over time and what is the distribution of the pay-back time in present value terms.

Second, he observes, economists should recognize the shortcomings of high-powered mathematical models (which are not substitutes for vigilant observation).  Johnson also cites Kenneth Arrow who said: “The math takes a life of its own because the mathematics pushed toward a tendency to prove theories of mathematical, rather than scientific, interest”. Also Frank Knight observed that radical uncertainty, prevalent in times of crisis, means that expectations cannot be anchored as they have nothing to latch to.

•  Again spot on for the pay-off method: as there is no benchmark “mean” to which to “revert” to many models fail, and the information that is available must come from managers. This is something that the pay-off method can live with – unlike some other methods. Trying to be “infinitely precise” in the world where our ability to forecast with excellent accuracy even until tomorrow is compromised (as it is, or anyone want to bet on the weather tomorrow?) we are bound to go wrong, especially in uncertain times. For real options times are often uncertain…

• Interestingly, just an issue before the latest one, of the same Time Magazine, a story told about French quants and them being greatly overrepresented in the investment banks of the world, with a quarter or more of quants being French and about how financial mathematics has fallen out of grace in the Paris top universities. Obviously the financial shocks and the Black Swans as Nicholas Nassim Taleb would call them are real and give the models a bad name – not only give them a bad name, but actually prove that they do not work in turbulent times.

Third, Johnson discusses about introducing the context back to economics (modelling). Indeed if the mathematical models used to drive economic (and financial investment) decision-making are not based on the real world context then they are based on something that is really not of relevance. Objective, Cartesian, approach to economic models may work in theory, but if they work in practice is anybody’s guess.

• Context is in the heart of the pay-off method; it is partially because of the will and the need to include context in a simple way the model was built. Assuming  things and believing that “the market is like the assumptions say” is just plain ignorant. By using the know-how and expertise of the managers about the markets and the firm that owns the real option we can get just that, context, in their estimates for cash-flow scenarios.

Fourth, he argues economists must acknowledge the relationship between politics and economics.

All in all a very welcome viewpoint article with some, not so often voiced commentary on the dire state of economics. For someone who does not believe in the infallibility of mathematics and humans as estimators of the future I would gladly see more of the same…

Mikael Collan (posted from onboard Finnish State Railways train going from Lappeenranta to Salo)

Info about the articles mentioned:

Economists: A Profession at Sea – How to keep economists from missing the next financial crisis by Robert Johnson, Time Jan. 19. 2012 LINK

Making French Bread: Why are France’s Math Graduates in Such Demand? Time, Monday. Jan. 23, 2012 LINK