When considering sources of data for a cash forecasting process we need to have clarity regarding what are the key inputs, and what purpose they serve. Obviously we need forecast information, but to get maximum value from the process, we need to measure our performance, and we do this by recording the actual cash flows (actuals).
So the question becomes two-fold; where do we get our actual data and where do we get the forecast data?
Actual Cash Flows
The goal with actual cash flows is to minimise human touch as typically this information already exists in bank files, financial systems and Enterprise Resource Planning (ERP) software. Looking a little further into each:
ERP / Accounts Systems
Most large organisations run on an ERP system and are often using one of either SAP or Oracle, but there are many others. These systems have built in data inputting/outputting capabilities as standard.
If, for example, your AP and AR information is contributing to your cash flow forecast, it makes sense to have these cash flows mapped to your cash forecasting software. This is normally done via an Application Programming Interface (API) or a file load, depending on your cash forecasting software provider.
There will often be a mapping exercise to pre categorise and group the data from the source system into the appropriate grouping for your forecasting system. For example you may be forecasting in weeks but your ERP records transactions at a daily level. That is just a case of grouping the days into weeks before loading the information into your forecast.
Bank File Downloads
Most banks can provide bank statements in electronic formats. The two main formats are BAI2 and MT940s. In general, but not exclusively, BAI2 tends to be favoured in the U.S. and MT940s in Europe. Each of these standards have their differences. In the case of MT940 there are structured and unstructured formats, relating to how the data is organised in the files.
Unfortunately some banks have slight differences in their implementations, particularly in the case of MT940 structured files.This can be an inconvenience if you bank with multiple different banks, but again that is an issue of implementation rather than a treasury problem.
Forecast Cash Flows
There are fewer data sources providing forward looking or future information. The knowledge of future trading exists mainly in planning departments. There are still some systems which can provide information however.
Treasury Management Systems (TMS)
Your TMS system will have treasury and financing related cash flows such as interest and capital payments on loans or FX cash flows for example. These cash flows can be integrated into your forecast data via an API.
Data Modelling Software
There are some modelling applications which allow you to extrapolate from previous information to produce a forecast. Whether this will be accurate enough to provide any meaningful data will depend on many factors including; regularity of business, forecast duration, quality of models, quality of input etc. In many cases models will only offer a reasonable forecasts for a short period.
When it comes to forecasting the main source of information will be the people in the various business units/subsidiaries/projects (depending on the organisation). It is critical that head office gets buy-in from all people involved and explains to them the value and importance of the forecasting process. Then it is up to the various controllers or people responsible to produce their forecasts and this information then needs to be collated by your forecasting software.
People bring the thinking power to the process, for example an ERP system might provide 10-15 days forward looking AP/AR information but it is the insight and experience of the collections team who are best placed to make a judgement call on any cash flows that go beyond that period.
The treasury team need to review existing systems to see what information they already have access to and what can be used in the forecasting process. The value of connecting these systems really needs to looked at on a cost benefit basis. Any connection of systems will involve an overhead. It will come down to ease of integration versus the amount of usable data retrieved.
Once all automated data feeds are connected it is then down to people. The final piece of the puzzle is getting the right people involved in the process to maximise the use of your organisation’s knowledge of future trading.
How to make bank reporting easier: transaction mapping rules
For a large and complex business, bank account reporting can be painful. A large number of accounts, each containing a multitude of transactions, can make the process of analysing cash flows unwieldy and time consuming. This equally applies to completing the actuals component of a forecast model, where the data ...John Champion - September 12, 2019
How to set up a best practice 13-week cash flow forecast
13 weeks is the most popular cash forecasting time horizon. This is because it strikes a balance between accuracy and range, allowing companies to forecast accurately enough to strengthen decision making, and offering a view into the future that stretches far enough to support medium-term planning. Although we’ve covered the ...John Champion - August 08, 2019
How electronic bank statements enable automated bank reporting
Electronic bank statements are the cornerstone of automated bank reporting. They enable CashAnalytics, as a specialised cash flow forecasting software provider, to offer live and consolidated cash balance reporting to our clients. Although electronic bank statements can be obtained with a simple request to the issuing bank, banks typically don’...Martin Gillespie - May 17, 2019