Establish Opening Balances

From ADempiere
Revision as of 05:39, 5 January 2011 by MJMcKay (Talk) (A work in progress. More edits to follow.)

Jump to: navigation, search
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 }} Window.

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.
  • for each stocked product, ensure the product costs are correct. The inventory valuation will be based on the identified costs.
Caution.gif 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.

Process

Create new Account Elements

Create new Account Element for Debtor Clearing, Creditor Clearing, Bank Clearing and Inventory Clearing. All the accounts should be Balance Sheet accounts and the account numbers can be anything.

Create new Product

Create Product named Opening Product in System. Setup the Product to be Sold, Purchased and Stocked. The Product can be setup to any Product Category. The Default Account for Revenue of the Product should be set to Debtor Clearing and the COGS Default Account should be set to Creditors Clearing.

Setup Standard Cost for Products in Product Costs Window

Create Standard Cost record for all Products and update the Current Price field for each and every Product with the closing value of the Product.

Change Warehouse Default Account

The Inventory Adjustment Default Account for all Warehouses and Locators should be changed to Inventory Clearing. Note down the Default Account before change.

Change Default Bank In Transit Acount

For one bank account that you will use during imports, set the default bank in-transit to the Debtor Clearing account. Note down the Default Account before you make the change.

Obtain Closing Trial Balance

Obtain Closing Trial Balance with details for balances of Debtors, Creditors and Closing Stock amounts stated in the Trial Balance. Details should include Invoices, dates, amounts of individual Invoices and Business Partners.

Create new GL Journal and key-in Trial Balance

Key-in Trial Balance amounts into respective accounts in GL Journal. However, for the Debtors, Creditors, Bank in Transfer and Closing Stock amounts, select the account to be Debtor Clearing, Creditor Clearing, Bank Clearing and Inventory Clearing respectively. The accounting entries will be as follows:

DR Debtors Clearing (TB Debtors Amount) 
DR Inventory Clearing (TB Closing Stock Amount) 
  CR Creditors Clearing (TB Creditors Amount) 
  CR Bank Clearing (TB Bank In Transfer Amount) 

Create Invoice (Customers) for individual Invoices that make up the TB Debtors Amount

Each Invoice should be created with 1 Invoice (Customer) in the System. The Business Partner selected should be the actual Business Partner of the individual Invoices. The Accounting Date is the Opening Date and the Movement Date should be the actual Invoice Date. In the Invoice Line, the Product selected should be the Opening Product created above and multiple lines in the actual Invoice can be summarized into 1 Line. The amount should be the Total Amount of the actual Invoice.

Upon completely keying in the Invoice (Customer), the Invoice should be Completed and Posted. The account posting that occur should be as follows:

DR Debtors (Invoice Amount) 
  CR Debtors Clearing (Invoice Amount)

Create Pre-Payments (Customers) for individual customers that are part of the TB Debtors Amount

If you have prepayments or credits on account, create one payment (AR Receipt) per customer with the unallocated amount. The Business Partner should be the same as the individual payments. The transaction date should be the Opening Date. Complete and post each payment.

In the accounting facts, the following transactions should appear:

DR Debtors Clearing (Payment Amount)
  CR Bank unallocated Receipts (Payment Amount)

Upon completion, be sure to reset the default Bank in Transfer account.

Check Debtors Clearing account balance

Upon completely creating, completing and posting all Invoices (Customer) and pre-payments (customers), the Debtors Clearing Account should have a 0 balance.

Create Invoice (Vendor) for individual Invoices that make up the TB Creditors Amount

The procedure is the same as for Invoice (Customer) above. The account posting should be as follows:

 DR Creditor Clearing (Invoice Amount) 
   CR Creditor (Invoice Amount) 

Check Creditors Clearing account balance

Create Payment for individual Unpresented cheque that make up the TB Bank In Transfer Amount

Each unpresented cheque should be created with 1 payment in the System. The Business Partner selected should be the actual Business Partner of the individual payment. The Accounting Date is the Opening Date and the Transaction Date should be the actual Payment Date. The amount should be the Total Amount of the actual Cheque Payment.

Upon completely keying in the Payment, the Payment should be Completed and Posted. The account posting that occur should be as follows:

 DR Bank Clearing (Payment Amount) 
   CR Bank In Transfer (Payment Amount) 

Check Bank Clearing account

The bank clearing account should now have a balance of zero. If not, please review your previous steps.

Create new Physical Inventory record.

Create new Physical Inventory and key-in individual inventory quantity balance of Closing Stock as per closing stock count. The type of Physical Inventory selected should be Inventory Adjustment. Be very careful on the Warehouse selected for each record.

Complete and Post Physical Inventory record

Upon completely keying-in the Physical Inventory, Complete and Post the Physical Inventory. The account posting should be as follows:

 DR Inventory (Standard Cost) 
   CR Inventory Clearing (Standard Cost) 

Check Inventory Clearing balance

Upon completion of all Physical Inventory postings, the Inventory Clearing balance in the System should be 0.

Revert Default Accounts

Revert the Default Accounts for Inventory Adjustment for each Warehouse to the original Default Account.

Deactivate Opening Product

Select the Active checkbox for Opening Product in the Product Masterdata setup to No.

Generate Trial Balance from the System

Generate the Trial Balance as at the Opening Date and compare against the Closing Trial Balance obtained earlier.

Deactivate Debtor Clearing, Creditor Clearing, Bank Clearing and Inventory Clearing

Uncheck the Active checkbox for the above in the Account Element setup.

Summary Outcome

You should now have your opening balances established in Adempiere.