For financial reporting, can we use the Chart of Accounts as a hierarchy for all our reporting needs ?
The short answer is: In the ideal world – Yes. In the not ideal world – No.
The Chart of Accounts provides us with a way to categorise Account Codes (aka GL Codes) into an account name and and sometimes a heading that can be used for rollup for P&L and BS based reporting.
It’s often portrayed as Parent/Child in sample databases and some smaller accounting packages (QuickBooks loves their hierarchies), but more often than not, its a fixed two level hierarchy above the account code.
Typically we have a hierarchy like this:
-Account Heading. Operating Expenses
-Account Range. Labour Expenses
-Account Code / Name. Account Code 5020 (Bob’s Wages)
So why can’t we use the default chart of accounts for financial reports?. There’s a few reasons:
1) This assumes that there is ONE version of the truth and ONE pro forma hierarchy and rollup for Profit and Loss.
For small to mid sized companies that is certainly true, but as we move to larger organisations operating in many countries with many verticals, we end up with variations on the Profit and Loss.
For one division, say price variation might be realised at one line on the report and in another division it may be realised elsewhere.
Definitions like Net Profit may include different items above and below the line, especially as we switch from external focus to internal accounting for say Manufacturing.
So rather than one single Profit and Loss Report, we may need to support multiple Profit
and Loss hierarchies with an Account Code “moving” between sections of the report.
The company may have an accounting review and decide to restructure how account codes rollup, but want the BI platform to show both the old and new style of Profit and Loss Reports.
2) The P&L and BS are not the only Financial Reports
We defintatley have one “Account Name” per “Account Code”, and an account will either by a movement (P&L) or a balance (BS), but when we come to reporting ranges and reporting headers there are other financial report like CashFlow which rolls up account codes from both
P&L and BS into a different type of report showing both movement and balances and then reconciling them.
This gives us a choice. Do we:
– Have a single hierarchy “per report”
– Support multiple hierarchies with the chart of accounts being the “main” one.
I opt for the second option and this then means that one account code needs to map to multiple reporting ranges. Effectively a many to many.
2) The Chart of Accounts doesnt include calculated members.
A financial report will typically have many calculated members which are either
running sums, custom aggregates or alternate rollups.
If we look at example below, the following members are calculated:
– Net Invoice Sales: Running Sum
– Net Sales: Running Sum. Although accountants will explain it as Net Invoice Sales + Adjustments
– EBITA: Running Sum
– Profit Before Tax: Running Sum
– Profit After Tax: Running Sum
To handle these we need a custom hierarchy with these members added in, and the correct sort order to implement running Sums
There are a few ways to implement running Sums.Within DAX the SQLBI folks have it covered here
https://www.sqlbi.com/articles/computing-running-totals-in-dax/
