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.

Read More: How to Automate Financial Statements in Excel

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.

Read More: How to Link 3 Financial Statements in Excel

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.

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.

Read More: How to Prepare Financial Statements from Trial Balance in Excel

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.

Read More: How to Create a Personal Financial Statement in Excel

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.

Part 3 – Calculate Net Cash Flow of Investments

• The items relevant to the assets of the company include properties, machinery, loans, investment securities, etc.

Part 4 – Calculate Net Cash Flow of Financial Activities

• Include items like stock issuance, borrowings, dividends, etc.

• You will get the Cash flow statement of the company.

Download the Practice Workbook

Related Articles

• Consolidation of Financial Statements in Excel
• How to Create Pro Forma Financial Statements in Excel

<< Go Back to How to Create Financial Statements in Excel | Excel for Finance | Learn Excel