We live in an uncertain world. At every turn, plans must adapt to changing circumstances as risks become reality and unforeseen influences arise. In the business world, people like certainty and as finance professionals our fixed budgets and targets are a way of life. The risk to these certain plans is hard to quantify and explain (see my article ‘How the Gain-loss Spread Helps you Manage Risk’). So, while as humans we adapt all the time to change, in the business world inflexibility is often baked in.
Monte Carlo simulation is the ability to build a model and test one or more random variables through this model thousands of times to build up a data set of potential outcomes. It is surprisingly easy to build into day-to-day use and incredibly powerful once you do. Once mastered, the limitations are really around the imagination of the user. By testing the interaction of several variables at once it can be used to:
The starting point is to build a model, and the more time and effort invested in this stage the better the ultimate output. The model needs to reflect how one or more variables influence the outcomes. If we take a common situation, a new business opportunity, we usually default to a standard and familiar net present value (NPV) style format. The layout looks like a profit-and-loss statement with some modifications for cash, working capital and capex. At this point, how often do we ask ourselves: “How much can these assumptions change?”
Traditional methods of modelling investment decisions just cannot answer this question and, to be frank, at this point we as a finance community have started to lose the attention of the wider non-finance audience. We need better ways of reflecting the real world, or to put it another way, reflecting several moving parts.
For example, revenue might be based on the state of the economy, competition, commodity prices, even the weather. These factors lend themselves to an estimate of the likelihood of change, and it would be a poor management team that could not identify these influences on the businesses they manage. But the thought process, not numerical precision, is the objective here. What we should not be aiming for is overcomplication. The act of including Monte Carlo simulation in the analysis forces decision-makers to define and articulate the real-world factors that impact results. In many ways, that is a critically important activity in its own right.
A summary of the actual technique in Excel is included in the box below. Once built, a Monte Carlo-enabled model can be used to start answering lots more questions than the static model ever could. The graph shows a sample of the output for an investment project. The blue line is the NPV and, in a static model, that is all you see.
With a data set of thousands of outcomes, it is easy to see the probability of success or failure, and even the maximum cash outflow for liquidity planning purposes. The effect of de-risking measures can be quantified. For instance, mitigations such as buying insurance, paying extra at the design stage to build more flexibility or structuring the manufacturing process differently to be more adaptable can all be modelled to see if they add to or decrease the overall value and how they change the risk profile. While experienced management will already be considering these factors without using a Monte Carlo-enabled model, this may be happening in an unstructured way, especially when complex interactions are involved. Bringing the ability to mirror this thinking, at least in part, to financial analysis greatly increases its relevance to the business decision. All it takes is a small leap of faith in collating estimates for the probability outcomes around the handful of critical influences identified at the design stage. We are not used to doing this as finance professionals, but we think like this all the time in our business and personal lives.
Finally, no model can mirror reality to any real degree of precision. Our aim as finance professionals is to support strategic thinking but never replace it with a spreadsheet. Adopting the dynamic Monte Carlo approach espoused here opens many more doors in achieving this objective while at the same time allowing the results to be communicated in an understandable manner.
Ben Walters FCT ACA is a practising treasurer with a keen interest in the practical application of corporate finance in the business environment. For more on his views around the role of finance in supporting and enhancing strategic analysis, or for more detail on building Monte Carlo analysis into a spreadsheet investment model, contact him on enquiries@mwacc.co.uk
