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:

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:

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

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:

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:

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:

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:

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:

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:

• Press ENTER and copy the formula to cell D23.

We have the Total Equity.

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

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:

• 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.

Download Practice Workbook

Related Articles

• How to Create Comparative 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