Cumulative Totals for Balance Sheets, Simplified by Pyramid 2020.20

Pyramid provides time-based cumulative measures as a method for aggregating non-additive values on a cumulative basis in a data model, allowing users to solve analytical problems typically found in financial and accounting applications, where balance sheet values need to be accumulated to a given point in time.

The Problem

Cumulative totals are generally required when reporting balances over periods of time for balance sheet accounts in financial accounting. Usually, users need to create complex, inefficient models and formulas to replicate the accumulation effect or worse, they manipulate the database into producing raw balancing figures in tables. When done as formulas, the results are constantly recalculated for every cell in every report – further placing tremendous stress on the reporting system.

The cumulative option to aggregate the measure on the granular level ensures the accumulated totals are available and accurately calculated at the source.

Better yet, they automatically accumulate across the chosen time hierarchy in the query. So the balance effect by different period models (calendar vs. fiscal) is automatically handled without any programming or additional effort from the user.

Last, cumulative aggregates are the perfect companion feature to parent-child hierarchy capabilities, running directly on any data source surfaced in Pyramid. Together, the two symbiotic functions make the process of building and delivering accounting-based analytics accessible, fast, and entirely uncomplicated.

Example

Balance sheet values are always displayed as cumulative values. This report uses the classic context-based cumulative total to provide cumulative totals for all assets. Notice how the amounts accumulate per annum for all amounts existing on the report, so amounts for the year 2020 are cumulative from previous years starting in 2017.

When focusing on a specific year – for example, 2020, the cumulative effect is lost, displaying only amounts for the year 2020 because the context calculations are driven from the reporting “context” only. Notice how the amounts differ from the previous report.

A new measure – “Cumulative Amount”  – is now added by using the “Cumulative” measure aggregation method in the Data Model tool.

Adding the new measure to the report for 2020, we can see that the cumulative effect is not lost.

By using hierarchy chips for accounts and time, the user can expand or collapse the balance sheet or financial year at will, exposing whatever elements of the tree are relevant for the analysis, so the user can track how the allowance for debt has increased per annum, per quarter and by month.

And finally, by simply swapping the calendar date hierarchy with a fiscal date hierarchy, Pyramid automatically and effortlessly recalculates the cumulative amount and displays all hierarchies in their expandable and collapsible trees. By combining the power and flexibility of cumulative measures with parent-child hierarchies, Pyramid demonstrates its superior support for financial analytics.

Summary

Pyramid allows users to aggregate measures on the granular level in the data modeling phase. This cumulative feature enables users to independently view accumulated totals of an existing measure without creating complex models or formulas that constantly recalculate for all cells. This provides accounting-based analytics with instantly available, accurate totals, automatically accumulating across calendar or fiscal date hierarchies.