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.
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.
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.
Let’s discuss each of these processes in detail.
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.
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.
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.
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:
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.
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.
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:
Whether you plan to create consolidated financial statements in Excel or with financial consolidation software, here are examples of each method.
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.
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:
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:
If you’d like to try SoftLedger for yourself, schedule a demo today.
A CPA with more than 10 years of varied public and private accounting experience, Ben has led many complex financial projects to successful outcomes.
Ben holds a B.S. in Accounting from the University of Maryland.