Cash flow forecasting need not be a dark art if the right technology is at hand. But what is the right technology? And are Excel spreadsheets an absolute no-no?
Making statements about future events has been an enduring fascination for mankind – not that such predictions have always been well-received or been taken seriously. Arguably, the use of today’s complex computational algorithms applied to historic data to determine the direction of future events is more accurate than the animal entrails, hallucinogens or tea leaves of yesteryear. But in any facet of life, whether using the latest technology or resorting to witchcraft, a forecast will always contain an element of uncertainty.
In business, the intelligent application of the right knowledge, understanding and technology can go a long way towards reducing error, to the point where the results are now considered an essential part of the commercial process. One of the most important commercial uses of prediction is carried out by a company’s treasurer or cash manager who can draw upon a number of sources of data (via a TMS, ERP or bank feed, for example) to produce a cash flow forecast – the ongoing prediction of cash inflows and outflows over a period of time – an essential part of the liquidity and risk management process.
The results of any data gathering exercise require some form of analysis to make sense of it, and in this case the techniques applied often take the form of simple moving averages (picking out likely future cash flow positions from historical data), exponential smoothing (the same but with various weightings added to the data), regression analysis (spotting connections between forecast and historical data) and distribution modelling (using previous data patterns to extrapolate different future patterns).
By applying forecasting tools over a range of timescales (such as yearly, quarterly or monthly) the analysis of data can help to secure improvements in a range of company-wide interests including the overall treasury position, investment yields, cost of borrowing, short-term liquidity, payment flows, the use of bank facilities, hedging and hedge accounting decisions. A company may also use data analysis techniques to give longer term or strategic financial insight into areas such as pre-deal M&A analysis, divestitures, and stock buyback.
In a world of financial turmoil, being in a position to minimise working capital demands and the drawing down of credit facilities or other borrowings, whilst being able to assure liquidity and funding continuity, is an absolute must for most businesses.
In its simplest guise, data is typically collected from around the organisation, often as emailed spreadsheets from the subsidiaries with this being consolidated within a central forecast spreadsheet system. Establishing precisely which sources of data are needed, their collection frequency and who receives the results will usually depend on top-level variables such as treasury policy and organisational complexity.
At its most authoritative, cash forecasting can draw upon and consolidate input from a combination of enterprise-wide data providers such as individual entities, business units and subsidiaries, drawing upon and modelling data for diverse aspects such as bank account balances, the maturity schedules of financial flows, recurring outflows (such as payroll) and inflows of cash, and accounts payable (AP) and accounts receivable (AR) figures. Although somewhat unusual, data from credit departments may also be considered to give greater AR accuracy.
Clarity in a complex world
When establishing a cash forecasting regime, Dan Blumen, co-founder of the US-based consultancy, Treasury Alliance Group, points to a number of key issues that treasurers need to be aware of. These include the time horizon for the forecast, the complexity of the supply chain, the degree of centralisation within the organisation, and the nature of the company’s business model or models. These issues, he notes, are complex, technical and company-specific and when developing a cash forecasting process he believes that it is essential for treasury to maintain “clarity in its objectives and simplicity in its approach”.
In his paper, ‘Keep it Simple: Five Steps to Excellence in Cash Forecasting’, Blumen acknowledges that developing effective cash forecasts can be “difficult and time consuming”. He has come up with five distinct pointers that may ease the pain. The list includes: keeping the process simple (the objective is a cash forecast, not a weekly balance sheet and cash flow statement, he says); working towards full co-operation (data quality is in the hands of the business units – keep them onside); keeping the lines of communication open (language and cultural differences can disrupt progress – standardise processes as far as possible); maintaining structure (keep units and subs onside by setting up an efficient account structure, serviced by a reliable network of banks, to meet their immediate funding needs).
Blumen’s final pointer concerns the effective use of technology where, in combination with well-founded policies and processes, implementing the right tools in the right place, for the right people, can create desirable levels of cash forecasting consistency, efficiency and accuracy. Indeed, by enabling the aggregation and reporting of data at an enterprise-wide level through integrated technologies, treasurers can have access to more accurate information from subsidiaries, be able to strengthen interdepartmental communications and co-operation via simpler and far easier reporting, and ultimately raise the game in terms of the financial performance of the group.
So what’s wrong with Excel?
There is no doubt that spreadsheets remain king of the cash flow forecasting tools. Numerous industry surveys have been conducted on the topic, and at least 75% of respondents tend to favour spreadsheets over their TMS or ERP.
Clearly spreadsheets remain an integral part of the function and operation of business. They possess a familiarity that most users are immediately at home with, can be extremely rapid and agile, and can easily convert numerical data into easy to read graphical forms such as charts and graphs. Also, as technology goes, they are as inexpensive, accessible and as widespread as it gets.
And although spreadsheets do carry inherent risk – apparently many businesses are happy to accept that. For example, 90% of Asian treasury professionals recently polled by Reval in Hong Kong and Singapore indicated that they were dissatisfied with their ability to forecast their cash positions, only 7.5% identified operational risk as an area worthy of their attention. The survey of 80 finance and treasury executives from companies across multiple industries shows that whilst so few respondents are fully satisfied with their forecasting processes and quality of data, the fact that operational risk is not a major issue suggests that businesses are comfortable running their business on spreadsheets.
According to Tony Singleton, APAC Managing Director at technology provider, Reval, many of the region’s treasurers are still using spreadsheets for forecasting, leaving treasury “potentially exposed” to inaccurate global cash positions and forecasts. “Four years after the global financial crisis, I would encourage CFOs to consider how such a disconnect puts their overall business at risk,” Singleton says.
That spreadsheets contain an inherent risk at the hands of the operator is clear. If care is not taken, a materially incorrect value can be derived from an application and this can be used to make a related decision if the error is not picked up. Underlying errors can arise at the point of data input (accidentally or on purpose), mistakes can be made in the logic of the spreadsheet itself, or relevant updates to data may be omitted.
Some of the errors found in spreadsheets have amounted to well-publicised substantial financial and reputational losses: C&C Group, a Dublin-based alcoholic drinks maker, saw shares plummet 15% in 2009 after admitting that, due to a spreadsheet error, it had mis-stated quarterly revenue, claiming a 3% rise when in fact revenue had dropped 8%; in 2003, the US-based Federal National Mortgage Association (aka Fannie Mae) underestimated shareholder equity by $1 billion on the back of spreadsheet error; Fidelity’s Magellan Fund overstated a share distribution amount by $1.3 billion when a missing minus sign turned a $1.3 billion loss into $1.3 billion profit; and in 2003 the Canadian power company, TransAlta Corporation, suffered a $24m write-off through a ‘clerical error’ when someone pasted the wrong figures into an Excel spreadsheet whilst bidding on electricity contracts.
Another issue with Excel is that it doesn’t readily facilitate an audit trail over every data change which can lead to version-control problems. In cash flow forecasting terms this makes it more difficult (although not impossible) to ensure proper corporate governance, which in today’s compliance-heavy environment should be a concern. Additionally, if cash flow forecasts generated out of Excel are, for example, used to support FX hedging, subsequently demonstrating the necessary documentation around underlying exposures can prove difficult.
With all this in mind, a company might consider putting a spreadsheet management or governance programme in place, with proper ownership of that programme. Even so, automating technologies may do a better job.
Why not automate?
In November 2010, research firm, Aberdeen Group, published a study, Operational Cash Management: Streamlining Processes to Unlock Liquidity, which put cash flows from operations front-and-centre for 51% of all survey respondents. The paper also revealed that ‘leading companies’ were 47% more likely than their competitors to automate reporting of cash held and forecasted. This, said the report, ‘provided them with more accurate and timely information, enabling them to gauge their cash position and assess liquidity requirements on a regular basis’.
The apparent lack of uptake of more integrated systems for cash forecasting may be down to something as simple as the lack of a clear cost/benefit justification or an unwillingness to yield to new ways. But in 2010, a PwC global treasury survey (‘Treasury in the Crisis; Put to the test – can the crisis make treasury stronger?’) highlighted the need for ‘faster access to data via integrated platforms’.
Automation, it said, could ‘improve accuracy and timeliness of cash flow forecasts’. By automatically harvesting the required external and internal data (bank balances, treasury cash flows, AP and AR data and so on), it could at the very least allow cash managers to focus on more pressing matters rather than trying to tackle mundane issues such as correcting inaccuracies or finding out why data has not been submitted by certain units. It is a compelling argument but one which Aberdeen Group, in its 2011 paper, Liquidity Management: Leveraging Technology to Improve Cash Forecasting, added an important factor to the accessibility of data.
The report’s author, Senior Research Associate, Scott Pezza, wrote that with multiple banking relationships (each with proprietary systems and connections) and multiple in-house systems to manage, the processes used to interact with these partners and systems “can impact on a company’s ability to obtain a comprehensive view of current positions across the board”.
Citing data drawn from Aberdeen’s Operational Cash study, Pezza said top performing companies were 33% more likely to have standardised banking processes “enabling them to handle interactions with different banking partners on an identical basis”. The majority had an ERP system but those that also had a TMS and other electronic tools capable of facilitating data flow, such as electronic billing presentment and payment (on the AR side) or electronic invoice presentment and payment (on the AP and AR side), provided further differentiation between the top performers and the rest.
Senior Manager, Treasury Team
LG Electronics is a global technology innovator. Through its 117 operations and four business units – home entertainment, mobile communications, home appliance, and air conditioning and energy solutions – it achieved sales of $49 billion equivalent in 2011. The company operates four regional treasury centres, its Netherlands-based European operation assuming the role of global treasury centre and covering Europe, Middle East, Africa and CIS (40 subsidiaries) with pooling operations for America and Asia. It has in place two multicurrency pools with 18 currencies.
“Cash flow forecasts take a special place within the company as most of our business activities are translated into cash,” explains Natalia Kang, Senior Manager, Treasury Team, European shared service centre for LG Electronics. However, because LG’s forecasts rely on the provision of accurate data by multiple units, Kang admits that it can be “very difficult to fulfil the forecast”, the slightest deviation of results impacting directly on the final results. “Unfortunately we don’t have a manual explaining simply how to make a forecast to fulfil our company’s cash flow plan,” she says, adding that there is therefore a need to “reinforce communication and co-operation amongst all departments”.
The list of data sources includes a combination of LG’s Oracle ERP system and its SunGard AvantGard Quantum TMS. The TMS is the major source of bank account data, whilst the globally implemented Oracle ERP is used to extract data for areas such as AR, AP, payroll and tax. “Currently we have reached the stage that all our subsidiaries are using the same technology,” notes Kang. This means all parts of the company can use the same format and provide data in a timely manner. “It took us time to achieve it, but currently all subsidiaries manage to submit the data on time.”
As part of LG’s cash forecasting regime, data analysis naturally provides an important management tool, bridging the gap between business needs and the reality of the company’s day-to-day activities. As might be expected, the company uses a range of software and systems “to make the analysis activity easier”. However, spreadsheets are still used by many of its subsidiaries, Kang reporting that “according to our colleagues, it is much more flexible and an easier way to simulate all data before inputting into the system itself”.
This perhaps says more about the availability of user-friendly tools than any reluctance to move away from Excel, LG having already automated processes around data collection, data input and report extraction, and only partially around the analysis process.
In terms of data analysis procedures, the company often uses regression analysis techniques, checking and comparing forecasted data against actual data. “On a monthly basis we try to analyse the forecasted data versus the actual data of the previous month,” Kang explains. “From our ERP we can extract both data sets automatically, but the explanation or reason behind any deviations is extracted manually using human knowledge and skills.”
Once data has been harvested and analysed, Kang warns of the dangers of being too optimistic or too pessimistic in cash flow forecasts. An overly positive forecast can lead to the company being unprepared for any negative deviations. A swing too far the other way could prevent the company from leveraging its resources to full extent. Either way, she notes, extremes could force losses in interest income, disruptions in investment timing, or unnecessary changes in business terms (such as modifying payment terms or demanding early collections/payments).
Whilst she acknowledges that it is not possible to guarantee 100% accuracy within cash flow forecasting, Kang is happy that the level achieved by LG is constantly improving “by a thorough analysis of historical data and experiences”. She explains that LG does not have a fixed frequency of checking and updating procedures but these are checked “more or less regularly” during the monthly analysis cycle. Here they will be assessed to see if the current model is still adequate or if increases and improvements in accuracy could be gained from procedural updates – “but only if it is really necessary and required”.
For LG, automation is a real benefit but does not remove the need for human input. “Technology can do a lot to help us to carry out the forecasting process,” Kang states. “But valuable human input is still needed in order to make an accurate forecast; technology cannot provide background explanations to data anomalies.”
A visit to the European Spreadsheet Risk Group (EuSpRIG’s) website www.eusprig.org will enhance anyone’s understanding of the potential risks that lay in waiting for spreadsheet users. Despite the availability of automation tools (many banks, ERP/TMS system and niche vendors offer cash flow forecasting solutions for corporates), the spreadsheet persists. Indeed, on the EuSpRIG site, an academic paper lodged in Cornell University’s library and attributed to Mel Glass et al, offers the following learned quote: “...spreadsheets will always fill the void between what a business needs today and the formal installed systems...”.
Any system that is deployed for cash forecasting has the primary task of compiling and aggregating data, typically to a centralised database. As mentioned above, information may come from various sources such as a TMS, accounting system, ERP system(s), multiple banks and various subsidiaries and other entities within the group.
The different entities and subsidiaries within a company may have different cash forecasting requirements and models, these being dictated by organisational structures, business cycle patterns, cash management policies and the management information infrastructure that delivers the forecast information.
To ensure consistency, the central treasury could impose a standardised cash flow forecasting model across the entire group, but this can increase training requirements and initial set-up time and is, frankly, difficult where the source is beyond the direct control of the organisation.
Variations on a theme
Maintaining multiple models based on each subsidiary’s business operation arguably delivers more buy-in (and therefore better results) simply because each set of contributors will better understand their own model and needs. In this case, whilst the underlying data will need to be consistent with central treasury requirements, the solution should enable forecast templates to be customised for subsidiaries allowing, for example, different local languages or business terminology to be used.
Incorporating variables in this way can benefit central treasury, at least in the short to mid term, especially following M&A activity because any existing formats used by the acquired business can be consumed more readily and thus not slow down its contribution to the enterprise-wide cash flow forecasting process.
For all subsidiaries, a solution that supports workflow processes will ensure that all submit their cash flow forecasts on time. If workflow can be driven at subsidiary level, local forecasts can be entered and approved prior to submission, helping to ensure the accuracy of the aggregated figures. It should also be possible for the central treasury to incrementally fine tune the accuracy of all underlying data if the solution facilitates the reconciliation and analysis of actual cash flows versus forecasts.
The state of the art in liquidity and risk management is moving more towards an intraday risk view built on a centralised model with a single data source, says Alexander Dorfmann, Head of Risk, UK and Ireland, for business analytics software and services firm, SAS. Because no large organisation has one database, the challenge here for liquidity forecasting is being able to link into different data sources and to pull out the data at the point in time that it is needed. The technology that can be used to achieve data integration depends on the underlying data infrastructure, says Dorfmann.
His preferred way is to build direct links into the primary data sources – the ERP and the TMS, for example – because this gives access to the original data including prices and cash flows. “If you start moving away from the original sources, say to a data warehouse, what you can find is that the data is already pre-aggregated; it has been subjected to rules that, for example, alter the way it was booked in the front end system and how it appears in the data warehouse.”
In order to deliver cash flow forecasts from a data warehouse, certain assumptions or modelling techniques will be applied to derive what Dorfmann calls an “educated guess”.
If, however, data can be captured from source systems and processed in its “de-composed” form (data that has not been pre-aggregated), he argues the results will be more accurate simply because far more granular data is available.
In providing granular data from multiple sources and then applying numerical modelling and analytical tools, new patterns of market behaviour can be identified which can lead cash flow analysis into new levels of understanding, which in the current environment is no bad thing.
If granularity and system agility is required to produce clean, timely, accurate and even inspired data analysis then a major overhaul of a company’s IT infrastructure – at least to create the connectivity – may be required. This does not sound like a realistic proposition for many businesses, given the cost and the disruption it would cause. But, says Dorfmann, the solution to this lies in the implementation of an overlay in-memory database and risk management system.
By accessing data from its primary source and holding it ‘in-memory’ it reduces query times, providing faster and more predictable performance. And by overlaying an existing data structure and data warehouse with an in-memory database, it is therefore possible to extract transaction data from primary sources and the data warehouse can still be used to supply organisational and static data, all on a near real-time basis.
However it is achieved, accuracy of results ultimately ensures that the business understands its current position and, importantly, where it can and should go next. Appropriate technology can ease the process of accessing, collating, aggregating, analysing and waiting for the data that drives these decisions, but it will always require an experienced and knowledgeable treasurer or cash manager to explain the anomalies and to turn information into action.