How to prepare cash flow statement in Excel

Cash Flow Statement Template in Excel, OpenOffice Calc, and Google Sheet to record annually and monthly operating, investing, and financial cash flows for your company.

In addition to the above, there are yearly and monthly cash flow formats in each template. Download the desired file format and use the one that suits your needs.

Moreover, this article consists of information like definitions, types, methods, advantages, limitations, components of Cash Flow Statements along with a ready-to-use template. Thus, it can be useful for accountants as well as students to learn in detail about Cash Flow Statements.

Download Cash Flow Statement Template Using Direct Method (Excel, OpenOffice Calc & Google Sheet)

Click on the button below to download your desired file format of Cash Flow Statement prepared using the direct method:

Microsoft Excel OpenOffice Calc Google Sheet

Download Cash Flow Statement Template Using Indirect Method (Excel, OpenOffice Calc & Google Sheet)

Click on the button below to download your desired file format of Cash Flow Statement prepared using the indirect method:

Microsoft Excel OpenOffice Calc Google Sheet

Components Of The Cash Flow Statement Template

We have created the Cash Flow Statement by two different methods: Direct Method and Indirect Method. Let us understand the components of each template in detail.

Moreover, the article explains the Direct and Indirect methods of Cash flow in detail below template components.

Cash Flow Statement Template – Direct Method

This template consists of 4 sections: Cash Flow from Operating Activities, Investing Activities, Financial Activities, and Cash Flow Summary. Insert the amounts pertaining to the applicable items in the list below.

Line items of Direct Method Cash Flow Statement for Operating Activities

Accounts Receivable
Accounts Payable
Interest Paid
Income Tax Paid
Salaries
Other Administration Expenses

To find Net Cash Flow from Operating activities, subtract Accounts Payable and the remaining 4 items from Accounts Receivable.

Line items of Direct Method Cash Flow Statement for Investing Activities

Profit From Sale of Equipment
Profit From Sale of Property
Rental Income
Purchase of Property, Plant, or Equipment
Loss From Sale of Equipment
Loss From Sale of Property

To find Net Cash Flow from Investing activities, subtract the last 3 items from the first 3 items.

Line items of Direct Method Cash Flow Statement for Financing Activities

Proceeds from Issuance of Common Stock
Proceeds from Issuance of Long Term Loan/Short Term Loan
Interest Income – Long Term Loan
Interest Income – Short Term Loan
Capital Lease Obligations
Dividends Paid
Interest Paid on Long Term Loans
Interest Paid on Short Term Loans

To find Net Cash Flow from Financing Activities, subtract the last 4 line items from the first 5 line items given above.

Monthly Cash Flow Statement Template – Direct Method

The Monthly Cash Flow is a replica of the yearly cash flow. The only difference is that it consists of a separate column for each month to record monthly cash flows.

Cash Flow Statement Template – Indirect Method

Similar to the Direct Method, this template also consists of 4 sections: Cash Flow from Operating Activities, Investing Activities, Financial Activities, and Cash Flow Summary. The line items are different.

Insert the amounts pertaining to the applicable items in the list below.

Line items for Operating Activities of Indirect Method Cash Flow Statement

Net Income
Depreciation & Amortization
Bad Debt Provision
Gain on Sale of Facility
Increase in Accounts Receivables
Decrease in Accounts Payable
Decrease in Inventory

To find Net Cash Flow from Operating activities, subtract the last 6 items from Net Income.

Line items for Investing Activities and Financing Activities for Indirect Method Cash Flow is the same as the Direct method.

Monthly Cash Flow Statement Template – Indirect Method

The Monthly Cash Flow is a replica of the yearly cash flow. The only difference is that it consists of a separate column for each month to record monthly cash flows.

Important Note: A positive cash flow indicates surplus cash in hand whereas a negative cash flow balance indicates a deficit of cash against expenses.

What Is A Cash Flow Statement?

A Cash flow Statement is a Financial Statement that records money in and out from operating, investing, and financial activities of your business. It helps to understand the overall financial health of the company.

