If the company does not have an up-to-date statement of actual figures, the Treasury department often borrows from its neighboring department the traditional controlling data. Complementing the planning like this makes sense but if controlling data is the only source, this should nonetheless be looked at critically. The trouble is that data coming from Controlling looks at the data from a different perspective, i.e. “what should be” as opposed to Treasury’s “what will be”. For example, an aggressive P&L planning could result in a liquidity planning that is too risk-prone. As a result, the company will require short-term capital that can only be covered at a certain expense. So how can Treasury build a realistic and up-to-date cashflow statement?
Usually, corporate Treasury departments use the approach of direct liquidity planning. For this, budgeted operational items are calculated separately, such as cash receipts from turnover, payments to vendors or payroll. Current cashflows determined according to the direct method are often required for developing planning approaches, extrapolations, reports and optimizations.
The determination of historical cashflows has two main goals: on the one hand, an accurate statement of actual figures and the resulting transparent cashflow accounting helps to assess the accuracy of the liquidity planning. Without them, it becomes hard to verify anomalies and trends. The effort it takes to reconcile bank statements with underlying business transactions is akin to the work of a detective.
On the other hand, a detailed and up-to-date statement of actual figures with a value date display can form a starting point for liquidity planning using extrapolation methods or statistical forecasting models. In doing so, historical cashflows can be extended into the future using driver-based extrapolation calculations. Extrapolation approaches also offer a starting place to introduce a higher degree of automation, for instance, by introducing predictive analytics. Of course, extraordinary effects, such as upcoming investments still have to be considered individually.
If the company does not have an up-to-date statement of actual figures, the Treasury department often borrows from its neighboring department the traditional controlling data. Complementing the planning like this makes sense but if controlling data is the only source, this should nonetheless be looked at critically. The trouble is that data coming from Controlling looks at the data from a different perspective, i.e. “what should be” as op-posed to Treasury’s “what will be”. For example, an aggressive P&L planning could result in a liquidity planning that is too risk-prone. As a result, the company will require short-term capital that can only be covered at a certain expense. So how can Treasury build a realistic and up-to-date cashflow statement?
Many companies use extracts from bank accounts that they sift through according to rules, such as document text or business transaction code. However, this data is often not informative enough: for instance, bank account extracts do not differentiate between OPEX and CAPEX.
In a first step, the necessary data is loaded by directly connecting to the source system, e.g. an ERP system. The tool is configured in such a way that it establishes a connection between the individual accounting steps, so that the accounting chains can be traced with the help of the relevant accounting proof. As a result, only the accounting streams that relate to payments are considered. A cashflow item is assigned to each general ledger account so that the account at the opposite end of the accounting chain to the bank account serves as an indi-cator of the cashflow item.
A classic example for such a voucher chain is: Materials and supplies – Trade payables – Bank clearing – Bank. The analysis starts from the bank account and winds up with the general ledger account Materials and supplies. If this general ledger account is allocated to the liquidity item “Vendor payments”, the amount is dis-closed in the cashflow statement as such. Further transformation steps make sure that the amount is added or deducted as required. Because of the detailed allocation to general ledger accounts it also becomes possible to make the liquidity items more granular (in this example, “vendor payments” could be broken down into pay-ment for raw materials, payment for finished goods, etc.).
This approach mimics SAP-LP functionality. The result at the end of the processing is a modified database. The output could be either a database or a flat file so that further processing is possible, for example in a reporting tool.
ETL tools such as Alteryx are especially handy for a data transformation across systems because it enables complex data processing steps without any programming knowhow. The presentation and application of Alter-yx takes place with user-friendly user interfaces. Quality is ensured at all times with transparent processing steps. Even large masses of data can easily be crunched with this approach. Task controls help to highly au-tomate the data processing. Hence, there is no more excuse for not doing a regular data extract.
The output of the ETL process can now be used as consistent source for an up-to-date cashflow in the course direct liquidity planning. The extrapolation of the current numbers may serve as extension of an existing bottom-up planning approach, where planning input from different planning units is aggregated (e.g. Purchasing, Sales). This top-down expansion represents the possibility of a reference value and thus provides the possibility to validate and if necessary, adjust the procedure.
Extrapolation is a projection of the actual figures using historical payment curves. For instance, it is possible to deduce budget values for individual liquidity items by applying foreseeable trends in form of percentages. Other approaches come from the area of predictive analytics where budget values are forecast using statistical methods, e.g. by using a time series analysis.
The current cashflow analysis serves as the base for a direct liquidity planning and it can be done even without SAP or SAP-LP. By applying ETL tools such as Alteryx, companies not only maintain a high degree of flexibility in regard to source data but also for data processing. This solution is easy and cheap to implement and is low maintenance. In the end, using an ETL tool for a current cashflow analysis is in general neither better nor worse than the use of SAP-LP. However, it offers an interesting alternative for companies without a comprehensive SAP solution or whose IT systems are very heterogeneous.