Xero Out of Balance imported from MYOB

When converting information from MYOB to Xero there are some system differences that need to be taken into consideration. For example MYOB will allow you to create direct entries & manual journals to the Trade Debtor and Trade Creditor accounts. In Xero you are unable to perform these actions. An out of balance between the control and subsidiary ledgers can arise and must be dealt with as part of setting up the Xero file. An example there are instances in MYOB where the total Invoices does not equal the total amount in the Trade Debtor account and/or where the total bills does not equal the total amount in the Trade Creditor account.

This blog deals with how to identify an out of balance situation in Xero following a conversion from MYOB. Once you have identified why there is an out of balance this can help determine how it can be rectified.

Causes of Out of Balance

There are three main reasons why an out of balance occurs in MYOB:

  1. Journal, transaction or open balance to a header account (Trade Debtors/Trade Creditors).
  2. Deposit against a quote.
  3. Prepayment against invoice.

1. Out of Balance due to Journal Transaction or Open Balance on Header Account

The MYOB Company Data Auditor allows you to compare transactions (eg invoices) with the header account (eg Trade Debtors). Run a review for the period that you want to reconcile.

A Tick indicates that the reconciliation was successful and a Question Mark indicates that there are transactions that you need to review. Click on the Question Mark to see the transactions that may have been in error.  

The first item “RECONCILE INVOICES WITH LINKED RECEIVABLES ACCOUNT” has a question mark.

When reconciling Accounts Receivables, the total from your outstanding customer balances (eg the total of all the invoices) should equal the total in the Trade Debtors account. Here there is no ‘Out of Balance’.  

JOURNAL Reason A: Transaction Directly Entered to a Header

A common cause of ‘Out of Balance’ is when the invoice is created, a Receivables header account is selected, instead of an Income account. A RECAP shows that the amount has been posted twice to the Trade Debtors account.

Sometimes, accountants will post directly to the Trade Debtor/Creditor accounts using in a General Journal. This does not create a corresponding invoice and therefore MYOB will be ‘Out of Balance’.

Another example is when a Customer Payment is entered with the Receive Money window, and the Trade Debtors account is chosen, instead of an income account or the invoice being paid under the customer account.

Another problem occurs when Finance Charge Transaction and the Finance Charge Payment are not both removed when an invoice is removed.

JOURNAL Reason B: Open Balance Setup Not Correct

In MYOB the easiest way to see if the Opening Balance was not correct is the following procedure:

1, Setup + Easy Setup Assistant
2. Sales (or Purchases)
3. Historical Sales (or Historical Purchases)
4. Look at the bottom to find the out of balance figure.

Either the invoices entered are incorrect or the balance sheet balance of Trade Debtors is incorrect.

Jet Convert Fix for Journal – Header Account out of Balance

If your Trade Debtors/Trade Creditors accounts had transactions in it that were not invoices or credit notes to import these transactions into Xero the following changes will have had to be made:

We create the Trade Debtor/Creditor New as the system account for Accounts Receivable/Payable in Xero. In addition you will identify accounts called Trade Debtor/Creditor Original. The system accounts called Trade Debtor/Creditor New is where you will add your invoices, bills and credit notes. The Trade Debtor/Creditor Original contain the direct entry transactions or deposits.

We split the amount in the different accounts so that the out of balance amount stays in the original account and the invoices/bills actually match the balance in the new system account for Trade Debtor/Creditor. So there is no out of balance issue any more.

Example: MYOB Payable’s Reconciliation Report

Screenshot_010816_092557_PM

In Xero: We enter the ‘Out of Balance’ amount into Trade Creditor Original account

Screenshot_010816_093151_PM

If direct entries are identified these transactions will also be in the Trade Creditor original account and this will automatically offset the opening out of balance.

Screenshot_010816_093820_PM

If this has also occurred in the Trade Debtors account similar actions would have been taken during the conversion. If there is a remaining balance in the Trade Debtor / Creditor original account then you may need to investigate the MYOB file further.

Once the Trade Debtor/Creditor account(s) is balanced to $0 we recommend archiving and using Xero’s system account for the invoices, bills and credit notes.

2. Out of balance due to Deposit Paid on an Order

A deposit paid on an order will result in a payment before the invoice is created.  An order does not have a debit and credit entry and is not structured like an invoice.  An order is just a flat document and does not comply with accounting rules. Deposits paid on orders in MYOB at time of conversion will result in an Out of Balance.

Jet Convert Fix for DEPOSIT Paid on Orders

During the conversion process our conversion tool will go through the information in the chart of accounts and identify any account that have been ‘linked’ in MYOB. For example ‘customer deposits’ and ‘trade debtors’’. In MYOB you have the ability to apply deposits against orders in xero you cannot. During the conversion ‘orders’ are converted as ‘draft invoices’ and any ‘deposits’ are created into Credit Notes. Using the example above the Conversion Process creates a Credit Note for the amount deposited, eg $150. There will be a separate Credit Note for each Deposit and Contact.

In Xero you will need to determine if the order should stay in Draft, be deleted or need to be approved (processed). If the deposit needs to be allocated against an Invoice in Xero you can complete the following:

  1. Identify the draft invoices that need to be approved
  2. Approve the ‘draft Invoice so that this now appears in ‘awaiting payment’
  3. Allocate the Credit Note against these relevant Invoice

If you prefer to have your deposit account separated then you can complete the following:

  1. Re-Create the deposit account in the Chart of Account
  2. Move the balance from the Trade Debtor/Creditor to the relevant deposit account

Note: There could be a “deposit out of balance” issue. This can happen if your deposit account is not balanced to all open deposits against orders. If it is balanced, it will be $0 and all deposits show up as credit notes.

You will need to decide if the order should stay in Draft, be deleted or need to be approved (processed).

3. Out of balance due to Prepayment – Future/Past Dated Transactions

If an invoice is dated 7th July and the payment against the invoice is 30th June, then we have an Out of Balance if we were to setup the conversion balances as at 1st July. The MYOB Data Auditor will show a report for this.

Jet Convert Fix for Prepayment – Future/Past Dates Transactions

We create a temporary Pre Conversion invoice dated 30th June. The Prepayment credit note is offset against this invoice, so that at the conversion date, these two balance each other and the ledger is in balance with the control account. We then create a payment dated 1st July, which we match against the Pre Conversion invoice. This leave the original prepayment to match with the original invoice.

Reports in MYOB

The following MYOB reports can help identify the transactions that cause Out of Balance.

1. Sales + Receivables Reconciliation to view the amount.  Then run this report again with the As At date being 30/12/3000 (or way into the future). If there is no Future Dated transaction, then there is a pre payment.

2. Accounts – Prepaid Transactions. View the Payable out of balances for a series of dates:

1/11/11 = nothing
1/12/11 = 22478.76
31/12/11 =  =13815.5
1/1/2012 = 20910.65k
30/6/2013 = -340.45

3. Reports + Accounts + Exceptions + Payables Reconciliation

No More Out of Balance!

So to address Out of Balance we first identify the cause in MYOB and fix as appropriate in Xero. Each conversion is different and there may be situations in which different measures may need to be taken. Please review the Action Checklist that is emailed to you upon delivery of the conversion as this documentation contains information specific to your conversion.  If you do your own conversions you’ll need to consider Out of Balance and how best to manage. Then you can start a “balanced” life in Xero.

Zowie