Creditor Open Balances
Importing Creditor Opening Balances
Follow these steps to bring in the current creditor balances from the client's current ERP.
Obtain from the current ERP a list of open creditor transactions currently with an outstanding balance after all trading has ceased on the old system. The transcation types can include bills, credits and unapplied payments. All documents with balances MUST be included. The client cannot exclude any documents. If there are old bills 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 creditor 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 creditor transaction file to be imported must include:
- Creditor Code (or Creditor Name) (this must be able to be matched to an existing contact in Odoo)
- Unique Transaction Number (this will either be the bill#, credit# or payment journal reference)
- Invoice Date
- Due Date
- Quantity (set this to 1)
- Total Outstanding (this must be negative if a balance is owing to the creditor. 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.
- Accounting Date – this should be set to the go-live date on all lines
- Expense Account – this should be set to the creditor historical balancing account on all lines.
- Journal – this should be set to the OPBIL journal on all lines.
- Tax Code – this should be left blank on all lines as we do not want tax calculated on these transactions. 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 creditor trial balance in the old ERP.
From the vendor bill list view import the outstanding creditor 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 Vendor Credit Note view.
Validate the creditor outstanding import by checking the following:
- check that all bills/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 creditor historical balancing account
- run a creditor aged receivable report for go-live date and confirm that the totals match the creditor receivable report from the old system
- run a balance sheet report for go-live date and confirm that the creditor account amount matches the creditor aged receivable for go-live date and also matches the balance sheet report from the old system
- check the creditor historical balance account and confirm that it now has a zero balance i.e. credits and debits match
Example:
Below: A sample vendor bill imported.
A few things to note: The Accounting Date is set to the go-live date and the journal is using the opening creditor bill /credit journal. The expense account is set to the Historical Creditor Balancing acount, the price is set to the document's outstanding balance and there is no tax on the line. The Bill Date, Due Date and Bil Reference should be set based on the exported data from the old system.
Below: The journal entry created by the imported invoice will update the Creditor account balance and clear the Historical Creditor Account.
Below: The Balance Sheet before the creditor balances are imported. The Trade Creditor/Accounst Payable account has a zero balance and the Historical Creditors account has the GL balance imported for creditors.
Below: The Aged Creditor Balance after the outstanding creditor transactions have been imported. The total highlighted should match the old systems closing creditor balance.
Below: The Historical Creditor Account after the bills/credits have been created and validated.
Below: After the bills/credits have been created and validated into Odoo the Opening Balance Sheet at go-live date now has the correct creditor balance. The Historical Creditor 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 creditor 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 creditor trial balance report and the creditor account balance in 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 creditor historical balancing account. This amount will need to be manually journalled back into the creditor account. To do this follow these steps:
- create a new OPACC journal, with the date set to go-live date
- debit/credit the historical creditor balancing account to clear the account
- debit/credit the Trade Creditor 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 creditor 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 aged creditor report against the debtor “Unknown Creditor”. 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 creditor open balances are imported there is still a balance in the historical creditor account. The Creditor/Accounts Payable amount will also no longer match the old systems closing creditor balance from the balance sheet report.
Below: The Historical Balance account has a $50 debit balance that will need to be cleared by a separate journal.
Below: The clearing journal required to move the imbalance back to the creditor account.
Below: The Balance Sheet at go-live date is now correct.
Below: The Aged Payable Report will show this imbalance under the Unknown Creditor 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.