Multi-dimensional accounting for advanced reporting
In the previous issue, I've talked about reporting in double-entry accounting. We can generate reports like the balance sheet and the profit loss statement with our current database schema. However, there are more specific reports that require storing extra information. That's today's topic: multi-dimensional accounting!
Ledger accounts and dimensions
In the ledger account tree issue, I introduced the chart of accounts in a general ledger. Ledger accounts classify the journal entries and enable reports like the balance sheet and the profit loss statement. We also discussed the sub-ledger for relations. We saw how the sub-ledger provided extra options for new reports, like a debtor report or a sub-ledger report.
Traditionally, the ledger accounts and relation were the only dimensions used in double-entry accounting. But there are use cases where more detailed reports are needed. For example, maybe you want to report profits for a project, taxes for revenue, or expenses for a department. The extra information is added to the ledger account tree, for example: profit_loss.revenue.project_a.common, profit_loss.revenue.tax_low, or profit_loss.expenses.department_b.salary.
Working with only ledger accounts to manage these dimensions has some disadvantages:
- The ledger account tree grows exponentially, especially when you need to combine dimensions like a tax code and a project.
- It is challenging to present reports for the different dimensions without having extensive knowledge about the ledger account tree structure.
- The ledger account tree becomes more challenging to map to reporting standards like Standard Business Reporting (SBR).
Modern accounting software introduced multi-dimensional accounting, which solves these issues.
Multi-dimensional accounting
We want to label the journal entries in our database for specific use cases. These labels allow us to filter on journal entries and create detailed reports. Depending on your requirements, this is implemented with extra columns in the journal entries table or with a more flexible JSON structure in one column.
Some examples of reports that can benefit from these extra dimensions:
Tax reports
Tax authorities require you to file a statement with the total revenue and taxes you need to pay. You can tag each journal entry with the correct tax reference to keep track of these numbers. For example, tax_reference = NL/1a is a tax reference for the Dutch 21% VAT rate. By filtering on all journal entries with a tax reference and grouping by the value, you have a tax report in no time.
Cost centers or projects
Other valuable applications of dimensions are cost centers or projects. For example, they allow you to link sales invoices and purchase invoices to a project. Then, later on, you can filter the profit loss statement on this project. This states a specific project's revenue, expenses, and profit.
Departments or products
When you have a more significant business, you can use dimensions for the different departments or products inside the company. Like the projects, this gives specific statements for each department or product.
Conclusion
Multi-dimensional accounting provides a range of opportunities for better reporting. However, the accuracy of these reports depends on the information provided by other systems. You need to consider this when implementing extra dimensions in your double-entry accounting system.
Multiple dimensions in journal entries can be an extra performance bottleneck. Optimizing for only ledger accounts and relations is not difficult. You need additional measures to keep all reports fast when introducing multi-dimensional columns.