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

If you are searching for the solution or some special tricks to prepare financial statements in Excel then you have landed in the right place. You may need ready Excel templates for the financial statements like balance sheets, income statements, and cash flow statements. You can download free templates of balance sheets, income statements, and cash flow statements from here. This article will show you every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the main part of the article.


Download Practice Workbook

You can download the practice workbook from here:


What Is Financial Statement?

Financial Statements are summary reports which contain the state of the financial situation of a company or organization. They include the reports of the like balance sheet, Income statement, and Cash Flow statement. They are the written documents or records that the company performs throughout the year.

There are 3 main parts of a financial statement report:

1. Balance Sheet

It summarises 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 portions. And, these are Assets, Liabilities, and Owners’ equity  A balance sheet provides a glimpse of the company’s finances. The sheet consists of the company’s liabilities, assets, and shareholder’s equities. Balance sheets are organized according to the equation:

Assets = Liabilities + Shareholder’s Equity

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

Prepare Financial Statements in Excel


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: 1. Revenue 2. Expense 3. Profits. So, it contains all incomes and expenses in a certain period and calculates net profit.

Income Statement in Excel


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 interests of loans, etc are included here.
  • Financing Activity: Cash flows regarding any changes in equity earning or borrowing entities like bonds, stocks, or dividends.

Prepare Cash Flow Statement in Excel


Steps to Prepare Financial Statements in Excel

In this section, I will show you detailed steps to prepare financial statements in Excel including Balance Sheets, Income statements, and Cash Flow Statements on Windows operating system. You will find detailed explanations of methods and formulas here. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If any methods won’t work in your version then leave us a comment.

Step 1: Create Balance Sheet

To create a balance sheet, you have to make a layout first and then insert the data of assets, liabilities, and owner’s equity. Finally, the balance value should be zero and that is the target of a balance sheet. Follow the steps below to create a balance sheet.

⇒ Create a Layout:

First, you have to make 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. And, in the liabilities section, there will be two sections for Current and long-term liabilities.

Balance = Assets – Total Liabilities – Owner’s Equity

Create a Layout of balance sheet to Prepare Financial Statements

Note: You should add or remove rows in the tables as your need.

⇒ Calculate Total Assets:

Now, use the SUM function to calculate the Total Current Assets, Total Fixed Assets, and Total Other Assets. Then, calculate the Total Assets by doing sum of them.

Calculate Total Assets to Create a Balance Sheet

⇒ Calculate Total Liabilities and Owner’s Equity:

Then, insert the values of current and long-term liabilities and calculate the total of them.

Calculate Total Liabilities and Owner’s Equity to Create a Balance Sheet

After that, insert the values of the owner’s equity and sum them to calculate the total. Then, calculate the total liabilities and owner equity value.

Calculate Owner’s Equity to Create a Balance Sheet

⇒ Calculate Balance Amount:

Finally, calculate the Balance amount by subtraction of total liabilities and owner’s equity from the value of the total assets.

Calculate Balance Amount to Create a Balance Sheet

Finally, you have made a balance sheet for your company.

Prepare Balance Sheet of Financial Statements in Excel

Read More: How to Automate Financial Statements in Excel (with Easy Steps)


Step 2: Create Income Statement Sheet

In the income statement sheet, there will be three parts: Revenue, Expense, and Profit. First, create a layout then insert data and calculate the total value of them.

⇒ Calculate Total Revenue:

In the revenue part, you have to insert the following items:

  • First, calculate the total sales value and subtract the value of the fewer quality products from it.
  • Then, calculate the cost of goods sold. Here you have to add initial inventory, purchased goods and materials, and labor costs. Then, subtract the value of ending inventory from the total cost of goods.
  • Finally, calculate the gross revenue by subtracting the total cost of goods from the net sales

Calculate Total Revenue to Create the Income Statement Sheet

⇒ Calculate Total Operating Expenses:

Operating expense is the cost that is needed to run the operation. You have to add all relevant items here. and, finally, calculate the total operating expenses.

Calculate Total Operating Expenses to Create the Income Statement Sheet

⇒ Calculate Net Profit:

Before calculating the net profit, you have to calculate some additional items like interest expenses or profit, tax expenses, etc which are related to the operations. Also, you have to add some items as Nonrecurring items which happens occasionally. Taking all these into concern, you will calculate the Net Profit value.

Calculate Net Profit to Create the Income Statement Sheet

finally, you will get a complete Income Statement Sheet.

Prepare Income Statements of Financial Statements in Excel

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


Step 3: Create Cash Flow Statement Sheet

The cash flow statement is one of the important parts of the financial statements. It calculates the net cash amount in a certain period for a company or organization. This sheet contains 3 parts: operations, investment activity, and financial activity. Follow the steps below to create a cash flow statement sheet.

⇒ Create a Layout:

First, create a layout to allocate the cash flow items into 3 different categories of operations, investment activity, and financial activity.

Create a Layout of a Cash Flow Statement Sheet

⇒ Calculate Net Cash Flow of Operations:

After that, calculate the cash flow for 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. Finally, subtract the cash-paid value from the cash received value to get the net cash flow of operations.

Calculate Net Cash Flow of Operations of a Cash Flow Statement Sheet

⇒ Calculate Net Cash Flow of Investment Activity:

Then, calculate the cash flow of investment activity. Here, the items relevant to the assets of the company will be listed like properties, machinery, loans, investment securities, etc.

Calculate Net Cash Flow of Investment Activity of a Cash Flow Statement Sheet

⇒ Calculate Net Cash Flow of Financial Activity:

And, net cash flow of financial activity will include the items related to the financial matters of the company like stock issuance, borrowings, dividends, etc.

Calculate Net Cash Flow of Financial Activity of a Cash Flow Statement Sheet

As a result, you will now get the Cash flow statement of the company ready.

Prepare Cash Flow Statement for Financial Statements in Excel

Read More: How to Link 3 Financial Statements in Excel (with Easy Steps)


Conclusion

In this article, you have found how to prepare financial statements in Excel including balance sheets, income statements, and cash flow statements. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo