Useful journalism to understand, manage and grow a business.
SearchA useful spreadsheet shows how much cash is available, what receipts are expected, which payments are due, and when each movement will occur. This guide explains how to organize it, calculate projected balances, and review variances.
An SME can make a profit and still struggle to pay on time if money comes in after its obligations fall due. A cash flow forecast helps anticipate this gap: it does not measure only how much is invoiced, but how much cash is expected to be available and on what date.
The worksheet does not need to be complex to be useful. It should make it possible to track the balance, organize receipts and payments by date, project cash, and update estimates when circumstances change. It is a planning tool, not a guarantee that movements will occur as forecast.
Record the opening balance at the start of the period, using as a reference the cash and bank balances that the business considers available to meet payments. Specify the cut-off date and check that the figure matches the banking and accounting information used by the business.
If there are funds that are not available for ordinary use, it is advisable to identify them separately so they are not confused with operating cash. Apply the same criteria in each update: changes in how the balance is counted can make comparisons between periods less clear.
For each forecast movement, include at least an estimated date, a description, a category, and the amount. Where possible, add the customer or supplier and a reference that makes it possible to locate the invoice, contract, or source document.
For receipts, you can distinguish between outstanding customer invoices, sales expected to be collected during the period, and other relevant cash income. Do not treat an invoice as paid simply because it has been issued: record the likely date the money will come in, taking into account the due date and the information available about the customer.
For payments, include committed obligations and recurring expenses that affect cash. Depending on the business’s activity, these may include suppliers, payroll, rent, financing instalments, taxes, and planned purchases or investments. Show when the money is expected to go out, not just when the expense is recorded in the accounts.
A basic structure might be:
| Period | Opening balance | Forecast receipts | Forecast payments | Projected closing balance | Actual balance and notes |
|---|---|---|---|---|---|
| Week or month | Amount | Amount | Amount | Amount | Amount or note |
For each period, the calculation is straightforward: projected closing balance = opening balance + forecast receipts − forecast payments. The closing balance for one period becomes the opening balance for the next. If the spreadsheet records individual movements, add up the receipts and payments that fall within each interval.
The frequency should provide enough detail to see movements and take action. A monthly forecast may be suitable for broader planning, but grouping all movements for the month can conceal a week in which several due dates coincide. If the SME needs to monitor upcoming commitments, it can opt for a weekly view; when greater detail is required, working by day may be useful.
The time horizon also matters. A short-term forecast can be based on identified invoices, due dates, and commitments. As the period extends, it usually becomes more dependent on estimates of future sales, expenses, and transactions. For this reason, it is advisable to make clear which data are confirmed and which are assumptions.
One option is to maintain a rolling forecast: when a period closes, actual figures are incorporated and a new period is added to the end of the horizon. This keeps a current planning window in place, rather than relying on a spreadsheet that becomes outdated after it is prepared.
The spreadsheet is more useful if it makes it possible to distinguish movements supported by specific information from forecasts subject to uncertainty. An invoice with a known due date is not as certain as a sale that has not yet been closed. You can add a status column—for example, “confirmed,” “probable,” or “estimated”—and note the source or assumption applied.
For recurring payments or amounts that vary, document how the amount was estimated. For taxes and other obligations, record the disbursements the business expects to make on the relevant dates, using the information available to the business. This spreadsheet is used to monitor cash; it does not replace accounting or, by itself, determine the applicable tax or contractual obligations.
When there is significant uncertainty, prepare alternative scenarios by changing identifiable assumptions: for example, delays in certain receipts, variations in sales, or payments that could be brought forward. You can keep a central forecast and compare it with a more favorable alternative and a more cautious one, always explaining the conditions underlying each.
The aim is not to present these scenarios as certain outcomes, but to see how cash would change if the assumptions were met. If a scenario shows a tight or negative balance in a period, the spreadsheet highlights a need that warrants monitoring; it does not, by itself, confirm that a shortfall will occur or that a specific solution exists.
At each review, replace the movements for the elapsed period with actual amounts and compare what happened with what was forecast. Note significant differences and their cause, when known: a customer paid later, a supplier’s date changed, or an unexpected disbursement arose. Avoid changing previous figures without keeping a record, as this would make it impossible to learn from estimation errors.
The review makes it possible to adjust dates and amounts still outstanding, add new commitments, and check whether the assumptions remain reasonable. If the forecast depends on scattered data, assign responsibility for collecting the information and set out when the spreadsheet will be updated. The appropriate frequency will depend on the business’s activity, the volatility of its cash flows, and the decisions that need to be made.
A clear cash flow spreadsheet answers three questions: how much cash there is at the start, what inflows and outflows are expected, and what balance might remain in each period. Its value depends on the quality and currency of that data. Collection dates may change, unexpected payments may arise, and estimates become less reliable the further into the future they extend.
For this reason, it is advisable to use the forecast to help identify liquidity needs in advance and organize financial information. It does not guarantee results or replace the accounting, tax, or financial analysis that each business decision in Spain may require.
A practical guide for exporting SMEs to research tariffs, labeling, and certifications by country and product, and prepare documentation before shipping goods.
Read story
Clear definitions of roles, workflows, and metrics to improve operational coordination and internal communication in small and medium-sized enterprises.
Read story
Standardizing technical and operational language to optimize communication between management, technical and driving teams in the transport sector.
Read story