It’s surprisingly simple to produce Monte Carlo simulation in Excel and once you have understood this technique you can apply it across virtually any risk management situation.
Monte Carlo allows you to produce thousands of randomly generated results from a model: Not sure on the size of the risk or the financial impact? Build a model and then run it through your Monte Carlo engine. Not sure on the right hedging solution? Monte Carlo can again test the alternatives and optimise the balance of risk and cost. Monte Carlo’s versatility means you can easily adapt it to your own unique business situations and challenges and offer versatility and transparency beyond traditional analytical techniques.
We live in an uncertain world. At every turn, organisations 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 so while we adapt all the time as humans 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:
- Take investment appraisal to a new level, modelling real world factors and risks
- Test FX and interest rate risk management solutions to determine the optimal hedging solutions
- Test headroom and liquidity plans against budgets and business plans
- Determine the breaking point for debt covenants and credit ratings and mitigation options
- Develop real options strategies and test their value
- Challenge strategic assumptions especially where there is some reliance on market-derived inputs
- Model portfolio positions and asset allocation to optimise risk and return
The starting point is to build a model. This is something you would inevitably be doing anyway but the more time and effort invested in this stage the better the ultimate output. The model needs to reflect how one or more variables influences the outcomes – usually we stop there and copy and paste the model a couple of times, change an input or two, and produce a couple of scenarios. What Monte Carlo allows you to do is to test thousands of randomly generated inputs based on volatility parameters that you determine. The output is much more dynamic, visible and intuitively easy to understand than Value at Risk (VaR). Testing different assumptions or treasury responses allows you to determine the optimal response in terms of risk and reward which VaR often cannot do intuitively as it relies on static inputs and hedging solutions.
While we will concentrate on specific treasury areas, I want to run through an example of Monte Carlo application in a more general finance task, that of investment appraisal. My reasoning for doing so is twofold: firstly, it’s quite fun, and secondly it underlines the interaction between financial strategy, ie the capital allocation decision, and specific treasury risks such as liquidity or covenant management.
A new business opportunity
In this article I am going to describe the application of Monte Carlo analysis to a typical investment decision. While not a traditional treasury area, it illustrates several advantages from the technique, and it’s worth making the point of course that as risk managers the greatest risk we ultimately have to deal with in business is the risk of allocating capital to the wrong commercial strategy. No amount of treasury risk management can save a business that consistently backs the wrong commercial strategies.
When appraising a new business opportunity, we default to a standard 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 we often find ourselves asking “how much can these assumptions change?” Traditional methods of modelling investment decisions fall short of answering this question. Some scenarios may be produced or a sensitivity to break-even point may be calculated, but the versatility of these is limited. 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 not able to identify these influences on the businesses they manage. But the thought process rather than numerical precision is the objective here. What we should not be aiming for is over complication. 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 success 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. Figure one shows a sample of the output for an investment project. The blue line is the NPV, and in a static model that is all that 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 plugged into the model and then run through the Monte Carlo simulation to see if they add or decrease the overall value – as well as how they change the risk profile.
You will be forgiven if your mind starts to wander here towards thoughts of real options and you would be right to do so. What if there is a significant commodity exposure to the project? You can test the effect of fixing or capping the commodity price on NPV and downside outcomes through the Monte Carlo simulation. This is a much more hands on approach than relying on a standard deviation and VaR calculation. As long as you can correctly model the volatility of the commodity price in the Monte Carlo model you will get the same result as a VaR approach but the output will be dynamic in that the whole spectrum of outcomes can be seen, rather than a static single point answer. But even better than this, various hedging instruments can be modelled such as swaps, at the money or out of the money options and collar structures. Being able to see the cost of the hedging solution and the whole universe of outcomes makes deciding the optimal course of action in terms of risk and reward so much clearer.
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 around the volatility of outcomes of a handful of critical influences identified at the “design” stage. We are not used to doing this as finance professionals, although I would exempt many treasurers from that comment. However, we think like this all the time in our personal lives so why not apply it when making big ticket capital decisions?
A last word
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-enabled approach espoused here opens many more doors in achieving this objective whilst at the same time allowing the results to be communicated in an understandable manner. There are many uses of the technique for treasury and the example discussed in this article here not only added significant colour to the project appraisal decision but also informed on the level of liquidity, if the project went badly, that would be needed to support the business unit.
The last point I would like to make at this stage is I believe that the insight, experience and technical knowledge we bring to our businesses as treasurers has real value outside of the core traditional areas of treasury. We can be sure that technology will automate away many manual processes over the next few years. Where we can maintain competitive advantage for our organisations and for our profession is by expanding our influence beyond traditional treasury into a wider strategic finance role. In my opinion we have the perfect skill sets to do this. I often see the phrase “the strategic treasurer” and to me this means evolving into that expert advisor to the CFO on business issues where a risk minded and strategic financial perspective is required. Monte Carlo is a key enabler in this progression allowing as it does a spectrum of outcomes to be modelled based on identifiable risks to the business, and then in turn the effect of various actions in response.
|How do you build Monte Carlo analysis into a spreadsheet investment model?
- Build the model
- Identify one or two key influencers on the various elements of the model such as revenue, labour and materials costs, working capital or capex
- Estimate (or research the market data) on the range of these influences
- Build a link between this influence and its outcome to the financial model
- Consider the strength of correlations between these influences
- Combine the use of the Excel functions “random number” and “normal distribution” to generate a random outcome for the influence in question.
- Use Excel’s “data table” function to store the result of the financial model for this randomly generated outcome
- Repeat 1000+ times with a data table consisting of 1,000s of rows
Over the next twelve months or so Ben will explore various common treasury risk management and hedging topics from the perspective of a practising treasurer.