Consolidation of Financial Statements in Excel (2 Easy Examples)

In this article, you will learn about two quick examples of how to prepare a consolidation of financial statements in Excel. We will be using the Microsoft 365 version. The tutorial can be used with any version of Microsoft Excel.

Financial Statement

There are four main types of financial statements; balance sheet, income statement, cash flow statement, and owner’s equity statement. These statements summarize the financial situation of an organization or company. They are the written documents or records that the company keeps throughout the year. Here, we will talk about the first three main parts of a financial statement report:

Income Statement

It reports the profit and loss of a company over a certain period. So, the income statement is made up of three parts: 1. Revenue 2. Expense 3. Profits. So, it contains all income and expenses for a certain period and calculates net profit.

Balance Sheet

It summarizes the financial position of the company after a certain period and is also known as a Statement of Financial Position/condition. In general, a balance sheet consists of three parts. And, these are assets, liabilities, and owner’s equity. A balance sheet provides a glimpse of the company’s finances. The balance sheet consists of the company’s liabilities, assets, and owner’s equity. Balance sheets are organized according to the equation:

• Assets: These are the main resources owned by the company. Assets can be categorized into many types. Example- current and fixed assets, tangible and intangible assets, etc.
• Liabilities: They are things that the company owes to a person or another company, like cash, loans, etc.
• Owner’s Equity: It represents the value for a company’s shareholders after all the company’s assets have been sold off and all company liabilities have been paid off.

Consolidated Financial Statements

Consolidated financial statements show the exact financial condition of a parent company and its subsidiaries. Potential investors can easily know about these without looking at the statements individually. Therefore, it saves time and makes it easier to comprehend. The consolidated statements combine most of the financial items after discarding the common items.

Consolidation of Financial Statements in Excel: 2 Easy Examples

There will be two examples to show you the process of consolidation of the financial statements in Excel. Firstly, we will show you the consolidated income statement. Lastly, we will demonstrate the steps necessary to create a consolidated balance sheet.

1. Preparing Consolidation of Income Statement

In this first section, we will show you how to prepare the consolidation of the income statement in Excel. We will use the SUM function to find the total values of the consolidation in Excel. Let us consider an example where the parent company owns 75% of the shares of its subsidiary company. The subsidiary company sold goods worth $10,000 at a cost of $7,500 to the parent company. Moreover, 50% of those goods remain unsold. Now, we have to prepare the consolidated income statement for that parent company. Additionally, the income statements are provided for the two companies.

Steps:

• Firstly, the income statements for the two companies are given to us.

• Secondly, we will calculate the unrealized profit. The first two values are given yo us.

• Thirdly, type this formula to get the value of gross profit.

• Then, type another formula to return the value of the unrealized profit.

• Afterward, create this format for the consolidated income statement.

• Then, type this formula in cell D20 to get the consolidated value of sales.

• Then, type this formula to get the consolidated value of cost of goods sold.

• Afterward, use this formula to get the gross profit.

• After that, use this formula to find the consolidated operating expense.

• Then, insert this formula to calculate the income before tax.

• Afterward, type this formula to calculate the income tax expense.

• After that, type this formula to get the consolidated net income.

• Our objective is complete, however, we will also find the non-controlling interest. The parent company owns 75% of the subsidiary. So, the remaining 25% is non-controlling.
• So, type this formula to calculate the non-controlling interest.

• Then, type this formula to find the net income attributable to the owners of the parent company

• Lastly, add these two values to get the net income value, which should match the previously calculated value.

• Finally, the overall consolidation of the income statement should look like this. Thus, we have concluded by showing the steps for the first example of the consolidation of financial statements in Excel.

Read More: How to Prepare Financial Statements from Trial Balance in Excel

2. Creating Consolidation of Balance Sheet

Now, for this section, let us consider another scenario. Again, the parent company owns 75% of its subsidiary company. However, this time there is no intra-trade. Moreover, the parent company invests in the subsidiary company for $30,000. To consolidate the balance sheet, we will employ the proportionate share method.

Steps:

• To begin with, we have given the balance sheets for the two companies. Using these values, we will create the consolidated balance sheet.

• Then, we will find the non-controlling interest.
• So, these values are provided.

• Then, type this formula.

• After that, type another formula.

• Afterward, type the consolidated balance sheet format.

• Then, type this formula.

• After this, insert this formula.

• Afterward, insert this formula in cell D30 to find the consolidated current assets.

• Then, use this formula.

• After that, insert this formula.

• Then, type this formula to find the consolidated retained earnings.

• After that, insert this formula.

• Then, use this formula to calculate the consolidated equity.

• After that, type another formula.

• Then, type this formula.

• Then, use this formula to find the total equity and liabilities. Consequently, we will complete the second example of the consolidation of financial statements in Excel.

Read More: How to Create a Personal Financial Statement in Excel

Download Practice Workbook

You can download the Excel file from the link below.

Conclusion

We have shown you two easy examples of how to prepare the consolidation of financial statements in Excel. If you face any problems regarding these methods, feel free to comment below. Moreover, you can also leave any feedback for us, so we can serve you better.

However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Thanks for reading. Keep excelling!

Related Articles

• How to Automate Financial Statements in Excel
• How to Link 3 Financial Statements in Excel
• How to Create Pro Forma Financial Statements in Excel

<< Go Back to How to Create Financial Statements in Excel | Excel for Finance | Learn Excel