WHO HAS A FILE FOR EXCEL TO AUTOMATICALLY CALCULATE CASH FLOW STATEMENTS

Cash flow statement.

Prepared by: 2003.

Item Line Amount Item.

1. Cash flow from operating activities: 4. Impact of exchange rate changes on cash.

Cash received from the sale of goods and provision of services 1 0 V. Net increase in cash and cash equivalents.

Tax refunds received 3

Other cash received in connection with operating activities 8 Supplementary information.

Subtotal cash inflows 9 0 1, Adjust net profit to cash flow from operating activities:

Cash for the purchase of goods and payment for services 10 Net profit.

Cash paid to and on behalf of employees 12 166240 Plus: Provision for impairment of assets.

Taxes and fees paid 13 Depreciation of fixed assets.

Other cash paid in connection with operating activities 18 Amortization of intangible assets.

Cash outflows, subtotalled 20 long-term amortization expenses.

Net cash flow from operating activities 21 Decrease (minus: increase) of expenses to be amortized

2. Cash flow from investing activities: Increase (minus: decrease) in provision expenses

Cash received from the recovery of investments 22 Losses on disposal of fixed assets, intangible assets and other long-term assets (minus: gains).

Cash received from investment gains 23 Loss on retirement of fixed assets.

Net cash recovered from disposal of fixed assets, intangible assets and other long-term assets 25 Financial expenses.

Other cash received in connection with investing activities 28 Investment losses (minus: gains).

Cash flows into subtotal 29 deferred tax credits (minus: debits).

Acquisitions Cash paid for fixed assets, intangible assets and other long-term assets 30 Decrease (minus: increase) in inventories

Cash paid for investments 31 Decrease (minus: increase) in operating receivables

Other cash paid in connection with investing activities 35 Increase (minus: decrease) in operating payables

Cash outflow subtotal 36 Other.

Net cash flows from investing activities 37 Net cash flows from operating activities.

3. Cash flow from financing activities: 2. Investment activities and financing activities that do not involve cash receipts and expenditures.

Cash received from investment absorption 38 2,500,000 Debt capitalized.

Cash received from borrowings 40 convertible corporate bonds maturing within one year.

Other cash received in connection with financing activities 43 Financing leased fixed assets.

Subtotal cash inflows 44 2500000 3, net increase in cash and cash equivalents.

Cash paid to repay debts 45 Closing balance of cash.

Cash paid for dividends, profits and interest payments 46 minus: Opening balance of cash.

Other cash disbursed in connection with financing activities 52 plus: closing balance of cash equivalents.

Subtotal cash outflows 53 Less: initial balance of cash equivalents.

Net cash flow from financing activities 54 Net increase in cash and cash equivalents.

I have, you can email me and ask for it.

I'll send you your email address.

The formula for the cash flow statement calculation formula:

Cash received from the sale of goods and the provision of labor services = income from the main business of the income statement (1+17%) + income from other operations of the income statement + (the opening balance of notes receivable - the closing balance of notes receivable) + (the opening balance of accounts receivable - the closing balance of accounts receivable) + (the closing balance of accounts receivable - the opening balance of accounts receivable) - the closing balance of bad debt provision for accounts receivable.

Determine the net cash flow of investing activities in the main table.

1.Cash received from the investment is recovered.

Short-term investment at the beginning of the period - at the end of the short-term investment period) + (at the beginning of the long-term equity investment period - at the end of the long-term equity investment period) + (at the beginning of the long-term debt investment period - at the end of the long-term debt investment period).

In this formula, if the opening number is less than the closing number, the cash item paid for the investment is included.

2.Cash received from investment income.

Income statement investment income - (interest receivable at the end of the period - interest receivable at the beginning of the period) - (share receivable at the end of the period - dividend receivable at the beginning of the period).

3.Net cash received from disposal of fixed assets, intangible assets and other long-term assets.

The credit balance of the fixed assets clearance bridge + (the end of the period of intangible assets - the beginning of the period of intangible assets) + (the end of the period of other long-term assets - the beginning of other long-term assets).

4.Other cash received in connection with investing activities.

Such as recovering the principal of financial leasing equipment.

Net increase in cash = cash flow from operating activities + cash flow from financing activities + cash flow from investing activities.

Net cash flow = main business income in the income statement (1 + tax rate) + other business income in the income statement + (opening balance of notes receivable - closing balance of notes receivable) + (opening balance of accounts receivable - closing balance of accounts receivable) + (closing balance of accounts receivable - opening balance of accounts receivable) - closing balance of bad debt provision for accounts receivable.

Direct method: The direct method can be used to prepare the cash flow statement using the working paper method or the T-shaped account method, or it can be filled in according to the analysis of the relevant account records.

