How to fill in a financial model template

Congrats, you got one of my financial models. Now you are thinking, how the heck do I forecast my startup in a financial model template? Yup, everyone is going to wonder this, so if you are feeling lost, you’re in common company!

In this blog, I’m going to take you through at a high level the broad steps you should be taking. The most important things to remember, though are:

• Figure out your financial goals that you have to hit before you start. Make the model support those goals!
• Keep calm and carry on! Don’t aim for perfection at first run. Iterate.
• The earlier the stage you are, the less you know. Focus more on the metrics and growth rates you need to tell a fundable story. VCs know your #s are bullshit, so focus on ‘logical and believable’.

Start with pen and paper and set your goals for the financial model template

Don’t just start throwing in numbers. You want to work smart not hard. So here’s a big tip, start with pen and paper.

You want to know your answer before you start. A model is not the place to find answers, it’s a place to learn the implications of things.

When I was in M&A, your MD would say ‘this company is worth $500m. Make the model show that.‘ Actually, that’s a lie. They would make you work for days and then they would say that. In M&A it’s all about buying and selling stuff, so valuation is the objective. When you are in startup it’s about your growth rates.

As an investor, I care about two things, the growth rates and size of the numbers. Only then do I want to see what supports those numbers.

If you are super early, then maybe you can get away with a 12-month projection, but for the most part, my opinion is 3 years is the ideal. 5 years are fine, but you have no way of really thinking that far forward. Yes, 3 years is a crap shoot too, but you can sort of get your head around it.

There’s only a 2 year difference, why is that harder? Well, we overestimate what can be done in the short term and drastically underestimate what can be done in a longer term… so the issue is you really want your numbers to start looking crazy 😉 A shorter time horizon allows you to show an interesting ‘short’ future, without giving you the time to show some bonkers hockey stick. Sure, investors love hockey sticks, they’re just so cliche these days! Allow investors to dream about what might be possible themselves. Take this with a pinch of salt though, this is just an off the cuff opinion.

If you have a 5 year projection, I don’t really care about the line items, I just care about how big you think this will get. I’m going to look at the multiple of revenue (or other top end numbers) year over year. Then I’m going to times your year 5 (or year 3) number by 10x to get an idea of what the potential exit value is. I’ll half it for the bullshit factor, and double it to get the min/max range and quickly get a feeling if this startup is ‘VC fundable.’

So instead of just doing a model and then saying that’s what the model says, pick your outcomes then see what the implications are.

So get a pen and paper and map out what your revenue/GMV etc top goals are for 3-5 years. In your first year or two, you can have ‘stupid numbers’ like 1000x growth. But fairly soon you should have a year on year multiple changes along the lines of triple, triple, double, double, double (T2D3). Once you get growth stage then ~40% growth is your goal assuming you have a 0% profit margin (Rule of 40%).

Be careful with any ‘guidelines’ as everything always depends. If you are in SaaS, then you need about $1.5m ARR to get your Series-A (And man… the goal posts are moving and fragmenting these days!). So frankly, I don’t really care what your growth rates are unless you hit that ballpark number.

Call up some investors and ask them ‘what numbers would you want to see at S-A/B to fund us?’ Then you need to hit those numbers. Ok, most people can’t do that, but you get the point? Try if you can.

Then after your revenue or GMV or whatever, think, what CAC is normal, what are the benchmark LTVs, at what point will we break even etc? You want to note your big picture numbers. Then you want the model to do that.

But do you get what we are talking about here? Set your goals so you know what you need to hit.

You’re probably thinking ‘can’t you just give me an example, dude!‘ Eh fine. I just boshed out this ghetto draft in 5 minutes. Here is a picture. 

 

I didn’t exactly over think it. I just set some goals at the top to set the ‘general direction’ then made a financial table with some of the key numbers you might have for a SaaS model. I then added a couple of circles and arrows with some comments to bear in mind as to how your startup might evolve. You have plans right? You have a funding plan of sorts (well maybe you don’t). You need to get some idea about the kind of business you are building.

