Spreadsheet Hell: The Consolidation, Close and Reporting Conundrum

The financial consolidation, close and reporting process is one that most organizations continue to struggle with. Is it because the process is complex? Not necessarily. In a typical business, it usually involves the straightforward closure of books for the accounting cycle, collection of financial data, and generating financial statements for internal management and external statutory reporting.

However, increased regulatory compliance and stakeholder demands are exerting pressure on the financial consolidation and reporting process. And the disconnected nature of the process (which is partly supported by spreadsheets and disparate reporting and source systems) makes it problematic for finance to meet the heightened demands.

From our experience working with clients, we find that there are nine areas that typically pose challenges to the CFO and the finance team

Consolidation today is no longer the aggregation of financials of two or more companies in the group. For many organizations, consolidation of financial statements has assumed complexities of immense proportions. For one, the data collation process has become quite tedious and time-consuming, even as spreadsheets continue to be the focal point. The issue of laying down audit trails has also become a pressing need.

From our experience working with clients, we find that there are nine areas that typically pose challenges to the CFO and the finance team:

  • Data collection from multiple source systems
  • Over reliance on spreadsheets
  • Currency complexities
  • Intercompany eliminations
  • Multi-GAAP reporting
  • Complex ownership structures
  • Top level consolidation adjustments
  • Multiple reporting requirements
  • Disconnected audit trail of data

Data collection

There are still many organizations in Asia that require the finance function to extract information from multiple source systems that use different format, manually upload data, work with inconsistent mapping definitions, use spreadsheets for reporting and other legacy practices that were fine in the analog era, but not in the digital age.

We recommend to our clients that they implement a solution with ETL (extract–transform–load) capabilities.  In our experience, ETL enables:

  • Integration of source systems with a consolidation solution
  • Importation of data from disparate source systems, including Excel files
  • Pre-defined mapping tables that can be shared where required
  • Data-transformation capabilities such as sign flips (debit vs. credit) and custom calculations

In addition, a robust ETL tool has the following benefits:

  • A unified central repository
  • Consistency in data transformation
  • Detailed audit trails
  • Drill-through capabilities from consolidation system to source systems/files
  • Shortened time for data collection, speeding up the entire close process

One may question the need for an ETL tool if you have a global ERP rollout. But how about entities added to your group through M&A activities? Also, we need to consider if it makes sense to have thousands and thousands of accounts in our consolidation process just because these exist in our legacy ERP systems.  

Over reliance on spreadsheets

Many organizations continue to rely on spreadsheets to collect, format and process data that is often extracted from disparate source systems. As it is one of the most common Microsoft Office applications, Excel offers a cost-effective solution in the short term. But this approach is neither sustainable nor scalable in the long term.

The other challenges of using spreadsheets include:

  • They are error-prone, resulting in data inaccuracies
  • They have limited version-control capabilities
  • They have inherent limitations in handling complex computations
  • There are often broken links and macros
  • The more complex the workbook, the more checks that are required to ensure it is error-free
  • There is a lack of audit trail
  • Implementation requires manual controls 

Over time, the spreadsheet challenges only become more serious. Therefore, organizations must work towards moving away from spreadsheets and into investing in technology that leverages from the source systems as much as possible – while enabling the books to be closed in a shorter period of time and in a more error-free manner.

A note of caution: There have been many implementations where the business moves from spreadsheet hell to EPM (enterprise performance management) hell. This can happen when the organization simply replicates the spreadsheet files as they are, robbing itself of the gains that a robust technology platform can bring to the close process.

Can we even imagine taking care of intercompany eliminations requirements efficiently, accurately and on-time through spreadsheets?

Currency complexities

Multi-currency data creates issues that are intensified in the consolidation process. Finance teams will be familiar with complications associated with:  

  • Calculating and tracking currency translation adjustments (CTAs) across balance sheet line items
  • Need for historical currency overrides
  • Ability to view data of any entity in any currency 
  • Analyzing currency impact on actuals vs budgets/forecasts

In our practice, we find that a consolidation solution can address these challenges by ensuring that:

  • the required currency exchange rates, including historical translations, are captured and tracked for items such as equity and reserves
  • logics are in-built to ensure that currency translation adjustments are calculated consistently
  • different buckets store actual data at actual rates, not budget rates, for ease of comparison
  • accounts of a single entity can be translated into different currencies

A good multi-currency consolidation solution enables finance users to crunch huge data sets in significantly lesser time, allowing them to analyze financials in greater detail. Organizations can also separately track the impact of currency translations on cash flow, aiding budgeting and forecasting. And statutory audits, which always focus on CTAs, can proceed more smoothly.

Intercompany eliminations

