Building an In-house Solution to Cash-Flow-at-RiskVincent Delort, Risk and Reporting Manager, JTIIs spending tens of thousands on a vendor solution to compute Cash-Flow-at-Risk really necessary? Vincent Delort, Japan Tobacco International, proves that a low-cost, in-house solution using Excel can be just as effective – including the precise Excel formulae used.

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 company’s expected cash flows.

The confidence level at which you want to calculate the CFaR – which can vary from 90% to 95% or even 99%. At JTI we use 95%.

The given timeline over which you wish to monitor CFaR. At JTI, we choose to look at it over 1 year but that figure could easily be changed to 1 month or 10 years if appropriate. It depends how far ahead the company wants to look, and how much you are hedging over what period.

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?