I was recently asked about reporting Bank Balances and Overdrafts in BPC, particularly when the status can vary
from month to month. The initial discussion concerned reporting a Bank Account balance as a Short Term Asset - Cash balance or a Short Term Liability - Overdraft but it led on to some more complex scenarios.
If you are using BPC for financial reporting and consolidation this is an issue almost every client has so its worthy of considering the options.
"If you are using BPC for financial reporting and consolidation, [bank balances and overdrafts are] an issue almost every client has so its worthy of considering the options."
The original question related to how to load bank balances into the Short Term Assets - Cash at Bank, in the chart of accounts or Short Term Liabilities – Overdraft, based on the sign of the loaded data. Each entity had a single cash balance value in the load file sourced from the GL which, depending on the sign, represented a Cash balance or an Overdraft. The suggested solution was to load the balance into an account which was outside the reporting Chart of Accounts and then copy the balance either into the Cash Account in Short Term Assets or the Overdrafts account in Short Term Liabilities depending on its sign. An effective and simple solution, but with some considerations around logic:
This could be done in Script Logic but the syntax would vary depending on the BPC version, particularly whether the platform was Microsoft or NetWeaver. If the net balance was loaded into the account BANK_BAL_LOAD then the logic in Excel format would be:
CASH_BAL=IF (BANK_BAL_LOAD > 0, BANK_BAL_LOAD, 0)
OVERDRAFT_BAL=IF (BANK_BAL_LOAD < 0, BANK_BAL_LOAD, 0)
Related Content: Quick Wins in BPC Logic Tuning
A similar approach could be used for other data where the mapping is dependent on the sign of the loaded data and which may vary by Entity or from reporting period to reporting period.
Having resolved the question for a single bank account balance for each entity, we then had a client where companies have multiple bank accounts in their GL and source file. We want to facilitate or prevent the netting off of debit and credit balances and again have the correct allocation of balances for financial reporting into short term asset or liability depending on whether we have a positive or negative figure for each GL bank account grouping.
This can be achieved by creating additional accounts to allow analysis of the bank balances by bank or offset group (bank accounts where there is a right of offset) and then applying similar logic to the logic described above.
Often there is a mixture of global banks, where there is a need to make these available for input by all Entities, and local banks which are only relevant for one or a small number of Entities. Data for the latter can be collected by creating a number of generic accounts and using BPC comments to identify the local banks.
A similar approach to this is commonly used to collect data by Customer where there are both global and local customers.
The need to determine mappings based on data values will also be experienced in the automated generation of cash flow statements and the forecasting of balance sheets.
There are more complex banking arrangements which may arise such as pooling; the reporting and modelling of these may be managed in a Treasury application in BPC rather than a normal reporting or forecasting application but the Treasury model could take data from or feed the reporting models.
Pooling can take two main forms, although the fiscal and legal implications can be complex.
Notional Pooling does not involve the movement of cash between bank accounts. It means companies can minimise their overdraft interest and, since funds do not move, they retain local autonomy. Bank account balances within a notional pool are offset against one another and interest is paid (or earned) on the net balance. Cross border or multi-currency pooling arrangements are very complex and are consequently rare.
Cash concentration is less complex than notional pooling in that a company physically sweeps funds from almost any bank in almost any location to a central account, subject to local regulations. Many companies prefer to use cash concentration as it eliminates balances from the local accounts and provides maximum flexibility as to how surpluses are used, or how deficits are funded.
They may be zero balance accounts, whereby the bank accounts are completely zeroed, or they could be set up so that sweeps occur only when target balances have been achieved which reduces the number of transfers and consequently the cost.
In countries that do not offer notional pooling services, such as the US, and for most cross-border situations, cash concentration has proved to be the most practical solution to date.
For actual reporting, the approaches outlined above would allow the collection and reporting of pooled balances.
For planning applications both Notional Pooling and Cash Concentration could be modelled in BPC. This would include both the forecasting of balances and interest earned or charged.
If you would like to discuss these approaches in more detail or have other questions on how best to report and forecast your bank balances, then please add your comments below or contact Column5 for assistance.
BPC 10.x Reporting Workshop
14 - 16 November (Manchester UK)
November 15 - 17 (Scottsdale, AZ)
Column5’s interactive three-day, BPC 10.X Reporting training gives users a comprehensive toolbox for building dynamic reports and input forms in the EPM Add-in for Excel. Our training, applicable to both the Microsoft and NetWeaver platforms for BPC 10.0 and BPC 10.1, begins with basic reporting considerations and moves onto advanced reporting topics. With the tools to efficiently use the EPM Add-in for Excel, users have more time for analysis and spend less time mechanizing spreadsheets.
More Related Content:
SAP BW-IP, SAP BPC NW 10.1 Standard and SAP BPC NW 10.1 Embedded Compared
Customer Roundtable: Best in Class EPM Standards
Planning and Forecasting: Category Set-Up for SAP BPC
SAP BPC Staffing and Support Models
Author Bio:
Martin Lloyd is the Implementation Director for the UK (EMEA team). A solutions architect and Chartered Accountant with over 20 years' industry experience with Enterprise Performance Management. He is an internationally recognized expert in the design and implementation of Enterprise Performance Management (EPM) solutions. Certified in SAP BPC 10 and HANA.
