Building a Better Cash Forecasting Toolkit

Without effective forecasting, companies are essentially flying blind into the future. We look at the role of new technology evolving to help tackle this challenge.

While much of the finance function has digitised in recent years it’s fair to assume that most cash forecasting activity, even in very large corporates, is still spreadsheet-based. The spreadsheet is often the default tool for many planning, forecasting and data modelling activities due to the fact it is available to everyone and used for so many other activities as the aggregation point for data from a host of different systems. As Conor Deegan, CEO of cash forecasting provider CashAnalytics, says, “a spreadsheet is the easy choice”.

However, despite that easy familiarity, inevitably with spreadsheets, the cash forecasting process is often manually-intensive and time-consuming. And not only are they difficult to scale for a growing business, they can also be error-prone. As Paul Smithwood, Director of Product Development, Data & AI, Bank of America, notes, errors that are not detected can lead a company to believe it has materially more or less cash than expected. If that company then makes decisions based on those erroneous numbers, at best finding the offending cell is like looking for the proverbial needle in a haystack, at worst it can prove costly.

Conor Deegan
CEO, CashAnalytics

While some power-users will have set up macros (which come with their own issues) to automate parts of the process, generally it would be fair to ask, why not just get something better?

In Deegan’s experience, the reason many companies don’t evolve past the spreadsheet is that the other systems they may look to help manage the forecasting process – such as enterprise resource planning (ERP) and treasury management systems (TMS) – are designed for completely different types of activities, “and aren’t fit for the purpose of anything other than basic forecasting”. The case study in box 1 illustrates this point. 

Smithwood comments: “I see a lot of clients looking up future-dated invoices or payment runs and just plugging estimated receipt dates and values directly into the forecast, or making basic calculations from a rolling average from the last couple of weeks of cash flow, and that is their forecast,”. Clearly forecasting needs a better vision of the future. 

New vision

Enhanced technology is perfectly positioned to support cash forecasting in three key areas, notes Deegan. First, in the collection, management and manipulation of the data required to start the process. “In the majority of the projects we have worked on, the time spent on ‘cash forecasting’ prior to the implementation of CashAnalytics was often more about low-value manual activities rather than true forecasting or analysis,” he comments. “Technology can take this work off the table and quite quickly solve most of the base-level operational problems experienced.”  

Second, technology can transform the process of actually creating the forecast itself. Deegan comments: “The data collected from other systems and sources is just the starting point. The assumptions and adjustments made can transform this raw data into meaningful cash forecast data.” He continues: “In spreadsheets, activities such as analysing customer payment behaviour, and using this learning to adjust current invoice data to give a more realistic view of cash collection timing, is very difficult. So are other forecasting tasks such as transforming budgets and business forecasts into cash forecasts. Both can be greatly simplified using technology that is specifically built for cash forecasting, as opposed to generalist tech.” 

The third main area of support is where targeted technology enriches the analysis and reporting on the data. For example, how easy is it to carry out even quite simple actual-versus-forecast analysis in a spreadsheet, or drill down to transaction level of detail through a consolidated spreadsheet report? It can be done, notes Deegan, just not easily. “This type of analysis is a by-product of using a technology solution to support forecasting activity.”

Definition

But is there a danger that, as forecasting complexity increases (through, for example, the adoption of tools capable of intricate modelling), treasuries risk so-called ‘analysis paralysis’? “With every process using large amounts of data, this is always a risk; there’s always more you feel you can do,” Smithwood notes.

It’s important to accept that, with so many variables and unknowns, a cash forecast can never be 100% accurate. Indeed, he says while it’s prudent to try to improve forecasting accuracy, “it should only be to the point where the business is comfortable, from a risk standpoint, with its range of results”.

For Deegan, forecasting objectives, together with reporting and analysis output must be very clearly defined upfront. “While access to more analysis is never a bad thing, analysing the wrong thing, or not knowing what to do or how to interpret the output, is always a bad thing,” he cautions. “Analysis paralysis is simply a poor use of the technology, not a fault of the solution itself.”

Wider appeal

Assuming the correct setup and procedures are in place, two of the most important impacts of better cash forecasting, whether driven by technology or not, are a reduction in reliance on external funding sources, and improvements in working capital. Both are achieved through the more efficient use of cash flow within the normal business cycle.

However, says Smithwood, cash forecasts have many stakeholders – including various subsidiaries, and functions such as procurement, collections, AP/AR, tax and payroll –  with treasury usually extracting forecast data from each. Often these functions believe there is little benefit derived from what’s required of them, even seeing it as an imposition by treasury.

Anita Bubna
Senior Director Treasury, Flex Group

But, warns Smithwood, without buy-in from these stakeholders, treasury cannot do its job effectively, and the whole company suffers as a result. He suggests gaining CFO-level support to create incentives for these teams to acknowledge the value of their own input.

Some of the best forecasting processes he’s seen incorporate specific KPIs, measuring, for example, the accuracy of each stakeholder’s own forecast data, with results being reported up to the CFO. By escalating that data, in simplified form – perhaps through a dashboard – a clear view of the strengths and weaknesses of each forecast is delivered.

Next level

Due to the variety and depth of activities that ultimately feed into cash forecasting, the tools used to support it will also need to possess a depth and variety of functionality to truly take the process to the next level.

Banks hold the majority of data that their corporate clients use to forecast. They should be making it as easy as possible to access this data to aid cash forecasting. “Rather than forcing our clients to manually extract data and try to manage the process in Excel, we have built a forecasting solution called CashPro Forecasting that sits within our online banking portal that automatically connects to that data,” explains Smithwood. But clearly some clients want to push the boundaries, and for these, he says embedded analytics such as machine learning (ML), are a consideration.

With more bank and fintech providers delivering these advanced technologies, typically as a cloud service, the valuable information they can offer is increasingly attainable by smaller firms, says Smithwood. As might be expected, the quality and efficacy of software varies, and full evaluation of any new tool, comparing output to actuals, is advisable.

To initiate a programme of technological improvement, pragmatism is essential. As a simple guide to action:

While some specialised forecasting tools may not be appropriate for all companies, there is a clear case for most to move beyond the limitations of the spreadsheet. Indeed, where complexity and risk is rising, every finance professional can now choose to base their decisions not on the output of generic risk-laden software, but on insight derived from solutions optimised for their own unique needs.    

Erik Teiken
Interim treasurer and treasury consultant