Establish Opening Balances

This Wiki is read-only for reference purposes to avoid broken links.

⇐ Table of Contents#System Administrator's Guide{{#if: Implementation| | Implementation }}{{#if: | | [[{{{3}}}]] }}{{#if: | | [[{{{4}}}]] }}{{#if: | | [[{{{5}}}]] }} | Establish Opening Balances{{#if: | | [[{{{next}}}]] }} ⇒

The information in this page is based on the following post and https://sourceforge.net/forum/message.php?msg_id=4329875

There are suggestions on how to make this process much easier.

Establishing the opening balanced in a new implementation is an important part of setting up the new system. If done well, all the processes in the system will be functional from day one and few, if any, special general journal entries will be required to get things to balance.

The complexity of the process will vary exactly with the complexity of the system. A simple system with few outstanding items will be relatively easy to deal with. A large system with thousands of outstanding items and large AR/AP lists will be more involved.

Summary

Establishing opening balances in ADempiere is relatively easy. The key thing to note is that the accounting facts that make up the balances are all generated from documents in the ADempiere system. So you can think of establishing opening balances as establishing the opening documents. These documents, such as GL Journals, Payments, Invoices, Shipments, Inventory Counts etc..., provide simple ways to generate the necessary accounting consequences. The documents also provide the necessary information for the processes that will be followed when the new system is first in use. For example, on the next bank reconciliation, any outstanding payments that were made but had not cleared the bank at the time of the opening should appear as unreconciled payments in ADempiere. When a customer payment is received and allocated, all open invoices should appear in the {{#if: Payment Allocation|Payment Allocation|PaymentAllocation }} Form.

The basics of the process involve the following steps:

• Import the trial balance as a GL Journal Batch. This make the opening balances easy to change as the GL Journal can be re-activated later to fix problems. The GL Journal will set up all the accounts with the proper opening balance but it won't create any of the necessary documents that are likely still open in the system and shouldn't be used for any account that is "document controlled". To allow for the creation of these documents, import the trial balance with the following exceptions:
  • Import all AR accounts to a AR Clearing Account. You will clear this with a list of fake sales that will recreate the AR accounts in ADempiere. Negative AR accounts (customers who have unallocated funds) will also be cleared as payments received;
  • Import all AP accounts to a AP Clearing Account. Like the AR Clearing Account, this will be cleared by a series of fake purchases that will recreate the AP accounts in ADempiere;
  • Import all Bank accounts to a Bank Clearing Account. The opening bank balances and outstanding in-transit amounts will be set by a series of fake invoices and payments.
  • Import all inventory amounts to an inventory clearing account. Inventory quantities and values will be set by a physical inventory count.
• Import the open AR invoices but replace the product with an "Opening Charge" and only import the open (unpaid) amount.
• Import the unallocated or pre-paid AR Payments as AR Receipts.
• Import the open AP invoices, also using an "Opening Charge" and only import the open (unpaid) amount.
• Import any pre-paid AP amounts as payments using the "Opening Charge".
• Import all outstanding AR receipts and AP payments that have not yet cleared the bank, assigning the payments to the "Opening Charge" rather than an invoice. Use the correct bank for these payments as the payment amounts will appear in the appropriate in-transit account and will appear when you next reconcile the bank statements.
• Perform an opening bank reconciliation to reconcile the unallocated or pre-paid AR payment and pre-paid AP payments that have already cleared the bank. The closing value of this reconciliation should match the opening bank balance.
• Create a physical inventory count for the inventory valuation. When completed this will create an inventory shrinkage expense. Use a GL Journal entry to cancel this with the Inventory Clearing Account.

At the end of the process, all the clearing accounts should be balanced (have equal credits and debits), the opening balances of all accounts should be correct and all necessary documents should exist.

Prerequisites

Before you begin, ensure you have completed all the implementation steps up to this point. Specifically, ensure you have:

• setup the business partners and imported the business partners - as a minimum you will need all the business partners that will be identified on any of the opening documents.
• setup the products and price lists.
• setup charges for non-product expenses an revenue.

Note: A Charge is a simple way to create accounting consequences other than those caused by a product. They are often used for simple expenses and revenue that doesn't need to be tracked specifically - like utilities. This makes them easy to use. Don't over do it though since there is no easy way to track their use. If you need to track it, use a product.

• for each stocked product, ensure the product costs match the closing value in the closing inventory valuation report.

Caution! Ensure the product costs are correctly identified and are non-zero. A zero cost will cause hard-to-correct errors during physical inventory counts. For products with no cost, use a very small number like 0.000001 rather than zero.

• setup the taxes that will be used on the invoices
• created the banks and payment processors for credit cards

Prepare

First, pick the opening/closing date. This will be the account date used on all documents created in this process.

Create new Account Elements for AR Clearing, AP Clearing, Bank Clearing and Inventory Clearing. All the accounts should be Balance Sheet accounts and the account numbers/values can be anything. See the {{#if: Element Value|Element Value|ElementValue }} Tab of the {{#if: Account Element|Account Element|AccountElement }} Window. You should place these in the correct position in you account tree so that they will appear on the balance sheet.

Create two new Charges named "Opening Charge AR" and "Opening Charge AP". Setup the Charges to have no tax. Set the accounting for the Opening Charge AP (both Expense and Revenue) to the AP Clearing account and the Opening Charge AR accounts should be set to the AR Clearing account. On a customer invoice or payment, the Opening Charge AR will create a simple consequence matching AR to AR Clearing. The Opening Charge AP will do the same for vendor invoices and payments, matching AP to AP Clearing.

Note: Using a single charge with the accounting set to AR Clearing for revenue and AP Clearing for expense will only work with invoices. Payments with a charge will always make the accounting in the charge expense account. You can use a single charge, just restrict it to invoices only.

Backup the database. If you screw up, you will have a point of recovery.

It is possible to make all the entries by hand if you would like to do so. The process provides a learning opportunity on the entry of the various documents. However, for most installations, this gets tedious very quickly. Its better to prepare the import data into spreadsheets and import the data into ADempiere directly. To prepare, please read the Data Import page carefully. To map the data from your existing system to the ADempiere system, make spreadsheets that you can use to map:

• Accounts - its quite likely that the account structure has changed. Create a list that maps the old accounts to the new ones in ADempiere. When preparing the import data for the trial balance (the only place you will use accounts) you will identify ADempiere accounts using the account search key or "value" field. Its a good idea to put the old account information in the description field in the GL Journal Line.
• Business Partners - its also quite likely that the ids for business partners have changed. You'll need a way to map business partners on invoices to the proper business partners in ADempiere. If you've managed to use the same id's in the new implementation, consider yourself smart.
• Products - as above, you may need to map the old product list to the new product list.
• Charges - if you used them, they may need to be mapped to the charges in ADempiere.

Once the maps are prepared, gather your data. You will need the following spreadsheets:

• Opening Trial Balance - this is simply that list of all accounts with their total debits and credits. If necessary, add a column to map the accounts to the new account numbers using the map you prepared above. A spreadsheet function like VLOOKUP can be helpful for this. Be sure to map any AR accounts to the AR Clearing account and do the same for the other clearing accounts.
• Open AR Invoices - this is a list of all the open invoices that are included in the AR Clearing amount. Map the business partners carefully and only keep one line per invoice using the Opening Charge created above. It is only necessary to import the invoice and its amount - what was actually purchased is part of the old system but is not relevant any longer. The line items on the invoices can be ignored and replaced with the charge "Opening Charge AR".
• Unallocated AR Receipts - a list of any outstanding unallocated payments received on account (potential liabilities from customers who like to keep a positive balance on their account) which have already cleared the bank. Do not apply a charge to these payments.
• Open AP Invoices - a list of any vendor invoices with outstanding balances. Replace all line items with a single line with the charge "Opening Charge AP"
• Open AP Payments - a list of any prepaid expenses or vendor accounts where you have a credit. All the items should have cleared the bank. Do not apply a charge.
• Unreconciled Payments - a list of payments and receipts, along with the bank they have been assigned to, which have yet to clear the bank. To any payment that is fully allocated to invoices, apply the appropriate charge. Leave unallocated payments as simple payments.
• Inventory Valuation - a list of products and their counts by inventory location. Double check that the product costs in ADempiere match the closing costs on the inventory valuation.

Note: You can date the invoices & payments to their actual dates if you would like an accurate Aging report as of the opening day. Just make sure the associated periods are open in ADempiere.

Process

Data Import

Follow the instructions in the Data Import page to import the spreadsheets prepared above:

• Opening Trial Balance
• Open AR Invoices
• Unallocated AR Receipts - don't forget the charges where they apply
• Open AP Invoices
• Open AP Payments - don't forget the charges
• Inventory valuation - imported as a physical inventory count. Pay particular attention to the warehouses.

After each import, check that data to make sure it was successful.

Opening Trial Balance

After the import, you should be able to compare the opening Trial Balance in ADempiere with the closing Trial Balance. Apart from the clearing accounts, the two should match. If there are any problems, you can re-activate the GL Journal entry and fix particular lines or delete the whole thing and re-import the data.

(More to follow - see the talk page for details that will be included).