Cash flow template in Excel: money in and out of the month
Cash flow template in Excel: money in and out of the month
Some businesses sell well and still reach the end of the month without the money to pay payroll. The problem is almost never profit; it is cash. You issued the invoice, but the customer pays in thirty days, while suppliers and utilities have to be covered today. This cash flow template in Excel lays out the month movement by movement and shows, after each one, how much money is actually left in the drawer.
The file comes ready to record the month's money in and money out with an automatic running balance: you write the movement once and the balance column updates by itself, row by row.
⬇ Download the template (Excel .xlsx)What this template is and who it is for
For a small or medium business, cash is the air it breathes: without it, the operation does not last a week. Even so, most small companies only look at the bank balance when a payment is already due, and they keep no orderly record of how the balance got there. This template is for the owner, the manager or the bookkeeping assistant who needs to see the whole month on a single sheet: how much came in, how much went out, in what order, and what balance was left after each movement.
The axis here is cash and the running balance. Every line answers one question only: after this movement, how much money is there? It does not matter whether the movement is a collection, a payment, a deposit or a withdrawal; what matters is that it moves the business cash and that it is recorded with its date and its supporting document. That is why the template is ordered by date and not by category: the column that rules is the balance, because it is the one that says whether the business is breathing or drowning.
It is worth separating it from the income and expense tracker. That format classifies: it answers what it was (the category) and how it was paid (the payment method), and its goal is to understand where the money goes and where the income comes from. This template follows cash: it shows the sequence of money day by day so you can decide whether there will be enough for the next obligation. Both are useful, but they do not answer the same question and should not be used as if they were the same thing.
What the template includes
The file arrives ready to work with, with the formulas and the lists already set up. This is what it brings:
| Component | What it does |
|---|---|
| Movement rows | 200 rows to record the month's money in and money out with date, concept, category and document. |
| Editable opening balance | A cell in the header where you type the money available at the start of the period; the whole running balance recalculates from there. |
| Automatic running balance | Each row takes the previous balance, adds the money in and subtracts the money out, and leaves the new balance ready. |
| Automatic in and out | The amount is split by itself between the two columns according to the type of the movement, so you never keep two separate lists. |
| Period totals | Total money in, total money out and closing balance, calculated instantly. |
| Summary | A small block with total money in, total money out, opening balance and closing balance for the period. |
| Mini tables by category | Two tables: money in by category and money out by category, so you can see where the cash comes from and where it goes. |
| Drop-down lists | Type (In or Out) and category, so entries are always written the same way and can be added up without errors. |
The columns of the sheet
The sheet reads from left to right and ends in the column that matters most, the balance. These are the ten columns of the record:
| Column | What it is for |
|---|---|
| Date | The day the cash actually moved, not the day the invoice was issued. |
| Concept | A short name for the movement: collection of receivables, supplier payment, cash sales. |
| Type (In/Out) | Marks whether the money comes in or goes out; the automatic split between the two columns depends on this. |
| Category | The group of the movement (sales, receivables, suppliers, payroll, utilities) for the summary by heading. |
| Amount | The value of the movement, always positive and with no currency symbol. |
| Document | The number of the receipt, the invoice, the voucher or the support behind the movement. |
| In (automatic) | Repeats the amount only when the type is In; in every other case it stays at zero. |
| Out (automatic) | Repeats the amount only when the type is Out; in every other case it stays at zero. |
| Balance (automatic) | Previous balance plus money in minus money out: the number that shows what is left in the drawer. |
| Notes | Short remarks: whether a payment was left pending, whether a document is missing or whether it was paid in two parts. |
How the running balance is calculated
The running balance is a chained sum. The first row starts from the opening balance typed in the header; the second row starts from the balance of the first, and so on. If the type in a row is In, the amount is added; if it is Out, the amount is subtracted. The result stays visible on every line, so you can see at what point in the month the cash hit its lowest level, which is exactly the piece of information an income statement never shows.
| Day | Movement | In | Out | Balance |
|---|---|---|---|---|
| Start | Opening balance | — | — | 2,000,000 |
| 2 | Collection of receivables | 1,500,000 | — | 3,500,000 |
| 3 | Payment to suppliers | — | 1,100,000 | 2,400,000 |
| 5 | Cash sales | 900,000 | — | 3,300,000 |
| 8 | Payroll | — | 800,000 | 2,500,000 |
In the example the month starts with 2,000,000 available. On day 2 a collection of receivables for 1,500,000 comes in and the balance rises to 3,500,000. On day 3 a supplier payment of 1,100,000 goes out and the balance falls to 2,400,000. On day 5 900,000 comes in from cash sales and the balance climbs back to 3,300,000. On day 8 payroll of 800,000 is paid and the balance settles at 2,500,000. None of those movements is hard to record; what is valuable is the last column, which tells you at a glance how the cash breathes through the whole month and not only at the end.
Here the most important difference of the format appears. Cash flow follows cash, not profit, and the two rarely coincide. A credit sale increases income and improves the month's profit, but it puts not a single unit of cash in the drawer until the customer pays. A payment to a supplier for goods that have not been sold yet takes cash out today and does not reduce that month's profit. That is why a business can show profit on paper and, at the same time, have nothing to pay payroll with: profit is measured with accrual rules, cash is measured with what truly came in and went out. The cash flow template exists precisely to watch that second reality, the only one a supplier or a bank accepts without argument.
Step by step to use it
- Download the file, open it in Excel and check the header row to confirm the columns are in the expected order.
- Type the opening balance in the header cell: the money in the drawer and in the bank on the first day of the month.
- Record every movement on the same day it happens, with its real date and not the date of the invoice.
- Choose the type from the drop-down list (In or Out) and assign the category the movement belongs to.
- Write the number of the document that supports the movement: receipt, payment voucher or bank transfer.
- When the month closes, compare the closing balance on the sheet with the bank statement and the cash count; if a difference appears, track it down before the next month starts.
Tips and common mistakes
- Do not mix the opening balance with a movement. It is a separate cell; if you record it as money in, the month's balance is inflated from day one.
- Always use the same categories. If one day you write suppliers and the next day supplier, the summary by heading splits the figure in two and stops being useful.
- Do not write credit sales as money in. Until they are collected they are receivables, not cash; they come in when the customer actually pays.
- Record the real day of the movement. If everything is entered on the last day of the month, the running balance loses its value, which is to show the moments of strain.
- Do not leave movements without a document. A balance you cannot explain with a support is a balance that sooner or later will not reconcile.
- Check the lowest balance of the month, not only the final one. A month that closes comfortably may have been on the edge of an overdraft in the second week.
When to move to software
The template does its job well while the business has a single cash point and a manageable volume of movements. The problem appears when several people start recording, when there is more than one drawer or several bank accounts, or when the number of movements grows so much that keeping the balance up to date becomes a full-time task. At that point the risk is no longer arithmetic: it is that each person writes in their own way and the balance on the sheet stops resembling the bank's. That is where Kardex Tauro helps, because when collections, payments and purchases are recorded in the same system that moves inventory and receivables, cash stops being rebuilt by hand and starts being seen in real time.
The change is not about replacing judgement, but about automating the repetitive part. Software does not decide what gets paid first or who is chased for payment today; that is still the owner's call. What it does is prevent two people from recording the same movement, a payment from being left without a document, or the balance on the sheet from falling behind reality. The template teaches the logic of the running balance and the discipline of recording daily; once that discipline is in place, moving to management software is the natural next step and not a leap into the void.
With cash flow, the business stops guessing whether the month will hold: it sees it written, movement by movement, and decides with the number in hand.
⬇ Download the template (Excel .xlsx)