• Are you going to try to push more to premium plans than basic ones?
• What do you think is going to happen with CAC?
• Are you going to change pricing?
• When are you… going to start being profitable, if at all?
• How is churn going to change?
• Are you going to hire salespeople?
• Given your product roadmap what does your hiring plan need to be to support it?
• How fast do you really think you can scale revenue?

You know your business. What do you plan on doing? Sure, you won’t start and end with one piece of paper. You will probably need to do 5 till you can really get a decent view of what you want to do. I recommend boshing out ideas and playing with ideas really messy. Cover your table in paper. Then bring all the thoughts together in 1 or 2 sheets of paper. You want to use these like a cheat sheet when it comes to reviewing what you are doing in the model. There’s going to be a lot to think about so you need to keep triangulating till you get to roughly what you want.

I know this is a bit different, but I do a similar process when I am building these models. They take ages, so I map out all the logic on paper before I start. I create flow charts so I can see what links where and I make notes if there are weird adjustments I need to do in places. Yeah, I then map out the whole model in my head so I have a view of exactly what I am building, but when it comes to the details I follow my notes to make sure I haven’t forgotten something. You can use your ‘cheat sheets’ to ensure you are sticking to the plan and not getting lost in the weeds.

Don’t be fancy. Be messy

Modelling is a process. Don’t start out trying to be perfect. Just get shit done. Chuck in whatever assumptions and then make them better over time. You are effectually triangulating.

How annoying is it to spend a day putting in super accurate numbers only to realise the numbers just don’t work and you need to change your operating model? If you did ballpark numbers real quick, you can see what’s going on and keep triangulating without surprises.

Triangulating is also more fun. You see immediately what’s happening as you make some key assumptions and start thinking about the effects of all your decisions. Only once you think things make sense should you start nerding out and putting in the details.

If you couldn’t care less about email marketing then don’t!

My models aren’t for kids. They’re big boys’ toys. If you haven’t bought one of my models and you’re reading this to learn, you might think I’m joking or naive. I’m not. These things are huge.

Let’s pick on say marketing. There are a bunch of sheets on, for example, email and social marketing. Unless you really believe this is your core marketing channel (which it isn’t for most) then you might think spending a second is a second too long.

The whole model is pre-populated with numbers. I’ve looked at benchmarks to find reasonable default assumptions. So if you are looking at email marketing, you could just input the size of your current email list and leave everything else as is. Yeah, once you finish the model you need to know what assumptions ‘you’ made, but that’s it.

But why bother with this email stuff at all? Well, most startups have to buy customers. Paying users suck. Well, how do you decrease your effective CAC? By diluting down with free users. Some ways of doing that are by blogging, organic, emails etc. So a 20% ‘free’ traffic is a 20% lower paid CAC. So instead of $100 a user, you are paying effectively $80. Um, yeah. That’s good. So a little messing about with these sheets and you can argue lower CACs.

Anyway, if you really don’t care about this stuff and want to be basic, then just chuck in zeros. You don’t really have to set everything to zero. You just need to zero out the key drivers. So for emails, that’s your starting database and any conversion rate. If you don’t think super fast, then it is faster to just set every assumption to zero 😉 Just saying. Thinking is overrated when manual labour can just be used.

Chuck in your core numbers which aren’t really going to change

Depending on your stage, there are things you know. If you are later stage then you know a whole lot. Your numbers are not going to be total bullshite. But the earlier you are the more known unknowns there are and generally the harder it is.

But, regardless of stage, I would still do a similar process when building a model whether you are late or early stage. Start with what matters and then deal with stuff that is less important as you go. Frankly, it’s just more fun and rewarding to progressively build a model iteratively and see results.

Understand the basic logic

My models are based on the following bottom-up logic:

