How to Create Comparative Balance Sheet Format in Excel

Wanna compare between financial figures of your company on different time periods? You will need a comparative balance sheet then. A comparative balance sheet will pave the way to determine the ups and downs your company has faced in different periods. This article provides a clear idea about the comparative balance sheet format in Excel.

Comparative Balance Sheet

A comparative balance provides insight into a company’s financial figures of assets and liabilities. This type of balance sheet may serve three types of purposes:

1. Two or more time periods of the same company
2. Two or more branches of the same company
3. Two or more companies in the same industry.

A comparative balance sheet contains two columns that differentiate between the financial objective of the concerning terms. For comparing the financial condition of different periods of the same company, the periods would be added to the balance sheet and the changes and fluctuations between the periods will be described.

Stepwise Illustration of Comparative Balance Sheet Format in Excel

Let’s say, we are dealing with an XYZ Limited company. We have the data for the financial year that ended on 31^st December 2020 and 2021. We want to create a comparative balance sheet format in Excel for these two consecutive financial years where we will try to show a comparison between these two years and changes in the particulars between them.

In this section, you will find some simple and useful steps to create a comparative balance sheet format in Excel. I will demonstrate them one by one here. Let’s check them now!

⏩ Step 1: Assign Particulars for the Balance Sheet

The first and foremost step to creating a comparative balance sheet is to assign particulars for several components under specific categories. Just proceed as below to serve this purpose.

• First of all, create a table like the one stated in the image below. Usually, a balance sheet consists of two main categories of particulars.

• Then, assign particulars for these two main categories. For Equity & Liabilities, we have assigned particulars:

Again, for Assets, the particulars considered are:

• Now, for each particular, assign some components as per your business strategy. Your business may have different types of Assets & incomes and also different types of Liabilities. Input them with the particulars.

Read More: How to Make Balance Sheet Format in Excel for Individual

⏩ Step 2: Input Values for the Time Period

After assigning the input variables for the business, it’s time to input values for these particulars and components.

• Here, assign the number of input particulars for the first occurring financial year (i.e. 2020).

• Now, calculate the total amount of Equity & Liabilities for the financial year 2020. To do so, just select a cell where you want to show the total amount of Liabilities and type the following formula in that cell while applying the SUM function.

Here,

• E7 = First cell of Equity & Liabilities to calculate the sum
• E11 = Last cell of Equity & Liabilities to calculate the sum

• Then press ENTER to let the cell get you the total amount of Equity & Liabilities.

• After that, applying the same function, calculate the total amount of Assets available in that financial year.

• Now, repeat the same process for the following financial year (i.e. 2021) and find out the total amount of Assets and Liabilities for this year also.

Read More: How to Prepare Charitable Trust Balance Sheet Format in Excel

⏩ Step 3: Finding Absolute Change Between Financial Years

Well! We have shown the balance sheet for the consecutive encountering period. Hold on! It’s not over. Now, we will establish a comparison between these two consecutive years.

• Let’s find out the change in the absolute value of one particular (i.e. Share Capital). So, type the following formula in the column named “Change in Absolute Value”.

Here,

• E7 = Amount of Share Capital in 2020
• F7 = Amount of Share Capital in 2021

• Now, press ENTER, and the cell will show you the change in the absolute amount between the financial years.
• After that, drag the Fill Handle tool downward to Autofill the formula just to the bottom end value of the table.

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

⏩ Step 4: Determine Change in Percentage

At this stage of our building process, we need to determine the change and represent it in the percentage format.

• To determine the change in percentage concerning the financial year 2021, apply the formula below.

Here,

• G7 = Change in Absolute Value of Share Capital
• F7 = Amount of Share Capital in 2021

• Here, you will get the result in Decimal format. Just go to the Home tab> click Percentile Style on the Number group to change the format to percentage.
• Drag the formula down and for every particular, the changes will be found in percentages.

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

Advantages of Comparative Balance Sheet

• Analyzing the comparative balance sheet helps to imply the condition of a financial figure. Let’s say, for the balance sheet we are dealing with, Reserve & Surplus has increased by 5% in a financial year.  On the other hand, Long Term Liabilities have decreased by 2%.
• A comparative balance sheet projects the trend of the company for several years and shows the fluctuation in the amount of Assets & Liabilities.
• Observing the balance sheet by making a financial comparison between 4-5 consecutive periods helps to predict the financial condition of the company. It helps to make decisions and challenges for the managerial committee.
• Comparing different companies in the same industry helps to make decisions in which sector more priority should be given for ultimate profit.

Download Practice Workbook

You can download the practice book from the link below.

Conclusion

In this article, you have learned to create a comparative balance sheet format in Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box.

Related Articles

• How to Create Monthly Balance Sheet Format in Excel
• How to Create Provision Balance Sheet Format in Excel
• Create Average Daily Balance Calculator in Excel
• Net Worth Formula Balance Sheet in Excel

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