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