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.
Download Practice Workbook
You can download the practice book from the link below.
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:
- Two or more time periods of the same company
- Two or more branches of the same company
- 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 31st December 2020 and 2021. We want to create a comparative balance sheet format in Excel for these two consecutive financial years where w 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.
- Equity & Liabilities
- Then, assign particulars for these two main categories. For Equity & Liabilities, we have assigned particulars:
- Shareholder’s Fund
- Non-Current Liabilities
- Current Liabilities
Again, for Assets, the particulars considered are:
- Non-Current Assets
- Current Assets
- 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.
⏩ Step 2: Input Values for the Time Period
After assigning the input variables for the business, now it’s time to input values for these particulars and components.
- Here, assign the amount 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 with applying the SUM function.
- 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.
- How to Perform Balance Sheet Ratio Analysis in Excel
- Debit Credit Balance Sheet with Excel Formula (3 Suitable Examples)
- How to Make Profit and Loss Account and Balance Sheet in Excel
- Schedule 6 Balance Sheet Format in Excel
- How to Make a Forecasting Balance Sheet in Excel (With 3 Steps)
⏩ 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”.
- 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.
⏩ 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.
- 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.
Advantages of Comparative Balance Sheet
- Analyzing the comparative balance sheet helps to imply the condition of a financial figure. Let’s say, for the concerning 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 take decisions and challenges for the managerial committee.
- Comparing different companies in the same industry helps to take decisions in which sector more priority should be given for ultimate profit.
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. For more queries, kindly visit our website ExcelDemy. Have a great day!
- How to Make Stock Balance Sheet in Excel (with Quick Steps)
- How to Tally a Balance Sheet in Excel
- Net Worth Formula Balance Sheet in Excel (2 Suitable Examples)
- How to Create Common Size Balance Sheet in Excel
- How to Create School Balance Sheet Format in Excel (with Steps)
- Create Projected Balance Sheet Format for Bank Loan in Excel