How to Create a Cash Flow Statement Using Direct Method in Excel (Step-by-Step)

What Is a Cash Flow Statement?

A cash flow statement contains information about the cash generated and spent during a certain period. It lets you understand where the money comes 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 a Cash Flow Statement

Operating Activities:

Operating activities are the leading sources of cash inflow and outflow. Generally, they depict how much cash is generated from a company’s products or services. These might include sales receipts, suppliers’ payments, salaries, rent, taxes, and other operating activities.

Investing Activities:

It includes the cash inflow and outflow related to investing activities. These activities can include sales of fixed assets, purchases of fixed assets, interest and dividends received, 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. These include equity shares, loan repayment, interest and dividend payments, and stock issuance. Usually, these activities are long-term liabilities on a company’s balance sheet.

Methods of Creating Cash Flow Statements

Direct Method:

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

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 1 – Create a Table for Operating Activities

• Create a table for the Cash Flow from Operating Activities.
• Insert the cash flow sources in the table.
• Create two columns for the Payments ($) and Receipts ($).
• In the Payments ($) column, add the amount of the cash outflow—for example, payments to suppliers, expenses, wages & salaries, and income taxes.
• In the Receipts ($) column, add the amount of the cash inflow—for example, money received from the customers.

• After creating the table, calculate the Net Cash using the SUM function. Select cell E10 and enter the formula below:

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

Step 2 – Design a Table for Investing Activities

• Include 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 fixed asset sales goes into the Receipts ($) section, and others will be listed in the Payment ($) section.

• Select cell E18 and enter the formula below to calculate the Net Cash:

Step 3 – Generate a Table for Financing Activities

• Equity shares, repayment of loans, paid interest, and dividends are the financing activities.

• Select cell E26 and enter the formula below:

Read More: Cash Flow Statement Format in Excel for Construction Company

Step 4 – Insert a 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.

• To get the Net Increase in Cash and Cash Equivalents, select cell E28 and enter the formula below:

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

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

Step 5 – Determine Net Cash from Different Activities

• Insert the values of the Operating Activities.
• The Net Cash will update automatically.

• Add the cash flow from investing activities.

• Insert the values of Financing Activities. Here, the Net Cash is negative.

Step 6 – Calculate Cash and Cash Equivalents

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

Download the Template

Related Articles

• How to Create Cash Flow Statement Format in Excel
• How to Create Monthly Cash Flow Statement Format in Excel

<< Go Back to Cash Flow Template | Finance Template | Excel Templates