The Ledger

A digital finance blog

The Ledger

Posted by Sarah Werner

Getting finance calculations and allocations out of Excel

The finance department is inundated with data points that need to be transformed, allocated, converted, and combined to meet the demands of a complex reporting and regulatory environment and create a coherent picture of the business. This presents a significant opportunity for CFOs to add value to the organization but also highlights the challenge of siloed source systems, legacy solutions, and inefficient data processes.

The case for a finance calculation engine

The challenge with spreadsheets

The ability to automate complex finance calculations, conversions, and allocations are often not available in existing source systems or solutions, leaving finance teams to manage a complex matrix of calculations and/or allocations in spreadsheets that lack control, auditability, and the ability to scale. Other challenges include:

  • Multiple reporting requirements: Often cost and cash flows are treated differently depending on the reporting standard - US GAAP vs IFRS for example - which then requires two allocations to be generated from the same cost pool.
  • Data volumes: Increasing data volumes and source system complexity can result in performance issues when calculations or allocations are performed at scale.
  • Aggregated allocations: Many reporting standards need allocations at a highly granular level, requiring aggregated allocations to be transformed into Units of Account.
  • Lack of transparency: When calculations are performed in spreadsheets or in disparate source systems, audit trails are lost and version control is challenging.

For many organizations, the increase in data, regulatory standards, reporting requirements, and the desire for real-time analytics is driving the need for an engine capable of automating the complex calculations and allocations required by banks, insurers, media, software companies, and beyond.

A broad range of use cases

Regardless of industry, there are a broad range of use cases for a finance calculation and allocation engine including any instance that requires a cost, expense, or investment allocation or any supplementary calculation needed to support the finance team in its functional and compliance data preparation and reporting requirements.

With Aptitude Calculate, all calculations, no matter how complex, can be broken down into a series of simple steps.  First, data is integrated from upstream systems, typically using a CSV import option. Then, a GUI allows the user to build and present calculations visually. This enforces a consistent methodology and breaks a calculation down into simple, repeatable steps which can be reused. The calculation or allocation output can then be presented as a report in the inbuilt reporting suite or fed into a downstream reporting process.

These are just a few of the use cases:

  • Multi-dimensional cost allocation by cost cluster and region
  • Subsidy amortization 
  • Subscription royalties
  • Allocation of back-office costs to the front office
  • Allocation of service center costs from lower-cost locations
  • Revenue Transfer pricing
  • Tax calculations 
  • Modeling results of multiple allocations and calculation methodologies to understand impacts

allocations

Aptitude Calculate

Aptitude Calculate (AC) is a stand-alone calculation and rules engine that fully supports automated allocations and calculations by entity, LOB, portfolio/cohort, contract, or any other dimension required.

Aptitude Calculate provides the elements and processing power required for businesses across industries to define and generate allocations, calculations, and conversions. The use cases are many and can include anything that requires a cost, expense, or investment allocation or any supplementary calculation needed to support the finance team in its functional and compliance data preparation and reporting requirements.

It is is flexible, scalable, and integrates seamlessly with existing systems to meet a wide variety of use cases.

Interested in learning more?

Share Tweet about this on Twitter Share on LinkedIn Share on Facebook Email this to someone