• You do marketing and that creates registered users or trials
• Those trials can % convert to paid on a schedule of your choosing
• You segment customers into buckets to get averages and model distinct avatars
• You make revenue from those customers on a first-time basis. For SaaS that recurs on a monthly or annual basis. For ecommerce there is a first-time purchase
•  Your customers will churn over time
• Some or all customers will repurchase
• There are costs of servicing the customers (CoGS)
• There are costs of running the business (Staff etc)
• All the exceptionals like bad debt are dealt with in the P&L sheets which makes it easier to manipulate numbers without dealing with them in the main logic engines

Start with revenue

Start with the key revenue assumptions. Do everything easy. If you are doing SaaS get your pricing packages in. If you are ecommerce, your average ticket sizes etc. Do everything that really matters with revenue. There’s nothing to see yet though because marketing is the driver of revenue so you need to deal with that.

Then marketing

You probably care about paid. So chuck in what you think your CPCs and conversion rates are. See what that is approx for your CAC. Now you have pricing and revenue come from marketing spend, get a feel for what revenue that creates.

Look at the P&L and KPI sheets to get familiar with where the line items are.

You may find your numbers are far too small or large, so have another stab, probably at marketing and get things to the ball park you want. Let’s face it, you’re charging $69 a month for the basic package, so that’s that. But maybe you might be able to sell more of the pro package? You can make some assumptions around headline line items, but some levers only will do so much.

Churn and repeat purchases

Depending on your business model, stuff like repeat buying or churn etc matter. They can make a big difference. You want to make some assumptions and see what happens.

If you don’t have a lot of data, then these can be stabs in the dark. I would pick reasonable numbers and then forget about them. Go deal with inputting the rest of the model and then you can play about with these variables at the end to tweak things.

Honestly, you are going to mess about with key numbers like churn at the end to try get your CAC etc around what you want it, so don’t be anal at the start. Changing marketing conversion and churn rates are one of the easiest ways to make your P&L/KPIs hit those targets you wrote down.

Pick a section and do it

Once you have most of the main variables input and you have a feel for what is going on, it’s time to start ploughing into a section. There are a couple, from marketing to staff, general expenses, revenue generation, P&L etc.

Pick something. Marketing is never the worst place to start. Pick a sheet and put in some assumptions. Understand a bit what’s going on, but prioritise. Apply the 80/20 rule and generally fill out a sheet. Don’t go spending hours researching a number. Get about right and move on.

Go sheet by sheet. Once you have finished a section such as marketing, sense check. Get a general feel for what is going on. What % of traffic, trials etc come from what channels. What are the evolutions over time? Do they make sense? If not, screw it. Get things back on track.

Think in terms of ‘order of magnitude in the right direction‘. There’s so much to do or that you can do, that you need to ensure everything is in the right direction. Why dick about with something with a low impact when you can focus on the big 80% pareto things?

Note I still haven’t told you to be super anal. I’m just saying pay more attention and get the assumptions pretty solid. That extra 20% to get perfect takes 80% of the time.

Keep getting through each section/sheet till you have got through them all roughly.

Calibrate

Once you have filled in the model generally, you want to calibrate and figure out what is going down in funky town. The P&L forecast is useful to see how you are generally trending, but you’ll figure out if things are illogical in the KPI sheets.

The KPI sheets are your logic checks. If ratios don’t make sense, then there is some imbalance in your assumptions somewhere. It would take me a book to explain each ratio in terms of ‘making sense’ so, yeah, I’m not going to. You need to put in some time to understand what is ‘industry’ to know if things look really dumb.

Generally, you either make too much money or spend too much money and vice versa. So if you have a CAC/LTV of 20x and a payback period of 1 month… you’re making too much money and/or your CAC is too low. If you make most of your traffic from paid then either your CPC is low or your conversation rate is too high. Then on the other side, your churn is probably too low, pricing too high etc.

