# How to Create Monthly Balance Sheet Format in Excel (6 Steps)

In this article, we we will cover how to create a Monthly Balance Sheet in Excel.

## Balance Sheet Components

A Balance Sheet, also known as a Statement of Financial Position/Condition, summarizes the financial position of a company after a certain period. In general, it consists of three parts: Assets, Liabilities, and Owner’s Equity. Balance sheets are organized according to the equation:

Assets = Liabilities + Owner’s Equity
,

Where,

• Assets: The main resources owned by the company. Assets can be categorized into current and fixed assets, tangible and intangible assets, etc.
• Liabilities: Are amounts owed to a person or another company, like cash, loans, etc.
• Owner’s Equity: Represents the value of the company to the shareholders after all the assets have been sold off and liabilities settled.

## Creating a Monthly Balance Sheet Format in Excel

To create a Monthly Balance Sheet, we’ll need to enter our assets, liabilities, and equity, as these are the main components of any Balance Sheet.

### Step 1 – Insert the Components

• Start by entering all the components of the company’s assets, liabilities, and owner’s equity, like in the image below.

### Step 2 – Set up the Balance Sheet Format

Next, format your sheet as desired. Here is an example of a suitable Balance Sheet format:

### Step 3 – Enter and Calculate the Assets

• Enter the monthly asset values in your balance sheet. Here, we have values for Aug ’22 and Sep ’22.
• In cell C9, enter the following SUM function to calculate Total Current Assets:
=SUM(C6:C8)

This sums the values of the different Assets categories.

• Drag the Fill Handle to the right to copy the same formula to cell D9.

We have the Total Current Assets values for both months.

• In cell C12, enter the following formula to calculate the Net Fixed Assets for Aug ’22:
=C10-C11

This subtracts the Accumulated Depreciation from the value of the other Fixed Assets.

• Press ENTER and use Autofill to copy the formula to cell D12.

We have the Net Fixed Assets for both months.

• In cell C13, enter the following formula:
=C9+C12

This adds the values of Total Fixed Assets and Net Fixed Assets for the first month.

• Press ENTER and drag the Fill Handle right to get the Total Assets for the second month.

### Step 4 – Calculate the Liabilities

• Input all the values for the different categories of Liabilities.
• In cell C18, enter the following formula:
=SUM(C15:C17)

This adds up the values of the different categories of Liabilities and returns the Total Current Liabilities for Aug ’22.

• Copy the formula to cell D18 to return the Total Current Liabilities for Sep ’22.

• In cell C20, enter the following formula:
=C18+C19

This sums the Total Current Liabilities and Long Term Debt to find the Total Liabilities for the first month.

• Press ENTER and copy the formula to cell D20.

We have the Total Liabilities.

### Step 5 – Calculate the Equity

• To calculate the Owner’s Equity, select cell C23 and insert the following formula:
=C21+C22
• Press ENTER and copy the formula to cell D23.

We have the Total Equity.

### Step 6 – Evaluate the Balance Sheet

In this final step, we will calculate the Total Liabilities and Owner’s Equity.

• In cell C24, enter the following formula:
=C20+C23
• Press ENTER and copy the formula to cell D24.

We have the Total Value for both months.

The Total Assets are equal to the Total Liabilities and Owner’s Equity, which means our Balance Sheet balances, and is therefore correct.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF