How to build a financial model from scratch

Start with the question, not with the spreadsheet

The most common mistake in building a financial model is to open Excel straight away and start filling cells.

First work out what exactly the model has to show.

How much money will there be in a year is one question.

Will there be enough money if three people are hired in three months is another.

Which is better, going on renting or taking a mortgage, is a third.

The wording decides the model. One scenario needs a few accounts and some regular payments. Another needs a loan, interest, changing costs, a large purchase and several versions of the future.

A good model starts from the decision you want to test. That is how modern planning tools work: first the starting situation is described, then the changes are modelled, then the consequences of different scenarios are compared.

Step 1. Write down where the money is

The first layer of the model is everything that holds money, counts as an asset or a liability, or records a financial result.

For personal finances that can be a bank account, a deposit, a brokerage account, a flat, a loan and money a client owes.

For a business the list is different: the current account, the cash desk, receivables, a loan, equipment, equity, income and expenses.

Do not try to describe every operation at this point. Start with a map of the financial situation.

Cashflow uses seven kinds of account for this: liquid assets, illiquid assets, receivables, payables, equity, income and expenses. The split keeps your own money, your obligations, the money owed to you and the financial result visible separately.

That gives the first layer of the model:

where the money is now and what else affects your financial position.

Step 2. Describe how money moves

Now the accounts need to be connected.

A salary lands on the bank account. Rent is paid out of it. Loan payments leave from it, and money moves from it to a deposit.

In a business a client pays an invoice, the company sends money to a supplier, pays staff and taxes, and part of it stays on the current account.

Every such movement is worth describing as a rule:

from where → to where → how much.

That matters more than writing down salary 300 000 or rent 100 000. What we need to know is where the money came from and where it went, because the links between accounts are what shapes the future picture.

The amount does not have to be fixed either. It can be a share, an interest rate, or something derived from another account. A payout can be a set percentage of revenue, for instance.

What you end up with is not a list of operations but a scheme of how money moves.

Step 3. Add time

Without time a financial model cannot answer most of the questions that actually matter.

A salary is paid every month. Rent goes at the start of the month. Deposit interest accrues by its own rule. A large purchase happens once, on a particular date.

So every movement needs a schedule.

Regular operations are not the whole story. In real life monthly payments sit next to yearly costs, one-off purchases, early repayments and changes that only take effect from a certain date.

That is why modern forecasting tools work on a timeline rather than on yearly totals: what matters is seeing when a receipt or an expense occurs. In a cash flow forecast the order of the movements decides the future liquidity directly.

In Cashflow schedules can be added to and subtracted from one another, and the dates are recalculated while you are still setting the model up.

After that the model starts living in time.

Step 4. Set what changes the value of money

Some financial changes cannot be described by a single amount.

100 000 today and 100 000 in ten years are different economic quantities. The same goes for conversion between currencies, or any other coefficient that changes the original sum.

So the model needs rules that describe those changes.

Inflation shows how the value of money changes over time. Conversion restates a figure in another currency or on another scale you define.

In Cashflow these are effects: you set the coefficient and it is applied to the model.

That helps most when the question is not how many rubles there will be but, for instance:

What will this money be worth in five years?

or

What is the real value of this balance at the inflation rate I set?

Step 5. Tie the model to a goal

You now have a position, the movements and time. A path for the money can still not be enough on its own.

Financial planning almost always has a goal attached.

Save a down payment. Build a reserve. Clear a debt. Have a certain sum by a certain date.

Instead of just looking at a chart, set the condition that has to be met and check whether the current model gets there.

If the goal is a reserve worth six months of expenses by a given date, the model has to show not only the balance but whether that balance is enough at the moment you picked.

In Cashflow goals are plans tied to accounts. Once the calculation runs you can see whether the scheme you built reaches the goal, and when.

That is how a financial model turns from a forecast into a planning tool.

Step 6. Build several versions of the future

The interesting part starts with the question what happens if?

What happens if you take a loan?

What changes after hiring new people?

Is it worth opening another location?

What happens if the job goes in six months?

How much does the result change with regular early repayments?

One forecast will not do here. You need options.

Keep the base scenario as it is and make a few more versions with changed conditions. That way you compare the consequences of specific decisions rather than abstract figures. This is exactly what scenario modelling is for: individual assumptions are varied against the original plan and the results are compared.

In Cashflow separate versions of a model are called flows. You can create several and compare them.

When a financial model really starts to pay off

A complete model does not have to be huge.

For one question a few accounts, a couple of rules and a few dates can be enough.

Say someone is deciding whether to overpay a mortgage. They do not need a model of their whole life. Current money, income, expenses, the loan, the payment schedule and a few overpayment options will do.

Then you look at how the balances and the overall position change under each scenario.

In another case the model is far more involved. An owner may be holding several accounts, receivables, staff, rent, loans, investments and several plans for the business at once.

The principle stays the same:

1. work out what exists;
2. describe how money moves between it;
3. say when the changes happen;
4. allow for what changes the value or the size of those movements;
5. decide where the model is supposed to get you;
6. check what happens under different decisions.

That order is what keeps you from drowning in detail.

From a spreadsheet to a model of the future

Excel is excellent for simple calculations and small models. The trouble comes not from Excel itself but from the number of links.

When a model holds dozens of interdependent operations, several time rules and several scenarios, every change has to be dragged through the connected calculations. The more dependencies there are, the harder the model is to keep consistent.

Specialised tools move that work inside the system itself.

Cashflow builds the model around money moving between accounts. You define the entities, the rules and the schedules; the system works out their sequence in time.

The main question changes as a result.

Instead of:

How do I calculate this figure?

you can ask:

What happens if I change this?

And get the answer from the model.

FAQ