Difference between revisions of "Establish Opening Balances"

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
m (Unmatched Material)
 
(34 intermediate revisions by 6 users not shown)
Line 1: Line 1:
= Overview =
+
{{Breadcrumb|Table of Contents#System Administrator's Guide|Implementation}}
When setting up your accountign you will need to establish opening balances.  The information in this page is based on the following post and https://sourceforge.net/forum/message.php?msg_id=4329875
+
[[Category:Table of Contents]]
 +
[[Category:System Admin documentation]]
 +
[[Category:Accounting]]
  
= Process =
+
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.
  
== Create new Account Elements ==
+
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.
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 ==
+
= Summary =
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 ==
+
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 {{FormRef|PaymentAllocation|Payment Allocation}}; etc...
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 basics of the process involve the following steps:
The Inventory Adjustment Default Account for all Warehouses and Locators should be changed to Inventory Clearing. Note down the Default Account before change.  
+
* 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 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 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.
  
== Obtain Closing Trial Balance ==
+
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.
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 ==
+
= Prerequisites =
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)
+
+
== Check Debtors Clearing account balance ==
+
+
Upon completely creating, completing and posting all Invoices (Customer), 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 ==
+
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.
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.  
+
* setup the products and price lists.
+
* setup charges for non-product expenses and revenue.
Upon completely keying in the Payment, the Payment should be Completed and Posted. The account posting that occur should be as follows:
+
{{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.  
+
  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 zeroIf not, please review your previous steps.
+
  
== Create new Physical Inventory record. ==
+
The reason that a "Charge" is used rather than simply selecting a general ledger account is because of the multiple account schema capabilities of ADempiere. 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.   
   
+
 
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.  
+
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.
== Complete and Post Physical Inventory record ==
+
{{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
Upon completely keying-in the Physical Inventory, Complete and Post the Physical Inventory. The account posting should be as follows:
+
* created the banks and payment processors for credit cards
   
+
 
  DR Inventory (Standard Cost)  
+
= Prepare =
    CR Inventory Clearing (Standard Cost)
+
 
 +
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 {{TabRef|AccountElement|ElementValue|Element Value}} of the {{WindowRef|AccountElement|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 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:
 +
* '''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 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 =
 +
 
 +
==  Unmatched Material ==
 +
 
 +
{{Note|At the time of writing ADempiere did not have an importer for material receipts so the creation of material receipts had to be done by hand.}}
 +
 
 +
In ADempiere, 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 [http://www.chuckboecking.com/blog/bid/186154/Advanced-ERP-Accounting-Tutorial-2 detailed tutorial] how and what general ledger accounts are used during the matching process.
 
   
 
   
== Check Inventory Clearing balance ==
+
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.
   
+
 
Upon completion of all Physical Inventory postings, the Inventory Clearing balance in the System should be 0.  
+
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 ADempiere following the instructions in the [[Data Import]] page.
+
 
== Revert Default Accounts ==
+
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.
   
+
 
Revert the Default Accounts for Inventory Adjustment for each Warehouse to the original Default Account.  
+
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.
+
 
== Deactivate Opening Product ==
+
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.
+
 
Select the Active checkbox for Opening Product in the Product Masterdata setup to No.  
+
{{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.}}
   
+
 
== Generate Trial Balance from the System ==
+
== Data Import ==
   
+
 
Generate the Trial Balance as at the Opening Date and compare against the Closing Trial Balance obtained earlier.  
+
Follow the instructions in the [[Data Import]] page to import the spreadsheets prepared above:
   
+
* Opening Trial Balance
== Deactivate Debtor Clearing, Creditor Clearing, Bank Clearing and Inventory Clearing ==
+
* Open AR Invoices
   
+
* Unallocated AR Receipts
Uncheck the Active checkbox for the above in the Account Element setup.
+
* 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 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.
 +
 
 +
=== Open AR Invoices ===
 +
 
 +
Create or import each outstanding AR Invoice with one Invoice (Customer) in ADempiere. 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 ADempiere. 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 paymentThe 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 ===
 +
 
 +
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 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.
 +
 
 +
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 ==
  
= Summary Outcome =
+
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.
You should now have your opening balances established in Adempiere.
+

Latest revision as of 11:39, 8 January 2013

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

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; 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 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 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.gif 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 ADempiere. 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.
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
  • 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. 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.gif 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.gif 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:

  • 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 ADempiere match the closing costs on the inventory valuation.
Note.gif 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

Unmatched Material

Note.gif Note:

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

In ADempiere, 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 ADempiere following the instructions in the Data Import page.

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.gif 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

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

  • 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 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.

Open AR Invoices

Create or import each outstanding AR Invoice with one Invoice (Customer) in ADempiere. 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 ADempiere. 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

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 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.

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

Note.gif 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.