Traditionally, creating consolidated financial statements was a time-consuming process that exposed your financial statements to error.
While many companies still consolidate their data with Excel, modern accounting software exists to help companies create consolidated financial statements automatically.
This post will walk through the financial consolidation process using Excel and modern financial consolidation software.
What Is a Consolidated Financial Statement?
A consolidated financial statement shows the financial data (liabilities, assets, income, equity, expenses, and cash flow) for various subsidiaries owned by one parent company rolled up into a single statement.
Depending on the company’s situation, it may be required to provide consolidated financial reports that comply with accounting standards such as GAAP and IFRS.
How Do You Write a Consolidated Financial Statement?
Consolidating financial data is often more complex than just tallying various accounts (income, expenses, etc.). Instead, here are a few other calculations you’ll have to make before consolidating the data.
Intercompany eliminations
Foreign currency consolidation
Accounting for partial ownership
Match the financial periods
Let’s discuss each of these processes in detail.
Intercompany Eliminations
It’s not uncommon for subsidiaries to borrow money from each other. So while the various subsidiaries still record the intercompany transactions in their books (i.e., one subsidiary records an accounts payable and the other records an accounts receivable), you don't need to record it in the consolidated financial statement.
Therefore, before creating the consolidated financial statement, it’s important first to eliminate any of the transactions that occur only among the various subsidiaries.
Foreign Currency Consolidation
If the subsidiaries deal with multiple foreign currencies, you’ll have to consolidate them manually before creating a consolidated financial statement. For example, if a parent company in the U.S. owns a subsidiary that operates in Europe and uses the euro for most of its accounting activities, you would have to translate the statement into U.S. dollars.
This means you’ll have to apply the foreign exchange rate to that subsidiary’s statement before creating the consolidated financial statement.
Accounting For Partial Ownership
Most larger parent companies have partial ownership of a variety of companies. However, the accounting process is different depending on the percentage of ownership.
Specifically, if a parent company has a controlling interest in a subsidiary (it owns more than 50% of the company), that subsidiary is accounted for with the traditional consolidation accounting method (combining expenses, revenues, assets, and liabilities).
However, if the parent company only owns, say, 25% of the company, you can use the equity method of accounting. This means that the parent company records the investment in the subsidiary on the balance sheet as an asset that is equivalent to the initial investment.
In other words, the revenue gained or lost by that subsidiary is recorded in accordance with the percentage owned by the company.
So let's say the parent company owns 25% of the subsidiary. If that's the case, then you will record 25% of the gain/loss in the parent company's financial statements.
Match The Fiscal Periods
The fiscal period for parent companies and subsidiaries must be no more than three months apart. So if your subsidiary is more than three months apart, you’ll have to adjust it to match the parent company’s fiscal period.
This process is what you call calendarization, and here’s the exact formula you can use:
Methods of Creating Consolidated Financial Statements
There are two ways to create consolidated financial statements. You can either manually execute the process above and perform final calculations in Excel or use financial consolidation software to automate the process. Below you'll see how to create a consolidated financial statement with Excel and with financial consolidation software.
Creating Consolidated Financial Statements With Excel
If you decide to create your consolidated financial statements with Excel, here's a brief overview of how to do it.
First, ensure that the subsidiaries' fiscal periods match up with the parent company's fiscal period. Once all of them align, the next step is to create an Excel spreadsheet and label tabs for each financial statement (liabilities, assets, income, equity, expenses, and cash flow).
From there, you'll copy and paste all of the data from the individual subsidiaries into each of those tabs. Once you've entered all of the data, double-check that it is correct, as even one wrong number could lead to hours of revising all other impacted calculations.
After the data is entered correctly, you can perform intercompany eliminations and then consolidate the data into the final consolidated financial statement.
As you can see, this process is very manual and prone to error. For this reason, many people are turning to software that automates the financial consolidation process.
Creating Consolidated Financial Statements With Software
Modern financial consolidation software can automate the entire financial consolidation process.
However, as you select a financial consolidation software, it's important to consider whether it automates the entire financial consolidation process or only part of the financial consolidation process.
For example, some software might consolidate the final data automatically, though only after you've manually prepared the data by performing intercompany eliminations, applying foreign currency rates, etc.
In addition, some software makes users pay for automatic consolidation features while others include it in the core offering.
So as you look for financial consolidation software, here are a few things to look for:
Does it automatically apply foreign currency rates?
Does it automatically perform intercompany eliminations?
Do you have to pay extra for automatic consolidation features?
Is it flexible? (can you create custom entries/make adjustments for subsidiaries as needed?)
Does it provide a clear audit trail?
Example of Consolidated Financial Statement
Whether you plan to create consolidated financial statements in Excel or with financial consolidation software, here are examples of each method.
Example of a Consolidated Financial Statement in Excel
Below is an example of creating a consolidated financial statement in Excel.
As you can see, there are several different tabs (the one directly below is on the income statement tab), and each of the "Samples" is the data for three different subsidiaries. After creating the subtotals, you have to perform the intercompany eliminations and then consolidate them manually.
Below is an example of consolidating the data for the balance sheet.
As you can see, you’ll create a similar sheet for all of the other statements (liabilities, assets, etc.). You would then create a final consolidated financial statement with all of the totals.
Example of a Consolidated Financial Statement With Software
While manually consolidating data for two or three subsidiaries may only take a few hours, it can quickly become time-consuming and prone to error if you have more than a couple of subsidiaries.
Therefore, financial consolidation software might be a good option for those with more than two subsidiaries.
Below is an example of a consolidated financial statement created in SoftLedger. The reason why SoftLedger is ideal for multi-entity consolidation is that it offers real-time data and performs all calculations automatically.
So as you can see, it automatically consolidates foreign currencies, performs intercompany eliminations, and consolidates the data in real-time. This means that whenever an entry is made for one subsidiary, SoftLedger automatically adjusts all impacted accounts. This way you have an up-to-date consolidated financial statement at any given moment throughout the month.
In this example, you can see all of the children of United States, United States standalone, any eliminations, and the consolidated balance. When booking a journal entry in San Francisco, you can immediately see that entry reflected in the balance at that level and its parent entities as well.
Additionally, see the below file that includes example journal data converted to the SoftLedger import format. This example file was set up using the steps below:
On the Journal tab (QB report), create formulas (highlighted in orange) to complete the blank journal rows.
Copy these to the SL Template tab, determining which columns to use in which places (e.g. Date to Date fields).
Add filters to the SL Template tab and filter on the Date field for "Blank" rows, which you can then delete before the file will be ready for upload.
Note: All columns highlighted YELLOW are required for the SoftLedger Journal Upload.
If you're looking for automatic consolidation software, consider SoftLedger. We built it specifically for multi-entity companies that want access to real-time, automatically consolidated financial data at any time during the month. This helps companies close the books faster and safeguards the data against manual error.
Here are just a few of the key benefits SoftLedger provides multi-entity companies:
Whenever an entry is made to a subsidiary's account, SoftLedger automatically adjusts all impacted accounts and the general ledger.
SoftLedger automatically executes additional calculations required for financial consolidation, such as intercompany eliminations and foreign currency consolidation.
Data is always audit ready with clear audit trails and drill-down capabilities.
The software is incredibly flexible, and you can easily customize it to suit your needs.
You never have to pay for additional consolidation features as it is our core offering.
Yes, users have the option to use their own chart of accounts or SoftLedger’s standard chart of accounts when getting started with SoftLedger. SoftLedger’s flexibility allows users to make changes to their charts by easily adding new accounts.
Ready to Get Started?
Book a demo for a free test-drive of the SoftLedger software and APIs
