Financial Modeling 101: Tutorial and Template
Caya
April 28, 2020
  |  

Financial Modeling 101: Tutorial and Template

Caya
April 28, 2020
  |  

Join 100,000 entrepreneurs who read us every month

We've got it!
You'll be hearing from us soon.
Oops! Something went wrong while submitting the form.

What is a financial model?

A financial model is an essential tool for every business owner that’s built in Excel to calculate their business’ financial performance into the future.  The calculation is typically based on the company’s historical performance, or assumptions about the future. A startup financial model is usually split into SG&A (Selling, General, and Administrative Expenses), COGS (Cost of Goods Sold), Revenue, and CAPEX (Capital Expenditure).

If you're starting a business, a Financial Model is a critical tool to estimate the potential of your company. How much do you plan to charge for your product? How much do you expect you'll pay to acquire a customer? How much time do you need to finish development, and how much will those salaries cost? 

I see tons of pitch deck that just show a crazy hockey stick chart that is not backed up by any data. They literally just came up with some numbers that looked good on a graph. 

That's not the way it works. Investors expect you to do the math, estimate the money you need to get to a milestone and provide an accurate estimate of your expenses. 

I can't count the number of founders I've talked to that doesn't have this figured out. I know because now, five years ago, when starting Slidebean, we hadn't figured it out either. 

When we launched our product, we charged $5/mo for it. And we had no idea that there was NO POSSIBLE WAY to get positive unit economics on that pricing. 

$5/mo means $60 in annual revenue, assuming nobody cancels. That means that to make some money, we'd need to acquire customers for around $20 per customer- which is pretty much impossible for any SaaS company. 

We would have known this if we had a financial model. We do now, but it took us years to develop and to understand, and it's now become the backbone of my work as CEO.  I work on that spreadsheet 2-3 times per week, estimating future revenues, adjusting with historical data, and understanding where and when we can expand our team or our growth efforts. 

Remember we run a profitable operation, so spending our profits efficiently is critical. There are many kinds of financial models; this one is closer to a Forecasting Model, which is used for financial planning and analysis (FP&A)

In a nutshell, what an effective model should do is: 

  1. Take an estimated ad/growth/marketing/sales spend. 
  2. Estimate the revenue generated from it. 
  3. Estimate the costs associated with generating that revenue, including team, office, server, etc..
  4. And finally, give you an answer on whether this combination of variables in making the company grow. 

For early-stage startups, the model should take in the team size, the team expansion, and compare it to the available cash, perhaps from a round of funding, do understand what the exact company runway is. 

If you've done your model right, you should be able to scale your team and your budget, understand the revenue impact of those changes and measuring how much that will affect your runway. 

Here are the BASIC parts of a model: 

COGS Sheet 

That stands for Costs of Good Sold and relates to the direct costs associated with providing your service. 

For a supermarket or an eCommerce store, COGS is straightforward. It's the cost of the groceries or the items sold that the company pays to the suppliers. 

Most software companies use COGS for server costs and other essential tools that the platform needs to be functional. In our case, tools like Intercom and Amazon Web Services are part of COGS. 

If your compare revenue to COGS, what you get is the Gross Margin: the margin your company makes before accounting for the administrative expenses. 

Revenue Sheet

Revenue is used to track and estimate, well, your revenue. 

What is absolutely vital for any model is what drives your revenue. Revenue doesn't just 'come.' You have to bring it in. Depending on your business, you'll need to pay to market your app or pay to get leads. You might even need to pay for a sales team to close those sales. 

If, for example, your model estimates a $10,000/mo marketing budget that doesn't increase, it makes no sense that your revenue grows to $10MM in year 3. That's just impossible. 

There needs to be some correlation with reality here, and many of these benchmarks are available out there. 

Some examples, 

  • App installs using Facebook ads can cost a few cents in low-competition countries, and $2 in competitive markets like the US. 
  • There is no way to get Google Search Traffic for less than $1 per click, and most keywords require bids of $5 or $6 per click.
  • An average conversion rate on a landing page could be around 25%. 50% is remarkable. More than that is unrealistic. 
  • As the company grows, acquiring customers with paid ads usually gets more expensive, not cheaper. 

