How to set up Intraday Bank Statement reporting in SAP
Intraday bank statement (IBS) reporting, a service that your house bank can provide your company, enables your cash manager to understand which debits and credits have cleared on your bank accounts throughout the current day. We explain how to implement it in SAP.
Intraday Bank Statements offers a cash manager additional insight in estimated closing balances of external bank accounts and therefore provides the information to manage the cash more tightly on the company’s bank accounts.
Compared to intraday bank statement reporting, end-of-day (EOD) bank statement reporting is only available the next calendar day. The information therefore always comes too late to be meaningful for cash management decisions – apart from providing an opening bank balance for the next day.
Business rationale behind IBS reporting
So, why would a Treasury typically start implementing IBS reporting in its cash management processes?
1. Cash visibility: In general, IBS reporting will provide your cash management function an additional tool to improve cash visibility. Achieving cash visibility intrinsically might not be a goal of its own, but by achieving visibility, the cash manager now has information to make certain economically relevant decisions in certain situations.
2. Managing cash: By creating cash visibility, we now have an opportunity to manage cash on our accounts in an intelligent way. In case we estimate a positive closing balance, we could decide to invest this surplus in, for example, a money market fund or overnight deposit to earn some return. In case of an expected deficit, we need to fund the account to ensure no EOD negative position happens. This can be achieved by transferring funds from another bank account (in same currency), swapping funds from another bank account (in different currency), or funding it from, for example, a facility drawdown.
3. Reduced risk of delinquency: As we now implemented a process to increase control over our bank balances, we now have less chance of e.g. rejected payments due to insufficient available funds and therefore less chance of being delinquent on certain obligations to pay.
4. Reduced requirements on overdraft facility: By reducing the chance of having insufficient funds on our account, the overdraft facility requirements can also be reduced.
5. Timely clearing of open items: IBS can also be used to clear off open items throughout the day, as opposed to only rely on clearing from EOD statements. Benefit here is that KPI’s like days sales outstanding (DSO) will improve and that reconciliation effort is spread out more through time.
This article will now only focus on the cash management side; the IBS reconciliation process may be discussed another time. If you like to know more about bank reconciliation using intraday statements, feel free to reach out to us. We have a pre-developed solution that we can implement at your side.
There are a few design considerations that need to be looked at before attempting to implementing this solution in SAP.
1. Reporting formats: MT942, CAMT.052, BAI2 are formats that can be imported by SAP standard and are also supported by most banks to some degree. There may be some informational or structural benefits that one format has over the other which should be considered in the design.
2. Reporting frequency: It is possible to agree with the bank on reporting frequencies of IBS. Ten times through working hours? Or one time only, half an hour before the payment cut-off time? In most cases, the bank will charge a fee for every statement it sends, so this should be considered in the design.
3. Delta vs cumulative reporting: As it is possible for the bank to report multiple times a day, it is important to understand how the data is reported. There are two methodologies. In case of delta reporting, only new transactions are reported, relative to the previously distributed IBS. Alternatively, there is cumulative reporting, where all booked items are reported on the statement throughout the day. Delta reporting typically means that the data in your SAP system needs to be appended for every new IBS. Cumulative reporting means that every time you process an IBS in SAP, the data needs to be rebuilt completely.
4. Data integration: The intraday data as provided by the bank needs to be integrated with already existing cash-relevant data to compile a proper reporting view of estimated closing balance for the day. This needs to happen in the cash management module of SAP (FF7* reports). The design of the structure of the cash management report should be carefully aligned with the liquidity structure (i.e. ZBA structure).
5. Prevention of duplications: Integrating the intraday data with existing data should be designed with data duplication in mind. It is paramount that the data on the same cash movement is not counted twice from two sources and data duplication should always be prevented while designing the solution. For example, if we are not careful, a payment flow can be included in the report twice, once from the intraday statement when it is debited and once from the payment in transit GL in the SAP administration. This would result in a skewed estimated closing balance.
Ultimately, the goal here is to receive and upload intraday bank statements throughout the day and to load cash movement data into your SAP system. This cash-relevant data needs to be made visible through the cash management reports so that the cash manager can better estimate EOD balances and make intelligent decisions related to funding accounts or investing excess funds.
Setting up Intraday Bank Statement reporting in SAP
We will now go into detail on how to setup intraday statement reporting and assume that the basic FI-CO settings for e.g. the company code are already in place. We also assume that the EOD bank statement process has already been implemented. To learn how to set this up, please read this article on virtual accounts.
It is important to understand that intraday statement data is converted into so called ‘Memo Records’ once loaded in SAP. These memo records can be visualized in the cash management reports (FF7AN/FF7BN). We will now explain the necessary settings on the cash management report section to ensure that the intraday data can be made visible in these cash management reports.
Define planning levels
First, we need to define a planning level; a label that is assigned to all cash movements as reported on the intraday statement. The planning level is used to structure the data in the cash management reports.
The level is a two-digit label, freely definable. We set it to C1.
The sign we need to set to blank as cash movements reported on this level can be both positive and negative.
The source will be ‘BNK’. This ensures that this planning level is reported on both ‘cash position’ and ‘liquidity forecast’ in the FF7AN/FF7BN reports.
The descriptions are freely definable. We define it as ‘INTRADAY’.
Define planning types
A planning type is a label under which a ‘memo record’ is stored on the SAP database. A planning type is subsequently linked to a ‘planning level’ to ensure the underlying data can be visualized in the cash management reports.
First, we define the planning type label: we set it identical to the planning level; C1 and link it to planning level C1.
We need to define an archiving category. This defines the data retention period of the memo records. If the period is exceeded and the reorganization program is executed; the memo record data will be cleansed.
The auto-expiry option defines whether the memo record will expire automatically and becomes invisible in the cash management report output. This needs to be enabled. The idea here is that the intraday statement data will be superseded by the EOD statement data once this is loaded after midnight next calendar day. To ensure we do not double count identical cash movements from both sources, the intraday data needs to be expired.
Also, a number range and description need to be entered. No specific functional considerations are needed here.
Define grouping and maintain headers
A ‘grouping’ is a label that is used to structure the cash management report data in a meaningful manner for the user. The grouping can be selected in the cash management reports and is going to dictate how the data is shown to the user.
We will configure a grouping ‘CASHPOS’.
Under the grouping we can now maintain the structure of the cash management data. For our report, we are including two components. The first component is the planning level., the second will be the GL account under which we record our bank account balances. This is the GL account we typically maintain in the house bank account data (table T012K, transaction FI13, NWBC).
For the first component we are going to add an entry as follows:
The grouping we set to ‘CASHPOS’.
The type we set to ‘E’ for planning level. Now we can define a planning level that is going to be relevant to our cash management report output.
We set the selection to C1 (our intraday planning level we defined earlier).
This setting will ensure all cash management data as stored under C1 planning level is going to be selected in the report output.
For the second component we are going to add an entry as follows:
The grouping we set to ‘CASHPOS’.
The type we set to ‘G’ for GL Account. Now we can define the bank GL account that is going to be relevant for our cash management report output.
The selection we are going to set to a GL account is saved in our bank account entry in table T012K.
This setting will ensure all cash management data as stored under the GL account and relevant for our bank account will be selected in the report output.
The combination of these two lines is going to ensure that we will only see the C1 data for our one bank account. We can add multiple lines to increase the scope of the reports output.
Importing and processing bank statements
We should now be in good shape to import our first intraday statements. We could download these statements from our electronic banking platform. Also, we could be in a situation where we already receive them through some automated H2H interface or even through SWIFT. In any case, the statements need to be imported in SAP. This can be achieved through e.g. transaction code FF.5. The most important parameters to understand here are the following:
- File parameters: Here we define the filename and storage path where our statement is saved. We also need to define what format this file is going to be; MT940, CAMT.053, or one of the many other supported formats
- Posting parameters: Here we can define whether the line items on the bank statements should be posted to general or sub-ledger. This section is not relevant for intraday statements, as SAP does not support GL postings and reconciliation from intraday statements out of the box.
- Cash management: This is the most important section, specifically for intraday statement processing. The fields and tick boxes control a few parameters:
A/CM payment advice: This needs to be enabled to ensure that SAP creates the memo record data from the intraday statements.
B/Summarization: This tick box controls whether a single memo record will be created for the whole delta balance as reported on the statement or for each reported debit and credit on the statement. If high volumes are expected, summarization can reduce the number of memo records and improve performance a bit. Obviously, it does reduce the data granularity.
C/Planning type: Here we set the planning type under which the memo records are going to be recorded. In our sample we set this to C1.
D/ Account balance: This needs to be set if we are loading intraday statements.
- Algorithms: Here we need to set the range of customer invoice reference number (XBLNR) for the electronic bank statement (EBS) algorithm, to search the payment notes for any such occurrence in a focussed manner. If we would leave these fields empty, the algorithm would not work properly and would not find any open invoice for automatic clearing. This section is not relevant for intraday statements as SAP does not support GL postings and reconciliation from intraday statements out of the box.
Once these parameters are maintained in the import variant, the system will start to load the statements and generate the required postings.
Transaction code: FF.5
Now we can check if the memo records are updated in table FDES.
Subsequently, we can check the FF7BN report for grouping ‘CASHPOS’ and observe the output.