In simple terms, it is a statement that describes the amount of cash generated and spent within a certain period. It is also known as Statement of Cash Flows.

Purpose of Statement of Cash Flow

The main purpose of cash flow statements is to measure how well the company manages and uses its cash to pay its debt and fund its operating expenses.

Not only that, but the cash flow provides the necessary information to design and execute the growth and expansion plans.

Another purpose of cash flow is to keep an eagle’s eye on cash movement. This means how the cash was generated and how it was used.

It provides information about the non-core business activities like investing and financing that play an important role in a company’s future cash flow.

Moreover, the purpose or objective of the Statement of cash flow is to indicate the management strategy and the future outlook of the firm.

Furthermore, it is a legal obligation for companies listed in stock markets to regularly provide the cash flow statement to the regulatory authorities to make sure that the investor’s money is safe.

Types of Cash Flow Activities

The Statement of Cash Flow consists of majorly 3 activities: Operating, Investing and Financing. Let us understand each in brief.

Operating Activities

Operating activities are activities that are the main source of income for any business. These can either be selling/manufacturing products or providing services.

It consists of both inflow activities and outflow activities. Inflow activities are those that bring in cash or cash equivalent for the business whereas outflow is the expenses made by the business to accomplish those activities.

Operating activities cash flow differ according to the size of the company. Usually, such businesses use a direct method of cash flow.

For example, for a pizza shop inflow operating activities are selling baked pizza. Outflow activities include paying for rent, ingredients, electricity, labor, etc.

On the contrary, for a web developer inflow activities are his services to the clients and outflow includes paying for rent, internet connection, etc.

Whereas, for corporates, their Net income from the Profit and loss accounts needs to be adjusted by adding an increase in accounts payable, depreciation, and inventory.

Moreover, we need to deduct the increase in account receivables from the net income. Hence, the adjusted net income is the actual cash flow from operating activities.

Investing Activities

Investing activities consist of cash inflow and outflow from sale/purchase of machinery/plant/land/building and rental income from real estate.

Thus, the cash inflow from investing activities includes gains from selling these cash equivalents. It also includes rental income from real estate.

The cash outflow from investing activities includes losses on the sale of equipment, property, or machinery. The cash flow from investing activities in the direct method, as well as the indirect method of cash flow, are the same.

Financing Activities

The Financial activities consist of inflow and outflow of cash from financing activities like loans, stock issuance, dividends, lease agreements, etc. Usually, these items are the long-term liabilities on a company’s Balance Sheet.

The cash inflow from Financing activities includes the issuance of common stock, proceeds of long/short term loans, and interest income from long/short term loans.

The cash outflow activities include Capital Lease, payment of dividends, and interest paid on long/short term loans. Financing activities in the direct method, as well as the indirect method of cash flow, are the same.

Methods of Preparing Statement of Cash Flow

You can measure cash flows by two different methods: Direct Method and Indirect Method. Let us understand each in brief.

Direct Cash Flow Method

The direct method is a type of accounting method to generate a cash flow statement. It uses actual cash inflows and outflows from operating activities. You don’t need to modify the cash operating activities from accrual basis to cash basis.

In simple terms, it measures cash inflow from what it receives from customers and the cash outflow from payments to suppliers payment of interests, income tax, salaries, and other administrative expenses.

Moreover, the direct method is also known as the income statement method.

Indirect Cash Flow Method

The indirect method is another type of accounting method to generate a cash flow statement. The indirect method uses the balance sheet line item to derive the cash flow from operating activities.

The increases and decreases of balance sheet line items are taken into account to modify the operating cash flow from the accrual to the cash basis.

Under this method, you need to take net income from the balance sheet and adds or subtracts non-cash items. This gets you the actual cash flows from operating activities.

Generally, as most businesses use accrual accounting, they often use the indirect method as it is easier to derive the figures.

Rules To Follow While Preparing Statement of Cash Flow

Four simple rules to remember as you create your cash flow statement:

