Establish Opening Balances

Click here if you are an ADempiere user.

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 iDempiere 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 iDempiere 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 iDempiere; When a customer payment is received and allocated, all open invoices should appear in the Payment Allocation Form; etc...

The basics of the process involve the following steps:

• To avoid problems later in the use of the new system, it is important to enter documents that will allow for easy purchase order, invoice or receiver matching for material that may have been received but not invoiced or invoiced but not received. These transactions can have complex impacts on the trial balance so do them first and then adjust the trial balance in the next step accordingly.
• 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 iDempiere. 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 iDempiere;
  • 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 and revenue.

Note: A Charge is a simple way to create accounting consequences in a particular GL account. Charges are often used for simple expenses and revenues that don't need to be tracked the way a Product transaction might - like utilities, freight, loan repayments, salaries and associated costs etc. The reason that a "Charge" is used rather than simply selecting a general ledger account is because of the multiple account schema capabilities of iDempiere. If you are using multiple Account Schemas you need to able to define "electricity" purchases go to GL account 123 in the first Accounting Schema and to GL account ABCDE in the second Accounting Schema. The Charge construct provides the capability to achieve this. Note that when a transaction is posted to a Charge from within a Document, the Organization of the Document (and other associated GL components) will be substituted in place of the Organization which may have been selected when the Charge's accounting was defined. Note that the actual Charge name or key is not carried through to the posted accounting transactions, only the account codes for each Accounting Schema affected by the Charge. So, don't over do the use of Charges 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.

Note: 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 Element Value Tab of the Account Element. You should place these in the correct position in you account tree so that they will appear on the balance sheet. If you need to use a Bank Clearing Account, create a new bank & account for it and assign the bank account and in-transit account elements to the Bank Clearing Account.

Note: You can avoid the use of the Bank Clearing Account by adjusting the opening balance of each bank account by the amount of unallocated and open payments that have previously been reconciled. If you do this, set each payment to the actual bank and the opening bank reconciliation balance should match the opening bank balance.

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 iDempiere directly. To map the data from your existing system to the iDempiere 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 iDempiere. When preparing the import data for the trial balance (the only place you will use accounts) you will identify iDempiere 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 iDempiere. 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 iDempiere.

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

• Unmatched Material - This is a list of all the purchase orders for material that has been received but not invoiced or invoiced but not received.
• 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. The AR Clearing account should include all AR accounts and all unreconciled AR Receipts. If the unreconciled AR Receipts are included in the opening bank balance, reduce the Trial Balance Bank balance accordingly. The same conditions apply to the AP Clearing account. The Bank Clearing Account balance should be the sum of all Unallocated AR Receipts and Open AP Payments which have already been reconciled.
• 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. This list should include only allocated payments. Unallocated payments should be included in the Open AR/AP Receipts/Payments above.
• Inventory Valuation - a list of products and their counts by inventory location. Double check that the product costs in iDempiere 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 iDempiere.

Process

Unmatched Material

Note: At the time of writing iDempiere did not have an importer for material receipts so the creation of material receipts had to be done by hand.

In iDempiere, material received is matched with invoices and purchase orders in a process that ensures that there is a connection between the material that has been invoiced and the material received. In the chart of accounts, the financial consequences of the unmatched documents are different than the documents that were matched. If these temporary accounts are not setup properly, the new system may develop some financial consequences that are difficult to deal with outside of an undesirable General Journal entry into document controlled accounts. Here is a detailed tutorial how and what general ledger accounts are used during the matching process.

Dealing with unmatched material should be performed first so that the trial balance can be adjusted to take the financial consequences of this material into account.

To prepare for unmatched material, find all the material in the old system that has been received but not invoiced or invoiced but not received and import purchase orders for this material in iDempiere.

For the goods received but not invoiced, enter the material receipts for the goods received but not invoiced with a date that is (say) the day before the go-live date of the new system.

For the goods invoiced but not received, create or import the invoices for this material. If these invoices are paid, create or import the relevant payments.

When you are finished, a number of accounting consequences will have been created in the new system. You will have to take these into account when importing the trial balance below and may have to adjust the trial balance accordingly.

Note: Instead of creating your unmatched Material Receipts before system launch, it might be easier to simply code these transactions to a Not Invoice Receipt (NIR) Clearing account. To make this possible, you first import your NIR balance from your old system into a newly created NIR Clearing account element as part of your trial balance GL import. After go-live, you create AP Invoices for Material Receipts in your old system using a newly created NIR Clearing charge. When this invoice line posts, it will offset your originally imported NIR balance.

Data Import

see the free iDempiere data import tutorial by Chuck Boecking (2nd last video on page)

• Opening Trial Balance
• Open AR Invoices
• Unallocated AR Receipts
• Open AP Invoices
• Open AP Payments - don't forget the charges
• Unreconciled Payments - the list of all payments that have not cleared the bank yet.
• Inventory valuation - imported as a physical inventory count. Pay particular attention to the warehouses.

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

Opening Trial Balance

After the import, you should be able to compare the opening Trial Balance in iDempiere 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.

Open AR Invoices

Create or import each outstanding AR Invoice with one Invoice (Customer) in iDempiere. The Business Partner selected should be the actual Business Partner of the individual Invoice. Collapse all Invoice Lines to one line, use the Opening Charge AR, and ensure no tax consequences occur. On the main tab, the Grand Total should be the amount open of the actual Invoice. The accounting consequences should be simply Debit Accounts Receivable - Credit AR Clearing.

Following this step, the Aging report should be correct.

Unallocated AR Receipts

The Unallocated AR Receipts are simply payments that have not be allocated to an invoice. They can be entered directly as AR Receipt payments. Use the correct Business Partner. For the bank, if the payments have already cleared the bank, use the Bank Clearing Account. If not, use the payment processor where the payment is in transit. Do not use any charges as this will allocate the payment. The accounting consequences will be according to your default accounting but will be something like Debit bank in transit - Credit Unallocated Receipts.

After the imports are complete you will have to reconcile the various bank accounts on the opening day to reconcile the payments that have cleared the bank and leave the others in transit.

Open AP Invoices

Almost identical to the Open AR Invoices, Open AP Invoices are Invoice (Vendor) documents with outstanding open amounts. The total should make up the Accounts Payable. Create or import each outstanding AP Invoice with one Invoice (Vendor) in iDempiere. The Business Partner selected should be the actual Business Partner of the individual Invoices. Collapse all Invoice Lines to one line, use the Opening Charge AP and ensure no tax consequences occur. On the main tab, the Grand Total should be the amount open of the actual Invoice. The accounting consequences should be simply Debit AP Clearing - Credit Accounts Payable.

For vendor credits, use a AP Credit Memo as the document type rather than AP Invoice. If the credit is from a payment on account as opposed to a vendor credit, you should record this with an Open AP Payment rather than a credit memo.

Following this step, the Aging report for non-sales transactions should be correct.

Open AP Payments

These are unallocated payments made on account to a particular business partner (vendor). Create or import these as simple AP Payments. If the payment has not cleared the bank, use the correct bank account, otherwise use the Bank Clearing Account. The accounting consequences will be Debit Payment Selection - Credit bank in transit. An opening reconciliation of the banks will reconcile any payments.

Unreconciled Payments

Create or import any payments not already created above that have not cleared the bank. Create these payments using the appropriate Opening Charge (AR or AP), the correct bank and payment date. The Opening Charge will ensure that the payment will not appear as an unallocated payment. The accounting consequences will be

• for AR Receipts Debit Bank in transit - Credit AR Clearing
• for AP Payments Debit AP Clearing - Credit bank in transit

Inventory Valuation (Import Inventory)

Double check that all products being added to inventory are marked "stocked" and that all have the correct costs assigned. A zero cost product creates problems.

Create or import (Import Inventory) the opening inventory counts as a Physical Inventory count. Pay particular attention to the warehouse to ensure the correct one is used. When the data has been entered and the Physical Inventory completed, the accounting consequences will be Debit Inventory - Credit Inventory Shrinkage. There may be multiple Inventory Shrinkage accounts depending on how you set up the warehouses.

Note: In a fresh and virgin environment that was configured to use i.e. the average invoice costing method, no costing records may exist for the products that will be affected by the Import Inventory process. The costing records therefore need to be created first. In order to achieve this, simply tick the "update costing records" checkbox in the Import Inventory -> Import Physical Inventory pop-up window. This tells iDempiere to create the required costing records while importing the inventory data. Once the import process was successfully completed, open the Physical Inventory form in order to trigger the posting (it may already be posted if you chose the Document Action "Complete" in the Import Physical Inventory pop-up window). After the posting is completed, check the posting journal (the accounting consequences) in the Physical Inventory form by clicking on the "Posted" button again.

Balance out the shrinkage accounts with a single GL Journal entry that Debits the Shrinkage accounts and Credits Inventory Clearing.

Note: If the GL Journal seems a messy approach, you can change the default Inventory Adjustment account in each warehouse to Inventory Clearing. Note the original account. After the physical inventory is completed, the accounting consequences will be Debit Inventory - Credit Inventory Clearing. Be sure to change the warehouse defaults back to their original accounts.

Clean Up

At this point, all the documents should be entered and your clearing accounts should be cleared (balanced with equal debits and credits).

Perform an opening bank reconciliation on each bank account and reconcile any payments that have cleared the bank. This should leave the in-transit accounts with the correct balances and each bank account with the correct balance as well.

Run the following reports and check with the closing reports from the old system:

• Trial Balance - the opening trial balance should match the trial balance from the old system (taking any differences in the Chart of Accounts into consideration).
• Aging report - the aging report should match (if you used the correct dates on the documents).
• Inventory Valuation
• Balance Sheet
• Income Statement

Other Considerations

Not every situation is similar. Complex implementations may have other outstanding documents, such as shipments or material receipts which will have to be duplicated. For these cases, follow the same approach as above. Determine the accounting consequences of creating these outstanding documents and then take that into consideration when importing the Trial Balance so that after the documents are created, the Trial Balance will be correct.