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 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 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.
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.
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.
The PMO helped the treasurer build an ROI model of the project using the components in the following schematic:
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.
Turning a BI/DW concept into a functioning cash forecasting application able to solve existing challenge involves five basic steps.
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?