Learn what the drivers are for your business

I know it’s nerdy, but you’re just going to have to learn the relationships between all the drivers. The model is fully integrated so changing something will impact everything. If you want to be that founder that VCs think ‘just gets it’ you need to understand all the relationships.

For example:

• What do you do if your CAC is too high?
  • CAC comes from your marketing channels and your conversion rates.
  • You have paid and earned channels. Paid is a cost and the earned ones (email etc) bring down your aggregate number. So if your CAC is too high you can basically decrease your CPC from paid channels like display or Adwords or you can increase your traffic to trial %, or trial to paid conversion rates.
  • Fully loaded CAC includes marketing staff, so maybe you are hiring too many people relative to spend and growth?
• What do you do if your revenue is too low?
  • Revenue is a function of marketing to acquire, conversion rates to paid, pricing of paid and churn as well as the pricing packages.
  • So you need to increase prices by package, reallocation % of packages by customers, decrease churn, increase marketing spend, increase upsell or change your package switching to higher packages.
• What do you do if your LTV is too low?
  • Your LTV is a function of churn, ARPU, package switching and COGS. So you need to mess about with the revenue and retention related numbers, right?
• What if you lose too much money?
  • You need to decrease costs, make more money, or both.
  • If you decrease marketing spend you save some money on marketing paid, but that also affects your revenue long term. Lower churn can help make more money, but is your assumptions too low now? Are you hiring too many people, but can you afford not to with your expansion plans? Do you have to change your approach to customer care? Maybe you need to sell a higher priced product?

Everything is connected and every choice has consequences. Are you starting to understand why I am telling you to put in ‘sort of right numbers‘ instead of ‘set in stone and perfect’ numbers? Your numbers are unlikely to be right first time and your aspirations may be totally unreasonable…

Shizzle may change a lot

I originally made the app and social fundraising model for a client and then changed it to make it more broadly applicable to you. I can tell you that the original model I did for them according to the founder’s outline and the one that ultimately went to investors was pretty different.

In the first iteration, there was a lot more staff. People got paid bonuses. Management had nice salaries…. yeah, the computer said no.

After filling in the assumptions it was evident costs had to change. The raise would simply not cover the cost assumptions. So what do you think happened?

Management lost the six-figure salaries, no one got bonuses and the number of staff dwindled dramatically! True story.

That’s the cool think about a model like these. Your assumptions will absolutely be inaccurate, but it can tell the future. High staff costs come at a cost. If you aren’t making the money and you don’t have a monster raise, you can’t afford what you want. So everything was slashed.

So again, do your model roughly so you see if you are living in la la land or not first. It will save you a lot of time.

Kill your darlings

Be totally cool with the fact that if reality bites your plans in the ass you just have to adapt. What you can afford is what you can afford.

The point I am making is very similar to the above section, but I want to make a clear point that if things don’t make sense financially, they don’t and you need a new plan.

I was doing a SaaS fundraising model for a client’s fundraise and I built it all on the assumptions he gave me. Once we got into it it was clear the amount of investment he thought he could reasonably close simple wasn’t going to be enough to meet his plans in the model. So what now? Not only did the costs come down, the marketing budget came down, but so did the revenue projections. We just had to be real, we weren’t going to get the monster growth. Hey, the numbers weren’t terrible, but:

1. This was not the story he thought he was telling investors
2. He could not be nearly as aggressive as he thought he could be

So the story (pitch deck) and the plan had to change. Tough, but reality gets in the way of plans if you are logical and reasonable and there is no point in being in denial.

Denial is not a river in Egypt

Sure, every founder needs a bit of a reality distortion field, but you also need to acknowledge reality if it punches you in the face.

Any model is only as good as the assumptions you put into it. If you have reasonable assumptions and the math just does not add up, then you need to shake hands with reality.

