How to build a financial model

You see that picture of an atom with electrons flying around the nucleus on little monorails? Atoms don’t really look like that. Nor do they look like billiard balls, cherry buns or a tiny solar system. They may not look like any of those images but, in certain circumstances, each is a useful model of how atoms behave.

Models don’t capture the beautiful complexity of real life. That’s not their job. We use them – whether of atoms, Spitfires, organisations or anything else – to help us imagine, help us picture, help us think.

Two models in particular dominate go-to-market thinking: business and financial. Confusingly, different people use these terms to mean different things.

I use business model to mean a simple block diagram that shows the flow of goods and services in to and out of an organisation. Picture a whiteboard drawing – all boxes, labels and arrows – that explains to every new recruit, in two minutes flat, exactly where this business fits into the world and what it offers.

Show me the money

A financial model shows the flow of money inside an organisation. It shows the making and spending of money. It’s all numbers. It lives in a spreadsheet.

But just because everything is neat and tidy and all the columns add up doesn’t mean a financial model is any more real than the atom pictures or a plastic Airfix kit. Like all models, it’s a thinking tool.

Like most plans, the benefit of a financial model is in the modelling. A collective understanding of how your business works is vital to good management and a short cut to a cohesive strategy. But there are other benefits too. A good model builds confidence, shows everyone what you’re talking about, and makes it easier to raise funding – whether you’re inside a company, looking to external funders, or trying to win over a sceptical spouse, when it comes to crunch time, numbers are much more powerful than Powerpoint.

How to build a financial model

The process of building a financial model starts with defining your assumptions and I’m going to start with laying out a few of mine for this post: you know that a financial model is necessary; that it’s a tool to help you think about, experiment with, and understand what might happen in your company; and that you want to avoid tying yourself in knots trying to come up with an exact map of real life with as many special cases as there are stars in your salesman’s eye.

Ok. Although models live in spreadsheets, they get born on paper. The first thing to do is close your laptop, take out a pen and settle down with a few colleagues to answer three questions:

1. How do we make money?
This is a prompt to get complete clarity within the team about all the types of revenue you have and when they’ll come on line? This can be more difficult than it seems, especially if you have a complex proposition. There’s often a range of views on what the product actually is, when it will launch, how long it will take to hit its stride and how much to charge. Don’t try to kill differences of opinion, but use them to mine for every assumption and get the ranges down on paper.

2. How do we spend money?
This should be easy. The major categories to consider are usually people, things, marketing, running expenses, and all the extra things you have to do to complete a sale.

3. How are the two related?
The real magic in a model is in the relationship between the answers to question one and two. The weakest models I’ve seen are all lacking on this point with no explicit link between marketing activity and sales, for instance, or with an increase in subscribers to an online service having no impact on hosting costs. Almost everything is related – dig for the links and work them into the model.

Avoid “top down thinking” – the market is worth $500 million, we aim to get 2% share in three years. It may catch headlines but it’s no way to plan for success. Instead, build from the “bottom up” – we’ll have three sales people (assumption) who will each make four calls a day (assumption) with a 15% conversion rate (assumption) and an average order value of £12,000 (assumption).

Take out your spreadsheets

When you’ve got a good grasp of how the business works, close the meeting and open your spreadsheet. Get some help on the cleverness of Excel if you need it, but do as much of the work as you can yourself. The more you’re involved, the more ownership you’ll feel.

Design

When building the model itself take the trouble to come up with a decent design. This isn’t just about making it look pretty – don’t underestimate this point though; you’d be surprised how people are more willing to get interactive with a reasonable looking spreadsheet – but good design will make it easy to use too.

Easy to use means easy to read and easy to flex. To do this:

Make it clear what you’re looking at. In particular, separate as much as possible the three main kinds of worksheet:

• Inputs/assumptions – everything you found during the discussion should be identified clearly as an assumption by colour coding them, I like to highlight these cells in yellow.
• Calculations – avoid “programming”, use human readable cell and range names, colour code whenever it helps, show your workings out (most people will never look at them but those that do should be able easily to follow what you’ve done).
• Outputs – make sure the output sheets are easy to print or copy to other documents. For example, a minus sign is easy to miss on a printed sheet so use brackets around red negative numbers which are much easier to see on a page.

Group things together – it may seem a bit basic but make sure to put everything about staff on one sheet. For example, if your assumptions tell you that you’ll spend £500 on equipment per new member of staff, don’t calculate the new number of staff on the equipment sheet, calculate ‘new staff’ on the people sheet and then use the result on the equipment sheet.

Work it

Use real numbers wherever you can but don’t be afraid to put dummy numbers in as talking points. The cost of office space should be pretty easy to work out, for example, but many assumptions are difficult to pin down in a low-data environment like a new product or new company startup – use extreme numbers to start the discussion. And discuss you must. Is this how our business works? What about that 15% conversion rate? Can we really collect our payment in the second month after shipping?

Iron it out with the team – it’s likely to be an iterative process, and it’s likely to need quite a bit of metal bashing. Don’t expect to get any useful feedback if you send drafts by email; pull the team back together as often as necessary, walk them through the model, hammer it out.

Eventually, you’ll have a model that reflects the collective view of how this business works. Still full of assumptions, but they’ll be shared assumptions, something to manage.

Now what?

A robust model is not an end point. Use it to play scenarios, agree a plan and build your budgets. But don’t abandon it.

Keep an eye on those assumptions, they’re likely to be wrong, and in a startup of any kind, they can whip around and bite you.

Every time you get new data, check back. Try to eliminate them as quickly as possible, or at least set yourself a schedule to review and adjust them. For example, that 15% conversion rate, now you’ve been in business three months, does it still look good or should we adjust it down to 10%? Does that mean we need more salespeople? Better salespeople? More funding? Or is it our lead time that’s out? Does it come back to 15% after five months, not three?

A financial model lives on a spreadsheet – don’t bury it in an archive, keep it fresh.

All financial models are more complex than you’d like and too simple to reflect reality, but they’re a primary source of strategic thinking and make for good general management. They may not be as tasty as a cherry bun, but if they’re good, all human life is there: your own private solar system.

The picture of the atom comes from Wikipaedia.

Get a daily nudge by subscribing to email updates.

Category:
Models