1. An increase in assets results in a decrease in cash flow.
2. A decrease in assets results in an increase in cash flow.
3. An increase in liabilities results in an increase in cash flow.
4. A decrease in liabilities results in a decrease in cash flow.

Source: www.Bench.co

Advantages Of Statement of Cash Flow

• The management can understand and verify the profitability and liquidity positions of the business.
• It provides an actual picture of the business’s capital cash balance
• It helps in proper cash management.
• Further, it helps in planning future growth.
• Helps to predict the future cash flow of companies.
• Reflects the overall financial health of the company.
• Impact of bank loan payments on your cash flow.
• Helps you arrange for any cash shortfalls.
• To plan and control the financial operations easily.

Limitations of Statement of Cash Flow

• Only displays cash availability and does not project the profits.
• Minor adjustments can hide the actual liquidity of a business.
• Doesn’t provide the complete financial position of a business.
• It uses cash bais instead of an accrual basis. Hence, the accuracy of the cash flow statement is questionable.
• It does not measure the efficiency of the firm. Thus, comparison with other inter-industry companies is not possible.

How To Analyse A Statement of Cash Flow?

Cash flow from operating activities is the most important part of a company’s cash flow statement. Consistently higher cash flow from operating activities are signs of a healthy and self-sustaining business.

Generally, in bigger businesses, cash flow from investing activities is negative. This indicates that the business uses its cash flow for expansion as well as replacement of old assets.

On the contrary, while analyzing you need to see if the investments are such that will generate revenue growth in the future or not.

Usually, businesses with a negative Investing cash flow will have positive Financing cash flows. This is because it helps the company to raise additional equity capital to support their investments.

Conclusively, cash inflows from operating activities should be enough to cover operations as well as lender’s and shareholder’s obligations.

In simple terms, having operational cash flow higher than investing and financing outflows is a sign of a financially healthy business.

Moreover, when a company has low or negative operating cash flows it has to liquidate assets to funds its operations. It is an alarming call for a company.

Furthermore, such negative operating cash flows can lead companies to bankruptcy.

To safeguard the interest of investors, the companies sell their assets to pay dividends. Because dividend cuts depict a bad financial health pf the company.

Keep in mind that such a sale of assets doesn’t belong to the core business. Additionally, the sale of such assets must not be an additional burden to the company. Thus, as an investor digging deep is necessary.

Some companies have low or negative operational cash flow and their financial cash flow is positive. Thus, such imbalance indicates the inefficiency of management to operate and manage cash flows.

Uses of Cash Flow Statements

• To evaluate the cash position of a firm.
• Plan to arrange for the future requirements of cash.
• Compare historical data to find deficiencies in the performance.
• Enables the firm to take effective decisions.
• Plan future long-term cash flows for the repayment of loans and acquiring fixed assets.
• Useful while making mergers or acquisitions.
• Explains the cause of poor variables in the financial statement.
• To determine the liquidity and solvency of a business.
• For valuation of your business worth.

Pro-Tips to Improve Cash Flow

• Lease supplies, equipment, and real estate instead of buying.
• Offer discounts to buyers for early payments to increase cash flow.
• Keep an eye on creditors and their financial positions to avoid bad debts.
• Clear outdated stock for a small loss before it reaches 0 value.
• Manage supplier payments.
• Use saving accounts that provide higher interest.
• Price your products according to market standards.

Frequently Asked Questions

The operating cash flow ratio is a measure to evaluate the ability of a business to pay for its short-term liabilities. It displays the ratio of money generated and used by the core operations of a business.

Operating cash flow ratio = Cash flow from operations ÷ Net income

The current ratio, also known working capital ratio, is a liquidity ratio to determine the companies ability to pay off its current liabilities of current financial year by the assets hold by the company during that financial year.

1. Decrease in net income of the company.
2. Over withdrawal of capital by the owner.

If you like this article, kindly share it on different social media platforms. So that your friends and colleagues can also benefit from the same. Sharing is Caring.

Moreover, send us your queries or suggestions in the comment section below. We will be more than happy to assist you.