# Consolidation of Financial Statements in Excel: 2 Easy Methods

### Method 1 – Preparing the Consolidation of Income Statement

Steps:

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

• We need the unrealized profit. The first two values are given to us.

• Use this formula to get the value of gross profit.

`=D36-D37`

• Use another formula to return the value of the unrealized profit.

`=D38*50%`

• Create this format for the consolidated income statement.

• Use this formula in cell D20 to get the consolidated value of sales.

`=C8+D8-D36`

• Use this formula to get the consolidated value of the cost of goods sold.

`=C9+D9-D36+D39`

• Use this formula to get the gross profit.

`=D20-D21`

• Use this formula to find the consolidated operating expense.

`=C11+D11`

• Insert this formula to calculate the income before tax.

`=D22-D23`

• Apply this formula to calculate the income tax expense.

`=C13+D13`

• Apply this formula to get the consolidated net income.

`=D24-D25`

• We will also find the non-controlling interest. The parent company owns 75% of the subsidiary. So, the remaining 25% is non-controlling.
• Use this formula to calculate the non-controlling interest.

`=25%*(D14-D39)`

• Insert this formula to find the net income attributable to the owners of the parent company.

`=D26-D30`

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

`=SUM(D29:D30)`

• The overall consolidation of the income statement should look like this. We concluded by showing the steps for the first example of consolidating financial statements in Excel.

### Method 2 – Creating a Consolidation of Balance Sheet

Steps:

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

• We need to find the non-controlling interest.

• Use this formula.

`=D45*25%`

• Apply another formula.

`=D46+D47*25%`

• Insert the values in the consolidated balance sheet format.

• Enter this formula.

`=C9+D9`

• Insert this formula.

`=D28`

• Insert this formula in cell D30 to find the consolidated current assets.

`=C12+D12`

• Use this formula.

`=D29+D30`

• Insert this formula.

`=C15`

• Apply this formula to find the consolidated retained earnings.

`=C16+D16*75%`

• Insert this formula.

`=D48`

• Use this formula to calculate the consolidated equity.

`=D33+D34+D35`

• Enter another formula.

`=C19+D19`

• Use this formula.

`=D37+D38`

• Use this formula to find the total equity and liabilities. We will complete the second example of the consolidation of financial statements in Excel.

`=D36+D39`

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF