Rethinking Cash Flow Forecasting – Building an Operational Tool with TM1

Cash Flow Forecasting is getting the focus of CFOs and finance professionals. The economic crisis certainly puts the pressure on in terms of available cash and efficiency not to mention risks of late paying or defaulting customers.

Problems associated with common approaches

Accounting systems don’t provide foresight and their perspective – the reports output – are quite limited anyway. Thus the office of finance is forced to roll their own solution. More often than not, the tool used is Excel. Such approaches have several shortcomings. They are tiresome to maintain involving regular and intensive communication to gather all the required data and forecasts, suffer from all the too well known issues of spreadsheets and due to inherent limitations fail short to provide enough detail to be useful as an operational tool.

Business Intelligence (BI) and related technologies are not new to organizations facing these kinds of issues. There is a good chance IT has some queries or even a data mart in place to support the various data needs of finance. But does that live up to the expectations? Usually not or only to the limit of providing basic detailed data that has to be processed by business users according to their own business logic. And business users are just as their name implies business professionals. They rarely have the skills and tools available to efficiently process data – that’s supposed to be IT’s job.

Tool selection

a tool is needed that can handle massive data volumes and provides a straightforward, not-too-technical way to implement business’ logic. In this case IBM’s Cognos TM1 has been chosen being a specialist tool for these kind of problems and having quite a long and successful track record.

Once the tool is available it’s a matter of translation. It’s easy to get lost, illustrated by many IT (-logic) driven solutions providing inflexible data-centric results.

So what can CFOs do to alleviate this situation? It is critical to consider and embrace both aspects of the problem – both the business and the IT side and their interaction.

Rethinking cash conversion metrics – introducing Payment Behaviour Analytics

The cash conversion cycle provides the theoretical background and metrics used for cash flow forecasting:

The DSO (Days Sales Outstanding) and DPO (days Payable Outstanding) are the traditional building blocks to estimate when Accounts Receivable (AR) and Accounts Payable (AP) are realized. This approach has one significant drawback diminishing the forecast’s accuracy: both metrics are based on total (aggregated) data not taking individual customers or deals’ payment terms into account.

However today’s BI tools making invoice / document line level data available through connecting directly to the accounting system it is feasible to build
Payment Behaviour Analytics. This provides average payment terms, days late/early, receivables volumes on customer, entity, transaction currency, intercompany, etc. level. It’s certainly possible to analyze customers payment behaviour along the time axis thus also providing early warning for finance and accounting.

Given the right model and tool like TM1 this can be further expanded to analyze payment terms (whether or not they are related to the end-of-month) given this is not recorded in the operational systems. Some examples of the possibilities:

Payment behaviour histogram – average days late by receivables volume

Top customers – bubble size represents average days late

Average days late by selected top and total customers per month

The Cash Flow Forecast – as an operational tool

Using payment behavior instead of DSO/DPO it becomes possible to forecast outstanding receivables and payables significantly more accurately. There is another major gain: this logic can be tracked back, thus it’s feasible to drill through from forecasted due payments in a certain period to individual customers, or even invoices.

Drillthrough enables to check forecast on customer or even invoice level

This provides an unprecedented operational tool for the AR department, extending the Cash Flow Forecasting system’s reporting use into active monitoring, AR management and collection.

The Payables side can be readily influenced by the organization, hence it makes sense to be able to simulate how much headroom finance can gain this way.

Putting it all together: complete actual side is recorded in the books of the organization thus loadable into the Cash Flow system after proper mapping to report lines has been done (e.g. accounts to cost lines taking special ‘suppliers’ like government for taxes into consideration). AR and AP forecasting for all outstanding is covered in the Payment behavior paragraphs. Another rather straightforwardly automatable component of the Cash Flow Forecast is open orders from the operational systems where the same payment behavior based logic can be applied.

This already gives a fairly accurate picture of the expected cash flows for the short and midterm outlook. Needless to say it does not make sense to automate everything from a cost / benefit point of view. Some inputs – however significant their impact is – are easier managed in standardized spreadsheets and then uploaded into the forecasting system.

The following diagram provides an overview of the included Cash Flow Forecast components:

Currency handling certainly has to be thought of providing an additional dimension of analysis and simulation capability while providing the final Cash Flow Forecast in the chosen (reporting) currency.

Cash Flow Forecast as stacked chart marking actuals->forecast cutover

Having the cash flow calculated by TM1 on a detailed, daily level makes reporting either on weeks or on months simple and straightforward putting an end to maintaining the Cash Flow Forecast in multiple report structures.

Using a sophisticated business intelligence tool can provide additional benefits: manual input values can be statistically forecasted based on a moving average algorithms or calculated as simple proportional change using data spreading functionality.

The later functionality could become quite handy if there are no or no recent budget figures available for forecasting; Projecting sales for one is likely to be a good candidate for such an approach.

Project and implementation overview

No article would be complete without reviewing the internal and external resources and time required to deliver such a Cash Flow Forecast.

The typical timeline for such projects using in-memory (like IBM Cognos TM1) technology lies between 1 and 3 months. By the end of the first month a working proof of concept is expected to be ready. The technical platform used was Cognos Express providing additional reporting capabilities on top of TM1.

High level dataflow

Involvement of finance has a key impact on the bottom line, in fact it is a necessity as the cash flow forecast is not just finance’s responsibility but many of the Cash Flow Forecast’s building blocks are managed in-house. Best practice is to not only own the data and reports but have reasonable control and skills in using the tools to be able to timely and efficiently meet the organization’s continuously changing requirements.

---