Cash flow forecasting: from spreadsheet to continuous steering

In many SMEs and mid-sized companies, the cash flow forecast lives in a spreadsheet built several years ago, maintained by one person and refreshed when a deadline nears or when the bank asks a question. While activity is stable, this works. Once payment terms stretch, input costs move or higher rates make credit more expensive, the spreadsheet shows its limits: it describes a situation, but it does not help steer one.

This guide offers a six-step path from a one-off file to continuous steering. It is part of our approach to finance and performance: start from the decision to be made, deploy what is necessary, then track the results.

Why the spreadsheet eventually becomes a problem

The spreadsheet is not at fault in itself. Its weaknesses come from how it is used:

• It depends on one person. Assumptions, formulas and exceptions sit in a single head. One absence is enough to weaken visibility.
• It is fed by hand. Every update means exports, copy and paste, and checks. Updates get postponed, and the information ages.
• It does not measure its own error. Few files compare forecast with actual, so the model never learns.
• It mixes the certain and the estimated. An overdue supplier invoice and a hoped-for sale in two months sit in the same column.

The outcome is a document consulted under stress, precisely when it is least reliable.

What continuous steering changes

A continuous forecast is not necessarily a piece of software. It is a process with three features: a fixed update rhythm, largely automatic feeding from bank and accounting data, and systematic tracking of variances. The spreadsheet can even remain the reporting tool for a while, provided the process around it changes.

The guide in six steps

1. Name the decision to inform

A forecast exists to support decisions: renegotiating a credit line, committing to an investment, bringing forward or delaying a dividend, tightening collections. Write down the three or four concrete decisions your leadership must take this year. They set the required precision and the useful horizon. Without this exercise, one builds an exhaustive model that nobody uses.

2. Choose the horizon and the granularity

For most mid-sized companies, two views are enough. The first covers thirteen weeks at weekly granularity and serves day-to-day management. The second covers twelve months at monthly granularity and serves financing and investment decisions. Resist the urge to detail everything: a dozen well-defined lines of receipts and payments are worth more than two hundred approximate ones.

3. Start from bank actuals

The starting point is the bank balance, not the accounting result. The ledger says what was invoiced; the bank statement says what was paid. Where possible, connect the bank flows of all entities and currencies to one consolidation point. If customer or employee data travels in those flows, the revised Swiss Federal Act on Data Protection, in force since 1 September 2023, requires you to know who has access and why. Our data, AI and compliance expertise covers this ground.

4. Separate the certain from the estimated

Classify each flow by degree of certainty:

• Committed: overdue or scheduled invoices, payroll, rent, VAT, debt service.
• Probable: receipts on issued invoices, using observed payment delays rather than contractual ones.
• Estimated: future sales, unsigned investments, seasonal effects.

This split makes the forecast readable to a banker and to a board member alike, and it shows where to concentrate the effort to improve reliability.

5. Set an update rhythm

Fix a thirty-minute weekly meeting with finance, sales and purchasing. The agenda is short: the week's variances, three changes to assumptions, decisions to take. Rhythm matters more than sophistication. A simple model updated every Monday is more useful than a complete one updated every quarter.

6. Measure the variance and correct

Each week, compare forecast with actual by major line. If receipts are consistently 8 to 10 percent below forecast, the issue is not bad luck: it is an overly optimistic payment-delay assumption. The variance becomes the quality indicator of the forecast, and it belongs naturally in an executive dashboard that actually supports decisions.

A worked example

Take a fictional Swiss industrial company with these assumptions: CHF 40 million annual revenue, contractual payment terms of 60 days, a CHF 2 million credit line, a thirteenth-month salary paid in December and VAT settled quarterly.

The spreadsheet assumes collection in 60 days. Observing actual payments shows 72 days on average. With daily revenue of roughly CHF 110,000, those twelve days of difference represent in the order of CHF 1.3 million of cash that is not there when expected. Stacked on top of the thirteenth-month salary and the year-end VAT payment, this can mean drawing more than half of the credit line over a few weeks, while the spreadsheet showed a comfortable margin.

Nothing dramatic here: replacing a contractual assumption with an observed one is enough. That is exactly what continuous steering does, systematically. We describe an implementation of this kind in our use case on continuous cash flow forecasting.

Where automation and AI help, and where they do not

Automation is useful for retrieving bank flows, matching payments to invoices and computing the delays actually observed. Statistical or machine learning models can refine payment-delay forecasts customer by customer, when the history is long enough. They do not replace judgement on a large contract, a supplier renegotiation or a change in commercial policy. And on a short or irregular history, a well-chosen average is often more robust than a complex model. If you use AI tools, keep in mind the timetable of the EU AI Act, in force since 1 August 2024, whose obligations apply in phases through 2026. US readers will recognise the same logic in their treasury practice: the discipline of the process matters more than the model.

Mistakes we see most often

• Wanting a tool before defining the process and the responsibilities.
• Forecasting along too many axes at once: entities, currencies, projects, products.
• Not naming a deputy for the person who maintains the model.
• Presenting a single figure rather than a central scenario and two sensitivity scenarios.
• Forgetting that sales and purchasing hold the information on future receipts and payments.

Where to start

A realistic path takes four to eight weeks: framing the decisions, building the thirteen-week view on bank data, running the first weekly cycles, then extending to twelve months. The associated dashboard can follow, as in an automated executive dashboard. If you want to assess the maturity of your current forecast and define a starting point proportionate to your size, our diagnostic is designed for that.