Financial reconciliation in Data Migration

“The ultimate proof of a successful data migration lies in the complete reconciliation of the general ledger and the successful generation of a trial balance, balance sheet and profit and loss statement from the migrated data”

Migration process

Before we start let us briefly look at a migration process. It starts with creating a migration strategy document, followed by data mapping and a financial mapping. In financial mapping, the general ledger of source is mapped to a target general ledger. This activity is evolutionary like data mapping and gets refined over a period of time and mock executions. It should stabilize at a certain stage like dress rehearsal and the production execution cannot have any changes. The finance team responsible for mapping should certify the same and the resultant migration should be accepted unconditionally by finance. The financial reconciliation is done as part of every mock round on the basis of the version of financial mapping for the round. Financial reconciliation can happen at different stages of migration. 

  • Account/contract wise balances should be reconciled as soon as they are loaded to the target will form a part of data validation activity.
  • Internal account balances should be reconciled once all internal accounts in target are loaded
  • Migration suspense accounts should be reconciled once all financials are loaded from source to target including internal accounts, general ledger balances and contractual balances. We will look at the concept of migration suspense accounts in a bit.
  • The trial balance reconciliation is done post the suspense reconciliation and the execution of GL batches if any.
  • In some banks, trial balance reconciliation is replaced by financial statement reconciliation where by the trial balance is classified into respective balance sheet or P&L categories. Based on this the bank’s balance sheet is constructed from target and compared with that of source.
  • All of the above gates have to be successfully cleared for a migration to be certified as successful by the finance department aka CFO.

Importance of Financial reconciliation

In any data migration from one core banking system to another or from one application landscape to another, the financial reconciliation is one of the critical success factors. The financial information like balances in customer accounts, internal ledgers of the bank, the investment portfolios, etc. should not just move to the target but should also move accurately like pieces of a jigsaw puzzle. Otherwise the financial statements of the bank will not reflect the truth, thereby exposing the bank to financial risk, regulatory risk, reputational risk and market risk.

Now that we have understood the criticality of financial reconciliation, let us understand how financial mapping and financial reconciliation works.

Financial mapping and migration suspense (Control) accounts

Financial mapping

As already explained financial mapping is the activity of mapping a source general ledger to target general ledger i.e, every general ledger account from source has a mapped general ledger from target.

  • Ideally a one to one mapping is preferred for a simple migration of GLs.
  • This means that each and every source GL has a unique target GL. However this is not always possible due to reasons like target system behavior, expected revised GL structure, revised product cataloguing.
  • Hence a many to one mapping and a one to many mapping may also be part of the financial mapping.
  • In case of many to one mapping a group of more than one source GL maybe mapped to one target GL.
  • This is easier to handle technically in a migration as the balances of this group of GLs are moved to a single GL in target.
  • However a one to many mapping is more difficult to handle and should be avoided as much as possible.
  • The reason for this is, it is difficult to split a balance in a single GL into multiple GLs in target.
  • It cannot be done arbitrarily and needs proper rules.
  • This can also lead to reconciliation challenges.

Migration suspense (Control) accounts

These accounts are migration control accounts through which all balances are moved from source to target. These control accounts will receive the source amounts as take on balance and the amounts posted from target as utilization of the take-on balances. Ideally post the migration of all balances these control accounts should not have any balances. During early rounds of mock migrations some balances may remain in these control accounts due to issues faced in the migration. There could be several reasons for the outstanding balances in these control accounts and some of them are listed below:

  • Migration failure of some account/contract from source to target
  • Source/target system behavior mismatch leading to lack of placeholder to migrate the balances
  • Unmapped product/accounts leading to non-creation of target account or product.
  • Some configuration issues in target GL, etc.

Sample of Financial mappings:

Sample of Migration suspense (Control) Reconciliation

Financial reconciliation

Financial reconciliation as already explained is performed over a few stages. However in this section we will focus on the final reconciliation of Trial balance or a financial statement reconciliation. This is in general the final reconciliation related to migration and it is only post this activity any further downstream activity is commenced. Hence this financial reconciliation is the final gate of migration. Let us look at the sequence of steps for the final reconciliation

Step – 1: All migration uploads to be completed with financial and non-financials loaded to target. This means all customers, accounts, loans, deposits, internal accounts are created and the respective balances are loaded to target.

Step – 2: The migration suspense control accounts are reconciled with no balances remaining or all remaining balances are known with valid explanations for the same.

Step -3: The GL Build activities as part of end of day activities to be completed.

Step -4: Trial balance is generated from target system post GL Build up. All accounts rolled up at GL level should be included in trial balance including, balance sheet, P&L and Off balance sheet accounts.

Step – 5: Balance sheet and Profit and loss statements are generated based on trial balance from source and target for comparison. Each GL is assigned a category of either balance sheet, profit and loss or contingent based on the bank’s classification. Following are the general categories of classification

Step – 6: Document the differences and obtain CFO sign-off. All variances if any are to be recorded properly with actions clearly documented. This becomes a baseline document to be used for next round of migration. This list should not have any repeated issues and should not have any further actions before final rounds of migration.

Sample of reconciliation document:

As you can see from the above sample. Every GL is assigned a category and sub-category in both source and target. Based on the financial mapping done before migration the comparison is done and if any changes are needed in mapping or in execution it will come out on the basis of this comparison and reconciliation.

Once all actions are identified it is maintained in a tracker with clear actions identified against each owner and a target date is assigned for completing the changes before next round of execution.

Based on the above actions during reconciliation a CFO is in a position to evaluate the correctness of the migration activity and provides a sign off for the migration activity.

Back to Blog Series

If you need more about this topic please drop a mail

About Author