Difference between revisions of "Chart of Accounts"

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
(COA Import File)
m (Importing the COA: Fix link.)
 
(34 intermediate revisions by 8 users not shown)
Line 1: Line 1:
== Purpose of the Chart of Accounts ==
+
{{Breadcrumb|Table of Contents#System Administrator's Guide|Implementation|next=Initial Client Setup Process}}
 +
[[Category:Documentation]]
 +
[[Category:System Admin documentation]]
 +
[[Category:Accounting]]
  
== Chart of Accounts Design ==
+
[[Image:EventAcct.gif|right]]
*The COA as found in ADempiere is in the form of an excel spreadsheet where users can adapt to their own format.  
+
A '''Chart of Accounts (COA)''' literally means a list of ledger accounts that are to be maintained in the Client's accounting system.  This article gives some background and provides hint and tips on designing a COA for use in ADempiere. A COA is required before a Client can be created in the system, making the COA one of the first elements that must be completed during an implementation of the ADempiere software.
*It can then be saved as a comma separated value file or accting.csv and imported into ADempiere.  
+
 
*Daniel Norin has created a nice [[COA Editor]] to access that csv file to change or create a new COA without looking at the excel file.  
+
== Background ==
*The COA follows accounting classifications underlying General Accepted Principles of Accounting (GAAP) in practice throughout the world.
+
 
===Summary List of COA===
+
Before calculators/computers came into existence, accounting was performed by hand in a ledger. A separate ledger was maintained for similar or related entries and this was called an Account.  For a small business, the group of ledgers were often kept in one or more books.  Processes were adopted that allowed the accounting to be done easily:
*Here we lay out the general structure of the COA by displaying only the ''top level summary'' elements.
+
 
 +
# Subsidiary Ledgers for all kinds of transactions were kept and then just the totals of these were posted into the general ledger. The reasons were simplicity and the fact that two people could not write in the same account book at the same time. Computers don't have this problem but a common hang over in todays financial software is the breakdown of the General Ledger into Debtors (Accounts Receivable), Creditor (Accounts Payable) and General (sometimes referred to as Nominal) Ledgers.
 +
# Debits and credits (meaning only left side and right side) were kept in separate columns because it was easier to add all the positive numbers in the debit column and then all the 'positive' numbers in the credit column and then subtract one from the other to find the net movement. It was beyond people's capabilities to accurately add a column which contained both positive and negative numbers.
 +
# Some accounts were called '''Permanent''' (or '''Real''') since their balance at the end of a period was passed to the next period as a beginning balance.
 +
# Other accounts were called '''Temporary''' accounts since they only tracked changes in income and expenses that would affect Retained Earnings.
 +
# A balance sheet was maintained to ensure that the basic equation ''Assets'' = ''Liabilities'' + ''Owner's Equity'' held for all permanent accounts. Asset accounts, by arbitrary convention, increase on the Debit (left hand) side of the ledger.  From this, the meaning of a debit or credit to any other account can be derived.
 +
# Any transaction had to have at least two entries - the double entry principle - which balanced credits and debits and maintained the balance sheet balanced.
 +
# A Trial Balance or listing of the sum of all entries in each account at a point in time should always have equal debits and credits.
 +
# At year end it was necessary to find the profit or loss and update the retained earnings of the owners. It was also necessary to start entering transactions in P&L accounts for the new year from a zero balance. This was done by actually posting entries in the manual ledgers at year end to bring each Temporary P&L account to a zero balance and increase the retained earnings account accordingly.
 +
# For complex situations, multiple sets of books could be maintained and transactions would be duplicated in both sets of books following, perhaps, different rules or using different accounts.
 +
 
 +
The introduction of computers has made the task of accounting much less tedious but the basic principles remain the same.  Computers offer the ability to make order out of a random pile of data and reorder it all again using a different set of criteria.  On the other hand, the power of computers and flexibility of the software has allowed managers to over complicate accounting where it need not be so complex.  When dealing with a book of ledgers, it was often the size of the book that determined how many ledger accounts were included in the book.  With a computer, there is no limitation.  Without the restriction, management will often desire to use granular accounts defined for very specific reporting purposes that can be easily rolled up and summarized in a variety of ways.  Overdone, this can result in a huge number of accounts that are difficult to keep track of or manage. 
 +
 
 +
==Recommendations and Pitfalls in COA Design ==
 +
 
 +
In practical terms, managers must think of COAs as more than just a list of accounts.  They must think of all of the controlling data structures that (should) reflect the informational needs of the business for regulatory accounting to management economics.  These should answer questions like what is the loan default rate on various portfolios of mortgages, how much revenue do we make per kilometre of optical fibre owned/operated, or simply what is our revenue this month/quarter/year from this product, product group business area.  Understanding the reporting requirements is critical to ensuring the COA has enough but not too much detail.
 +
 
 +
It is difficult (if not impossible) to satisfy all of the informational needs with a single dimensional chart of accounts.  ADempiere provides the following '''Accounting Dimensions''' which can be 'sliced and diced' in order to meet your reporting requirements:
 +
* organization (business)
 +
* account element (the actual account)
 +
* product
 +
* business partner
 +
* sales region
 +
* activity
 +
* project
 +
* campaign
 +
 
 +
'''TIP: You should use the ADempiere accounting dimensions for the purpose they are intended rather than trying to recreate dimensions within your account elements.  A common mistake is to build your products in to the account elements in order to report revenue / margin by product.'''
 +
:In one case (a Telecoms company), one of their subsidiary businesses had over 130,000 accounts to account for a relatively small part of the business.    This is by no means uncommon and I've seen it literately dozens of times. Obviously, as a result of the large number of accounts, there was a large amount of administration around changing an account and new accounts were still being added.  A target to aim for in large businesses is around 1000-1400 accounts.  This can be difficult when you operate in many countries and use of permissions in the system can be used to control access to posting to account combinations which allows accountants to feel happier about sharing account codes rather than creating specific accounts that belong only to them. (i.e. the people own a slice of the matrix of the code rather than the whole code and this is enforced with user permissions)
 +
 
 +
'''TIP: Create a process for adding and re-organising account codes and other organisational dimensions.  Find and allocate owners and build them into the approval process (you can use summary accounts to delegate sub-groups of your structure)'''
 +
:Each country has special requirements imposed by the government and in some cases, reflective of that societies values.  For example, when doing some work on a global chart of accounts for a business, the Japanese contingent explained to me that it is polite in Japanese culture when requesting payment from your customer, to provide them with a bank account at the same bank that they use for which they can use to pay you.  As Japan has many banks, this requires polite organisations to maintain many bank accounts in order not to offend their business partners.  Whilst I accept this is a business need, we do not need to manage these accounts at a management level (whilst I accept they need to be reconciled and accounted for separately).  You can gain informational benefits by using sub-accounts to record the myriad of bank accounts.  A similar technique can be used to sub-ordinate information about local payroll deductions and other social costs and taxes.
 +
 
 +
'''TIP: Use sub accounts to deal with local variations and names whilst preserving your COA structure'''
 +
:Many organizations find themselves in a position where in each new region they have entered, they have allowed the accountant to select an accounting system and get on with it - knowing they will ensure the correct forms are lodged and that the business has complied with the filing requirements.  However, with no common strategy defined, it is common to select an accounting system and structure your COA on your local financial and other reporting requirements.  Whilst this practice is a fast track route to satisfying the requirements of external users, it does not balance the need for internal users and hence many organizations find themselves in a position where satisfying the internal users for never ending informational requests is time consuming.  Whilst the use of a good BI tool and some analysts can mitigate this issue in some businesses, a better solution is to balance the informational needs when designing your COA (and other master data structures)
 +
 
 +
'''TIP: Consider designing your COA structure on your Management COA rather than your Financial COA'''
 +
:A chart of accounts is different for every organization because:
 +
:* in many countries there is no hard and fast rule requiring COAs to be conforming to a specific structure (however there are suggested forms like the EU level 7 schedule of accounts which has been adopted to various degrees of prescription in EU member states)
 +
:* the chart of accounts should reflect the informational needs of the organization which include the needs of the regulatory environment(s) in which the operation (industries, countries, etc), the needs of the share holders & stakeholders (private, public owned businesses, banks, private equity firms etc) and the needs of management and various other reporting/informational requirements.
 +
 
 +
== Creating and Importing a COA File ==
 +
 
 +
ADempiere users may design and import their COA using an excel spreadsheet that they can adapt to their own format. This file can then be saved as a comma separated value file or accting.csv and imported into ADempiere.
 +
{{Note|Daniel Tamm has created a nice [[COA Editor]] to access that csv file to change or create a new COA without looking at the excel file.}}
 +
Here is an image of a COA spreadsheet as inherited by us from the Compiere GPL project.
 +
{{Space}}
 +
[[Image:COA.gif]]
 +
{{Space}}
 +
The COA import spreadsheet is a file provided to assist end users in importing their chart of accounts. The ADEMPIERE_HOME/data/import directory contains a number of example COA files.  The spreadsheet includes a number of fields to assist with the creation of report dimensions.
 +
 
 +
{| class="wikitable" style="margin: 1em 1em 1em 1em"
 +
|- bgcolor=#5797bb
 +
! '''Column    || Description           
 +
|-
 +
| Account value || unique ID of the account
 +
|-
 +
| Account name || short name of the account
 +
|-
 +
| Account description || a description of the account
 +
|-
 +
| Account type || type of the entries written to this account (Asset/Liability/Owner's Equity/Expense/Revenue/Memo)
 +
|-
 +
| Account sign || Natural(+/-)/Debit(-)/Credit(+)
 +
|-
 +
| Document controlled || if yes, then it is not possible to post manually to this account
 +
|-
 +
| Summary account || No entries can be posted to this account, this is a summary of the subaccounts. All entries have to be submitted to the subaccounts
 +
|-
 +
| Default account || Name of the Default Account Column - this is a link to a system controlled default account
 +
|-
 +
| Account parent || Summary account where the entries of this account are summarized to
 +
|-
 +
| Balance sheet || If this item appears in the balance sheet report - it's identifier
 +
|-
 +
| Balance sheet name || If this item appears in the balance sheet report - it's name as displayed in the report
 +
|-
 +
| US 1120 Balance Sheet || If this item appears in the form 1120 (US tax balance sheet) report - it's identifier
 +
|-
 +
| US 1120 Balance Sheet Name || If this item appears in the form 1120 (US tax balance sheet) report - it's name as displayed in the report
 +
|-
 +
| Profit&Loss || If this item appears in the profit and loss report - it's identifier
 +
|-
 +
| Profit&Loss Name || If this item appears in the profit and loss report - it's name as displayed in the report
 +
|-
 +
| US 1120 Income Stmt || If this item appears in the form 1120 (US tax profit and loss) report - it's identifier
 +
|-
 +
| US 1120 Income Stmt Name || If this item appears in the form 1120 (US tax profit and loss) report - it's name as displayed in the report
 +
|-
 +
| Cash Flow || If this item appears in the cash flow report - it's identifier
 +
|-
 +
| Cash Flow Name ||  If this item appears in the cash flow report - it's name as displayed in the report
 +
|-
 +
|}
 +
{{Note|In regards to US tax return form 1120, see: http://www.irs.gov/pub/irs-pdf/f1120.pdf.}}
 +
At a minimum, each line in the spreadsheet will need to have value, name, and type defined.
 +
 
 +
It is often useful to group the detail accounts in a tree, using summary accounts to collect the balances from the subordinate detail accounts.  The standard way of numbering these accounts is to use single digits for the values of the top level accounts and adding a digit for each branch of the tree until the detail account is reached.  Each detail account gets the maximum number of digits - padded with zeros if necessary. In the sample image above, you can see:
 +
* 1 Assets
 +
** 11 Cash
 +
*** 11100 Checking Account
 +
*** 11110 Checking Account in transfer ...
 +
 
 +
A classic general structure of the COA displaying only the ''top level summary'' elements would appear as follows:
 
{| class="wikitable" style="margin: 1em 1em 1em 1em"  
 
{| class="wikitable" style="margin: 1em 1em 1em 1em"  
 
|- bgcolor=#5797bb
 
|- bgcolor=#5797bb
Line 26: Line 132:
 
|}
 
|}
  
 +
Another way of summarizing the information that will make the balance sheet easier to manage would be as follows:
 +
{| class="wikitable" style="margin: 1em 1em 1em 1em"
 +
|- bgcolor=#5797bb
 +
! '''Account Value    || Account Name || Account Type           
 +
|-
 +
| 1 || Assets || Asset 
 +
|-
 +
| 2 || Liabilities || Liability
 +
|-
 +
| 3 || Owner's Equity/Net Worth || Owner's Equity
 +
|-
 +
| 4 || Net Income || Revenue
 +
|-
 +
| 41 || Sales || Revenue
 +
|-
 +
| 42 || Cost of Goods Sold || Expense
 +
|-
 +
| 43 || Expenses || Expense
 +
|-
 +
|}
 +
 +
However you choose to summarize the data, note that you can easily make changes to the tree structure later using the {{WindowRef|AccountElement|Account Element}} {{TabRef|AccountElement|Element Value}}.
 +
 +
The main constraint you have to meet in establishing a COA is to map the COA accounts to the necessary default accounts that ADempiere expects to use in its internal accounting rules.  These are identified in the default_account column and each of the necessary default account names must appear once in the imported COA file. See [[Default accounts]] for a full list.  The [[COA Editor]] mentioned above will check that all defaults have been mapped to COA accounts.
 +
 +
The COA file must have '''one and only one''' account defined for each of the Default Accounts. Otherwise, the import process will fail. Also, avoid changing the column order. To change the example COA files:
 +
* Delete all lines with exception of the ones, which have ''Default Accounts''.
 +
* Change the lines (value/key, name, description) as you need for your chart of accounts.
 +
* Add the other accounts in your chart of accounts.
 +
* Save the file as a CSV file.
 +
 +
Again, the [[COA Editor]] does a lot of this for you.
 +
 +
=== Importing the COA ===
 +
Once the COA file is ready, there are two steps required to import the data:
 +
# In the initial Client creation process, the default accounts in the COA are imported as the Client is created.  See [[Initial Client Setup Process]].
 +
# After the Client has been created (or if one already exists), the non-default accounts and the hierarchy in the COA can be imported manually.  To do this:
 +
## Log in to  the system with your new Admin Role.
 +
## Go down the Menu Tree to {{TreeRef|System Admin|Data|Data Import|Import File Loader}} and choose your COA file (set the Import Format to 'Accounting - Accounts' value). Confirm your choice and the accounts will be imported into the I_* table in the database.
 +
## Go down the Menu Tree again to {{TreeRef|System Admin|Data|Data Import|Import Account}} and open the {{WindowRef|ImportAccount|Import Account}}. In there just press the 'Import Accounts' button. Choose your relevant Element and data-importing behavior and confirm.
 +
## After the process is finished you have to log out and log back in. If you select the {{TabRef|AccountElement|Element Value}} in the {{WindowRef|AccountElement|Account Element}}, you will see your Chart Of Accounts organized in your preferred hierarchy.
  
 +
The second part of this import is included in the [[Initial Client Setup Review]] process.
  
 +
See [[Data Import]] for more details on the data import process.
  
== Field Descriptions ==
+
=== Example Charts of Accounts ===
 +
Standard chart of accounts for UK government entities, funds, public corporations and reporting funds
  
 +
http://www.wga.gov.uk/documents/SCOA_changes_2005-06_2006-07.xls
  
=== I_ELEMENTVALUE ===
+
http://www.hm-treasury.gov.uk/d/wga_200809_appendix_4_list_of_scoas_and_changes.xls
Te fields below are from the I_ElementValue table. I_* tables provide a 'staging area' for importing data before this data is tested against business rules and if found to be conforming to these rules, is imported in to the C_ElementValue table.  These tables describe 'element values' which might otherwise be described as the members of the element dimension - and most notably the Accounts dimension known to accountants as the chart of accounts.
+
  
        Column        |            Desc
 
i_elementvalue_id    | ID for import processing
 
ad_client_id          | Client ID
 
ad_org_id            | Organisation ID
 
isactive              | Is Active flag
 
createdby            | Adempiere core field
 
created              | Adempiere core field
 
updated              | Adempiere core field
 
updatedby            | Adempiere core field
 
i_isimported          | Flag for import processing
 
i_errormsg            | Error message if import failed
 
c_element_id          | Element ID - see c_element
 
elementname          | Element Name
 
c_elementvalue_id    | Internal Element ID
 
value                | Account "Natural" code
 
name                  | Account Name
 
description          | Account description
 
accounttype          | Account classification - A = Asset, L = Liability, E = Expense, R = Revenue, O = Owners Equity, M = Memorandum
 
accountsign          | Indicates the Natural Sign of the Account as a Debit or Credit
 
isdoccontrolled      | If the account is document controlled, users are unable to post directly to the account (you must post documents and they post to the account)
 
issummary            | If the account is a summary account then it is a header / total of the children accounts
 
parentvalue          | Key of the Parent
 
parentelementvalue_id | The parent (summary) account - Parent node reference
 
postactual            | Is the user allowed to post actual values? (Y/N)
 
postbudget            | Is the user allowed to post budget values? (Y/N)
 
poststatistical      | Is the user allowed to post statistical values? (Y/N)
 
postencumbrance      | Is the user allowed to post commitments / encumbrance values? (Y/N)
 
default_account      | Name of the Default Account Column
 
ad_column_id          | Column in the table
 
processing            | Flags for import processing
 
processed            | Flags for import processing
 
  
=== COA Import File ===
+
== Managing the COA ==
  
The COA import file is a file provided to assist end users in importing their chart of accounts.  The data contained in the spreadsheet is imported in to the I_ElementValue table and once validated, this is imported in to the C_ElementValue table where is is accessible via the element tree editor.  The spreadsheet includes a number of fields to assist with the creation of report dimensions.
+
After you have imported a COA, you can manage it and make changes using the {{WindowRef|AccountElement|Account Element}}.  The {{TabRef|AccountElement|Element Value}} provides a tree tool where the various accounts can be organized in the desired hierarchy.  Note that the tree organization has no relation to the "value" field in the accounts mentioned above.  The tree tool will allow the accounts to organized in any order.  What is important is the relationship between subordinate and summary accounts as the summary accounts can be used in reporting - which simplifies the report setup - while the details of what accounts are included in the summary is held in one place. If you include the details in the reports and then reorganize the tree or add an account, you will have to update all the reports. Thankfully, there is no need to do so as there is a handy feature on the reports to display the details which will expand all the summary accounts in the report.
  
* Account value - unique ID of the account
+
After making changes to the COA tree, you may have to '''Recalculate the Report Cube''' before the changes are visible in the reports.
* Account name - short name of the account
+
* Account description - a description of the account
+
* Account type - type of the entries written to this account (Asset/Liability/Owner's Equity/Expense/Revenue/Memo)
+
* Account sign - Natural(+/-)/Debit(-)/Credit(+)
+
* Document controlled - if yes, then it is not possible to post manually to this account
+
* Summary account - No entries can be posted to this account, this is a summary of the subaccounts. All entries have to be submitted to the subaccounts
+
* Default account - Name of the Default Account Column - this is a link to a system controlled default account
+
* Account parent - Summary account where the entries of this account are summarized to
+
* Balance sheet - If this item appears in the balance sheet report - it's identifier
+
* Balance sheet name - If this item appears in the balance sheet report - it's name as displayed in the report
+
* US 1120 Balance Sheet - If this item appears in the form 1120 (US tax balance sheet) report - it's identifier
+
* US 1120 Balance Sheet Name - If this item appears in the form 1120 (US tax balance sheet) report - it's name as displayed in the report
+
* Profit&Loss - If this item appears in the profit and loss report - it's identifier
+
* Profit&Loss Name - If this item appears in the profit and loss report - it's name as displayed in the report
+
* US 1120 Income Stmt - If this item appears in the form 1120 (US tax profit and loss) report - it's identifier
+
* US 1120 Income Stmt Name - If this item appears in the form 1120 (US tax profit and loss) report - it's name as displayed in the report
+
* Cash Flow - If this item appears in the cash flow report - it's identifier
+
* Cash Flow Name -  If this item appears in the cash flow report - it's name as displayed in the report
+
* tri (only in definition csv/xls) - ?
+
  
* reference in regards to US tax return form 1120: http://www.irs.gov/pub/irs-pdf/f1120.pdf
+
You can use the {{WindowRef|AccountElement|Account Element}} to add new accounts. Accounts can only be deleted if they have no recorded activity and they are not referenced from one of the Accounting Tabs - meaning they are not one of the default accounts.
  
==Common Errors in Design ==
+
=See Also=
 +
* [[Accounting]]
 +
* [[Default accounts]]
 +
* [[Default Accounts Usage]]

Latest revision as of 09:31, 7 August 2011

Table of Contents#System Administrator's Guide{{#if: Implementation| | Implementation }}{{#if: | | [[{{{3}}}]] }}{{#if: | | [[{{{4}}}]] }}{{#if: | | [[{{{5}}}]] }} | Chart of Accounts{{#if: Initial Client Setup Process| | Initial Client Setup Process }} ⇒
EventAcct.gif

A Chart of Accounts (COA) literally means a list of ledger accounts that are to be maintained in the Client's accounting system. This article gives some background and provides hint and tips on designing a COA for use in ADempiere. A COA is required before a Client can be created in the system, making the COA one of the first elements that must be completed during an implementation of the ADempiere software.

Background

Before calculators/computers came into existence, accounting was performed by hand in a ledger. A separate ledger was maintained for similar or related entries and this was called an Account. For a small business, the group of ledgers were often kept in one or more books. Processes were adopted that allowed the accounting to be done easily:

  1. Subsidiary Ledgers for all kinds of transactions were kept and then just the totals of these were posted into the general ledger. The reasons were simplicity and the fact that two people could not write in the same account book at the same time. Computers don't have this problem but a common hang over in todays financial software is the breakdown of the General Ledger into Debtors (Accounts Receivable), Creditor (Accounts Payable) and General (sometimes referred to as Nominal) Ledgers.
  2. Debits and credits (meaning only left side and right side) were kept in separate columns because it was easier to add all the positive numbers in the debit column and then all the 'positive' numbers in the credit column and then subtract one from the other to find the net movement. It was beyond people's capabilities to accurately add a column which contained both positive and negative numbers.
  3. Some accounts were called Permanent (or Real) since their balance at the end of a period was passed to the next period as a beginning balance.
  4. Other accounts were called Temporary accounts since they only tracked changes in income and expenses that would affect Retained Earnings.
  5. A balance sheet was maintained to ensure that the basic equation Assets = Liabilities + Owner's Equity held for all permanent accounts. Asset accounts, by arbitrary convention, increase on the Debit (left hand) side of the ledger. From this, the meaning of a debit or credit to any other account can be derived.
  6. Any transaction had to have at least two entries - the double entry principle - which balanced credits and debits and maintained the balance sheet balanced.
  7. A Trial Balance or listing of the sum of all entries in each account at a point in time should always have equal debits and credits.
  8. At year end it was necessary to find the profit or loss and update the retained earnings of the owners. It was also necessary to start entering transactions in P&L accounts for the new year from a zero balance. This was done by actually posting entries in the manual ledgers at year end to bring each Temporary P&L account to a zero balance and increase the retained earnings account accordingly.
  9. For complex situations, multiple sets of books could be maintained and transactions would be duplicated in both sets of books following, perhaps, different rules or using different accounts.

The introduction of computers has made the task of accounting much less tedious but the basic principles remain the same. Computers offer the ability to make order out of a random pile of data and reorder it all again using a different set of criteria. On the other hand, the power of computers and flexibility of the software has allowed managers to over complicate accounting where it need not be so complex. When dealing with a book of ledgers, it was often the size of the book that determined how many ledger accounts were included in the book. With a computer, there is no limitation. Without the restriction, management will often desire to use granular accounts defined for very specific reporting purposes that can be easily rolled up and summarized in a variety of ways. Overdone, this can result in a huge number of accounts that are difficult to keep track of or manage.

Recommendations and Pitfalls in COA Design

In practical terms, managers must think of COAs as more than just a list of accounts. They must think of all of the controlling data structures that (should) reflect the informational needs of the business for regulatory accounting to management economics. These should answer questions like what is the loan default rate on various portfolios of mortgages, how much revenue do we make per kilometre of optical fibre owned/operated, or simply what is our revenue this month/quarter/year from this product, product group business area. Understanding the reporting requirements is critical to ensuring the COA has enough but not too much detail.

It is difficult (if not impossible) to satisfy all of the informational needs with a single dimensional chart of accounts. ADempiere provides the following Accounting Dimensions which can be 'sliced and diced' in order to meet your reporting requirements:

  • organization (business)
  • account element (the actual account)
  • product
  • business partner
  • sales region
  • activity
  • project
  • campaign

TIP: You should use the ADempiere accounting dimensions for the purpose they are intended rather than trying to recreate dimensions within your account elements. A common mistake is to build your products in to the account elements in order to report revenue / margin by product.

In one case (a Telecoms company), one of their subsidiary businesses had over 130,000 accounts to account for a relatively small part of the business. This is by no means uncommon and I've seen it literately dozens of times. Obviously, as a result of the large number of accounts, there was a large amount of administration around changing an account and new accounts were still being added. A target to aim for in large businesses is around 1000-1400 accounts. This can be difficult when you operate in many countries and use of permissions in the system can be used to control access to posting to account combinations which allows accountants to feel happier about sharing account codes rather than creating specific accounts that belong only to them. (i.e. the people own a slice of the matrix of the code rather than the whole code and this is enforced with user permissions)

TIP: Create a process for adding and re-organising account codes and other organisational dimensions. Find and allocate owners and build them into the approval process (you can use summary accounts to delegate sub-groups of your structure)

Each country has special requirements imposed by the government and in some cases, reflective of that societies values. For example, when doing some work on a global chart of accounts for a business, the Japanese contingent explained to me that it is polite in Japanese culture when requesting payment from your customer, to provide them with a bank account at the same bank that they use for which they can use to pay you. As Japan has many banks, this requires polite organisations to maintain many bank accounts in order not to offend their business partners. Whilst I accept this is a business need, we do not need to manage these accounts at a management level (whilst I accept they need to be reconciled and accounted for separately). You can gain informational benefits by using sub-accounts to record the myriad of bank accounts. A similar technique can be used to sub-ordinate information about local payroll deductions and other social costs and taxes.

TIP: Use sub accounts to deal with local variations and names whilst preserving your COA structure

Many organizations find themselves in a position where in each new region they have entered, they have allowed the accountant to select an accounting system and get on with it - knowing they will ensure the correct forms are lodged and that the business has complied with the filing requirements. However, with no common strategy defined, it is common to select an accounting system and structure your COA on your local financial and other reporting requirements. Whilst this practice is a fast track route to satisfying the requirements of external users, it does not balance the need for internal users and hence many organizations find themselves in a position where satisfying the internal users for never ending informational requests is time consuming. Whilst the use of a good BI tool and some analysts can mitigate this issue in some businesses, a better solution is to balance the informational needs when designing your COA (and other master data structures)

TIP: Consider designing your COA structure on your Management COA rather than your Financial COA

A chart of accounts is different for every organization because:
  • in many countries there is no hard and fast rule requiring COAs to be conforming to a specific structure (however there are suggested forms like the EU level 7 schedule of accounts which has been adopted to various degrees of prescription in EU member states)
  • the chart of accounts should reflect the informational needs of the organization which include the needs of the regulatory environment(s) in which the operation (industries, countries, etc), the needs of the share holders & stakeholders (private, public owned businesses, banks, private equity firms etc) and the needs of management and various other reporting/informational requirements.

Creating and Importing a COA File

ADempiere users may design and import their COA using an excel spreadsheet that they can adapt to their own format. This file can then be saved as a comma separated value file or accting.csv and imported into ADempiere.

Note.gif Note:

Daniel Tamm has created a nice COA Editor to access that csv file to change or create a new COA without looking at the excel file.

Here is an image of a COA spreadsheet as inherited by us from the Compiere GPL project.

 

COA.gif

 

The COA import spreadsheet is a file provided to assist end users in importing their chart of accounts. The ADEMPIERE_HOME/data/import directory contains a number of example COA files. The spreadsheet includes a number of fields to assist with the creation of report dimensions.

Column Description
Account value unique ID of the account
Account name short name of the account
Account description a description of the account
Account type type of the entries written to this account (Asset/Liability/Owner's Equity/Expense/Revenue/Memo)
Account sign Natural(+/-)/Debit(-)/Credit(+)
Document controlled if yes, then it is not possible to post manually to this account
Summary account No entries can be posted to this account, this is a summary of the subaccounts. All entries have to be submitted to the subaccounts
Default account Name of the Default Account Column - this is a link to a system controlled default account
Account parent Summary account where the entries of this account are summarized to
Balance sheet If this item appears in the balance sheet report - it's identifier
Balance sheet name If this item appears in the balance sheet report - it's name as displayed in the report
US 1120 Balance Sheet If this item appears in the form 1120 (US tax balance sheet) report - it's identifier
US 1120 Balance Sheet Name If this item appears in the form 1120 (US tax balance sheet) report - it's name as displayed in the report
Profit&Loss If this item appears in the profit and loss report - it's identifier
Profit&Loss Name If this item appears in the profit and loss report - it's name as displayed in the report
US 1120 Income Stmt If this item appears in the form 1120 (US tax profit and loss) report - it's identifier
US 1120 Income Stmt Name If this item appears in the form 1120 (US tax profit and loss) report - it's name as displayed in the report
Cash Flow If this item appears in the cash flow report - it's identifier
Cash Flow Name If this item appears in the cash flow report - it's name as displayed in the report
Note.gif Note:

In regards to US tax return form 1120, see: http://www.irs.gov/pub/irs-pdf/f1120.pdf.

At a minimum, each line in the spreadsheet will need to have value, name, and type defined.

It is often useful to group the detail accounts in a tree, using summary accounts to collect the balances from the subordinate detail accounts. The standard way of numbering these accounts is to use single digits for the values of the top level accounts and adding a digit for each branch of the tree until the detail account is reached. Each detail account gets the maximum number of digits - padded with zeros if necessary. In the sample image above, you can see:

  • 1 Assets
    • 11 Cash
      • 11100 Checking Account
      • 11110 Checking Account in transfer ...

A classic general structure of the COA displaying only the top level summary elements would appear as follows:

Account Value Account Name Account Type
1 Assets Asset
2 Liabilities Liability
3 Owner's Equity/Net Worth Owner's Equity
4 Sales Revenue
5 Cost of Goods Sold Expense
6 Expenses Expense

Another way of summarizing the information that will make the balance sheet easier to manage would be as follows:

Account Value Account Name Account Type
1 Assets Asset
2 Liabilities Liability
3 Owner's Equity/Net Worth Owner's Equity
4 Net Income Revenue
41 Sales Revenue
42 Cost of Goods Sold Expense
43 Expenses Expense

However you choose to summarize the data, note that you can easily make changes to the tree structure later using the {{#if: Account Element|Account Element|AccountElement }} Window {{#if: |{{{3}}}|Element Value }} Tab.

The main constraint you have to meet in establishing a COA is to map the COA accounts to the necessary default accounts that ADempiere expects to use in its internal accounting rules. These are identified in the default_account column and each of the necessary default account names must appear once in the imported COA file. See Default accounts for a full list. The COA Editor mentioned above will check that all defaults have been mapped to COA accounts.

The COA file must have one and only one account defined for each of the Default Accounts. Otherwise, the import process will fail. Also, avoid changing the column order. To change the example COA files:

  • Delete all lines with exception of the ones, which have Default Accounts.
  • Change the lines (value/key, name, description) as you need for your chart of accounts.
  • Add the other accounts in your chart of accounts.
  • Save the file as a CSV file.

Again, the COA Editor does a lot of this for you.

Importing the COA

Once the COA file is ready, there are two steps required to import the data:

  1. In the initial Client creation process, the default accounts in the COA are imported as the Client is created. See Initial Client Setup Process.
  2. After the Client has been created (or if one already exists), the non-default accounts and the hierarchy in the COA can be imported manually. To do this:
    1. Log in to the system with your new Admin Role.
    2. Go down the Menu Tree to System Admin{{#if: Data |  » Data }}{{#if: Data Import |  » Data Import }}{{#if: Import File Loader |  » Import File Loader }}{{#if: |  »  }} and choose your COA file (set the Import Format to 'Accounting - Accounts' value). Confirm your choice and the accounts will be imported into the I_* table in the database.
    3. Go down the Menu Tree again to System Admin{{#if: Data |  » Data }}{{#if: Data Import |  » Data Import }}{{#if: Import Account |  » Import Account }}{{#if: |  »  }} and open the {{#if: Import Account|Import Account|ImportAccount }} Window. In there just press the 'Import Accounts' button. Choose your relevant Element and data-importing behavior and confirm.
    4. After the process is finished you have to log out and log back in. If you select the {{#if: |{{{3}}}|Element Value }} Tab in the {{#if: Account Element|Account Element|AccountElement }} Window, you will see your Chart Of Accounts organized in your preferred hierarchy.

The second part of this import is included in the Initial Client Setup Review process.

See Data Import for more details on the data import process.

Example Charts of Accounts

Standard chart of accounts for UK government entities, funds, public corporations and reporting funds

http://www.wga.gov.uk/documents/SCOA_changes_2005-06_2006-07.xls

http://www.hm-treasury.gov.uk/d/wga_200809_appendix_4_list_of_scoas_and_changes.xls


Managing the COA

After you have imported a COA, you can manage it and make changes using the {{#if: Account Element|Account Element|AccountElement }} Window. The {{#if: |{{{3}}}|Element Value }} Tab provides a tree tool where the various accounts can be organized in the desired hierarchy. Note that the tree organization has no relation to the "value" field in the accounts mentioned above. The tree tool will allow the accounts to organized in any order. What is important is the relationship between subordinate and summary accounts as the summary accounts can be used in reporting - which simplifies the report setup - while the details of what accounts are included in the summary is held in one place. If you include the details in the reports and then reorganize the tree or add an account, you will have to update all the reports. Thankfully, there is no need to do so as there is a handy feature on the reports to display the details which will expand all the summary accounts in the report.

After making changes to the COA tree, you may have to Recalculate the Report Cube before the changes are visible in the reports.

You can use the {{#if: Account Element|Account Element|AccountElement }} Window to add new accounts. Accounts can only be deleted if they have no recorded activity and they are not referenced from one of the Accounting Tabs - meaning they are not one of the default accounts.

See Also