08-02-2022 | treasuryXL | CashAnalytics | LinkedIn |

While data seems like an obvious component of forecasting, it’s different to let it drive the whole process. Adopt a data-driven method, and you’ll find that the data can actually do most of the projection work for you.

Data-driven cash flow forecasting uses organization data you already have to project future cash flow. This method will reduce manual effort, so you can focus on analysis. And, in turn, it will transform how you forecast and manage cash flow.

For instance, the head of treasury at a treasury solutions provider noted the largest con to manual cash forecasting processes — time.

“A lot of finance professionals swear by Excel. The time that you put into the output does not match the value of the output,” the head of treasury explains. “It makes sense to spend less time inputting data and more time on analysis. With Excel, you spend a lot of time compiling and consolidating data and checking for errors. The value add comes from understanding the data — not from compiling it.”

So, the company turned to CashAnalytics to support the data-driven cash forecasting services it provides to clients.

“It’s a tool that’s been specifically built for cash flow forecasting that focuses on making the input of information as easy as possible so that you can spend time on the analysis and reporting of the outputs,” the head of treasury says. A lot of companies rely on a [treasury management system] for cash forecasting, but this does not let you get into the details without exporting the data to Excel. By the time someone starts analysing the data, they need to look at forecasting again.”

And they’ve reaped the rewards of adopting data-driven cash forecasting via CashAnalytics for their clients.“The team can now concentrate on analysis,” the head of treasury said.

“The information helps position them as a more strategic partner with their clients and for someone in-house, this would be in the broader business.”

An Introduction to Cash Forecasting Data

Now that you know what data-driven cash forecasting is, it’s time to dive a bit more into the data sources that shape your forecasts — and the benefits they provide.

Key Cash Flow Forecasting Data Sources

A cash flow forecast is simply a collection of data from other systems with input and adjustments from the forecaster. Start with the data sources that contain the most up-to-date and important data and build from there. A cash forecast can be built using some or all of the following data sources and inputs.

Bank Account

Bank portals and electronic bank account statements provide actual balance and transaction details for forecast model population and variance analysis. Data for these statements is accessible and downloadable via online bank portals. Bank account transaction and balance information can also be automatically captured from a bank using connectivity options, like application programming interfaces (API) and secure file transfer protocol (SFTP) in a variety of formats (e.g., MT940, BAI2, etc.)

Enterprise Resource Planning Ledger

Every bank account transaction is loaded onto a company’s enterprise resource planning (ERP) system and then assigned to an outstanding invoice or allocated to a specific account during the bank reconciliation process. After the reconciliation process, this data is useful for actualizing the forecast model. Each company will have their own connectivity procedures around their ERP, but it will most likely be via API or SFTP. ERP systems contain accounts payable (AP) and accounts receivable (AR) data used to create short-term forecasts. The ERP is also a source of actual cash flow data and includes longer-term planning data in some instances.

PROS

  • Bank account data can be refreshed in real-time.
  • Bank account data comes in standardized formats like MT940 and BAI2.
  • If, for example, the forecast is used for treasury liquidity forecasting focused on tracking net cash movements and positions, capturing the data from the bank will suffice.

PROS

  • The transaction data is pre-classified.
  • No reliance on any external parties (e.g. banks) for the data.
  • Data can be used for detailed customer, supplier, and working capital analysis.

CONS

  • Bank data needs to be classified into different categories to make it useful.
  • Sometimes reference detail isn’t available to make classification easy (e.g., a customer’s name).

CONS

  • There may be a time lag due to the reconciliation process.
  • The raw export formatting may need to be tidied up before use.

AP and AR ledger data

AP and AR ledger data — typically sourced from an ERP system — is an essential component of a short-term forecast and details both outstanding and paid invoices. This data helps paint a picture of what cash the business is likely to receive from customers and pay to suppliers in the coming days and weeks. The AP and AR ledger data will help build the operating cash flow forecast for up to six weeks into the future — depending on the payment terms offered to customers and received from suppliers. To build and drive a short-term cash flow forecast with ledger data, two data sets across both AP and AR are needed:

Outstanding AP & AR

This is essentially a list of unpaid AP and AR invoices sitting on the balance sheet or ledger. As standard, the invoice data extract must contain:

  • Document number, type, date
  • Customer/ supplier name or ID
  • Due date of invoice
  • Amount
  • Currency

With this information, a baseline customer- and supplier-level forecast can be created.

Cleared/Paid AP & AR

This data outlines what’s been paid since the last forecast and, in some cases, what is needed to net off against the original invoice amount to calculate outstanding balances. Required fields mirror those of outstanding AP and AR and also include:

  • Payment date
  • Connected invoice ref (if netting required)
Click here to download the full whitepaper

Sometimes, exported ledger data may not translate into a meaningful cash flow forecast on the first try. This happens because the data set may need to be organized and transformed ahead of forecasting by:

  • Cleansing data sets: Remove any non-cash items or irrelevant invoices ahead of forecasting that could impede accuracy.

  • Accounting for document type: The ledger will often contain a number of document types alongside the standard invoice. Each of these different document types has a unique impact from a cash flow point of view and must be accounted for within the forecast model. For example, a credit note has the effect of reducing the amount of an expected invoice. It is difficult to calculate an outstanding customer or supplier balance without first taking into account credit notes.

Other Resources

Many other systems and data sources can flow into the cash forecast, including:

  • The annual budget, which serves as the baseline for medium to longer-term cash forecasting while providing the entire forecast for recurring items such as payroll and rent.
  • Customer relationship management (CRM) tools that include early-stage invoice data.
  • Treasury management systems (TMS) with financing flows.
  • Forecaster input that provides context that will influence the forecast. Because they’re humans, forecasters are more subjective and can think critically and holistically — whereas a system cannot.

Actual Cash Flow Data Benefits

Actual cash flow data is a critical component of any cash flow forecasting process. The balance and transactional data — most often sourced from ERP systems and bank statements — lets you actualize the cash flow model on an ongoing basis,  which provides a range of benefits, such as:

  • Analysis of recent past: Understanding what has happened with cash flow in the recent past is essential to building a picture of the future.

  • Building current positions: Actual cash flow and balance data are used to calculate current cash and liquidity positions — which is the starting point of any forecast.

  • Variance analysis: Actual versus forecast analysis is a central part of any high-value forecasting process that is reliant on up-to-date actual cash flow data

  • Trend forecasting: Any trend forecasting model or algorithm will require historical actual cash flow data that is frequently refreshed to create accurate forecasts.