How to Create Consolidated Balance Sheet Format in Excel

The consolidated balance sheet is a report that represents the whole financial state of a parent business and all of its subsidiaries on a single sheet, without separating the entities. It is structured similarly to a conventional balance sheet. In this article, we are going to demonstrate the step-by-step procedure to create a consolidated balance sheet format in Excel. If you are also curious about it, download our practice workbook and follow us.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Step-by-Step Procedure to Create Consolidated Balance Sheet Format in Excel

In this article, we will show you the step-by-step procedure to create a consolidated balance sheet format in Excel. After completing all the steps, the final summary layout will be like the image shown below:

Step 1: Creating Preliminary Summary Layout

In the first step, we will input the essential particulars to create the preliminary summary layout. The steps are given below:

• First of all, we will insert a shape to show the title of the sheet. For that, select cell B1.
• Afterward, in the Insert tab, click on the drop-down arrow of the Illustration > Shapes and choose the Scroll: Horizontal shape.

• Now, write down the title of the sheet as Balance Sheet Summary.

• Then, in the range of cells B5:B6, write down the following entities and allot the corresponding cells to input the values of that company.
• Modify the cell format according to your desire.

• Next, in the range of cells B8:B16, write down the following particulars and denote the corresponding cells of the next column to input the values.

• At last, insert the logo of your financial institution. To demonstrate the process, we insert the logo of our website.
• In the Insert tab, click on the drop-down arrow of the Illustration > Pictures and choose the This Device option.

• As a result, a small dialog box called Insert Picture will appear.
• After that, select the picture titles ExcelDemy and click on Insert.

• You will get the image, and our first task ends here.

Thus, we can say that we have completed the first step to creating a consolidated balance sheet format in Excel.

Read More: Balance Sheet Format of a Company in Excel (Download Free Template)

Step 2: Estimating Total Assets

In this step, we will calculate the value of total assets. The procedure is described below:

• First, in the Summary sheet, select rows 1:6.
• Now, press ‘Ctrl+C’ to copy the rows.

• Then, create a new sheet and press ‘Ctrl+V’ to paste the data.

• After that, change the title of the sheet from Balance Sheet Summary to Total Assets.

• Afterward, select cell C5 and write down the following formula using the IF function to import the Company Name into the cell.

• Press Enter.

• Then, drag the Fill Handle icon to copy the formula upto cell C6 to import the Address.

• Now, we will estimate the value of total Current Assets. For that, create a dataset in the range of cells B9:D13 with a sample dataset.

• At last, we will use the SUM function to calculate the value of total current assets.
• For that, select cell D14 and write down the following formula in the cell.

• Press Enter.

• Similarly, create a dataset for determining the values of total Fixed Assets and total Other Assets.

• In the end, select cell D26, and write down the following formula to evaluate the value of total assets.

• Press Enter for the last time.

Hence, we can say that we have finished the second step to creating a consolidated balance sheet format in Excel.

Read More: Balance Sheet Format in Excel with Formulas (Create with Easy Steps)

Similar Readings

• Balance Sheet Format for Construction Company in Excel
• How to Make Balance Sheet Format in Excel for Individual
• Debit Credit Balance Sheet with Excel Formula (3 Suitable Examples)
• Schedule 6 Balance Sheet Format in Excel
• Balance Sheet Format in Excel for Proprietorship Business

Step 3: Evaluating Total Liabilities and Owner’s Equity

In the following step, we will estimate the value of total liabilities and owner’s equity. The process is explained below:

• At first, create a new sheet and entitle it as Liability&Equity.

• Now, in the Assets sheet, select rows 1:6 and press ‘Ctrl+C’ to copy the rows.

• Then, go to the Liability&Equity sheet and press ‘Ctrl+V’ to paste the content.

• Change the title of the sheet from Total Assets to Total Liabilities and Owner’s Equity.

• After that, like the previous step create a dataset in the range of cells B9:D14 with the sample data value to get the list of Current Liabilities.

• Next, select cell D15 and write down the following formula using the SUM function to get the value of Total Current Liabilities.

• Press the Enter key.

• Similarly, produce a dataset with a sample dataset to get the value of Total Long-Term Liabilities and Total Owner’s Equity.

• Finally, to calculate the value of Total Liabilities and Owner’s Equity, select cell D27 and write down the following formula in the cell.

• Afterward, press Enter.

Therefore, we can say that we have accomplished the third step of creating a consolidated balance sheet format in Excel.

Read More: How to Create Vertical Balance Sheet Format in Excel

Step 4: Determine All Values in Summary Sheet

In the final step, we will determine all the values we listed in the Summary sheet to complete a consolidated balance sheet format in Excel. The procedure is shown below step-by-step:

• Firstly, input the Company Name and the Address in the range of cells C5:C6. You will also notice those values will export into our other two sheets.

• Now, to get the value of Assets, select cell D8 and write down the following formula into the cell.

• Press Enter.

• Similarly, to import the value of Liabilities and Owner’s Equity, write down the following formula into cell D9.

• Then, press the Enter key.

• After that, we will estimate all the financial ratios. First of all, to calculate the Debt Ratio, select cell D12 and write down the following formula. For that, we will use the IF function.

• Again, press Enter.

• Secondly, to get the value of the Current Ratio, write down the following formula in the cell D13 through the IF function.

• Press Enter.

• After that, to calculate the value of Working Capital, select cell D14 and write down the following formula into the cell.

• Press the Enter Key.

• Afterward, select cell D15 and write down the following formula to estimate the value of the Assets-to-Equity Ratio by the IF function.

• Press Enter.

• At last, to get the value of the Debt-to-Equity Ratio, write down the following formula in cell D16.

• Press Enter for the last time.

• Our job is completed.

Finally, we can say that we have finished the final step of creating a consolidated balance sheet format in Excel.

Read More: How to Create Common Size Balance Sheet in Excel

Step 5: Verify with Sample Data

In this step, we will input a new sample dataset to check the accuracy of our formula. We have to input in our Assets and  Liability&Equity sheets.

• First of all, input the following dataset in the Assets sheet.

• After that, input the following data in the Liability&Equity sheet.

• Now, go to the Summary sheet and you will get all the results.

Thus, we can say that all of our formulas work properly and we are able to create a consolidated balance sheet format in Excel.

Read More: How to Create a Balance Sheet for Small Business in Excel

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to create a consolidated balance sheet format in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!

Related Articles

• How to Create Monthly Balance Sheet Format in Excel
• Create Ledger Balance Sheet in Excel (with Easy Steps)
• How to Create School Balance Sheet Format in Excel (with Steps)
• Create NGO Balance Sheet Format in Excel (4 Easy Steps)
• How to Make a Forecasting Balance Sheet in Excel (With 3 Steps)
• Create Projected Balance Sheet Format for Bank Loan in Excel
• Revised Schedule 3 Balance Sheet Format in Excel with Formula