Javatpoint Logo
Javatpoint Logo

Cash flow statement format in excel

Introduction

A financial report that summarises a company's cash inflows and outflows over a given time period is called a cash flow statement. Together with the income statement and balance sheet, it is an essential part of financial statements. The three primary components of the cash flow statement are the operating, investing, and financing operations.

  1. Operating Activities: Cash transactions pertaining to the main company activities are included in this part. These transactions include payments made to suppliers and workers as well as client receipts. It displays the capacity of the business to produce a positive cash flow from its core operations.
  2. Investing Activities: This section describes the financial exchanges that take place when long-term assets like real estate, machinery, and investments are bought or sold. It aids in evaluating how successfully a business is handling its investments and capital expenditures for future expansion.
  3. Financing Activities: The cash transactions associated with the capital structure of the organisation are outlined in this section. These transactions include the issuance and purchase of shares, borrowing, and debt repayment. It offers information on how a business is financing its growth and operations.

The cash flow statement is crucial for investors, analysts, and stakeholders to analyse a company's liquidity, financial health, and its capacity to satisfy short-term and long-term obligations. It provides a thorough understanding of a company's cash situation and its capacity to create and manage cash, which enhances other financial statements.

Operating activities:

Cash inflow

The cash generated by a company's main business operations is represented by the cash inflow from operational activities on the cash flow statement. It offers information on how successfully the business's fundamental operations-aside from cash flows from borrowing and investing-are producing cash. Transactions involving the selling of products or services and the corresponding operating expenses of the company are often included in operating activities.

Example:

To demonstrate how cash inflow from operational activities is computed, let's dissect a straightforward example:


Cash flow statement format in excel

Cash Outflow

Cash payments made by an organisation as a part of its regular business operations are shown as cash outflow from operational activities in the cash flow statement. It includes the monetary outlays required to maintain the business's daily operations. Determining the effectiveness and long-term viability of an organization's operational performance requires an understanding of cash outflows from operating operations.

Example:

To demonstrate how cash outflow from operational operations is computed, let's take the following straightforward example:

$30,000 in supplier payments

Employee Payments: $15,000.

Operating Costs: Ten Thousand

$5,000 in taxes paid

Additional Operating Charges: $2,000.

Sales in Cash: $50,000

Customer Collections: $20,000.

$5,000 in additional operating receipts

Cash flow statement format in excel

Investing activities:

Cash inflow:

The cash created by a firm via its investment operations during a given period is shown as cash inflow from investing activities in the cash flow statement. Long-term assets and investments, which are normally not a part of a business's daily operations but are crucial to its expansion, strategic goals, and financial stability, are acquired and disposed of as part of investing activities.

Example:

To demonstrate how cash inflow from investment activities is computed, let's take a straightforward example:

$15,000. Proceeds from the Sale of Real Estate, Machinery, and Equipment

$10,000 was received from the sale of investments.

$5,000 in additional investing receipts

$30,000 is the total cash inflows from investing activities (15,000 + 10,000 + 5,000).

The firm received $30,000 in cash from different investment operations throughout the reporting period, as indicated by the example's total cash inflow from investing activities.

Cash flow statement format in excel

Cash outflow:

A company's cash outflow for investing operations, or cash payments for long-term asset investments and other investment-related activities, is shown in the cash flow statement. These activities involve the purchase or sale of assets that have the potential to yield returns or increase in value over time, but they are not directly related to the day-to-day operations of the business.

Example:

To demonstrate how cash outflow from investment activity is computed, let's take the following straightforward example:

Acquisition of Plant, Machinery, and Supplies (Capex): $40,000.

$25,000 was spent on investments.

$5,000 in additional investing payments

The entire amount of money out of investing activities is $70,000 (40,000 + 25,000 + 5,000).

In this case, the company's whole cash outflow from investing operations is $70,000, which suggests that it was used to buy long-term investments and assets.

Cash flow statement format in excel

Sample format:

Step 1: Set Up the Excel Spreadsheet

Open Microsoft Excel and create a new blank workbook.

Cash flow statement format in excel

Label the columns in the first row for each section:

A1: Cash Flow Statement

A2: Operating Activities

A3: Net Income

A4: Adjustments for:

A5: Depreciation

A6: Changes in Working Capital

A7: Net Cash Provided by Operating Activities

A8: Investing Activities

A9: Purchase of Property, Plant, and Equipment

A10: Sale of Investments

A11: Additional Investing Activities

A12: Net Cash Used in Investing Activities

A13: Financing Activities

A14: Issuance of Common Stock

A15: Issuance of Debt

A16: Repurchase of Stock

A17: Repayment of Debt

A18: Dividends Paid

A19: Net Cash Used in Financing Activities

A20: Net Change in Cash

A21: Beginning Cash Balance

A22: Ending Cash Balance

Cash flow statement format in excel

Step 2: Input Data

Enter financial data in the respective cells under each section:

  • Input the Net Income, Depreciation, and Changes in Working Capital values under the "Operating Activities" section.
  • Input values for Purchase of Property, Plant, and Equipment, Sale of Investments, and Additional Investing Activities under the "Investing Activities" section.
  • Input values for Issuance of Common Stock, Issuance of Debt, Repurchase of Stock, Repayment of Debt, and Dividends Paid under the "Financing Activities" section.
Cash flow statement format in excel

Example of the format:

Step 1: Set Up the Excel Spreadsheet

Open Microsoft Excel and create a new blank workbook.

Label the columns in the first row for each section as mentioned in the previous response.

Cash flow statement format in excel

Step 2: Input Data

Enter the following example data in the respective cells:

Net Income: $50,000

Depreciation: $10,000

Changes in Working Capital: $5,000

Purchase of Property, Plant, and Equipment: $20,000

Sale of Investments: $15,000

Additional Investing Activities: $5,000

Issuance of Common Stock: $10,000

Issuance of Debt: $15,000

Repurchase of Stock: $5,000

Repayment of Debt: $7,000

Dividends Paid: $8,000

Beginning Cash Balance: $30,000

Cash flow statement format in excel

Step 3: Formulas

In cell B7, use the formula =B2+B3+B4 to calculate Net Cash Provided by Operating Activities.

In cell C7, use the formula =C2+C3+C4 to calculate Net Cash Used in Investing Activities.

In cell D7, use the formula =D2+D3+D4+D5+D6 to calculate Net Cash Used in Financing Activities.

Cash flow statement format in excel

In cell B9, use the formula =B7-C7-D7 to calculate Net Change in Cash.

In cell B10, use the formula =B8+B9 to calculate the Ending Cash Balance.

Cash flow statement format in excel

Step 4: Format the Spreadsheet

Format the cells:

Select the cells with financial data and format them as currency.

Use bold or different colors for headers and important figures to improve readability.

Cash flow statement format in excel

Step 5: Review and Adjust

Review the cash flow statement to ensure accuracy.

Adjust formulas or data as needed based on your specific business context.







Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA




news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news
news