How to Prepare Financial Statements in Excel (with Easy Steps)

What Is a Financial Statement?

Financial Statements are summary reports that contain the state of a company or organization’s financial situation. They include reports like balance sheets, income statements, and cash flow statements.

There are 3 main parts of a financial statement report:

Part 1 – Balance Sheet

It summarizes the financial position of the company after a certain period. It is also known as a Statement of Financial Position/condition. In general, a balance sheet contains 3 sections:

Assets: These are the main resources owned by the company. Assets can be classified into many types. Like Current and Fixed assets, Tangible and Intangible assets, etc.
Liabilities: They are things that the company owes to a person or a company like cash, loans, etc.
Owners Equity: It represents the value for a company’s shareholders after all the company’s assets were sold off and all company liabilities were paid off.

Part 2 – Income Statement

It reports the profit and loss of a company over a certain period. So, the income statement is made of 3 parts:

Revenue
Expense
Profits.

So, it contains all incomes and expenses in a certain period and calculates net profit.

Part 3 – Cash Flow Statement

Cash flow statements are the bridge between the Income statement and the Balance sheet. There are also 3 parts to it:

Operations: It is the main source of revenue for a company or organization. Cash flows regarding main operations will be included here.
Investment Activity: Cash received or paid due to buying or selling any assets, taking loans, paying interest on loans, etc., are included here.
Financing Activity: Cash flows regarding any changes in equity earning or borrowing entities like bonds, stocks, or dividends.

How to Prepare Financial Statements in Excel: with Easy Steps

Step 1 – Create a Balance Sheet

Part 1 – Create a Layout

Create 3 individual sections for Assets, Liabilities, and Owner’s equity and assign a cell to calculate the balance value.
In the assets section, you can create 2 parts for current assets, fixed assets, and other assets.
In the liabilities section, there will be two sections for Current and long-term liabilities.

Balance = Assets – Total Liabilities – Owner’s Equity

Part 2 – Calculate Total Assets

Use the SUM function to calculate the Total Current Assets, Total Fixed Assets, and Total Other Assets.

Part 3 – Calculate Total Liabilities and Owner’s Equity:

Insert the values of current and long-term liabilities and calculate their totals.

Insert the values of the owner’s equity and sum them to calculate the total.
Calculate the total liabilities and owner equity value.

Part 4 – Calculate the Balance Amount:

Subtract the total liabilities and owner’s equity from the value of the total assets.

Here’s a sample balance sheet for a company.

Step 2 – Create the Income Statement Sheet

Part 1 – Calculate Total Revenue:

Calculate the total sales value and subtract the value of the fewer quality products from it.
Add initial inventory, purchased goods and materials, and labor costs.
Subtract the value of ending inventory from the total cost of goods.
Calculate the gross revenue by subtracting the total cost of goods from the net sales.

Part 2 – Calculate Total Operating Expenses

Add all relevant items here and sum them to calculate the total operating expenses.

Part 3 – Calculate Net Profit

Calculate interest expenses or profit, tax expenses, or anything else that affects your profit.
Add some items as Non–recurring items.
Sum them to get the Net Profit value.

You will get a complete Income Statement Sheet.

Step 3 – Create Cash Flow Statement Sheet

Part 1 – Create a Layout:

Create a layout to allocate the cash flow items into 3 different categories for operations, investment activity, and financial activity.

Part 2 – Calculate Net Cash Flow of Operations:

You can receive cash from clients or many other sources and cash be paid for many reasons like inventory, salary, administrative expense, interest, expense, etc.
Subtract the cash-paid value from the cash received value to get the net cash flow of operations.