Data Migration:
This section includes a worked example of migrating financial data to a new system. It covers the TB, Debtors and Creditors and the correct method to ensure seamless continuity with bank reconciliations in the new system.
Migrating Your Financial Data
When you're migrating your financial data to your new system you will want to accomplish a number of key objectives, no less of which include
- A clean cut off
- A clear audit trail from one system to the next
- No loss of control over balance sheet control accounts
- No impediments to collecting debt, i.e. transactions on statements are still understood by customers
Open and Closed Transactions
To achieve this we need to distinguish between the open and closed transactions in our data. The open transactions comprise unpaid invoices, unallocated payments and uncleared receipts and payments, typically represented by our aged debtors, aged creditors, and the uncleared items on the cashbook.
Closed items can be mapped into the new system as balances rather than taking in detailed transactions, for example, a balance on a GL account. We will treat our control accounts differently.
Importing Open Items
To retain control over our control accounts we should import the open items as transactions rather than balances. We should be able to extract from the legacy system all open transactions, such as a detailed aged debt in file format. We will then map these transactions, ensuring we follow the field conventions in the new tables, for example the transaction type may be known as "SI" in the new system and something else in the legacy system.
Remember to also set the VAT code for all transactions to "out of scope" as we do not want to create any VAT transactions in our import file.
The Import File
Our import file may have three transaction types; sales invoices, sales credit notes and unallocated receipts. We might have to import these as three separate files. To retain accurate ageing we might have further imports to do depending on how the period system works. By doing so the transactions in your statements will continue to be a key tool in the cash collection process. It is likely, anyway, that we will have to split the file to spread the data transfer load and speed up the import process if errors occur, so creating separate file for each period, if we have to, is not an added overhead.
If you have debt in the last financial year, ensure you understand whether this can be imported in or whether you might have to amend the transaction date and import into the current financial year (using another field to keep the actual transaction date). If you have only a handful of transactions on a file, you might be better off to just key them in.
Check Totals
Before you begin to import anything ensure your list of transactions and the sum of your import files comes back to the debtors balance. There might be some odd transaction type you've overlooked in the mapping of your data. When importing use full rollback, i.e. if a validation error occurs no transactions will be imported. Fix the error and import the whole file again. Make sure you do this on the test system first.
Double Entry
If we are importing open transactions as well as the balances on our TB we need to ensure we don't double count. Lets explore how we do this concentrating on debtors, creditors and the cashbook. We'll use the following abbreviated trial balance to work through the numbers.
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Fixed Assets | 150 | ||
| Trade Debtors | 500 | ||
| Other Debtors | 40 | ||
| Bank | 75 | ||
| Trade Creditors | 420 | ||
| Other Creditors | 140 | ||
| Reserves | 205 | ||
| Totals | 765 | 765 | |
Method
Usually at the end of you chart of accounts you will have a range of codes you can use as suspense accounts. We will use one of these to control our migration. We will use it to post our transactions and to also post the Trial Balance. If it doesn't equal zero at the end of the exercise then the migration contains an error.
Bank
As we want to retain the integrity of our bank reconciliation on our new system we need to disaggregate our TB bank balance. The balance is comprised of.
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Bank Statement | 95 | ||
| Uncleared Payments | 28 | ||
| Uncleared Receipts | 8 | ||
| Sub-total | 103 | 28 | |
| TB Bank Balance | 75 | ||
We will import or key in the uncleared payments and receipts. Our two entries will be
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Bank | 8 | ||
| Migration Suspense | 8 | ||
| being uncleared receipts | |||
| Migration Suspense | 28 | ||
| Bank | 28 | ||
| being uncleared payments | |||
| Totals | 36 | 36 | |
This leaves us with a credit balance on our Bank so we have one more journal to post. This is to take in the reconciled balance from our legacy system. If we post this balance as a bank transaction rather than a pure GL journal it means we can open up our bank reconciliation program in our new system and reconcile this single entry. Our bank account, after we post the following entry, is then clean.
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Bank | 95 | ||
| Migration Suspense | 95 | ||
| being reconciled bank balance | |||
| Totals | 95 | 95 | |
At this point our Trial Balance looks like this:
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Fixed Assets | |||
| Trade Debtors | |||
| Other Debtors | |||
| Bank | 75 | ||
| Trade Creditors | |||
| Other Creditors | |||
| Reserves | |||
| Migration Suspense | 75 | ||
| Totals | 75 | 75 | |
Debtors
Excluding the effect of provisions which we'll ignore in this example, our Debtors balance consists of all open transactions, i.e. unpaid invoices, unallocated credit notes and payments. Leading up to the go live date ensure your credit control dept. are proactively allocating cash and cleaning up the debtors. In any case you are still likely to end up with a debtors balance consisting of, for example
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Open Invoices | 515 | ||
| Unallocated Credits | 3 | ||
| Unallocated Receipts | 17 | ||
| Part Paid Items etc. | 5 | ||
| Sub-total | 520 | 20 | |
| TB Debtors Balance | 500 | ||
We will key in or import these items. Our journal entries being:
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Trade Debtors | 515 | ||
| Migration Suspense | 515 | ||
| being unpaid debtor invoices | |||
| Migration Suspense | 3 | ||
| Trade Debtors | 3 | ||
| being unallocated credit notes | |||
| Migration Suspense | 17 | ||
| Trade Debtors | 17 | ||
| being unallocated receipts | |||
| Trade Debtors | 5 | ||
| Migration Suspense | 5 | ||
| being unpaid debtor invoices | |||
| Totals | 540 | 540 | |
At this point our Trial Balance looks like this
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Fixed Assets | |||
| Trade Debtors | 500 | ||
| Other Debtors | |||
| Bank | 75 | ||
| Trade Creditors | |||
| Other Creditors | |||
| Reserves | |||
| Migration Suspense | 575 | ||
| Totals | 575 | 575 | |
Creditors
The exercise on creditors is simply the reverse of the process for Trade Debtors. There are those who argue to pay off all creditors before the go live date. This is argued on the basis of reducing the amount to import and, if there were any problems with the payment program in the new system, we would have bought ourselves some time. I'm not an advocate of this method, the main reason being the adverse effect on cash flow but also because I don't really see any advantage in doing it. The creditors should be cleaned in the same way as the debtors leading up to the go live date, and imported just the same. Our disaggregated creditors balance is:
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Open Invoices | 425 | ||
| Unallocated Credits | 3 | ||
| Unallocated Payments | 2 | ||
| Part Paid Items etc. | |||
| Sub-total | 5 | 425 | |
| TB Creditors Balance | 420 | ||
The journal entries will be:
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Migration Suspense | 425 | ||
| Trade Creditors | 425 | ||
| being unpaid supplier invoices | |||
| Trade Creditors | 3 | ||
| Migration Suspense | 3 | ||
| being unallocated credit notes | |||
| Trade Creditors | 2 | ||
| Migration Suspense | 2 | ||
| being unallocated payments | |||
| Totals | 430 | 430 | |
Our trial balance now looks like this
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Fixed Assets | |||
| Trade Debtors | 500 | ||
| Other Debtors | |||
| Bank | 75 | ||
| Trade Creditors | 420 | ||
| Other Creditors | |||
| Reserves | |||
| Migration Suspense | 155 | ||
| Totals | 575 | 575 | |
The Trial Balance
So now our control accounts are complete we can journal in the rest of the balance sheet. As we have already taken in the debtors, creditors and bank balances we do not need to journal these in. Our remaining journal will be as follows.
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Fixed Assets | 150 | ||
| Migration Suspense | 150 | ||
| being opening TB Fixed Assets | |||
| Other Debtors | 40 | ||
| Migration Suspense | 40 | ||
| being opening TB other debtors | |||
| Migration Suspense | 140 | ||
| Other Creditors | 140 | ||
| being opening TB other creditors | |||
| Migration Suspense | 205 | ||
| Reserves | 205 | ||
| being opening TB Reserves balance | |||
| Totals | 535 | 535 | |
Our Trial Balance now looks like this:
| Dr £'000 | Cr £'000 | ||
|---|---|---|---|
| Fixed Assets | 150 | ||
| Trade Debtors | 500 | ||
| Other Debtors | 40 | ||
| Bank | 75 | ||
| Trade Creditors | 420 | ||
| Other Creditors | 140 | ||
| Reserves | 205 | ||
| Migration Suspense | |||
| Totals | 765 | 765 | |
Conclusion
Our new trial balance agrees to our old trial balance. We've retained the audit trail of our file imports and journal entries and printed a hard copy of our new TB. Users on the legacy system should now only have read only access to it. Our control accounts and ageing reports agree to the legacy system, and because we've taken the bank balance in as a file import and as a bank transaction our bank reconciliation program works from day one. Many implementations treat the bank balance as though it were an ordinary GL account. Post implementation this can lead to a drain on qualified resource and a weakening of the internal control system.
Inspired Notes
Resources