Jul 14 Ugh, Financial Modelling - Part Four

So now you’re looking at your basic financial model, feeling a little bit proud. Rightly so!The skeleton model is great, and you have momentum.

The problem is, your numbers are probably wrong.Let’s fix that.Here are some things you can do to improve your financial model – things investors will be looking for:

Top Down vs Bottom Up

There are two main ways of guessing things like “number of customers per day”.

The first is called Top Down.This is where you take a big number you know as a fact (i.e. total potential customers), then shrink it until it sounds about right.“We run an ethical clothing store on High St. Each day there are 4,000 people that walk past the shop. If just 3% of them come in and buy something, we’ll have 120 sales per day”

This way of thinking can be dangerous, because it’s completely made up.That said, it’s a good way of sizing up a market.If you have a maximum customer base of 800 (say you run a social enterprise café in an office building), then you know there’s a cap on the number of sales you can have in a day, even if your café adds more seats.

Bottom-Up is a more granular way of thinking.This is starting with a small number (customers served per minute), and growing it until it seems right.“Each of our hairdressers can do up to eight appointments per day. If we have three staff members and are open six days per week, that’s 192 per week”

This is a useful thought process, identifying the maximum production you can get from your staff or machinery.It doesn’t guarantee that you’ll have enough customers to fill up every available minute, nor does it account for peak/off peak times.

Try both Top Down and Bottom Up, and use a combination of numbers to find the truth.

Scenario Planning

Predicting the future is almost impossible.Fortunately, we don’t have to plan for exactly what is going to happen.There are no prizes for perfectly tipping your financial performance.The “reward” is that you stay in business.

Remember, we are building a financial model that can survive what the world throws at it.A model that isn’t going to collapse if things don’t go to plan.The way to do this is to build a few different versions of the future.Some will be conservative and assume low sales, other versions might include branching out into new products.

When you change one part of a model, there’s probably 2-3 other parts that change with it.If your sales increase, you need to buy more products or ingredients.You may need to hire more staff, and service your machines more regularly.If you expand the business, you may need another manager, additional insurance, increased internet, more cleaners, etc.

The same goes for a downturn in sales. You may drop staff hours, move to a smaller office, or avoid increasing wages.Let’s hope it doesn’t come to that, but if it does, let’s hope we keep our heads above water.

Sensitivity Testing

We want to see what it takes to break the business.There will be a line, an invisible but very real line, where low sales and high costs mean that the business is no longer viable.Learn everything you can about that line.

There may well be another line at the top – growing too quickly becomes expensive, and the business implodes.This happens when your costs shoot up faster than your income.

You might need more staff and equipment to keep up with a spike in demand, but are left extremely vulnerable to a drop in income.

Paul Steele describes this as being like the weedkiller Roundup, which causes the weed to grow way too fast, and ultimately the whole thing dies.

Using your spreadsheet, you can trial-and-error your way into determining the point at which the model breaks.That gives you a sense of “Margin of Safety”, the buffer between your current position, and the point at which you’re losing money.

Reality Check

One of the dangers of a model is that calculations don’t neatly match real life.

You may have deduced that for every additional 50 customers, you need an additional staff member, and so each customer carries 2% of a new person’s salary.Nice on a spreadsheet, useless in an office.

What it doesn’t tell you is that, unless you have the ability to hire 2% of a person, you need to hire someone at the 51st-customer mark.That’s wildly inefficient, but it’s how the world works.

Think of our café example – the fancy La Marzocco espresso machine might be able to do 100-200 coffees an hour.As soon as you want to sell 201 coffees in an hour, you need a new machine, and another barista.

These are called Semi-Fixed costs, things that go up in sharp amounts as your enterprise gets bigger.

It’s worth scanning your numbers to check for these, and round them up to a proper number.Your margins will look worse, but now they reflect the real world, which is what counts.

Assumptions Lists

By now you’ve made a lot of guesses, which have been mixed in with some rock-solid facts.That’s going to confuse everyone, especially your management team.

Let’s bring some clarity to the model.In the same workbook, create a new sheet called “Assumptions”This new sheet is really simple, just three columns:

Assumptions Amount Source

This might be something like:

Monthly Power Cost - $200 – Based on the last two bills

Dishwasher - $1,800 – Based on quote from Jim’s Appliances

Staff Training Costs - $2,100 – Guess

You can’t build a model without guessing, and you can’t finish a model with key numbers that haven’t been verified.

By tracking which is which, you avoid getting lost and make the document easier for the reader.