For consolidation groups on multiple ERP systems, the tracking and elimination of intercompany balances can often be a heart-burning exercise. Many organizations have dedicated intercompany matching teams that spend anywhere between one to three weeks to identify and resolve intercompany issues.

At times, the entire process of intercompany eliminations is further complicated by:

  • Large numbers of intercompany enabled accounts in financial statements, causing confusion among users on what amounts are to be reported where
  • Incorrect grouping of intercompany accounts to identify and eliminate corresponding partner transactions
  • Lack of standardization on how intercompany mismatches can be resolved
  • Impact of currency on intercompany mismatches and the need to eliminate profits on such transactions
  • Different reporting rollups such as legal, managerial, segmental, geographical
  • Complex ownership patterns

Can we even imagine taking care of these requirements efficiently, accurately and on-time using spreadsheets? This again highlights the need for a proper consolidation solution, which should be capable of:

  • Properly eliminating intercompany transactions and balances
  • Providing detailed analysis of intercompany mismatches with parties and accounts and any other required detail such as products or cost centers
  • Capturing comments on mismatches
  • Calculating the impact of ownership on such eliminations

Multi-GAAP reporting

Depending on the geographical presence of the group entities, the selected reporting GAAP as well as the stock exchanges where the group is listed, the need to generate consolidated financial statements in IFRS, US GAAP and other GAAPs is a common requirement.

The key challenge is to track how financials have been converted from local GAAP to reporting GAAP. The consolidation solution can help by ensuring a separate bucket is made available to capture GAAP-related adjustments, along with the flexibility to attach relevant documentation justifying those adjustments.

Complex ownership structures

The life of the Group Consolidation Manager will be made much easier if all companies are owned fully by the same entity. However, minority interests, associates, joint ventures or equity method are very common.

Business groups are constantly looking at how to maximize returns and create better synergies. Organizational restructuring is the new normal. Inorganic growth in many organizations is fast-paced, and new entities may be getting added at the rate of three to five a month.

The consolidation solution should thus provide pre-built equity control functionality for minority interest, proportional ownership and equity pickup. Also key is the flexibility to change these ownership structures with varying ownership percentages.

The right EPM platform should have a detailed audit trail capability, ability to drill down to source/calculations, and task management, built-in workflow, close management, confirmation and certification functionalities

Top-level consolidation adjustments

The move from standalone to consolidated financial statements leads to the need to track goodwill adjustments, investment changes and deferred tax impacts, to name a few. It is difficult to know the history of such top-level adjustments carried forward by referring to spreadsheets prepared 10 years ago.

A good consolidation software can give a better overview of historic adjustments, apart from current-period close adjustments. This makes the close and reporting process faster.

Multiple reporting requirements

Detailed analysis of actual data, segmental analysis, statutory and managerial reporting requirements, GAAP reporting requirements, cash flow statement – the list goes on and on.

Depending on the business dimensions across which the data is gathered and the various KPIs used for monitoring the performance, reporting requirements are quite diverse and ever increasing. Variance analysis and comparisons between actuals and budgets/forecasts are always the need.

The consolidation solution should cater to capturing all the required details to give the business the ability to do a deep dive of the numbers and generate required reports. Last but not the least, proper training and documentation will go a long way to help your consolidation team manage the close process better.

Disconnected audit trail of data

A lack of connected audit trails is an internal control and efficiency issue, as well as an external audit issue. For finance teams, having a connected audit trail of financial transactions helps speed up any investigation and verification of figures. For external audit, it is an issue when particular transactions cannot be traced properly from source system to point of disclosure.

In addition, having a connected and automated audit trail from recording to reporting (R2R) can be a key productivity tool for the finance organization to improve the consolidation and reporting process. This is especially true given the typical case of a period close where there are often last-minute data changes and journal entries.

The right EPM platform should have a detailed audit trail capability, ability to drill down to source/calculations, and task management, built-in workflow, close management, confirmation and certification functionalities, as well as approval and rejection processes.

Closing thoughts

Streamlining and optimizing consolidation, close and reporting help companies further drive competitive advantage through timely, accurate and actionable information.

In our experience, a better EPM platform enhances financial stewardship to meet and exceed expectations of varied stakeholders.

About the Author

Darshana Sanghavi is Founder and Director of WizProTek Consulting, an EPM and BI consulting company that specializes in designing, implementing and delivering business-focused technology solutions. 


Suggested Articles

Some of you might have already been aware of the news that Questex—with the aim to focus on event business—will shut down permanently all media brands in Asia…

Some advice for transitioning into an advisory role

Global risks are intensifying but the collective will to tackle them appears to be lacking. Check out this report for areas of concern