... Direct Liquidity Planning – Even Without Using SAP
Liquidity planning ensuring solvency at all times remains a hot topic for German treasury departments, representing an essential aspect of cash and liquidity management.
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 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?
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.
The additional granularity needed may be obtained by using tools, such as the Liquidity Planner (SAP-LP), to analyze the current cashflow based on SAP FI bookings. It generates a current cashflow statement by chain-analyzing accounting vouchers: starting with bank accounts, the tool evaluates bookings and amounts and allocates these to certain liquidity items using the last account assignment. However, not all companies use SAP or they have a very heterogeneous and/or decentralized SAP environment. Such companies should consider alternatives for generating a current cashflow account.
Companies with many data sources would need a tool that is easy to implement in their current system landscape and that is useable across various systems. So-called ETL (Extract, Transform, Load) tools that can be integrated flexibly into the IT architecture and that are capable of bundling data flows from various source systems offer an ideal workaround. Good examples of ETL tools are Pentaho, Talend or Alteryx.
The challenges that arise due to non-SAP-based or heterogeneous SAP landscapes may be circumvented by using an ETL analysis tool that is flexible and works across different systems. After having extracted the data from the relevant source (i.e. any of the conventional database systems or flat files), the data is then cleansed and transformed. The whole thing should be thought of as the stringing together of many macros that are applied to a huge mass of data one after the other.
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 indicator 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 disclosed 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 payment 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 Alteryx 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 automate 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.
Source: KPMG Corporate Treasury News, Edition 91, May 2019
© 2019 KPMG AG Wirtschaftsprüfungsgesellschaft, ein Mitglied des KPMG-Netzwerks unabhängiger Mitgliedsfirmen, die KPMG International Cooperative (“KPMG International”), einer juristischen Person schweizerischen Rechts, angeschlossen sind. Alle Rechte vorbehalten.