T -3: Trial balance

It's time to set the Accounts receivable and payable balances in your new system.

You’ll need to decide on a start date (New System Date a.k.a. NS Date) for accounting for new transactions in the new system. This will be the day after your Cut-off date.

#	From Quickbooks	From another system
1	'Open' posting open transactions preparation: You'll need AR balance by Customer, and AP balance by Supplier. Collate unused AR and AP credit.	'Open' posting open transactions preparation: You'll need AR balance by Customer, and AP balance by Supplier. Collate unused AR and AP credit.
2	Setup Migration Control account: Create a General Journal and transfer the Trial balance.	Setup Migration Control account: Create a General Journal and transfer the Trial balance.

Read this balance migration process overview

Customer Balances (Accounts Receivable)

The transactions that make up the balances in your old system are safely stored and recorded. So why would you move them?

Transfer the AR balance (as per the Trial Balance) from the old system to the Migration Control Account (MCA) to set the opening balance.

Make a memo note ‘ Old system balances - this account will be cleared at a later stage’.

The overall objective is to zero the old system balance recorded in the MCA. To do this use a journal to debit the Customer account with their opening debit balance and credit the MCA.

Creating a Journal with the balance is significantly less effort and less prone to error and confusion than moving Invoices and Credit Notes.

When the journal is completed the AR balance for each Customer will be automatically reflected on the Customer master.

When the customer makes a payment for an Invoice in the old system, you use a journal to debit the bank and credit the customer's AR balance.

To make it efficient and easy to record multiple journals there is an ‘Upload Journals’ sheet in Setup>Import Data.

Supplier Balances (Accounts Payable)

It’s just the inverse of the Customer balances method.

See the Migration Control diagram below for an example.

Migration Control diagram

How do I report my Customer AR balance detail in Quickbooks?

You need to report 'Open' Invoices and Credit Memos.

Go to Quickbooks>Reports>Customers & Receivables>Customer Balance Detail

Make sure you set 'Dates' to 'All'.

Click 'Customize' and enable the following columns:

‘Paid’, ‘Open Balance’.

Disable ‘Class’ and ‘Balance’.

The report will list all of the ‘Open’ AR transactions by Customer with their individual ‘Paid’ status and Open Balance.

Notice unapplied Credit memos appear as ‘Unpaid’ and with a negatively signed balance.

You need to add the ‘Open’ balances of AR transactions to a General Journal.

How do I report my Supplier AP balance sheet detail in Quickbooks?

You need to report 'Open' Item Receipts, Bills, and Debit Memos.

Go to Quickbooks>Reports>Vendors & Payables>Customer Balance Detail

Make sure you set 'Dates' to 'All'.

Click 'Customize' and enable the following columns:

‘Paid’, ‘Open Balance’.

Disable ‘Class’ and ‘Balance’.

The report will list all of the ‘Open’ AP transactions by Customer with their individual ‘Paid’ status and Open Balance.

Notice unapplied Debit memos appear as ‘Unpaid’ and with a negatively signed balance.

You need to add the ‘Open’ balances of AP transactions to a General Journal.

How and where do I set up the Migration Control Account?

You’ll need to set up a Migration Control Account as a Current Liability account in the general ledger of your new system.

A successful accounting data migration relies upon detail. So you should separate and organize your migration control accounts like below. If you're using nominal codes we suggest the following:

• 99990 Migration Accounts Payable

• 99991 Migration Pending Item Receipts

• 99992 Migration Accounts Receivable

• 9999X Migration XX etc.

Accounts Payable, Pending Item Receipts GL Accounts? - remember, the balances for Item Receipts go into the Pending Item Receipts account. Quickbooks puts both the Bills and Debit notes, AND the Item Receipt balances in the Accounts Payable account. So please don't forget to split the former from the latter.

What does the Migration Control Account do?

You can create the migration control account as a current liability account.

The Migration control accounts will be used to record the relevant debits and credits from their corresponding transactions in the new system.

For example, a customer balance debited in the new system accounts receivable would have a corresponding credit recorded in the migration accounts receivable account. Do you see where I am going with this?

If you’re a non-accounting person reading this, then you're recording how much your customer owes as a balance in the new system. And to adhere to the double entry accounting principle, every debit has to match with a corresponding credit. This means you have to put the credit somewhere. In this case the migration control account. Make sense?

The migration control account keeps a record of the balances from your old system at the time of the migration. The objective is to get the aggregated balance of the migration account to zero.

The notional ‘opening balances’ in your new system are recorded using a general journal. I strongly suggest you record in the general journal and in the working paper what this balance is. I mean the customer, and each of the underlying transaction IDs. I’m referring to the Invoice number.

I also suggest you create a classification you can add to each type of journal, i.e. ‘migration AR’. This will help you follow the trail of any discrepancies, and tell you what the journal was for.

A reliable accounts receivable migration journal would contain a debit and credit for each customer. So if you’ve hundreds of customers and open invoices you’d need to create a large journal. The good news is there’s an ‘Upload Journals’ import sheet in the Setup>Import Data section of your system.

If you're an accountant, you will have spotted I’m referring to the simple process of moving the unadjusted trial balance and the underlying transaction detail to the new system.

Also, you’ll understand why, and how over time when the closing trail balance is determined, the migration control account will be reduced to zero, and if it is not then how useful the detail in the migration control account will be in debugging the problem.

If you’re not an accountant, here’s a simple explanation:

So, you have an open and unpaid invoice in your old system. The payment for this invoice is received in your new system. The amount owed is in your new system. The migration control account maintains the integrity of the accounting relationship of the two transactions. (I promise you).

“What about aging debtors and creditors?”, I hear you whisper. You can run the aging reports in your old system, right? This is the map of what is open and needs to be transferred over. It also tells you what and to who you need to pay attention to.

How do I create/upload journal(s)?

You need to know you can create journals that apply debits and credits directly to the Customer (AR) or Supplier (AP) account. See General Journals.

You can bulk-upload journals using the Upload Journals template. You'll find this in Setup>Import Data>Upload Journals.

Good to know - you can reverse Journal entries. See Reverse General Journal.