Excel template statement of cash flows

Excel template statement of cash flows

This article provides details of Excel template statement of cash flows that you can download now.

A company’s cash flows can have 3 origins. They are linked either to current activity, to investment operations, or to financing operations.

The flows linked to the activity include operating cash flow (purchases and sales), but also financial expenses and income, exceptional expenses and income, income tax and employee participation if these elements correspond to cash flows and if they are linked to the activity of the company.

Investment flows correspond to investment operations, i.e. acquisitions of fixed assets, but also to disinvestments, i.e. disposals of fixed assets.

Microsoft Excel software under a Windows environment is required to use this template

These Excel template statement of cash flows work on all versions of Excel since 2007.

Examples of a ready-to-use spreadsheet: Download this table in Excel (.xls) format, and complete it with your specific information.

To be able to use these models correctly, you must first activate the macros at startup.

The file to download presents four Excel template statement of cash flows

Analyze or enhance your company’s cash flows for the past twelve months with these accessible models. Sparklines, conditional formatting, and attractive design make these models both useful and beautiful.

STATEMENT OF CASH FLOW

Statement of cash flow

  • also referred as Cash Flow Statements
  • act as a bridge between the income statementand balance sheet by showing how money or the cash and cash equivalents moved in and out of the business.  
  • Is a component of financial statements summarizing the operating, investing and financing activities of an entity.

Cash Flow Statement is Utilized;

  1. allows investors to understand how a company’s operations are running, where its money is coming from, and how money is being spent
  2. Creditors, on the other hand, can use the CFS to determine how much cash is available (referred to as liquidity) for the company to fund its operating expenses and pay its debts. 

Purpose of Statement of cash flows

  1. The primary purpose of the statement of cash flows is to provide relevant information about cash receipts and cash payments of an entity during a period.
  2. Cash flow information is useful in assessing the ability of the entity to generate cash and cash equivalents.
  3. The statement of cash flows also enhances the comparability of operating performance by different entities.
  4. Users of an entity’s financial statements are interested in how the entity generates and uses cash and cash equivalents.
  5. Entities need cash to conduct their operations, to pay their obligations and to provide returns to their investors.

Cash and Cash Equivalents

            The statement of cash flows is designed to provide information about the change in an entity’s cash and cash equivalents.

Cash comprises cash on hand and demand deposits.

Cash equivalents are short term highly liquid investments that are readily convertible to known amount of cash and which are subject to insignificant risk of change in value.

PAS 7, paragraph 7, provides that an investment normally qualifies as a cash and cash equivalent only when it has a short maturity of three months or less from the date of

acquisition. In other words, the investment must be acquired three months of less before the date of maturity.

Examples of Cash Equivalents

  1. Three-month BSP treasury bill
  2. Three-year BSP Treasury bill purchases three months before date of maturity
  3. Three-month time deposit
  4. Three-month money market instrument or commercial paper.

CLASSIFICATION OF CASH FLOWS

Cash flows are inflows and outflows of cash and cash equivalents.  

The statement of cash flow shall report cash flows during the period classified as operating, investing and financing activities.

Classification by activity provides information that allows users to assess the impact of those activities on financial position of the entity and the amount of its cash and cash equivalent.

The cash flow statement is partitioned into three segments, namely:

  1. Cash flow resulting from operating activities
  2. Cash flow resulting from investing activities
  3. Cash flow resulting from financing activities
  4. A fourth category, disclosure of noncash activities, is sometimesincluded when prepared under the generally accepted accounting principles, or GAAP.

Direct and Indirect Method of Preparation

Cash flow is calculated by making certain adjustments to net income by adding or subtracting differences in revenue, expenses and credit transactions (appearing on the balance sheet and income statement) resulting from transactions that occur from one period to the next. These adjustments are made because non-cash items are calculated into net income (income statement) and total assets and liabilities (balance sheet). So, because not all transactions involve actual cash items, many items have to be re-evaluated when calculating cash flow from operations.

As a result, there are two methods of calculating cash flow: The direct method and the indirect method.

Direct Method

  • Shows in detail or itemizes the major classes of gross cash receipts and gross cash payments.
  • The cash receipts are listed one by one, the cash payments are listed one by one, and the difference represents the net cash flow from the operating activities.
  • It is “cash basis” income statement.

Formulas may be necessary for determining for determining cash receipts and cash payments:

Computation of collections
Trade accounts and notes receivable-beginningXX
Add: Sales(accrual basis)XX
TotalXX
Less: Trade accounts and notes receivable-endXX
Collections of accounts and notes receivableXX
Computation of payments to merchandise creditors
Trade accounts and notes payable – beginningXX
Add: Purchases (accrual basis)XX
TotalXX
Less: Trade accounts and notes payable – endXX
Payment to merchandise creditorsXX
Computation of payments for expenses
Expenses (accrual)XX
Add: Prepaid expense – endXX
Accrued expense – beginningXX
TotalXX
Less:Prepaid expense – beginningXX
Accrued expense – endXXXX
Expenses paidXX

ILLUSTRATION-OPERATING ACTIVITIES

Luhan Company reported the following comparative statement of financial position and income statement for 20×6.

ASSETS20×620×5
Cash       3,000,000       2,000,000
Accounts Receivable          940,000          350,000
Inventory          175,000          100,000
Prepaid Insurance            15,000            20,000
Property, Plant, and Equipment       2,000,000       2,000,000
Accumulated Depreciation(550000)(500000)
Patent            40,000            50,000
Total Assets       5,620,000       4,020,000
LIABLITIES AND EQUITY
Accounts payable          170,000          150,000
Accrued Salaries Payable            25,000            10,000
Accrued Interest Payable            10,000            15,000
Income Tax Payable          350,000          250,000
Unearned Rent Income            10,000            40,000
Mortgage Payable       5,000,000          500,000
Share Capital       2,000,000       2,000,000
Retained Earnings       2,555,000       1,055,000
Total Liabilities and Equity       5,620,000       4,020,000
  • cash flows from operating activities are reported by adjusting net income for revenues, expenses, gains, and losses that appear on the income statement but do not have an effect on cash.
  • It means that the net income/loss is adjusted for the effects of transactions of anon-cash nature, any deferrals or accruals of past or future operating cash receipts and payments, and items of income or expense associated with investing and financing activities.

The following general guidelines are offered for the adjustments of net income to cash basis:

  1. All increases in trade noncash current assets are deducted from net income.
  2. All decreases in trade noncash current assets are added to net income.
  3. All increases in trade current liabilities are added to net income.
  4. All decreases in trade current liabilities are deducted from net income.
  5. Depreciation, amortization and other noncash expenses are added back to net income to eliminate the effect they had on net income.
  6. Any gain on disposal of property or gain on early retirement of nontrade liabilities is included in net income but it is a nonoperating item.Thus, this is deducted from net income.
  7. Any loss on disposal of property of property or loss on early retirement of nontrade liabilities is deducted from net income but this is a nonoperating item.

Thus, this is added back no net income.

Tags