This article illustrates how to create a balance sheet in a proper format in Excel with formulas. A balance sheet contains a summary of the financial amounts of a company or organization. Usually, it contains the assets, liabilities, and equities associated with that organization. A balance sheet is necessary to quickly assess the progress or the financial stability of a company. So, follow this article to learn how to make a balance sheet in a proper format in Excel with formulas.
Steps to Create a Balance Sheet Format In Excel with Formulas
Carry out the following steps to create your balance sheet in a proper format in Excel with formulas.
📌 Step-1: Make a Proper Heading for Balance Sheet
- First, type the name of your company in cell B2. Then write “Balance Sheet” in cell B3. Next, enter the date in cell B4. Now merge cells B:C for rows 2 to 4. Then, apply the Bottom Border from the Home Finally, the balance sheet heading will look as follows.
📌 Step-2: Insert Balance Sheet Components
- A balance sheet has Asset, Liabilities & Shareholder’s Equity. We will enter these components in column B. On the other hand, the corresponding amounts will be entered in column C.
📌 Step-3: Format the Amount Column
- It is better if you format the Amount column before starting to enter data into the balance sheet. First, select column C by clicking on the column number at the top. Then, press CTRL+1 to open the Format Cells dialog box. Now choose the Accounting number format with 2 decimal places. Make sure the $ is visible. Then click OK.
Read More: How to Create Tally Debit Note Format in Excel
📌 Step-4: Insert Current Assets
- Now we will enter the current assets into our balance sheet. Usually, assets that have a period of less than one year are considered current assets. For example, Cash and Equivalents, Account Receivable, Inventory, etc. Enter these components in cells B7 to B9. Then, enter the corresponding amounts in cells C7 to C9
- After that, enter the following formula in cell C10 to calculate the total current assets.
=SUM(C7:C9)
📌 Step-5: Input Fixed Assets
- Now we will enter the fixed assets (Plant & Equipment) in cell B11 and the corresponding amount in cell C11. The depreciation amount must be entered as a negative amount. Notice the depreciation amount is inside brackets indicating a negative number.
- Next, enter the following formula in cell C13 to calculate the Net Fixed Assets.
=SUM(C11:C12)
📌 Step-6: Calculate Total Assets
- Now, enter the following formula in cell C14 to calculate the Total Assets (= Total Current Assets + Net Fixed Assets).
=SUM(C10,C13)
Read More: Revised Schedule 3 Balance Sheet Format in Excel with Formula
📌 Step-7: Input Current Liabilities
- Now, enter the current liabilities ( Accounts Payable, Short-Term Notes Payable, etc.) in cells B16 to B18 and the corresponding amounts in cells C16 to C18
- Then, enter the following formula in cell C19 to get the total current liabilities.
=SUM(C16:C18)
📌 Step-8: Estimate Total Liabilities
- Now, enter the long-term liabilities (Long-Term Debt, etc.), and their corresponding values. Then, enter the following formula in cell C21 to get the total liabilities.
=SUM(C19:C20)
- After that, we need to enter the Shareholder’s Equity (Common Stock, Treasury Stock, Retained Earnings, etc). These are also known as the Owner’s Equity.
- Then, apply the following formula in cell C24 to get the total shareholder’s equity.
=SUM(C22:C23)
Read More: Schedule 6 Balance Sheet Format in Excel
- At this stage, enter the following formula in cell C25 to find the Total Liabilities & Shareholder’s Equity. Make sure this is equal to the total assets obtained earlier.
=SUM(C21,C24)
📌 Step-11: Prepare Final Balance Sheet
- Finally, apply some fill color or other necessary formatting as required. After that, the balance sheet will look as follows.
Things to Remember
- You must put a negative (-) sign before any depreciation amount.
- The total assets the total liabilities and shareholder’s equity in your balance sheet must be equal.
Download the Free Templates
You can download the following templates from the download button below.
Conclusion
Now you can create your balance sheet in a proper format in Excel using formulas. Do you have any further queries or suggestions? Please use the comment section below for that.
