Using account hierarchies for reporting

Submitted by ehu on Sat, 10/10/2015 - 11:25

Starting 1.4.17 account hierarchies - which were part of LedgerSMB for a looong time already - will now be used for financial reporting: balance sheet and income statement. In addition to grouping accounts into the subgroups defined by the hierarchy, amounts are aggregated up the tree as well. This article demonstrates how to use this feature. Scroll all the way to the end to see what happens if you don't want to update your configuration (yet).

Hierarchies?

There are multiple reasons for wanting to use hierarchies. One of those reasons is that expense accounts are currently listed as one long list under Expenses while income accounts are listed as a -usually shorter- list under Income. Personnel expenses are usually spread across multiple expense accounts. With lots of other expense accounts also listed, what's the total expense on personnel?

Another use-case for using hierarchies might be because you're running a "margin business". That is, you're only every buying what you're selling and the cost of goods sold is the large part of your expenses. In cases like this it can be a good idea to use a different setup of the income statement: one where cost of goods sold is immediately subtracted from income, leaving gross margin to cover all other expenses in the company.

In all versions (that I ever used) prior to 1.4.17, there was no way to create a report layout which matches either of the use-cases described above. As off 1.4.17, the account hierarchy in the reports solves exactly this problem.

Example output

As shown in the output below (left) the new balance sheet aggregates numbers into the headings and headings of headings: The "Current assets" heading is the total of allowance for doubtful accounts and Checking account while "All assets" is the tottal of "Current assets" and "Capital assets" heading amounts. On the right, the old balance sheet report is shown. Even though the first-level headings (Capital Assets, Current Assets, ...) were defined in the system, they did not show up in the report.

Please scroll down for a comparison of the income statement report.

On the right, there's the old report again to be compared to the new report on the left. The right report shows only the total expenses, while the one on the left shows total expenses, total payroll expenses and total General & administrative expenses.

Income statement comparison

 

Additional configuration

The example above has been created using the "General" US Chart of Accounts which can be found in the standard LedgerSMB distribution. The standard version only includes only a single level hierarchies, meaning only "5600 - General & Admin expenses" (and the like).

To create the demo above, I added the other levels (with nodes marked <pre-existing> part of the standard CoA as distributed):

  • ALL ASSETS
    • CURRENT ASSETS <pre-existing>
    • CAPITAL ASSETS <pre-existing>
  • ALL LIABILITIIES
    • CURRENT LIABILITIES <pre-existing>
  • 3299 TOTAL EQUITY
    • SHARE CAPITAL
    • 4998 NET MARGIN (P&L Result)
      • GROSS MARGIN
        • ​INCOME <pre-existing>
        • COST OF GOOS SOLD 
      • OPERATIONAL EXPENSES
        • PAYROLL EXPENSES <pre-existing>​​​

​​​Headings with a heading

In order to build the hierarchy of headings, each heading needs to have a heading on its own, except for the "root" headings "ALL ASSETS", "ALL LIABILITIES" and "TOTAL EQUITY". The P&L is reported as part equity, meaning that the P&L result will have "TOTAL EQUITY" as its parent.

In order to set a heading's parent, go to the Chart of Accounts menu item under the General Journal main menu. Click on the [Edit] link in the column before the last. The resulting screen shows below. The "Heading" dropdown allows selection of any of the other existing headings.

Please note that an error will be thrown if you're trying to set the heading of the current heading to one which has the current heading as its own heading: this creates a cycle in the headings which is not supported (where does roll-up begin and end, if you're going round in circles?).

The above is enough to make your Income Statement work. For the Balancesheet, there's one more step to be taken.

Identifying the profit/loss heading

In order to summarize the P&L result as a single line no the balance sheet, the heading which totalizes the profit/loss must be marked as such. This should de done through the main menu item System, under the Defaults submenu as shown below.

As you can see, in casu the example above, the heading "4998 -- NET MARGIN" has been selected; exactly the heading which is used as the highest level for the P&L and lowest level at which income and expenses are being reported in the balance sheet.

What if I don't ...

... want to change my configuration (just yet). Will it stop working?

Backward compatibility report

No, it won't: backward compatibility selections have been added to the Income statement and Balance sheet reports. The image below shows the compatibility setting on the Income statement report.

When running with the "Account category" option, the account category ("Income", "Expense") will be used as the hierarchy instead of the account hierarchies. The output of the income statement from the example above with this setting looks like the picture below, with a balance sheet run with the same compatibility setting right next to it.

Note that the compatibility report is *not* the default setting, meaning that in order to be provided the default report, the "Account category" "hierarchy" needs to be selected explicitly.

Release