Appearances matter. Just ask Tinder 😉 When it comes to your financial model, you need to pay attention that not only that it makes sense but that it looks the part too.
In this blog I’m going to:
- Explain why appearances matter
- Show you a real founder’s model and how I improved it in just 3 hours
- Teach you the logic and tips on making your model professional
Video guide
Eh, why read when you can watch? I go side by side on the two models. Sorry, I did this at midnight so got a bit tired after calls. Excuse me if I don’t sound like the energiser bunny discussing Excel formatting 😉
Why your financial model needs to look professional
Sure, there is a load of reasons, but let’s not waste time on academics. There are two main reasons:
- Other people need to understand it
- It provides credibility
Other people need to understand it
When you make a financial model, or Excel file of any type, someone else is always going to see it. They are either:
- Internal
- External
If you make a model and you want your CFO, head of sales, whatever to look at it and action, they need to understand what the hell is going on (ideally with you not having to take them through it).
If you are making a model for investors, then you sure as hell care that they are able to follow your logic so that they then give you money!
It provides credibility
If someone takes one look at it and thinks ‘oh god this is going to be a pain to deal with‘ they have already programmed a pessimistic lens into their head. It’s only if you really surprise them that you will be able to change the first impression. And let’s face it, that’s not all that likely.
Wouldn’t it just be easier to provide something that looks fab but might not have quite as much vigour in it? You might get the benefit of the doubt. Hell, they might not dig into it as much too!
The earlier you are, the less likely those numbers are to be real. The later stage you are the more they are based on actual experience but we are talking larger checks so they better be credible.
How your model appears and the more clear the logic, the more your model and by proxy your forecasts are deemed to be logical and credible.
Credibility is currency. Write yourself some currency.
Improving the model step-by-step
Some awesome but (as usual) strapped founders needed to send out their model to their investors the same day. They called me up to see what I could do for them.
Now, there is only so much you can do in a short period of time so you need to prioritise. What I told them I could do for them in a few hours was this:
- Add a KPI sheet
- Expand the P&L and review the logic of the line items
- Make it look a lot more professional
I did say that I couldn’t:
- Add functionality
- Change the logic of the model
- Refactor the formulas so they were easier to follow
If you are short of time, you have to prioritise and get real with what you can do so that things actually get shipped on time.
Here you can see the before and after of one section.
Now I’m going to run through a number of the things that I did so you can pick up some tips.
Cover
This is a super easy win. Create a tab called ‘Cover.’
- Pop your logo at the top
- If you have any general model assumptions, put them here. Though if you have a load make a tab called ‘assumptions’ and put them there. In this case, there is only one and that’s to set the start of the model’s month date
- I add a basic overview of assumption formats in the model so that people understand what everything means and what they can change or not. This is based on best practice from investment banking
KPI
A KPI sheet is a great way to dig into the meaningful numbers in a model without going through every sheet. Every model should have one. At its core, you are just pulling in numbers across sheets through links. You don’t have to create new numbers. Of course, it’s best to if you can and know how.
In this example, there are not a tonne of KPIs, so I made enough to make it look interesting. They also had feedback from a prospective investor so I made sure those were included.
Don’t just list a tonne of line items. That’s hard to follow. You want to break up everything into nice little categories, such as financial, marketing, operations etc.
P&L
Firstly, your P&L needs to follow proper accounting practices. The thing is that it can be hard to know exactly where costs should be assigned.
One of the areas that can be hard to make a call on is COGS, depending on your business model. COGS are basically anything involved in the serving of customers. In SaaS, your customer success and customer care are easy examples.
In this model, the founders did things wrong. I changed the headers for everything to anonymize them so it’s a little hard actually to understand the accounting changes I made. Basically, the costs in COGS are only associated with say ‘listing costs’ in order to execute the business.
They also had marketing in the ‘cost of service’. You don’t put marketing in COGS. Marketing is technically an OpEx. But let me teach you something useful. And it’s not ‘GAAP/IFRS’ accounting.
Marketing is a variable for growth. If you want to grow faster you spend more money on marketing. This makes you unprofitable in the short term but will drive greater revenue. If you want to ‘get profitable’ faster you can cut marketing but it will impact revenue. You should split out revenue explicitly to show how it affects the P&L and draw attention to it. After your marketing, you have your general OpEx and can review them differently.
Some other quick additions are adding in more growth rates and occasional margin calculations so you can visually see what is happening more easily.
To make sure their business made sense I checked. How? Well, they have a similar competitor which is public. So I went to the investor relations section and pulled out their annual report. I reviewed how they structured their P&L and assigned costs. Annoyingly it was pretty condensed so their split out was rubbish. It wasn’t super useful, but it provided a bit of guidance to ensure their P&L followed best practices.
Cost assumptions
You want to disaggregate assumptions into groups if it makes sense. Separate them with a header so they are easy to follow. In their case, they had too many assumptions together and it was hard to understand.
Next, if you look at the ‘wages’ they have one assumption for the whole time series for three classes of staff. I hate this method. What you want to do is keep all assumptions in line for each month. This is a lot easier to audit. Furthermore, if you want to make changes, it’s a lot faster to update. So if you look at my version, everything is now in line.
For calculations, you want to spell everything out clearly. Show all the logic so one can understand how any calculations work.
A key thing I didn’t have time to do is to really spell out calculations. Don’t have formulas where you link to other sheets within it. I don’t know what the hell the link is when I look at a formula. When I do modelling I add each and every line so if someone is auditing the formulas you can see exactly what is happening.
Your goal is to make it really easy to follow a model. If someone has to really dig into something it’s just too much effort. It makes it hard for both you to audit and for anyone else to.
Formatting
Now let me touch on some formatting changes. Clearly, you can see that everything looks very different!
This is really not hard to achieve if you know what to do.
Sparklines
This is something that makes everyone lose their minds. They are super simple to add and they look super cool. Only, no one knows how. Just click on the insert tab and then ‘sparklines’ and boom you have mini graphs in each cell. See for yourself.
Narrow rows
To create gaps between sections I create narrow rows. This spaces things out nicely. If you check the ‘row height’ I set it to 5.
If you know a bit about Excel you might know you can make larger rows and the play with cell alignment to get a similar effect. Screw that. If you want to move things around it’s a ball ache. If you use narrow rows you can just move everything around and copy insert a row as needed. It’s just a lot easier.
No black lines
You might like to add lines to show the separation between you actuals and forecast or something. The reality is you are always going to be fiddling with your model and those lines are a pain in the ass to reformat. Yes, in the templates I make I sometimes have lines, but these are finished products that are not likely to be changed much. For most founders and models, just don’t deal with lines as your model is always a work in progress.
Remove grids
You’ll see that my version of the model has no grids. It’s all white. No, I didn’t set the background as white as that has some consequences that I’m not going to get into. The simple solution is to simply remove grids. In Mac, just click on the Page Layout tab, and at the end click the button ‘View’ under gridlines. That’s it. No grid lines.
Fonts
Click all the sheets, select all (CTRL-A, A) and then change the font to Calibri and font size 10. I like size 10. Fonts can be different, but Calibri isn’t bad. At my old investment bank, Lazard, we used Garamond, but frankly, I think that looks awful.
Restricted viewing
If you are using the keyboard and going to the bottom and right of sheets it can be annoying to go to the millionth cell. If you restrict the viewing you can only get to the end.
To stop this from happening I remove all the cells on the right. So go to one call after the last cell and select to the end. Then click ctrl or cmd and – and then 0. That deletes all content and then hides it.
Why don’t I do this for the bottom?
Well, if you group things and add comments then Excel has some weird bug that is super annoying. Basically, you can hide, but grouping won’t work. So if you have no grouping and comments you can do the same thing which is ctrl or cmd and – and then 9.
Conclusion
That’s enough for now. I can go on, but best to just pick up a few tips. Now, download the two models and have a play. The way to internalise the learnings is to really review and understand how things were changed rather than just reading a few tips.
If you found it useful, send me a testimonial I can add. Appreciate you.
The excel doc link is missing — is it possible to share?