A New Approach to Reliable Cash Flow Forecasting

With the credit crisis evolving, it is no surprise that cash flow forecasting is a top priority for banks and companies. In times of tight credit, it is vital to know where, when and how much cash will be available. Reliable cash forecasting is not only a prerequisite for efficient liquidity management, it is also is required to assure sufficient credit lines. In other words, lacking a good understanding about future cash flow adds unnecessary cost, can put reputation on the line and, in the worst case, even jeopardise a company’s future.

When discussing cash flow forecasting with treasurers and reviewing recent publications, one cannot fail to notice that little progress has been made in the past decades. Even though it has been consistently voted a top three priority, cash flow forecasting processes have not improved materially. Admittedly, spreadsheet and network technology have speeded up collecting and consolidating data. However, reliability of the output has not improved - many local finance managers across companies are involved and treasurers still complain about lack of consistency and quality.

The weakest link remains the involvement of ‘n’ different people from across the company for collecting, pre-processing and shipping data points. Irrespective of local staff expertise, current practices cannot prevent delay in availability of information and inconsistency across data sources.

Local staff interpret and summarise the data sources manually. More often than not, the process of interpreting is art and/or politics, not science. This erodes reliability and accuracy of the cash flow forecast report. Because of this erosion, some companies have abandoned cash flow forecasting completely - in their opinion getting the forecast right is not justified by the benefit of having it right.

A Paradigm Shift

The alternative approach to cash flow forecasting presented here is based on the assumption that past behaviour is the best prediction of future behaviour. I propose tapping and analysing the raw data instead of asking finance managers across an enterprise to interpret the data.

Historic payables and receivables data stored in financial systems across the enterprise can be used for calculating payment distribution and seasonality patterns for defined data buckets. The results can be used for a short-term cash flow forecast calculated as the sum of payment probability distribution of open items corrected for seasonality.

The forecast horizon can be extended when this approach is also applied to the order book. Analysis of how long it took to convert booked orders into invoices can be used to estimate when open orders are likely be turned into invoices. From there, a second order calculation can be triggered for estimating the cash flow resulting from the open order, given the statistical output already applied to the open payables and receivables.

The forecast horizon can be extended even further by applying analysis of invoice date distribution to sales and cost lines in business outlook and budget reports. Once a probability distribution of invoice amount across future dates is calculated, a second order calculation can be made for calculating a probable cash flow forecast.

Figure 1 summarises the described alternative approach to forecasting. The bank statement provides information on WHERE the cash is available at T=0. The other sources provide information on WHEN the cash will be available. Each arrow in Figure 1 represents a statistical analysis of historic data available at T=0. The analysis provides probability about the timing of the future cash flow related to each open item. Analysis also reveals interfering weekly, monthly and e.g. quarterly seasonal patterns of business activities within reporting periods.

Figure 1: Alternative Forecast Model: Data Sources

Source: Zanders

Grouping of data for calculation purposes can be validated for statistical relevance. Such segmentation could be by number of data points, amount, business unit, entity, country, currency or any combination thereof.

Each order that is converted into invoices and each bank transaction reconciled to an outstanding invoice adds to the actual data available for statistical analysis and calculating future cash flow. Furthermore, with sufficient historical information, the results can be back-tested, which will help understanding the reliability of the output. The results booked in projects where this approach is used are promising and strongly indicate it is able to take cash flow forecasting to the next level of sophistication.

When compared to the traditional approach, the proposed approach to cash flow forecasting provides a number of business benefits in terms of efficiency and quality of information. The cash flow forecasting process becomes more efficient because it:

• Eliminates the need for (and thus dependency on) local finance staff for data collection and interpretation.
  
• Pulls data from company sources in (near) real time, making the cash flow forecast report more dynamic and useful.

Quality of Forecast

As well as process efficiency, the approach will also improve the quality of the cash flow report. Deploying computing power instead of local staff for the interpretation of raw data adds objectivity and consistency that makes the report more reliable and useful. Some examples:

1. When using the proposed statistical approach to cash flow forecasting on the fly, the report responds dynamically to changes in the business environment. The statistical calculations make it transparent what the relationship is between the change in outlook or budget and the update of the projected cash flow.
2. Back-testing on historic data sets introduces r2 as an indicator for the reliability of the cash flow forecast report. Dependent on the calculated r2, treasury could execute alternative liquidity or hedging strategies. A low r2 for a business segment can be an indicator for inconsistent working capital management, locking cash unnecessarily in the cash conversion cycle.
3. Root causes of forecast errors can be analysed in great detail within the tool. Forecast errors are typically caused by incorrect input data or changes in lapse time to change orders into invoices and invoices into cash.Volume effects can be analysed by comparing the forecast input at different moments in time. Changes in conversion times can be analysed by comparing moving averages of calculated statistical information. This knowledge allows companies to address bottlenecks well before they cause serious damage to the business.
4. The impact of merger, acquisition and development (MA&D) activities on cash flow can be analysed quickly and effectively. Adding or deleting specific businesses will impact not only the cash flow amount, but also timing. Eliminating or adding a record set, the output of the statistical analysis will change dynamically, resulting in an updated projection of future cash flow.
5. It provides a far more detailed and accurate understanding of actual working capital requirements. The proposed statistical model uses actual trade credit terms rather than a misleading formula combining balance sheet and profit and loss (P&L) data typically used for traditional analysis. As such, it provides a more detailed understanding of intra-period working capital levels. This information can be used for defining key performance indicators (KPIs) realistically and monitoring working capital improvement programmes.
6. The financial success of (new) businesses can be tracked. The return on investment (ROI) of proposed (new) businesses is typically based on assumed credit terms and inventory levels. The proposed approach to cash flow forecasting could pinpoint root causes of under- or outperformance of businesses assisting business managers at managing their products and markets.
7. Economic scenarios like temporal slowdown of customer payments, volume changes and any combination can be stress tested. Changes in economic environment can impact corporate cash flows in many different ways. An economic downturn not only impacts sales volume, but also trade credit taken. The new approach to cash forecasting presented can fine tune the impact by adding a mark-up on the factors calculated. For instance, a scenario could be that in the next three months customers in country A but not in country B will, on average, delay their payment with x days whereas supplier payment of business unit II has to be paid y days earlier in order to assure continued delivery of raw materials. This type of scenario analysis supports executive management in their decisions and discussions with banks and investors.

Observations

For the purpose of this article the proposed approach is described at high level only. Real life is more complex than can be summarised in a few paragraphs. A number of issues have been addressed and tested against life data sets, adding depth and applicability to the model. The complexities referred to include:

• Intra-reporting-period cash flow patterns: seasonality will impact the probability of a cash flow on a certain date; the expected cash flow probability for day three of a month is different if this day would be a Sunday or a Wednesday. Furthermore an expected cash flow for a Wednesday might be different if this Wednesday is early or later in a quarter.
• Grouping of historic data adds reliability to the report to the extent that it is statistically relevant. If historic data or open items in a particular group is too small or not homogenous enough, reliability of the forecast will be poor.
• Some cash flow types are not suitable for a distribution method at all and need to be treated separately. Cash flows related to financial instruments, tax and e.g. large project (down) payments should not be anonymised or distributed across an interval. Such payment can be managed in a separate line and plotted on their anticipated settlement date.
• For longer report horizons, the predictability of the daily forecast will be poor because the calculated cash flow is the output of two consecutive statistical calculations. However, typically daily buckets are not used for longer horizons. Furthermore the alternative approach introduces issues that do not necessarily exist when using the traditional approach to cash flow forecasting.
• It is assumed that source systems are up-to-date. For outlook and budget information this might not be a major issue. For orders and invoices, however, this might be different; if transaction reconciliation is three weeks behind, the short-term cash flow forecast will not be as reliable as it can be.
• Fortunately auto-matching of bank statements is widespread today. It reduces the severity of this issue, but will not eliminate it. A few unmatched items might not impact the statistical calculations significantly and unmatched bank transactions can be reported on the forecast as a separate line item correcting the cash flow projection for paid but unmatched with its own distribution calculation applied.
• Budget and outlook data might not be available in a similar, detailed segmentation as ledger data. This however can be addressed by synchronising the filtering of historic data for the statistical analysis used for calculating future cash flow related to budget or outlook information to the segmentation used for budgeting.
• Data in outlook and budget reports might partially overlap with itemised order and ledger information. Clear handshake points need to be defined when budget rather than order information is used as input for the cash flow forecast report.

This approach would eliminate the role of local finance staff in the cash flow forecast process. It does, however, create a new dependency on interfacing and data exchange. Treasury and IT would need to work closely together. Of course, local finance staff can be a beneficiary of the cash flow forecast report.

Conclusion

The proposed alternative approach creates an objective and detailed cash flow forecast in (near) real time that can be used for liquidity management and business planning and control purposes. On the back of the forecast calculation, it also creates early warnings about changing business patterns and input for realistic incentives related to working capital improvement programmes.

In times of tight and expensive credit such as we are experiencing today, we need a better and more reliable understanding of future cash flow. The approach to cash flow forecasting described here is a paradigm shift that can bring the cash forecasting process to the required level of sophistication.

Article by Bas Rebel is a manager with Zanders, Treasury & Finance Solutions. He has over 14 years' experience in consulting, corporate treasury management, product management and banking. He has a broad understanding of treasury issues, especially related to transaction processing and information management within complex organizations. At Zanders, he is responsible for cash flow management addressing issues related to working capital management, payment strategies, banking structures and treasury automation.