These numbers need to be taken into account in your model. Growth and revenue don't come magically. 

Your model should show the math behind your expected cost of acquisition. This is absolutely key. I can't stress it enough. 

SG&A sheet

Pretty much every expense that does not classify as COGS goes into the SG&A sheet. That includes payroll, marketing costs, travel, office expenses, rent, accounting, consultants... you name it. 

Again, it's imperative to connect these to your revenue estimations. On a SaaS platform (software subscription), you could say that you'll need to hire a support agent for every 1,000 active customers on the platform. 

So, you can connect the number of active users on your revenue sheet, to the number of employees on your SG&A sheet. This lets you estimate your margins in the future. 

You can also connect the number of team members to the size of your office, and therefore, rent. Or, you can compare the number of employees the number of seats you'll need on your CRM. 

It's those connections that let you be very accurate about your growth expectations and your company expenses. I challenge myself every month so that my projected SG&A at the beginning of the month actually matches our bank accounts. 

Working capital and CAPEX

This sheet is meant for assets owned by the company. If the company buys a car, that car is not an expense; it's an asset. 

The car affects your cash flow, yes, because you no longer have that cash available in the bank, but the asset should be logged in the model. The number of assets a company has an impact on its valuation, or, for example, if the company goes bankrupt and needs to liquidate them. 

Desks, computers, and other matches are often assets and not expenses, and these are to be logged on this sheet. 

While most software companies don't need to pay a lot of attention to this section, eCommerce platforms do. Working Capital is critical for them because they might need to pay suppliers before they collect sales revenue. 

All I've done for our template is to implement an automatic laptop buying system. So, every time the number of employees increases, the model estimates that you'll need to buy a new computer for them. Neat, right? 

You can, of course, add other purchases of your own. 

All of this stuff gets consolidated on your Monthly Financial Statement sheet. These sheets provide you a summary of your gross margin, net income per month, cash in the bank, and so on. Taxes are also automatically estimated here. 

All this stuff gets consolidated on the Annual Sheet, and that's where you have your official growth estimations. 

Now I added a few sheets of my own, let me tell you about those.

Team and Salaries Sheet 

This sheet lets you automatically estimate future hires without having to dig directly on the SG&A sheet. 

You may, for example, define your dev team, how often do you expect to hire new team members, and how often do you plan to adjust their salary. 

It's designed with employee categories in mind, so you shouldn't add each employee by name, instead, classify your team in each one of these buckets. 

Make sure you use company cost for their salary, not actual salary paid out to them. In other words, include your payroll costs here, so that you don't have to deal with those on a separate line. 

Projections Sheet 

Now, this is where the magic happens. Programming all these formulas in is hard. You need some excel skills to do it, as well as time, and founders don't have a whole lot of that. 

So I've worked on creating some pre-built financial models for SaaS, e-commerce, and ad-based businesses (like a blog or a social network). They already have all the formulas programmed in, so all you need to do is add your pricing, your own team scaling plan, and voliá. 

The clean model is free, but you can download a copy of these pre-made templates for just $79. Also, if you need help programming in a more complex business model, our team can help!

All templates are available at slidebean.com/financial

KPIs Sheet 

I've also added charts and a KPI sheet that lets you visually check the results of the model, as well as giving you some critical insights like an estimate of the capital you need to raise. Whether you can reach profitability in the projected five years, the model includes. 

Some important announcements! 

1- If you didn't know, we have a Discord Server around our Youtube channels. You can join completely free and join a 1-hour Q&A we do after each video launches. Hosted by yours truly. We'll also create a Financial Model channel so you can exchange ideas with the community.

2- A giveaway. The first 100 people to purchase any of our SaaS/eCommerce/Ad-Based financial models will also get a free, 1-year Slidebean plan so you can put those impressive numbers into a solid investor deck. So, $79 for our built model, plus a Slidebean plan. Can't get better than this. 

3- Last but not least, I want a YouTube plaque. The team wants a YouTube plaque, and we don't get to buy one. We just need to reach 100,000 subscribers, and if you haven't done so, this would be the right time to do it on the button below.

Download our Financial Model