How to Create Common Size Balance Sheet in Excel (3 Simple Steps)

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, we often need to create a common-size balance sheet in Excel. By definition, a common-size balance sheet considers every balance sheet account as a percentage of Total Assets. In Excel, we can easily make a common-size balance sheet by following some simple steps. Now, let’s explore this article and learn 3 simple steps to do this.


How to Create Common Size Balance Sheet in Excel: 3 Simple Steps

In this section of the article, we will learn 3 easy steps to create a common-size balance sheet in Excel. In a common-size balance sheet, both the numeric and relative percentage data with respect to the Total Assets are displayed.

Let’s say, we have the Balance Sheet of ABC Store as our dataset. Our goal is to create a common-size balance sheet for this dataset. Now, let’s follow the steps mentioned below to do this.

common size balance sheet excel

Not to mention that we have used the Microsoft Excel 365 version for this article, you can use any other version according to your convenience.


Step 01: Create a New Table

In the first step, we will create our output table where we will display the relative percentage.

  • Firstly, create an identical table just like the dataset. Just keep the Amount column blank as shown in the following picture.

Create a New Table to create common size balance sheet in excel

Read More: How to Make Automatic Balance Sheet in Excel


Step 02: Calculate Relative Percentage

Now, we will calculate the relative percentage with respect to the Total Assets. Let’s use the instructions outlined below.

  • Firstly, enter the following formula in cell F6.
=C6/$C$14

Here, cell C6 represents a cell of the Category column, and cell $C$14 refers to the cell of Total Assets.

  • Following that, press ENTER.

Calculate Relative Percentage  to create common size balance sheet in excel

Consequently, the relative percentage of Cash will be displayed in cell F6 as marked in the following image.

  • Now, use the AutoFill option of Excel to get the remaining outputs.

Read More: How to Make a Forecasting Balance Sheet in Excel


Step 03: Format Output Table

As the relative percentages are not in a percentage format, we need to change the formatting of the cells to display percentage values. Let’s follow the procedure outlined below to do this

  • Firstly, select the cells as marked in the image below.
  • After that, go to the Home tab from Ribbon.
  • Then, click on the Number Format drop-down.
  • Now, select the Percentage option from the drop-down.

Format Output Table to create a common size balance sheet in Excel

Alternatively, you can multiply 100 by the inserted formula in Excel. So, the adjusted formula for the F6 will be-

=C6/$C$14*100

As a result, the cells of the output column will be formatted as percentage values as shown in the following picture.

  • Following that, select the cells E14 and F14.
  • Next, go to the Home tab from Ribbon.
  • Subsequently, click on the Fill Color drop-down.
  • Then, choose your preferred color from the drop-down.

Consequently, you will have the common-size balance sheet like in the following image.

Fina output of method 1 to create a common size balance sheet in Excel

Note: Here, the cell of the relative percentage of Total Assets works as an identifier. If its value is 100%, that will mean that we have done the calculations correctly.

Read More: Rental Property Balance Sheet in Excel


How to Create Common-Size Income Statements in Excel?

In Excel, sometimes we need to create a common-size income statement in Excel. It is almost similar to the previous method. But here we will calculate the relative percentage with respect to the Total Revenue instead of Total Assets.

Let’s say, we have the Income Statement of XYZ Tech Store for 4 years. Our goal is to create a common-size income statement with this dataset. Now, follow the steps outlined in the following section to do this.

How to Create Common Size Income Statement in Excel

Steps:

  • Firstly, create a table as shown in the following image.

  • Following that, enter the following formula in cell C16.
=C5/C$5

Here, cell C5 represents the Sales Revenue for the year 2019.

  • After that, press ENTER.

Note: Here, for the denominator, we only locked the row number (C$5). For this reason, we will be able to drag this formula both horizontally and vertically.

Using excel formula to create common size income statement in Excel

As a result, you will have the relative percentage of Sales Revenue for the year 2019.

  • Now, drag the Fill Handle in the row direction up to cell F16 and you will get the following outputs.

  • After that, by using the AutoFill feature of Excel, you can get the remaining outputs as demonstrated in the following picture.

After formatting the cells your output table will look like the following image.

Final output of method 2 to create common size income statement in Excel

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


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Practice section to create a common size balance sheet in Excel

Click on the image for better quality<\em>


Download Practice Workbook


Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to create a common-size balance sheet in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality.



<< Go Back to Balance Sheet | Finance Template | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo