Building an In-house Solution to Cash-Flow-at-Risk
By Vincent Delort, Risk and Reporting Manager, JTI
While some companies will spend tens of thousands of dollars on a vendor solution to compute Cash-Flow-at-Risk, JTI (Japan Tobacco International) has implemented a low-cost, in-house solution using Excel. Vincent Delort, Risk and Reporting Manager at the company, shares the inside track on the project – including the precise Excel formulae used.
Foreign exchange (FX) risk is rather like the proverbial banana skin. You know it’s there and that if, or when, you fall on it, there will be painful consequences. But with FX, you’re not in a position to pick the banana skin up and remove it entirely; instead, you must look for ways to reduce the impact in case you fall on it. This means working out what injuries you might sustain and determining the best protective clothing to wear to minimise any damage.
My role at JTI centres around this concept, with the aim of reducing the risk that the company’s financial results are impacted by adverse currency movements. A good way of measuring this risk is using a metric called Cash-Flow-at-Risk (CFaR). This is a measure of the potential maximum loss in the value of expected cash flows resulting from an adverse market move, within a given confidence level for a given time horizon (see box 2 for more detail on inputs). The beauty of CFaR is that it results in just one number, which is relatively easy to understand and explain.
Box 1: VaR vs CFaR: at a glance
The Value-at-Risk (VaR) metric is used by financial institutions to estimate the potential loss of market values on a portfolio.
Cash-Flow-at-Risk (CFaR) is a modified version of CFaR – a close relative, as it were. CFaR is often considered to be more appropriate than VaR for corporate usage, however, since it measures potential shortfalls in cash flow impacting the profit and loss (P&L) statement, as well as earnings per share.
Box 2: CFaR inputs
To calculate CFaR, you will need to know:
The journey begins
JTI had previously looked backwards to see where currency exposures had come from, but, around five years ago, my team and I began seriously entertaining the possibility of using and reporting CFaR on a regular basis – to take a more prospective approach to risk management.
We started by looking at CFaR solutions from TMS vendors, as we assumed that a third-party tool would be viable and relatively easy to implement. After a few months of scoping, however, it became apparent that the vendors in question were unable to deliver a solution that could be sufficiently adapted to JTI’s specific needs. What’s more, the price tag attached to such a solution was significant.
As a result, we embarked on a project to build a low-cost, in-house solution for computing CFaR using Excel. So how did we do it?