Md. Sourov Hossain Mithun
For every kind of business, making a balance sheet is mandatory. Because it gives you a clear idea and output of your business. And if any mistake occurs in the calculation then the balance sheet can detect it. I hope, this article will be a proper guide to tally a balance sheet in Excel with vivid illustrations.
Download Practice Workbook
You can download the free Excel workbook from here and practice on your own.
What Is a Balance Sheet?
A balance sheet is a financial document that can express the connection between a company’s assets, liabilities, and shareholder equity at a specific time. It helps to calculate a company’s net worth. Along with the cash flow statement and income statement, it is another important statement that a company should keep.
Here is our sample balance sheet, in which I’ll show how to tally between the liabilities and assets using a company’s data.
Steps to Tally a Balance Sheet in Excel
Calculate Liabilities
Liability is the amount or obligation for which a company is liable. First, we’ll calculate the total liabilities amount. There are three sections in this section. If you have more sections then add them here.
Steps:
- There is the capital of two persons, so we’ll find the total capital using the SUM function. Apply the following formula in Cell D7–
=SUM(C6:C7)- Hit the Enter button and you will get the total.
- Then place the loan amount. As there is only one loan amount, so we’ll insert it directly as the total in Cell D9. So, type the following formula and hit the ENTER button.
=C9
- Now, we’ll calculate the other current liabilities. I have shown three current liabilities, if you have more then add them in this section. To sum them up, insert the following formula in Cell D13–
=SUM(C11:C13)- Later, press the Enter button to get the total.
The amounts of all the section’s liabilities are calculated. Now let’s find the total liabilities amount.
- In Cell D18, write the following formula-
=SUM(D6:D13)- Then just hit the Enter button to finish.
Read More: Balance Sheet Format of a Company in Excel (Download Free Template)
Calculate Assets
Assets are the resources that a company holds, they can be property, equipment, cash, etc. Now we’ll calculate the total assets. It has also three sections. If you have more sections then add them here.
Steps:
- In the Motor Vehicle section, there is a depreciation so to find the net amount we’ll subtract it from the Opening Balance using a manual formula. The formula will be as follows-
=G6-G7- Hit the Enter button to find the output.
- There are depreciation and addition in the next Office Equipment section. So we’ll add and subtract them from the Opening Balance to find the net amount here. Type the formula as follows-
=G9+G10-G11- Don’t forget to press the Enter button to get the output.
- We have some current assets here like, cash in hand, bank accounts, etc. Add them using the following formula-
=SUM(G13:G16)- Press the Enter button to get the summed amount.
- All the individual assets are calculated. After that, apply the following formula to calculate the overall assets-
=SUM(H6:H16)- Later, just press the Enter button.
Read More: Balance Sheet Format in Excel for Proprietorship Business
Calculate Difference
If the difference between the liabilities and assets gets zero then we can say that we haven’t done anything wrong in our calculations, everything is all right. But if it becomes zero that means there’s something wrong in the data or calculations. Let’s check it for our dataset.
Steps:
- Subtract the liabilities from the assets by inserting the following formula-
=H18-D18- Finally, just hit the Enter button to finish.
Now have a look, it returns zero. So we have done nothing wrong.
Read More: How to Make Projected Balance Sheet in Excel (with Quick Steps)
Things to Remember
- Make sure you have inserted the right data.
- Make sure about the amount before inserting that it’s a liability or asset.
- The difference must be zero for a perfect calculation.
Conclusion
I hope the procedures described above will be good enough to tally a balance sheet in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.
