Ugh, Financial Modelling - Part Three
The Skeleton of the Model
Microsoft Excel is a much hated product.
On the surface, it is an endless sea of empty rectangles.
A big white grid.
What is all the fuss about? What am I supposed to do with this?
Excel doesn’t bring anything to the party.
You need to add all the figures, lists and bits of information.
Excel does two things well– it’s a great place to store information, and it can do really complex calculations with whatever numbers you’ve given it.
We’re talking stuff that our brains can’t intuitively do, and Excel does them instantaneously.
Here is the most important thing about spreadsheets:
They do EXACTLY what you tell them to do.
Tell them the right thing and it’s like magic.
Tell them the wrong thing and they’ll infuriate you – a whole page fills up with error messages, and you have no idea why.
The cells are powered by “Formulas”, which are specific commands you give.
This might be commands like:
“Add up these 27 numbers”
“Find the average of these figures”
“Tell me when this breaks even”
“Multiply the price by the customer numbers by the average number of purchases, then take away all of the…..” etc.
Setting up all of these formulas up is tedious, but worthwhile.
By building all of these instructions, you can later say “What would happen if we raise the price by a dollar?”, change one cell, and watch all of the other numbers jump up in a split second.
Can you imagine having to recalculate all of that?
Let’s get started on the skeleton. It’s made up of four areas:
Income – all of the money you earn
Variable Costs – the money you spend making each product or service
Fixed Costs – the money you spend on things that cost the same no matter how much you sell
Calculations – The amount of money you made or lost in the end
Income
The aim is to accurately guess how much revenue you will earn, because sales keep the business alive.
To start, you need to estimate two things:
How many customers will we have?
How much will each of them pay?
These might be complex.
Not to worry, Excel will help us get detailed down the track.
Let’s use our social enterprise café – there are three customers, each paying a different price:
Take-Away Customers – 100 per day, paying $4 each
Breakfast/Lunch Customers – 50 per day, paying $15 each
Catering Customers – 1 per day, paying $200 each
= (100 x 4) + (50 x 15) + (1 x 200)
= $1350
Any idiot with a calculator can tell you what this equals, but the beauty of excel is that it clearly lays out all the different components that make up your income.
That means we can later experiment with changing them up, and instantly see the results.
Variable Costs
These are the Costs of Goods Sold.
In the case of a café; the cost of the coffee beans, eggs, avocado, disposable cups, and so on.
Sometimes it’s easier to count it all out, e.g. Each cup of coffee uses this many beans, which cost this much per kg, so it’s ~17c per cup.
Other times it’s easier to use an educated guess, e.g. Ingredients cost 30% of what we charge.
Either way, we now have a few numbers that look like:
Cost of a Take-Away Coffee - $1.20
Cost of a Breakfast/Lunch - $6.50
Cost of Catering - $70
Now we tell excel to use the same number of sales as before, instead of us re-typing them.
That’s really important, because we know that when sales go up, these costs go up to.
More customers mean that we go through more beans, cups and avocados, and vice versa for when customer numbers drop.
Now we have:
= (100 x 1.20) + (50 x 6.50) + (1 x 70)
= $515
Fixed Costs
These are the costs that stay the same no matter how many customers we have.
For our café, it’s things like rent, staff, insurance and heating.
Sure, if the business doubles in size then they’ll go up, but for now they’ll do.
Rent - $130 per day
Cooks – 2 at $210 each
Barista – 1 at $200 each
Wait Staff – 1 at $180 each
Insurance - $10 per day
Utilities - $8 per day
= 130 + (2 x $210) + (1 x 200) + (1 x 180) + 10 + 8
= $948
Calculations
Now we look at what those numbers are telling us.
Firstly, we look at the profit/loss figure, which is drawn from the earlier calculations:
Income – Variable Costs – Fixed Costs
= 1350 – 515 – 948
= -$113
Uh oh.
Something isn’t working, but we don’t know what it is.
This is where the model saves us time – we can experiment with different changes, and the model instantly tells us what happens to the profit/loss figure.
For example, you can experiment with raising prices, selling more takeaway coffees, switching to fancier catering, dropping to 1.5 cooks instead of 2, and see if that makes things better.
Remember, these are all guesses, so our job is to now get some facts.
Maybe we guessed wrong about how much ingredients cost, or maybe we could switch to a cheaper building.
Either way, we now have a working financial model.
We can make it more detailed in the future, but the structure is now set. You survived!
Next in Part Four, we’ll be looking at some tricks that will make your model even stronger…
You can go straight to:
Part One: What is a Financial Model?
Part Two: What does a Financial Model do for me?
Part Three: Building the skeleton of the Model
art Four: Tips & Traps
