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:

📚 Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.


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.

Choosing proper shape to create a consolidated balance sheet format

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

Defining basic information area

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

Listing all the necessary particular to create a consolidated balance sheet format

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

Inserting image from our device

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

Creating Preliminary Summary Layout to Create Consolidated Balance Sheet Format

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.

Copying rows 1 to 6

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

Paste the rows in a new sheet

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

=IF(Summary!C5=0," ",Summary!C5)

  • Press Enter.

Using IF function to import company name from the summary sheet

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

Listing all the current assets items to create a consolidated balance sheet format

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

=SUM(D9:D13)

  • Press Enter.

Using SUM function to get sum of all current assets

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

Adding other two assets dataset to create a consolidated balance sheet format

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

=D14+D20+D24

  • Press Enter for the last time.

Estimating Total Assets to Create Consolidated Balance Sheet Format

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


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.

Copying 6 rows to paste another sheet

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

Pasting all copied cells

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

Listing all current liabilities items to create a consolidated balance sheet format

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

=SUM(D9:D14)

  • Press the Enter key.

Using SUM function to get the total value

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

Adding all equity items to create a consolidated balance sheet format

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

=D15+D20+D25

  • Afterward, press Enter.

Evaluating Total Liabilities and Owner's Equity to Create Consolidated Balance Sheet Format

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.

=Assets!D26

  • Press Enter.

Importing Assets value to create a consolidated balance sheet format

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

=' Liability&Equity'!D27

  • Then, press the Enter key.

Importing Liabilities and Owner's Equity value to create a consolidated balance sheet format

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

=IF(Assets!D26=0,"",(' Liability&Equity'!D15+' Liability&Equity'!D20)/Assets!D26)

  • Again, press Enter.

Estimating Debt Ratio value by the IF function

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

=IF(' Liability&Equity'!D15=0,"",Assets!D14/' Liability&Equity'!D15)

  • Press Enter.

Calculating Current Ratio by the IF function

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

=Assets!D14-' Liability&Equity'!D15

  • Press the Enter Key.

Getting Working Capital in a consolidated balance sheet format

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

=IF(' Liability&Equity'!D25=0,"",Assets!D26/' Liability&Equity'!D25)

  • Press Enter.

Evaluating Assets-to-Equity Ratio value by the IF function

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

=IF(' Liability&Equity'!D25=0,"",(' Liability&Equity'!D15+' Liability&Equity'!D20)/' Liability&Equity'!D25)

  • Press Enter for the last time.

Determining Debt-to-Equity Ratio value by the IF function

  • Our job is completed.

Determine All Values in Summary Sheet to Create Consolidated Balance Sheet Format

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.

Verify Consolidated Balance Sheet Format with Sample Data in Excel

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

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo