Last year in the US alone, there were 13,697 mergers and acquisitions deals. That’s a lot of business worth a lot of money, and it all makes for more complicated reporting when it comes time to roll up multiple merged business entities into one report for consolidated financial reporting.

Parent companies require consolidated reports in order to show a complete view of their current financial position by combining the financial info of all their entities. It’s all about having a full 360-degree view of not just the parent organization, but all its subsidiaries, too.

But at the same time, that adds significant complexity to reporting, especially if this is the first time you’re running reports for more than just your parent company. Don’t worry, we’ll break it down for you.

Anatomy of a Consolidated Financial Report

Typically, consolidated financial reporting is done on the same schedule as your currently established financial cycle (monthly, quarterly, or annually). Your consolidated financial report should include these four essential sections:

1. Consolidated Income Statement: Summarizes revenues and expenses for the entire group.
2. Consolidated Balance Sheet: Details the group’s cumulative assets, equity, and liabilities.
3. Consolidated Cash Flow Statement: Covers various cash flows from operating, investing, and financing activities across the entire group.
4. Consolidated Statement of Changes in Equity: Shows any changes in the group’s equity over the reporting period.

Now the big question: Can you do this all in Excel? Yes, you can. And here’s how.

Building Consolidated Financial Reports in Excel

The process of building consolidated financial reports in Excel is rigorous and requires careful standardization.

Preparation and Standardization:

• Prepare individual subsidiary financial statements.
• Standardize data: Ensure consistent labels, column headers, and data types across all entities.
• Align fiscal periods: Match all subsidiary data to the parent company’s fiscal period to avoid discrepancies.
• Build a blank master financial statement: Create this with all possible account numbers to ensure consistency.

Data Consolidation and Analysis:

• Consolidate data into a master Excel worksheet.
• Choose a consolidation method (Position or Category).
• Use Excel’s Consolidate feature: Found under the “Data” tab and “Data Tools.”
• Select the appropriate data ranges from each subsidiary’s worksheet/workbook.
• Check “create links to source data” to automatically update the consolidated report if/when any source data changes.

The Critical Step: Intercompany Eliminations

• Run intercompany eliminations: You must find and eliminate intercompany transactions (such as sales between the parent and a subsidiary) to prevent double-counting of revenues, expenses, assets, and liabilities.

Finalization and Review:

• Finalize and review: After running eliminations, use the consolidated data to build your final financial statements.
• Check for accuracy: Review everything again for duplicate values.
• Exchange currencies: If any subsidiaries operate in a different currency, convert their reports over to the parent company’s reporting currency.

Challenges to Consolidated Reporting in Excel

Building consolidated reporting in Excel seems straightforward, but it’s not without challenges:

• Risk of Human Error: Manual reporting, especially with large datasets and complex eliminations, runs a high risk of errors.
• Time Consumption: Doing this all “by hand” takes a significant amount of time, delaying management decisions.
• Version Control: Multiple subsidiaries submitting data means potential issues with version control or audit trails.

Scalability: The larger your set of subsidiaries, the harder it is to scale Excel to meet your reporting needs.

Consolidated Financial Reporting with Connected Spreadsheets

Does this mean reporting in Excel is totally out? Hardly. It just means your Excel could probably benefit from a little bit of supercharging using data integration.

Data integration works to pull crucial data from your most-used data sources (like your ERP, CRM, and billing systems) into a centralized location—such as Excel—to make it easier to manipulate, analyze, and report on.

• Real-Time Data: Data integration leverages a dynamic connection, meaning you can refresh the data to reflect to-the-moment numbers for easy rolling forecasting, or on demand with the click of a button.
• Simplified Consolidation: Integration solutions such as ExtendInsights are purpose-built to help you keep working in Excel while making it simple and efficient to pull data from NetSuite saved searches, HubSpot reports, Chargebee subscription data, and more into a dynamic spreadsheet.

With direct integrations enabled between Excel and your top data sources via ExtendInsights, you can be sure you’re always reporting with and making decisions on the most accurate and up-to-date information available. This means that after a merger and acquisition, you can quickly move forward with easy and accurate consolidated financial reporting.

See how ExtendInsights can improve your consolidated financial reporting. Try it FREE today.