Reconciliation is a vast topic, and the amount of data processing is increasing in the financial sector. New products such as e-wallets and fractional trading are not making it easier for reconciliation teams.
Without proper reconciliation, it is impossible to run core banking activities such as position management, legal reporting, revaluation, and treasury. It also means that you must rely on your counterparty data, which may not always be 100% trustworthy.
In this chapter, we will cover:
Who is responsible for reconciliation?
What creates reconciliation issues?
When do I need to initiate a reconciliation project?
Who is responsible for reconciliation?
Reconciliation must have three lines of defense in financial organizations:
First line: Business departments must own their accounts and ensure they are reconciled on a daily basis.
Second line: The accounting department must ensure that accounts are reconciled, and the general ledger is balanced each day.
Third line: Internal review, end-of-year accounting closing must verify that everything is in order.
What creates reconciliation issues?
The sooner a reconciliation issue is identified, the better. Reconciliation is often problematic due to external factors, and there is a significant risk of forgetting these external factors if the issue is noticed a month later. Reconciliation could also become problematic after a major project release or an upgrade of your existing application. It could be that a process needs reconciliation on a daily basis due to human error and volume. If not done, errors occur daily and are not addressed.
Last but not least, your counterparty might have one of the above reasons and impact your reconciliation process.
When do I need to initiate a project on reconciliation?
If the organization is not strong enough in the first and second lines of defense, it is likely that a project must be set up to reconcile the books once you discover :
- Accounting unbalance
- Profit and loss irrational
- Transit/suspend account not equal to 0
- MT950/MT535 differences
- Currency position mismatches.
What are the key considerations in reconciliation?
The key to a successful reconciliation project is to be independent and build your own database outside of the problematic tool. Reconciliation is not a topic that comes with a manual; the data must be checked in many different ways, and you must perform all checks independently to avoid delays.
The first rule is that the team must be 100% independent.
To be 100% independent, the bank will need to provide a good first extraction and all external data required.
The second rule is an initial extraction and external data availability.
The team must have good knowledge of what to reconcile and how. Reconciliation is always a topic to go from consolidated data to detail. You consolidate all entries per day, then ask for details for days in mismatch, consolidating per transaction type, then ask for transaction types in mismatch, until you find the problematic case. The team must have knowledge of revaluation, position, inter-branch, transit account, Nostro reconciliation, securities reconciliation, suspense account, as reconciliation topics go beyond Asset + liability + P&L = 0.
The third rule is to get a knowledgeable team.
Last but not least, depending on the amount of data, the team will need the proper tool.
The fourth rule is to get the proper tool.
Which tool to use?
This is a key question before starting to reconcile, and the answer depends mainly on the amount of data to process. There are three cases:
Less than 800K/1 Million lines to reconcile: Microsoft Access can be used, and the 2-gigabyte constraint should be okay.
Between 1 million lines and 4 million lines: Microsoft Access could still be used, but there is a lack of productivity due to multiple Access databases. The project will be longer due to multiple Access databases, but it should be manageable with 3 or 4 Access databases. Build your own SQL server database, but this comes with a cost as a server and an SQL license might be needed. Also, loading the data will initially require building an ETL. Get a specialized reconciliation software like, for example, Alteryx, but this comes with license fees.
More than 4 million lines: With this volume, you must either build your own SQL server database, but this comes with a cost as a server and an SQL license might be needed. Also, loading the data will initially require building an ETL. Or get specialized reconciliation software like, for example, Alteryx, but this comes with license fees.
Who can help?
External companies such as the Big Four can play a significant role in the solution. But often, they might need support to understand your application, and a specialized local consulting company would help. Also setting up tool, preanalyze the issue would make sense to be done by the bank or a local consulting firm. Luxat can most likely help you!
At the end, prefer to choose a provider that come with its extraction tool from your core banking, it will ease “What are the key in reconciliation” second rules as raw data will be extracted easily.