Indirect method: the net profit in the income statement of the reporting period is adjusted to the cash flow generated by the activities of Huaiqin through lead flushing; analysis and adjustment of major judgment investment and financing activities that do not involve cash receipts and expenditures; analysis of net changes in adjusted cash and cash equivalents; Preparation of formal supplementary information to the cash flow statement.

Factors influencing cash flow:

1. The increase or decrease of cash items will not affect the change of net cash flow. For example, withdrawing cash from a bank, depositing cash in a bank, and using cash to purchase bonds maturing in two months are all internal fund conversions between cash items and will not increase or decrease cash flow.

2. Changes in the increase or decrease between non-cash items will not affect the change in net cash flow. For example, the use of fixed assets to pay off debts, the use of raw materials to pay off foreign investment, the use of inventory to pay off debts, the use of fixed assets to invest abroad, etc., are all non-cash items of the increase or decrease between the changes, do not involve cash receipts and expenditures, will not increase or decrease cash flow.

3. Changes in the increase or decrease between cash items and non-cash items will affect the changes in net cash flow. For example, the use of cash to pay for the purchase of raw materials, the use of cash for foreign investment, the recovery of long-term bonds, etc., all involve the increase or decrease between cash items and non-cash items, and these changes will cause cash inflows or cash expenditures.

Supplementary Information.

1. Adjust net profit to cash flow from operating activities.

Net profit plus: provision for impairment of assets.

Depreciation of fixed assets.

Amortization of intangible assets.

Amortization of long-term amortized expenses.

Decrease (minus: increase) in expenses to be amortized

Increase (minus: decrease) in provision for expenses

Losses on disposal of fixed assets, intangible assets and other long-term assets (minus: gains) Loss on the retirement of fixed assets.

Finance Expenses. Investment losses (minus: gains).

Deferred tax credits (minus: debits).

Decrease (minus: increase) in inventories

Decrease (minus: increase) in operating receivables

Increase in operating receivables (plus: decrease).

Net cash flow from operating activities.

2. Investment and financing activities that do not involve cash receipts and expenditures:

Debt is capitalized.

Convertible corporate bonds maturing within one year.

Financing lease of fixed assets.

Net increase in cash and cash equivalents:

The closing balance of cash.

Less: The opening balance of cash.

Add: The closing balance of cash equivalents.

Less: Opening balance of cash equivalents.

Net increase in cash and cash equivalents.

When using financial software to prepare a cash flow statement, it is difficult for the financial software to fully identify its classification because the accounting matters are more complex, while it is relatively simple to compile the cash flow statement on the basis of manual identification and classification. This paper takes the cash flow generated by operating activities as an example to introduce a method of compiling a cash flow statement using Excel. 1. Classification of cash flow content For the cash flow part of operating activities, we can set the classification according to its project content** as shown in Table 1 below

2. Data collection: UFIDA, Kingdee and other financial software have data export functions, and the accounting entries of this month are exported and saved into an excel format file, and the file is named "accounting entries". Create a new excel file named "Cash Flow", open the file and create four data tables, named "Original Table", "Calculation Table", "Summary Table" and "Comparison Table". Open the file "Accounting Entries", and copy the original data of the exported accounting entries to the "Original Table" of the file "Cash Flow" by copying and pasting, see Table 2

The data items exported by different software may be different, but the items listed in Table 2 are the basic content of accounting entries. In general, the standard output items of the default software can be used to meet the needs, and there is no need to set the items of the output file separately. 3. Classification IdentificationClassification identification is to manually classify accounting entries according to the content of cash flow items in Table 1 "Classification ** Table" and their corresponding classification**.

Step 1: Adjust the accounting entriesFirst, copy all the data of the "original table" in the "cash flow" of the file to the "calculation table" of table 3, and adjust the accounting entries accordingly: the one loan and one credit entry involving the monetary fund account is not processed, and is directly added according to the classification**; ** is added after adjusting the processing or analysis of the multi-loan and multi-credit entries, which is an example as follows:

Step 2: Add Classification**In the "Calculation Table" of the file "Cash Flow", change the "Period" in the project column to "Classification**", change the "Voucher Number" to "Chinese Name", and add the cash flow classification according to the current accounting entries one by one**. Step 3:

Add FormulaThe purpose of adding a formula is to convert ** into kanji. The content of the "Chinese name" column in the calculation table is automatically replaced by the excel function vlookup according to the input **. The contents of the cash flow items generated by operating activities in the "Classification Table" are entered into the "Comparison Table" of the document "Cash Flow", and the corresponding area of the data is assumed to be A1:

c8, enter the formula in the "Chinese name" column of the calculation table: formula = vlookup($b$2:$b$680, **Comparison table!

b$2：$c$8，2，0）。In the formula, "$b $2:

b$680" is the input field of the classification ** in the "Calculation Table", that is, the field of all accounting entries, which can be entered according to the data of the most boring month of the accounting entry, or entered before the scrambled.