Create Cash Flow Statement Format Using Direct Method in Excel

In this article, we will learn to create a cash flow statement format using the direct method in Excel. A cash flow statement is prepared to enlist the detailed cash flow of a company for a specific period. Today, we will demonstrate step-by-step procedures. Using these procedures, you can easily create a cash flow statement format using the direct method. So, without any delay, let’s start the discussion.


Download Template

You can download the template from here.


What Is Cash Flow Statement?

A cash flow statement is defined as a financial statement that holds information about the cash generated and spent during a certain period of time. It allows you to understand where the money is coming from and how it is spent. The period can vary for a cash flow statement. Generally, the companies make monthly, quarterly, half-yearly, or yearly cash flow statements. Sometimes, the period can be more than a year.


Components of Cash Flow Statement

A cash flow statement covers the inflow and outflow of cash for a company. It has 3 basic categories of activities. They are Operating, Investing, and Financial activities.

Operating Activities:

Operating activities are the leading sources of cash inflow and outflow. Generally, it depicts how much cash is generated from the products or services of a company. It might include activities like sales receipts, payments to suppliers, salaries, rent, taxes, and other operating activities.

Investing Activities:

It includes the cash inflow and outflow regarding investing activities. The investing activities can be sales of fixed assets, purchase of a fixed asset, received interest and dividends, equity, and acquisitions. Generally, these are the long-term assets of a company’s balance sheet.

Financial Activities:

Financial activities denote the cash flow from different financial activities. It includes equity shares, repayment of loans, paid interest and dividends, and stock issuance. Usually, these activities are long-term liabilities on a company’s balance sheet.


Methods of Creating Cash Flow Statement

There are two approaches to creating a cash flow statement. They are Direct and Indirect methods.

Direct Method:

In the direct method, we have a tendency to add up all the cash receipts and payments of an organization. Payments to suppliers, receipts from customers, and salaries paid to employees are included in this method. We calculate the net increase in cash and cash equivalents and closing balance in this case.

Indirect Method:

It derives the cash flow from the operating activities. The net income is collected from the income sheet. The financial statement is generated on an accounting system. Hence, users ought to make changes. It also includes adding back the non-operating expenses that do not affect the cash flow.


Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

Today, we will apply the Direct Method to generate a cash flow statement format in Excel. In the following steps, we will create the template for the cash flow statement first and then, demonstrate the calculation. The steps are quite easy. Let’s observe the steps below to see how we can create a cash flow statement.


STEP 1: Create Table for Operating Activities

  • Firstly, we will have to create a table for the Cash Flow from Operating Activities.
  • In this table, we will insert the cash flow sources.
  • Also, we need to create two columns for the Payments ($) and Receipts ($).
  • In the Payments ($) column, we need to add the amount of the cash outflow. For example, payments to suppliers, expenses, wages & salaries, and income taxes.
  • In the Receipts ($) column, we need to add the amount of the cash inflow. For example, money received from the customers.

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

  • After creating the table, we need to calculate the Net Cash using the SUM function.
  • In order to do that, select Cell E10 and type the formula below:
=SUM(F5:F9)-SUM(E5:E9)

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

In this formula, we have subtracted the total money spent from the total money received.

Read More: How to Calculate Operating Cash Flow in Excel (2 Easy Ways)


STEP 2: Design Table for Investing Activities

  • Secondly, we need to design a table for inserting the investing activities.
  • Here, we have included sales of fixed assets, purchase of fixed assets, received interest, and dividends. There can also be other sources of investing activities depending on the company.
  • The money generated from the sales of fixed assets will go into the Receipts ($) section and others will be enlisted in the Payment ($) section.

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

  • In the following step, select Cell E18 and type the formula below to calculate the Net Cash:
=SUM(F14:F17)-SUM(E14:E17)

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

Read More: How to Calculate Net Cash Flow in Excel (3 Suitable Examples)


Similar Readings


STEP 3: Generate Table for Financing Activities

  • In the third step, we need to generate a table for financial activities like the previous two tables.
  • Here, equity shares, repayment of loans, paid interest, and dividends are the financing activities.

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

  • After that, select Cell E26 and type the formula below:
=SUM(F22:F25)-SUM(E22:E25)

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in ExcelRead More: Cash Flow Statement Format in Excel for Construction Company


STEP 4: Insert Formula to Find Cash and Cash Equivalents

  • After inserting the operating, investing, and financial activities, we need to find the Net Increase, Opening, and Closing cash and cash equivalents.

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

  • In order to get the Net Increase in Cash and Cash Equivalents, select Cell E28 and type the formula below:
=E10+E18+E26

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

In this case,

  • E10 is the Net Cash from Operating Activities.
  • E18 is the Net Cash from Investing Activities.
  • E26 is the Net Cash from Financial Activities.
  • The value of Opening Cash and Cash Equivalents is extracted from the balance sheet.
  • To find the amount of Closing Cash and Cash Equivalents, type the formula below in Cell E30:
=E28+E29

Step-by-Step Procedures to Create Cash Flow Statement Format Using Direct Method in Excel

Read More: How to Prepare Daily Cash Flow Statement Format in Excel


STEP 5: Determine Net Cash from Different Activities

  • In this step, we will insert the values of the Operating Activities.
  • As a result, the Net Cash will update automatically.

  • Also, we need to add the cash flow from investing activities.

  • Finally, we will insert the values of Financing Activities. Here, the Net Cash is negative.

Read More: How to Calculate Cumulative Cash Flow in Excel (with Quick Steps)


STEP 6: Calculate Cash and Cash Equivalents

  • Lastly, you need to type the Opening Cash and Cash Equivalents amount in Cell E29.

  • As a result, the Closing Cash and Cash Equivalents amount updates automatically.

Read More: How to Create Weekly Cash Flow Statement Format in Excel


Conclusion

In this article, we have discussed step-by-step procedures to create a Cash Flow Statement Format Using the Direct Method in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Moreover, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo