G/L Account Structure in Sage 300 ERP
Sage 300 ERP has a fairly flexible mechanism for setting up your General Ledger Chart of Accounts. This is a fairly important activity since it controls how you will be able to run financial reports and dice and slice your financial information. I’m not an Accountant so I might miss some of the finer points of accounting, and it’s always important to follow generally accepted accounting principles as much as possible. In some industries and in some countries your chart of accounts is specified for you. For instance Chine specifies the chart of accounts that companies must use.
So the actual chart of accounts can have some fairly hard constraints on how it’s set up. Fortunately there are some other mechanisms like account groups, account rollup, optional fields and transactional optional fields that can be used to enhance reporting capabilities.
I blogged on the general structure of G/L here. This blog posting is going to look a bit more in depth into the structure of G/L Accounts and how to generate some fairly flexible reports.
Each G/L Account can be up to 45 characters in length (formatted). It can consist of up to ten segments each of which can be up to 45 characters. One segment must be designated as the Account segment. Each combination of segments is called an Account Structure.
The account segments are defined in G/L Options on the segments tab and are stored in GLABK (GL0022). These are the building blocks for the G/L Accounts and must be defined first. In Options UI you also specify the segment separator character and which segment is the Account Segment. Next you would define the Account Structures in the G/L Account Structures setup UI and stored in GLABRX (GL0023), these specify the various combinations of account segments that you will be using. You also specify which is the default structure code. The Account Segment isn’t validated and can be any value. The other segment can only have specific values and you specify these in the G/L Segment Codes setup UI and stored in GLASV (GL0021). Then you would define the Account Groups you desire in the G/L Account Groups setup UI.
With these values setup, you can now enter your Chart of Accounts into the Accounts UI in the G/L Accounts folder. The Accounts are stored in GLAMF (GL0001).
If you are following generally accepted accounting principles you should have an idea of how you want your G/L Accounts structured and you should have an idea of how you want your Financial Reports to be structured which then dictates your Account Groups. The Account Groups specify the normal F/R reporting categories like “Cash and Cash Equivalents”, “Accumulated Depreciation” or “Provision for Income Taxes”.
Now that you have a structure, creating all these accounts one by one sounds rather tedious. Fortunately there is G/L Create Accounts function that will create all your Accounts en masse. Our Chart of Accounts isn’t a sparse system, meaning that you do need to create an Account before you use it, so this is a very useful tool.
There are two types of optional fields associated with G/L accounts, one are optional fields that are associated with the actual Account. These are typically used for reports where you are selecting various G/L Accounts to report on and you can then use these optional fields to control which Accounts. For instance the Chart of Accounts report or the Trial Balance report can print Accounts based on the values of these optional fields.
The other sort of optional fields associated with G/L Accounts are transaction detail optional fields. These control information that is flowing from the other ledgers, like A/R Invoices and will store the values for these fields with the transaction details. You can include these in reports like the Transaction or Batch listing reports.
Ultimately the final output of your ERP system are the Financial Reports. Generally CFOs want to look at their Financial Reports from all sorts of angles and all sorts of categorizations. Sage 300 ERP is quite efficient at Financial Reporting since it stores special Financial Set records when it posts batches that keep a lot of data all pre-calculated and easy to access. These are all stored in the GLAFS table (GL0003).
The main Financial Report UI lets you choose all sorts of range criteria and sort orders. These are very useful for getting out your financial statements, but within the Financial Statement specifications there are very powerful data inquiry functions and you can use the full power of Excel to manipulate the data, create charts, create pivot tables, show geographic distributions, etc.
The big drivers of Financial Reports are the Accounts, the Account Groups and the Account segments. However all the functions that you place in your financial reports like FRACCT, FRAMT, FRPOST take filters that can include all sorts of criteria on the fields in the G/L Account record along with optional fields that are specified like A.ACCTCLASS = ʺSalesʺ for Account optional fields and T.QUANTITY <= 0 for transactional optional fields. Keep in mind that account optional fields would be used for filtering accounts and transactional optional fields for filtering transactional amounts (like in FRPOST).
Usually you use the account groups to generate the main F/R statements by the usual accounting categories and then you restrict the account segments to get say departmental or geographic reports. Then you use optional fields to get more esoteric views of your financial data.
To some degree the power of the Financial Reporter depends on your having setup your Chart of Accounts properly in the first place.
But suppose you’ve been running for a while and realize you didn’t setup up things ideally for your financial reporting needs? You’ve now got lots of transactions posted for G/L accounts which you feel are in the wrong structure? Now what do you do? The answer is the GL Account Code Changer module that is included with G/L (you need to activate this separately from G/L). This module will do a search and replace on the database and change all instances of a G/L account from one thing to another. This way all your committed transactions and financial data will be set to the new account and your financial reports can now be printed off the more ideal new structure. The Account number changer can also change the account segment separator and the segments.
Sage 300 ERP has very rich and flexible features allowing companies to create Charts of Accounts that lead to very powerful reporting capabilities. A good knowledge of the whole process is important to design that perfect Chart of Accounts, but if you make a mistake you can always use the Account Number Changer to fix it.