Cash Flow Forecasting: Old Problem, New Solution

Cash flow forecasting – the accurate prediction of cash inflows and outflows over a period of time – is a challenge that financial professionals have been dealing with for decades. Its importance has fluctuated relative to liquidity squeezes, high interest rates and the degree of focus on financial control. Currently, cash flow forecasting is a […]

Date published
May 23, 2006 Categories

Cash flow forecasting – the accurate prediction of cash inflows and outflows over a period of time – is a challenge that financial professionals have been dealing with for decades. Its importance has fluctuated relative to liquidity squeezes, high interest rates and the degree of focus on financial control. Currently, cash flow forecasting is a top priority for most corporate treasuries because it is an essential ingredient in effective liquidity and risk management, and fulfills many key control requirements dictated by Sarbanes Oxley. Simply stated, a forecast needs to provide a reasonably accurate snapshot of the cash position at a given time in a given currency. This is a function of the cash on hand; intercompany and third party collections; and the intercompany and third party payments and treasury settlements, all adjusted for banking and payment systems.

The challenge of cash flow forecasting is that it must successfully integrate two basic approaches. The first, a customer facing approach, applies terms and conditions to accounts payable (A/P) and accounts receivable (A/R) to forecast cash flow within a reasonable time horizon of 7 to 60 days. The second, a finance facing approach, is grounded in the requirement to have a square position with financial institutions and uses banking and historical performance to come to a forecast cash position at a point in time.

Certain practical realities impact the effectiveness of forecasted data – customers don’t pay on time, sales terms are not universally enforced, historical performance is skewed by organizational change and banking data is not correctly integrated. This causes continued frustration to financial professionals. The problem is that an accurate forecast must incorporate internal information from a variety of data sources with external information from an entirely different set of data sources all with varying degrees of data consistency and format. However, it can be demonstrated that adoption of business intelligence tools by treasury – tools already deployed elsewhere in the organization – can eliminate many of the roadblocks to achieving accurate cash forecasting.

Business Intelligence Solutions

Business intelligence solutions are actively used by other organizational entities and serve a number of strategic needs. These include effective management of marketing and advertising, customer relationship management and resource planning. The solution itself usually involves:

One common example of a business intelligence solution is the technology and processes now used by many online retailers. The online retailer’s web application includes business intelligence to analyze customer characteristics and historical customer interaction to dynamically offer promotions or likely additional purchases to customers in response to their selections in a current e-commerce session. The customer sees the results of this business intelligence in subsequent web interaction advising that: ‘customers like you who have purchased X, have also purchased Y and Z. Would you like to make an additional purchase to save shipping costs?’

In this example, the business intelligence system provides the means to devise a realistic cross-sell or up-sell opportunity, while the operational e-commerce system provides the current business event information to request likely sales opportunities and presents these opportunities to customers before they reach the online checkout.

Treasury Application

Business intelligence systems can offer the same benefits to a corporate treasury by integrating detailed, historical A/R and A/P, financial institution, exchange rate, corporate trends, product, promotion and customer behavior information to create a highly sophisticated model for predicting cash flows. The technology itself provides the means to create a forecast, which when integrated with operational systems, such as accounting or invoicing gives treasurers the means to create an accurate forecast to meet their liquidity and risk management objectives.

Applying business intelligence to the cash flow forecasting challenge offers many advantages over traditional static factor or function-based models. The business intelligence infrastructure continues to capture history providing more data points for the forecasting model. Because the solution is also data driven it easily adapts to significant events that affect the markets, rates, customer or vendor behavior in terms of invoicing or payment. Business intelligence represents the logical solution to developing an accurate forecast as it uses the input of the working capital, or function based approach and the treasury, or static factor approach to create a dynamic (and automated) cash flow forecast.

Case Study

A good example is the case of an online retailer who survived the dotcom bust but continues to operate under close scrutiny from analysts, banks and investors due to growth induced working capital problems and a resulting liquidity squeeze. The corporate treasurer used a simple cash forecast based on historic AP and AR trends but did not incorporate other factors, such as the seasonality of the business or the effects of other business events, e.g. new marketing campaigns, on the corporate cash flow.

