Cash flow forecasting in Excel: where the spreadsheet ends

Excel has nothing to do with it

Excel remains one of the most universal tools for financial calculation. You can build a budget in it, work out cash flow, tie revenue to costs and forecast several periods ahead.

For a simple model that is quite enough.

The problem does not show up when the sheet has many rows. It shows up when it has many links between future events.

Payroll depends on headcount. A loan creates regular payments. A client pays a set number of days after the sale. A new cost starts on a particular date. Inflation changes the size of future amounts.

Now one change has to show up in several places.

This is where a financial model stops being a set of calculations and turns into a system of connected assumptions. The usual guidance on financial modelling assumes the same thing: changing an input should carry through the forecast on its own.

While there are few questions, Excel is convenient

Picture someone with a salary, rent, a few regular costs and a deposit.

They want to know how much will have accumulated by the end of the year.

Excel handles that without trouble.

Put the months in a column, add the receipts and the costs, drag the formulas down and you have a balance for every month.

If the salary changes, one cell changes. If the rent changes, another one does.

A model like that is simple and transparent.

On top of that, Excel needs no special financial service. The spreadsheet is already familiar, bends to the task easily and can carry almost any simple calculation.

So the question of whether a financial model can be built in Excel has an obvious answer:

yes.

The more interesting question is:

what happens when you need to test not one forecast but several versions of the future?

First problem: one change drags others along

Say an owner wants to hire three people.

The change itself is simple: payroll goes up.

But if the model is meant to show the real picture, that is not the end of it.

Extra taxes and other related costs appear. Revenue may change. More working capital may be needed. A few months later the question is whether there is enough money for the next payment.

Now changing one input affects several parts of the model.

In a small sheet that can be done by hand.

In a large one you have to keep track of every dependency. Otherwise the sheet goes on calculating, but calculating a different scenario from the one in your head.

That is why financial models are usually built as a system of connected assumptions: a change to the inputs has to travel through everything downstream.

Second problem: the future does not fit in one row

Picture a different scenario.

In three months the current lease runs out. In five months a large payment falls due. In six months the owner plans to open another location. And a client is due to transfer a large sum only in 45 days.

Knowing the month's income and expenses is no longer enough.

You need to know at which exact moment each movement of money happens.

It is the order of events that can create a cash gap even when the monthly total looks acceptable.

That is what separates a cash flow forecast from a plain budget: it answers not only how much money a period produces but when exactly that money arrives and when it leaves.

Excel can do this too. But the more detailed the timeline gets, the more rows, dates and formulas have to be maintained by hand.

Third problem: what if?

The telling moment comes when a second future appears.

The base version is already calculated.

Now you want to know:

what happens if you take a loan?

Then:

and what if you take it but overpay every six months?

Then:

and what if that money goes into the business instead?

The arithmetic here is easy.

The difficulty is elsewhere: every version has to stay connected to all the other calculations.

Excel offers several ways to organise scenarios: separate sheets, sets of input parameters, formulas, switches and the built-in what-if analysis tools. Excel supports that kind of scenario work itself.

But with many versions the manual work appears: you have to watch the inputs, the dependencies and the results of every scenario.

So several scenarios on their own do not make a model easier to work with.

The real question is how easily one condition can be changed and everything that follows from it seen.

The worst part of a model is not in the formulas

A complicated financial spreadsheet rarely breaks dramatically.

What is worse is when it keeps working and one change landed in the wrong place.

You changed the loan amount and forgot to adjust the payment tied to it.

Or you moved the date money arrives and one formula still uses the old period.

Or you copied an existing scenario, changed a few values in it, and a month later could not tell what exactly makes this version different from the original.

Spreadsheet errors have long been treated as a risk of financial modelling in their own right: the more manual entry, formulas and dependencies there are, the more attention checking the model takes.

So the problem with Excel is not that it cannot calculate.

It can.

The problem is that the structure of a large model starts demanding more and more supervision.

Where Excel is still the best choice

Not every financial job needs to move into a specialised service.

Excel is an excellent fit when:

• a one-off calculation is needed quickly;
• there is not much input data;
• there are few dependencies;
• one scenario is enough;
• the model has to be completely freeform;
• you want to control every formula yourself.

Working out the return on a deposit, totting up a month of expenses or building a simple budget all belong in an ordinary spreadsheet.

There is no point turning that into a separate system.

The problem appears when the sheet is no longer one calculation but a model of the futurethat is constantly changed and compared.

What changes when a model is built around the movement of money

Suppose that instead of a set of sheets we describe the entities of the financial system itself.

There are accounts.

Money moves between them.

Every movement has a rule and a schedule.

Inflation or conversion acts on the model.

There are financial goals.

And there are several versions of the future to compare.

That is how Cashflow is built.

Accounts describe where the money is, which assets and obligations exist, and the income and expenses. Pipes set the movement between accounts. Schedules decide when the operations happen. Effects handle inflation and conversion. Plans tie the model to financial goals, and flows let you build and compare different versions of the future.

The result is that you can change one rule, recalculate and look at the state of the finances on any date again.

For example:

What happens in five years if I take this loan?

And if I overpay it every six months?

And if I leave the money on a deposit instead?

This is no longer a set of separate calculations.

It is one model you can explore from different sides.

When to stop extending the spreadsheet

There is no universal number of rows or formulas after which Excel suddenly stops being suitable.

The line runs somewhere else.

As long as the sheet helps answer a question and changing it takes a few clear steps, it is doing its job.

When every new question means copying a sheet, rebuilding formulas, hunting for linked cells and comparing versions by hand, the sheet has started serving itself.

At that point what needs changing is not a formula but the way the problem is represented.

Financial planning gets more interesting when the model lets you explore a space of decisions rather than produce one forecast:

what happens if you change the term, the amount, the date, a cost, an income or any other condition?

Excel can still be used for that.

But sometimes it is easier when the model was built around those changes from the start.

FAQ