Cash Flow Statement in Excel: Choosing a Template and Preparation Guide
Learn how to prepare a cash flow statement in Excel: choose a template, collect actual payments, and check the classification, formulas, and closing cash balance.
If you need a cash flow statement in Excel, choose an adaptable template, compile actual receipts and payments, and compare the result with the cash balance on the balance sheet. The document shows movements in cash and cash equivalents during the reporting period by operating, investing, and financing activities, not just the bank account balance [riigiteataja.ee].
1. Cash Flow Statement in Excel: What Is It For and Where Can You Get a Template?
The statement helps explain where the company received money, where it spent it, and which sources of financing it used. Explaining the change in the cash balance is important, not just the positive or negative total: see which activity produced the result [RTJ 2].
The WordLayouts page “Monthly Cash Flow Statement Template” offers an Excel workbook with sample data and a blank worksheet. The page describes the download as free, but notes a personal-use licence and an attribution requirement; commercial use should be checked separately [wordlayouts.com].
The downloadable sample is not an official Estonian reporting form. Before entering company data, check both the usage rights and whether the report lines meet the applicable requirements.
Ready-made components include Excel export, CSV and Excel payment imports, and tabular report generators, but their existence does not prove that a ready-made or free report template is available. Connecting these components to a company's workflow is part of financial systems development.
2. Before You Start: Required Data and Time Needed
Compile the data for one and the same reporting period. With the direct method, every amount used must be traceable to an actual payment, so do not rely only on income statement lines.
- Opening and closing balance sheets for the period.
- The completed income statement for the same period.
- Statements from all bank accounts used.
- Cash transaction records and related accounting entries.
To estimate the time required, process one statement first: is the payment information clear, or do you need to find the related invoices? Set aside separate time for cleaning the data, checking the classification, and comparing the closing balance; there is no universal number of hours to rely on.
Is the statement mandatory and where can you check the requirements?
As part of a full annual report, this primary statement is mandatory. A micro or small company using the Estonian financial reporting standard may prepare an abbreviated report without it; check your size category under § 3 of the Accounting Act and the report contents under § 15 [riigiteataja.ee].
Check the current requirements in Riigi Teataja and the Ministry of Finance materials for RTJ 2 and RTJ 15. The ministry distinguishes guidance for periods beginning by the end of 2026 from versions for periods beginning on 1 January 2027: choose the guidance based on the reporting period, not the date the file was prepared [Accounting Standards Board].
Google search interest in Estonia has fallen by 24% over the last 12 months compared with the previous 12 months, and by 51% compared with November 2022 to October 2023. The peak was in February 2024; February, March, and May are more active than average, so it is worth preparing the template before the spring work period.
Direct or Indirect Method: Differences and Options
| Method | Preparing the operating activities section |
|---|---|
| Direct | Actual primary receipts and payments shown separately as gross amounts. |
| Indirect | Profit adjusted for non-cash transactions, changes in working capital, and investing and financing income and expenses [riigiteataja.ee]. |
The law allows either method for operating activities. This choice does not apply in the same way to the rest of the statement: cash flows from investing and financing activities are presented using the direct method [riigiteataja.ee].
Project briefs often request transaction-based direct-method Excel models, while ready-made IFRS reporting components may produce an indirect-method statement, so clarify the method before choosing a solution.
3. How to Prepare a Statement in Excel Step by Step
1. Start with a completed Excel sample
The WordLayouts workbook includes a “Cash Flow Statement” with sample data and a blank “Blank (Cash Flow Statement)”. Use the completed sheet to learn the calculation logic, not as proof of compliance with Estonian requirements [wordlayouts.com].
Compare the template lines with RTJ 2 requirements and check which method the workbook uses. Appendix 3 of the guidance contains a sample structure for an indirect-method statement, so it cannot be treated as a direct-method worksheet without modification [RTJ 2].
2. Collect receipts and payments from the bank and accounting records
Take the actual payments for the period from bank statements and cash records. Link the payment to its substance through the related invoice or accounting account: the bank description alone may not be sufficient for classification.
- Keep the payment date and amount.
- Add a reference to the related invoice or entry.
- Mark the activity group and report line.
- Flag transactions requiring review.
Do not replace missing payment data with sales revenue or purchase expenses. If the bank file format is unclear, check your bank's guidance on exporting statements in a format that can be processed in Excel.
3. Classify Cash Flows and Separate Non-Cash Transactions
| Activity group | Typical receipts and payments |
|---|---|
| Operating activities | Customer receipts, payments to suppliers, and wages. |
| Investing activities | Purchase and sale of fixed assets, loans granted, and their repayments. |
| Financing activities | Loans received and repaid, raising capital, and dividends [RTJ 2]. |
When classifying interest and income tax on dividends, check the alternatives permitted by RTJ 2. Do not decide based solely on the payment recipient, but consider the substance of the transaction and the applicable accounting policy [RTJ 2].
Do not include transactions without cash movement among receipts or payments. For example, converting debt into equity must not be treated as money received into the account; in an indirect-method worksheet, the non-cash effect must be separated from profit.
4. Cash Flow Statement Using the Direct Method: Compile Receipts and Payments
Show the main operating receipts and payments separately, not as one net amount. For example, distinguish the following lines on the worksheet:
- Sales payments actually received from customers.
- Actual payments made to suppliers.
- Payroll-related payments made to employees.
- Income tax actually paid during the period.
- Interest actually paid during the period [RTJ 2].
Add separate investing and financing sections. Sales revenue is not the same as sales receipts: use payment data here, not the sales figure from the income statement automatically.
5. Calculate Cash Flows Using Excel Formulas
Enter receipts with a plus sign and payments with a minus sign. In the sample layout, column B contains the activity group, column C the report line, and column D the signed amount.
To aggregate sales receipts, use the formula =SUMIFS(D2:D1000;B2:B1000;"Operating activities";C2:C1000;"Sales receipts"). SUMIFS adds values based on multiple criteria; change the relevant criterion for other lines [support.microsoft.com].
Calculate a subtotal for each activity group and add the group results together. The control relationship is closing balance = opening balance + total cash flows + effect of exchange rate changes; the exchange-rate effect must be shown separately from operating cash flows [RTJ 2].
6. Compare the Calculated Closing Cash Balance with the Balance Sheet
Create a separate check cell that subtracts the balance of cash and cash equivalents on the balance sheet from the calculated closing balance. Also compare the opening balance and ensure that all comparisons cover the same period and the same cash composition.
A zero control difference does not prove that the classification is correct. A payment assigned to the wrong activity line may still make the closing balance match while giving a false picture of the source of cash.
7. Prepare the Workbook for Reuse: Standardise Bank Data and Add Classification Rules and Checks
Project briefs for this type of work seek a reusable workflow: bank import, payment classification, and model updates using Power Query or macros. File structures from different banks must be standardised before feeding them into the same model. A more complex reporting workflow may require financial systems development.
Alongside statement preparation, clients also commission the following work.
- Linking balance sheet, income statement, and account reconciliation checks into the same workflow.
- Comparing sales revenue with sales and invoices, and distinguishing foreign exchange gains and losses.
- Tracking changes in owners' equity as part of the reporting process.
Acceptance checks compare the statement's cash with bank statements and verify that the balance sheet balances, rather than stopping at a final visual review. It is therefore worth making the same checks visible in the workbook immediately after import.
New unclassified transactions and changes in payment allocation create complexity, including reallocations of prepayments or claims. Leave such lines for separate review before approval.
A cash management workbook may also cover restricted cash, transfers between accounts, and the allocation of payments to owners or contributors. In that case, add checks for available cash and allocation rules.
4. Common Error: Why Does the Closing Balance Not Match the Balance Sheet?
Look for the difference in the source data before changing the summary formula. Check the following in order:
- Was the same bank payment entered more than once?
- Were transfers between your own accounts incorrectly left in operating cash flows?
- Was a non-cash transaction included in cash flows?
- Does the amount range include all rows, and are the amounts numbers rather than text?
- Was the effect of exchange rate changes accounted for separately [RTJ 2]?
Even if the closing balance matches, review the activity groups again. It is especially important to distinguish a loan received from a sales receipt and payment for a fixed asset from an ordinary supplier payment.
5. Frequently Asked Questions: The Difference Between a Statement and a Forecast, and Reporting Obligations
A statement describes cash movements that have already occurred; a forecast estimates future receipts and payments. Keep actual and expected amounts separate, even if you use a similar table structure for both.
In project practice, bank statements and accounting data are used to check actual cash movements, while forecast views may take expected sales revenue and anticipated transaction closing dates from the operational system. The output of a view combining actual and forecast data must therefore not be confused with a statement of recorded cash movements.
What consequences may follow from failing to submit a mandatory cash flow statement?
If the primary statement is mandatory, the annual report is not compliant without it. Check the e-Business Register guidance for how to correct the omission and, if necessary, submit a replacement report.
For breaches of reporting obligations, the registrar may impose fines and, subject to the conditions set by law, remove the company from the register. These are not automatic consequences of a missing Excel file: what matters is submitting a compliant annual report [ariregister.rik.ee].
Sources
Riigi Teataja: Accounting Act
Ministry of Finance: RTJ 2
WordLayouts: Monthly Cash Flow Statement Template
Ministry of Finance: Accounting Standards Board
Microsoft: Adding Values Based on Multiple Criteria
E-Business Register: Fines and Warnings
Business development team
Consultants and technologists helping businesses bridge the gap between strategy and implementation - turning digital transformation plans into operational reality.