The retailer’s sales and marketing group used business intelligence tools to develop reasonably accurate sales forecasts. This led the CFO to suspect that a similar approach on the cash side may have some validity. The retailer also used a business intelligence/data warehousing (BI/DW) program management office (PMO) that oversees implementation and operations of all corporate BI/DW applications.

The treasurer met with the BI/DW PMO team and they identified the key information sources for a new and improved cash-forecasting tool. Together, they developed a schematic of a business intelligence approach to cash forecasting which is reproduced below.

Figure 1

The PMO helped the treasurer build an ROI model of the project using the components in the following schematic:

Figure 2

The PMO and the treasurer were able to determine that the project was self-funding as a 5 per cent improvement in accuracy of the 0-7 day time period and a similar improvement over the 7-60 day period would provide a payback in less than six months. The improved accuracy would allow for more efficient borrowing and would also enable them to more effectively upstream cash from operating units around the world.

The end result of the retailer’s efforts was a data warehouse that included a rolling five-year A/R and A/P history from the ERP system, sales forecasts and marketing campaign information from the sales and marketing system, electronic statements from the corporation’s network of banks, and order and customer invoice information from the e-commerce system. The resulting business intelligence application used the information in the data warehouse to predict future collection and payment amounts based on seasonal factors, sales campaigns, supplier invoice history, customer payment history, financial institution fee schedules and processing history. The application could use recent activity from the e-commerce application to create accurate short-term forecasts and updated sales and marketing plans to create longer-term forecasts.

How It Works

Turning a BI/DW concept into a functioning cash forecasting application able to solve existing challenge involves five basic steps.

  1. Making an inventory the business-specific revenue and expense streams that drive cash balances along with the corresponding sources of data for these streams. For example, if revenue is derived from post-sales service, then include this in the inventory along with sources of data on post-sales service, such as service contracts or models, e.g. average time to service call. On the expense side data sources could include the A/P system along with departmental spreadsheets or databases.
  2. Analyze the inventory in order to understand how service revenue affects cash balances and assess the quality of the data sources. For example, service revenue could be subject to frequent billing disputes with customers and could also be invoiced incorrectly. The first part of this situation suggests that valid invoices might be written off to preserve customer relationships and give some indication as to the write-off factor. The second is that data coming from service A/R is not always reliable and that there is not a one-to-one correspondence between this number and actual cash received.
  3. Devise an IT architecture to deliver the cash forecast. This includes identifying and possibly procuring hardware and software to do the data integration, manage the data warehouse and host the business intelligence application. In many cases, the treasurer can piggyback on other BI/DW initiatives within the corporation. Hardware could include a network server and software could include packaged applications such as extract, transformation and loading (ETL) software suites and business intelligence tools. In all probability, both hardware and software may already be in ready use within the corporation.
  4. Devise rules for gathering relevant source data and transforming it for use by the business intelligence application. Using the inventory and analysis of steps 1 and 2, develop a rule for how each stream is to be evaluated and from which source it will come. The simplest example of a rule is the polling of banks for valid transaction and balance information.
  5. Implement the rules using the selected data integration technology and the cash forecast using the selected business intelligence technology and test the validity of the resulting cash forecast. Because this is a complex model, several iterations should be expected before the final cash forecast can be rolled out.


The BI/DW approach to cash flow forecasting is being driven by two powerful factors: the increasingly critical need for cash flow forecasting accuracy and the proven value of BI/DW solutions in the corporate marketplace. This mirrors the past migration towards the use of ERP workstations in lieu of third-party systems or simple spreadsheets as ERP systems established their value and IT professionals expanded the functionality of the systems to include corporate treasury.

The real question is whether demand for BI/DW cash flow forecasting systems will be driven by forward thinking corporate treasurers or imposed by CFOs/CIOs impatient with the slow pace of change in a critical business function?

Exit mobile version