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.
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.
- 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.
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.
=IF(Summary!C5=0," ",Summary!C5)
- Press Enter.
- Then, drag the Fill Handle icon to copy the formula up to 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.
=SUM(D9:D13)
- 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.
=D14+D20+D24
- 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:Â How to Create Monthly Balance Sheet Format in Excel
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.
=SUM(D9:D14)
- 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.
=D15+D20+D25
- 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 School Balance Sheet Format in Excel
Step 4: Determine All Values in the 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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 Prepare Charitable Trust Balance Sheet Format 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 Make Balance Sheet Format in Excel for Individual
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
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.
