Effective-Dated Multi-Level Consolidation with CloseCoda™ from Kog

Companies operating in a large corporate structure often face challenges in financial reporting, given the layers of complexity that can exist in their intercompany relationships. Organizations using Workday® Financials benefit from powerful functionality around managing company hierarchies, and automated elimination and consolidation entries. Bigger organizations using large hierarchies and making mid-year effective-dated changes to hierarchies in Workday® may experience difficulties presenting these complicated relationships using native reporting tools. Below are two such challenges, and how Kognitiv’s product CloseCoda™ provides a solution.

Challenge # 1: Sub-Level Eliminations

At the heart of Workday® functionality for consolidations are company hierarchies. Assigning companies to hierarchies allows the system to perform consolidations and eliminations at any hierarchy level in financial reporting. A corporate structure consisting of many sub-level hierarchy combinations must have the ability to report on pieces of the hierarchy, or the whole hierarchy, depending on the users’ needs. This can be accomplished in Workday® reporting for a single hierarchy at a time. For example, a trial balance with eliminations may look like this, with eliminations between any company in the hierarchy ‘Company J Consolidated’ happening in a single Eliminations column:

In Workday®, this trial balance can be generated for any hierarchy, either at the top or at any sub level, in this manner. However, if a company needs to present all levels of a hierarchy outlined together, with eliminations happening at each sub-level, we encounter some issues. Consider a report having this desired output: using a repeating column group to show sub-level eliminations:

In this example, the ‘A+B Consol’ sub-level hierarchy shows eliminations, and its parent shows another company, and presents any new eliminations at that level. However, a repeating column group to include eliminations in Workday® would have a few issues:

1. Column Outlining in Workday® can only accommodate up to 8 column groupings, consistent with the limits of Excel. A company having more than 8 company hierarchy levels in its structure would need to export them and manually combine them in Excel.

2. Using Repeating Column Group for this purpose would duplicate eliminations at every level, rather than presenting new eliminations at every level:
   

To overcome this challenge, the first goal of CloseCoda™ will be to present eliminations at a sub-level in a flat trial balance (without outlining, but obeying the logical structure of the hierarchy):

Challenge # 2: Effective-dated financial statements in Workday® Financials

One powerful aspect of using company hierarchies in Workday® financial reporting is that assignments to hierarchies can be effective dated, so that a company can live in more than one place in a hierarchy at different times. When a financial statement is run, Workday® will respect the date governing the report, and will calculate and present information consistent with that single date.

However, mid-year movements of companies or hierarchies can pose challenges, since some accounting must “remain” in the old hierarchy even after a mid-year movement. Consider the below example, where Company C was in the hierarchy ‘Company L3 Consolidated’ for part of the year, but by 12/31 (the financial statement date) it was no longer in that hierarchy. In a typical effective-dated Income Statement in Workday, Company C would not be included in the report output:

In this example, we need for part of Company C income to still contribute to the consolidated L3 balance, even though it is no longer in that hierarchy, for the period of time it was there, Feb through September of the year:

This can be accomplished in Workday® using an effective-dated financial statement approach, but has some limitations:

1. It applies only to an Income Statement, while a Balance Sheet or full Trial Balance is much more difficult.

2. A report like this can be generated for one single hierarchy at a time (the one entered at runtime.) For a full effective-dated report, one would have to run this report once for every hierarchy, export to Excel, and manually combine them, accounting also for eliminations. This may work well enough for a company hierarchy of 5 nodes or so, but in a large structure having hundreds of hierarchies and hundreds of companies, this approach is not scalable.

The Solution: CloseCoda™

To solve both of these complex issues at the same time, Kognitiv developed CloseCoda™, which relies on Workday’s financial reporting framework to call and put together a unique data set from which an effective-dated and sub-eliminated trial balance can be derived:

This trial balance in Excel will flow left to right from child hierarchy to parent, with the highest-level consolidation in the right-most column. Eliminations are presented at every sub-level in the Excel output, consistent with our desired flat trial-balance goal. Further, since the elimination data comes directly from the data query, these balances tie back to consolidated reporting in Workday®.

The Impact

Here is a real-life example of what CloseCoda™ did in minutes, something that previously was not possible: a complete effective-dated and sub-eliminated trial balance for every hierarchy and company – more than 700 companies in more than 400 hierarchies – for any month, at the push of a button. To do the same work manually would have taken around 3 years of full-time effort, just for one month’s reporting. In addition to providing consistent high-level reporting in a timely manner, CloseCoda™ accelerated the timing of their monthly close significantly.

Author: Brady from Arizona