Sure, I may have messed up something somewhere in the 100s of thousands of formulas, but nothing really meaningful. Nothing that will ever move the needle. So just get it out of your head that ‘the model is bullshit, I’m awesome and it’s still going to work.’

If you just want to do the VC funded thing and go on to raise 3 mega rounds by somehow lying to investors, if your model does not work you are still going to be broke as no one is going to buy you. Your business model needs to work for you to get rich (Here’s how to think about that).

My models tell the future based on your assumptions. Don’t fight math. Startup is based on math.

Let me give you a great example of meeting reality and learning from it. It’s actually pretty cool.

I did a custom model for a founder based on the SaaS model. He had hardware in his business model so it involved some refactoring and building some new sheets. Cool, fine. We did it. But once we were done there was a problem.

One of his core assumptions was he would be buying devices for each of the suppliers up front. That sort of sounds ok if you don’t like, do math, but as I said, math can get in the way of your hopes.

It was clear there was just NO WAY to scale buying the hardware. Did he complain? No.

A week later he messaged me on Skype something along the lines of “I realised it wasn’t going to work so I reached out to the head of [x] in France and agreed on a rental deal of €3 a month for the devices!“.

Seriously, boom!

The model we did found a fatal flaw in his business model. Instead of pretending it didn’t exist, he took pre-emptive action and it suddenly made his model viable.

If you find something doesn’t make critical sense, don’t ignore it. Do something about it.

Get your actuals in

You have seen the ‘P&L Actual sheet.’ Yeah, you need to fill it in at some point. You can do it as the first thing or that last. I would say though if you have meaningful numbers, you want to fill in most of it early.

If you have a lot of customers and revenue, then those are all going to contribute to your forecasts. Recurring users are going to make revenue. If you have no numbers then who cares. You can leave the sheet empty. But if you have real numbers you want to fill in the key actuals early so your forecasts get calibrated right.

It’s going to be annoying to just do forecasts and then realise that your revenue is suddenly 20% higher, and you need to revise a bunch of assumptions down.

Finalise

I’m not going to lie. Doing models is a beaaaatch. It’s real work. You can pump out some stuff but eventually, you are going to have to settle on actual numbers. And there are a lot of them. Some for sure is a lot more critical than others, but you are going to have to make some directional views and do some fiddling to get them directionally right by the right order of magnitude.

Your key goal is logical and reasonable. You want to feel ‘I can live with this‘ and you want investors to think ‘These are backable and I think they sort of might make sense if things work out.’

Only, how do you check for that?

This is more art than science. I would do this:

• Look at your forecast and/or combined P&L sheet (if you have actuals) and focus on the annual numbers. How does your revenue multiply year on year? Does that make sense? 10x is likely too much unless you have a super low base. Then run down the line items. Is anything weird? Are you making money too early? Do the margins sort of look ok? Is anything a % of revenue too much? Look at the spark lines to see how things trend over the months and watch out for weird spikes.
• Dig into your KPI sheet. Run line by line. Anything jump out as weird? In SaaS, I’m really going to look at key metrics like CAC, LTV and the ratios. If you have like a 10x CAC/LTV something really doesn’t make sense. Is there an expense item like G&A which is just too high? If so you need to check your staff or something. Without really understanding benchmark numbers, it’s a little hard for you to know. You just have to use common sense and see if any particular number is bonkers.
• Review the graph sheets. Maybe you are not a boss at KPIs. Well, look at all the graphs! How do things trend? Are there any weird spikes? Things should generally be smooth unless you are planning something peculiar and are aware of it. If you see something that doesn’t look right, check the title and think ‘what are the drivers of this chart?’ Then review the related assumptions. Maybe it is a timing issue, but if it really is weird, you want to spend time figuring out what is going on.

Founder feedback

A founder emailed me this. I think it’s valuable to illustrate their experience so you actually do what I tell you since you know I’m not full of BS!

