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

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

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.

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.

Expenses to Create the Income Statement Sheet" width="639" height="387" />

Part 3 – Calculate Net Profit

• Calculate interest expenses or profit, tax expenses, or anything else that affects your profit.
• Add some items as Non–recurringitems.
• 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.

Part 3 – Calculate Net Cash Flow of Investments

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

Flow of Investment Activity of a Cash Flow Statement Sheet" width="653" height="482" />

Part 4 – Calculate Net Cash Flow of Financial Activities

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

Flow of Financial Activity of a Cash Flow Statement Sheet" width="668" height="442" />

• 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