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.

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

• Create this format for the consolidated income statement.

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

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

• Use this formula to get the gross profit.

• Use this formula to find the consolidated operating expense.

• Insert this formula to calculate the income before tax.

• Apply this formula to calculate the income tax expense.

• Apply this formula to get the consolidated net income.

• 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.

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

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

• 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.

• Apply another formula.

• Insert the values in the consolidated balance sheet format.

• Enter this formula.

• Insert this formula.

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

• Use this formula.

• Insert this formula.

• Apply this formula to find the consolidated retained earnings.

• Insert this formula.

• Use this formula to calculate the consolidated equity.

• Enter another formula.

• Use this formula.

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

Download the Practice Workbook

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