I am very new to fundraising, but one of the biggest takeaways is something you preach: building your financial model to ACTUALLY operate from. We recently went through DD with a VC and they admitted that a pre-revenue financial model was somewhat of a shot in the dark, but they wanted to see we had a legitimate plan for capital utilization. I had a 2 hour zoom with one of the associates at the firm going line by line through inputs of the source and use model you have created (for free, btw – thanks a million), mostly asking what the logic was behind the inputs and why.

The feedback I recently received is that the biggest driver of their offer decision was the team, followed by the fact that we had a solid plan & financial model.

Takeaways:

• Investors know early-stage models are BS!
• 2 hour call!
• They went line by line
• They wanted to see there was a real plan
• They wanted to understand the logic behind the inputs, not the inputs!

You can’t fake logic. This is why I try to teach you to think for yourself.

Can I deliver?

You have reviewed all the key sheets and are thinking, maybe things are all good? If they are then you need to ask yourself a question: “Can I really, honestly, deliver on these with my targeted raise?”

I don’t mean to be a buzzkill, but I’ve written this blog for a reason, and that’s to help you get rich. That only happens if you exit. If you are playing the VC game then understand it’s a game of pass the parcel, ultimately.

Who you raise from now might not be who funds you at growth. You need to keep meeting your milestones to get further in the VC rounds. And… VCs have a memory.

People are going to remember what milestones you promised.

At series A you ideally want to have a chart which shows what you promised your seed investors. The same for your B round for the A guys. It states something like this ‘we exceeded our targets!’

You show your forecasts vs your actuals and you do more… not less.

Thinking this witchcraft model will help me dupe investors with inflated numbers is so narrow-minded. You need to deliver, buddy! What’s going to happen in 12-18 months? Results day.

If you really don’t believe you can deliver on numbers, what’s the point in faking it? You will get caught out. If you are not profitable and sucking on the VC teet then you’re dead anyway as no one will fund you unless you are lucky. You just jerked off a year calling yourself VC funded but delayed the fact you can’t pay your mortgage now.

So, I don’t care. You should. Look at your key numbers and ask yourself ‘If I work really hard, if I stretch and hire great people… can I really deliver on what this model says?‘

If not, it’s a pyrrhic victory.

What do I do with this model after?

You made this model for investors but also to help you think about your startup and how to run it.

So for you, hopefully, it helped you think better. Keep that up. You’ll get better over time. Trust me.

Now for investors:

• Don’t put a financials slide in your initial deck. You want to explain the model, tell a story and get buy-in to the concept first. Numbers get in the way of a story. The earlier you are the more they are a bit silly and raise too many questions. Show numbers once investors are already hot for you 😉
• Do not just send a model until investors are interested and have shown it. You are not a charity for data. Models are once investors really want to do the deal but want to feel safe, like an Excel comfort hug.
• VCs won’t sign NDAs. Be sure you are not sending models to investors who might have a conflict.

Feedback, please!

This is my MVP attempt at giving a general guide to how to fill out my models and think about them.

A couple of founders emailed me with the feedback they would like to have more specific guidance as to how to approach filling them out. So I executed.

I’m not going to write a book, but I’m happy to expand if you give me feedback on what doesn’t make sense, what would be worth digging into more detail and the like.

You have my email. Just be direct and honest and I’ll be happy to get back to you and update material so everyone benefits. Cheers.

 

Need help with your deck?

Head over to Perfect Pitch Deck. Competitive pricing and expert assistance to get you fundraise ready and confident.

Want to see a 100 pitch decks?

If you love pitch decks, check out the ultimate pitch deck collection here!

Looking for something interesting to read

• Convertible note conversion math at Series-A. You don’t know how it really works!
• Key convertible note terms that no one understands and cost you big
• Seed round convertible, priced and SAFEs. What venture capital terms are standard?
• AA 013: (High resolution financing) Can you negotiate different valuation caps for each investor, or does it have to be standard across the board when raising seed money?