Intraday Cash Forecasting In A Centralised Treasury
Information needs in a group treasury are fairly limited and straightforward. The most important information is an accurate estimation of the closing balance at the end of the day with information by bank, bank account and company – all available as early as possible. Calculation of the present day’s closing balance is based on the previous day’s actual closing balance. The previous day’s actual figures, however, should include not only the closing balance but also transactions to enable the group treasury to recognise the previous day’s unfulfilled transactions, which are therefore due on the present day. An estimation of the following few days’ balances and significant transactions should also be available, especially if they are in a foreign currency.
If the information requirements in themselves are not very demanding, then why is intraday cash forecasting considered so difficult? First of all, collecting all the data that influences the closing balance is time consuming and laborious. Cash transaction data is often spread between various systems in several business entities, which may even be located in different countries and time-zones. These systems are rarely accessible though interfaces which could be automated and set with rules to direct the data into the right field in a report or shift the due date to the estimated date of payment.
Manual collection of the data is usually very challenging. Some of the business units are not keen to reveal their current cash situation; some of them may just ignore enquiries. Frequently, the data disclosed is not accurate and changes in figures are not reported to the group treasury.
Even after the data is collected, it is rarely uniform. Transactions may have exceptional payment instructions concerning the actual payment day or payment account. Banks may have special rules for certain types of payments when calculating balances, defining value date for interest calculation, or defining when the cash is actually available. Some of these rules are regular; some are one-of-a-kind; and some are even against treasury’s rules and policies.
This complexity of data and lack of functional interfaces make it impossible to manage intraday cash forecasting with ordinary spreadsheets. They may be suitable for ‘happy day’ scenarios in simple environments but insufficient in complex real-life settings.
When trying to solve the above-mentioned problems concerning the collection of cash flow data, it is crucial to find a place where all the data is gathered. In Finland, where almost all company payments are electronic and bank account statements are posted and electronically transferred to financial accounting, solutions for intraday cash forecast are based on e-banking software.
Data concerning today’s payments
In most Finnish companies, payment data is usually located in multiple systems that are all connected to e-banking software, the focal system in the payment process. Of course, the transaction data has different information content in different phases of the process.
|ERP||A/P composes a payment list for the day.
A/R provides forecast data on incoming cash.
|TMS||TMS provides data on incoming and outgoing treasury payments.|
|Payment factory||If payments are made on group level in a payments factory, the payment instructions are transferred to the e-banking software to be sent to banks.|
|Payroll system||Wages are usually considerable payments and paid with e-banking software.|
|E-banking software||Manual entry of extraordinary payments is usually performed directly to the e-banking software. These include express payments, intra company transfers, payments that are not in any other system, etc.
Direct transfers from A/P to banks are also performed through the e-banking software.
Up-to-date information is always available because the payment process is run with e-banking software, whether a transaction is approved, ready to be sent or has already been sent to a bank. Data about bank balances and transactions on bank accounts can be received, e.g. a closing balance, real-time balance, bank account statement or transaction query. Finnish banks also provide review data of foreign receivables, i.e. data on incoming foreign payments that are available only after a bank’s float.
In addition, it is easier to find ways to collect the required data. Designing interfaces in order to obtain accurate and information rich data from single software’s database is also relatively easy. These interfaces should include user definable rules to direct and process the data according to the user’s needs.
Data concerning the following few days’ forecast
The banking software usually includes only the cash flow data concerning payments due today. Therefore the following few days’ forecast should also receive cash flow data from accounts receivable, accounts payable and other systems containing cash flow data. For this purpose, almost all ERP systems widely used in Finland have a de facto standard for exporting cash flow forecast data. It is relatively easy to create an interface based on this.
All required data is not always accessible electronically for various reasons though, e.g. system disparity and data security. In these cases, a web-based entry tool is required. With the entry tool, the most important issue is user discipline in the business units. Disciplined and reliable use of the tool can be enhanced with tight control and various incentives, such as offering cash flow reports tailored to business units’ specific needs.
Previous day’s actual figures
There are basically two alternative methods in allocating cash flow data into report rows or columns. In local Finnish bank account statements in TITO (tool for incremental threading optimisation)format, all transactions have a transaction code on which the data breakdown is based. If MT940 format statements have transactions codes, they are applicable as well (this depends on bank’s services). Furthermore, if transactions on the bank account statements are posted in the banking software, the previous day’s cash flows are even easier to break down into separate cash flows, such as treasury cash flow, operative cash flow in, foreign payments cash flow out according to the posting information.
Bank account balances are easy to obtain, since bank account statements for accounts significant to the group’s liquidity are included in e-banking software. Forecast tools should then be able to group the bank accounts according to the treasury practices, e.g. by cash pools.
Since intraday cash forecasts must follow the payment process closely, data must be brought into the forecast tool several times per day. When operating at this pace, extra care must be taken to ensure that no cash flow is omitted or duplicated in a report.
Processing the data must be as automated as possible. All interfaces must be run automatically and all data flows must be automatically allocated to their rows or columns in the calculation. All data must be stored in a way that enables analysis even down to transaction level. Rules that direct cash flows to the correct rows or columns should be possible to define in a way that supports special and provisional rules.
As stated at the beginning, the most important information is accurate estimation of the closing balance as early as possible. Since the report should reflect the payment process, a simple listing of estimated closing balances does not suffice, but there should be a clear overview of the situation at hand. As discussed before, reports on the present day, previous day and following few days are required with slightly different layouts for euro and FX.
An example of a basic present day’s euro report
|Bank A/C||Opening balance||Outgoing domestic payments||Outgoing foreign payments||Treasury payments||Estimated domestic receivables||Estimated foreign receivables||Withdrawals||Deposits||Estimated closing balance||Real- time balance|
|Bank A Subtotal|
|Bank B Subtotal|
|Bank C Subtotal|
The table above is an example of a very basic report. The report reflects the group’s payment process so it should always be tailored to the group’s information requirements. Typical fields of information would be: amount to be paid today but not yet sent to the bank, money transfers between the group’s own bank accounts, or breakdown of payments divided according to the source system. Reports should also be easily viewed according to the company, group of companies or cash pools.
When reports are browsed on screen, it must be possible to see the data even down to transaction level. Transactions should hold as much data as possible, even beyond basic data, such as due day, amount, currency and payer information. There should also be important information, such as cash flow group to which the transaction belongs to, counter value in the group’s home currency, where the payment originates from, when it is modified and who has entered the transaction, and who is the payee.
Since the system reflects all aspects of the payment process, the reports should also be multi-faceted. In a good intraday cash forecast tool, it is possible to make ad hoc reports easily and flexibly. These can be achieved by using an OLAP (online analytical processing) cube and its data mining techniques. OLAP as a tool is seldom user-friendly so the intraday forecast tool should make creating, defining and saving OLAP queries for everyday use easy for non-technical group treasury personnel.
The technical environment in local banks and in the group treasury itself is crucial to an intraday cash forecast system. A focal point is the e-banking software’s ability to communicate with all of the group’s banks and to include a sophisticated automated forecast tool. The forecast tool should have interfaces from all systems containing the group’s cash flow data as well as a good feature for manual entry. With this kind of system it is possible to follow the present day’s payment process accurately and obtain a closing balance early in the afternoon without manual labour. A forecast tool with powerful interfaces and good data controlling and processing capabilities would enable efficient intraday cash forecasting in a centralised treasury to gain extra earnings with no extra labour.