Debtor Open Balances
Importing Debtor Opening Balances
Follow these steps to bring in the current debtor balances from the client's current ERP.
Obtain from the current ERP a list of open customer transactions currently with an outstanding balance after all trading has ceased on the old system. The transcation types can include invoices, credits and unapplied payments. All documents with balances MUST be included. The client cannot exclude any documents. If there are old invoices that they want to exclude, the document must be brought across and then will then need to be written off after go-live in Odoo.
This information, if possible, should be exported out from the customer’s old system into csv/Excel format. If not possible, a debtor trial balance report should printed to file and then this can be manually tidied up in Excel to a format that can be imported.
The cleaned up debtor transaction file to be imported must include:
- Customer Code (or Customer Name) (this must be able to be matched to an existing contact in Odoo)
- Unique Transaction Number (this will either be the invoice#, credit# or payment journal reference)
- Invoice Date – this should be set to the go-live date on all lines
- Due Date - this should the document's due date
- Quantity (set this to 1)
- Total Outstanding (this must be negative if a balance is owing to the customer. This must be the total balance outstanding, not the original document value and must include tax). This should be mapped to the line price in Odoo.
- Sales Account – this should be set to the debtors historical balancing account on all lines.
- Journal – this should be set to the OPINV journal on all lines.
- Tax Code – this should be left blank on all lines as we do not want tax calculated on these invoices. This should be mapped to the line tax in Odoo.
TIP: Before importing into Odoo, use Excel and sum the total balance due to ensure it matches the current debtors trial balance in the old ERP.
From the customer invoice line view import the outstanding debtor file. You will need to map the columns to the correct Odoo field. A separate transaction will be created for each line in the import file. Any lines with credit balances will be created as credits and will be visble under the Customer Credit Note view.
Validate the debtor outstanding import by checking the following:
- check that all invoices/credits have an accounting date set to go-live date
- check that no tax has not been calculated on any document and the entire amount has been assigned to the debtor historical balancing account
- run a debtor aged receivable report for go-live date and confirm that the totals match the debtor receivable report from the old system
- run a balance sheet report for go-live date and confirm that the debtor account amount matches the debtor aged receivable for go-live date and also matches the balance sheet report from the old system
- check the debtor historical balance account and confirm that it now has a zero balance i.e. credits and debits match
Example:
Below: A sample invoice imported.
A few things to note: The Invoice Date is set to the go-live date and the journal is using the opening customer invoice/credit journal. The sales account is set to the Historical Debtor Balncing acount, the price is set to the document's outstanding balance and there is no tax on the line.
Below: The journal entry created by the imported invoice will update the Debtor account balance and clear the Historical Debtor Account.
Below: The Balance Sheet before the customer balances are imported. The Trade Debtor/Accounst Receibale account has a zero balance and the Historical Debtors account has the GL balance imported for debtors.
Below: The Aged Debtor Balance after the outstanding debtor transactions have been imported. The total highlighted should match the old systems closing debtor balance.
Below: The Historical Debtor Account after the invoices/credits have been created and validated.
Below: After the invoices/credits have been created and validated into Odoo the Opening Balance Sheet at go-live date now has the correct debtor balance. The Historical Debtor Account is now zero.
What should I do if the Historical Balancing acount still has a balance?
If the import has set all the data correctly, but you still have a balance in the debtor historical balancing account, it is possible that there was an existing imbalance between the accounts and the sub-ledger in the old system. This can be confirmed by comparing the debtor trial balance report and the balance sheet report in the old system. If these do not match, then there was an existing imbalance.
If this is the case, then this imbalance should be the same amount as the current balances in the debtor historical balancing account. This amount will need to be manually journalled back into the debtor account. To do this follow these steps:
- create a new OPACC journal, with the date set to go-live date
- debit/credit the historical debtor balancing account to clear the account
- debit/credit the Trade Debtor account for the offset amount. The partner field should be left blank.
- update the reference/line labels to explain the purpose of this journal E.g. Opening Balance – Existing imbalance between debtor account and sub-ledger.
After posting this journal, revalidate all the original steps under this item to confirm it is now all correct. The imbalance amount should now appear at the bottom of the debtor trial balance against the debtor “Unknown Debtor”. The client should be notified of this imbalance and instructed that they will need to write off this amount under instruction from their accountant.
Example:
Below: After the debtor open balances are imported there is still a balance in the historical debtor account. The Debtor/Accounts Receivable amount will also no longer match the old systems closing debtor balance from the balance sheet report.
Below: The Historical Balance account has a $2,000 debit balance that will need to be cleared by a separate journal.
Below: The clearing journal required to move the imbalance back to the debtor account.
Below: The Balance Sheet at go-live date is now correct.
Below: The Aged Receivable Report will show this imbalance under the Unknown Debtor line. The client sould consult with their accountant to determine how this imbalance should be handled. A journal entry can be performed by the client to clear this amount once this advice has been obtained.
There are no comments for now.