Since the original version of this post from early 2017, we’ve worked with many more SaaS companies and a common theme has been moving companies from a starter template to a more robust financial model. So I’m providing an updated model for SaaS CEOs and founders looking to improve their financial model from a number-crunching exercise to an operational tool.
This model allowed me to work with dozens of SaaS startups using spreadsheets, while we built our financial modeling software Flightpath. Although SaaS companies share many features across their business models, there is enough variation that requires differentiation in the financial model. In other words, the model needs to have a structure that makes it easy to update and add elements into, as opposed to spending countless hours trying to make yet another mega-template fit to your needs.
A Modular Financial Model
The structure of a strong SaaS financial model should be wholly modular. This means that you need to be able to add individual forecasts, such as a marketing funnel, in a way that doesn’t require re-building the entire model. Similarly, you’ll want to be able to look at new metrics as they become relevant to your business. Say, your customer acquisition efforts are starting to pay off, and you need to keep an eye on your Customer Acquisition Cost (CAC).
A modular structure will also enable you to bring in your team leads to own pieces of the overall forecasts. After all, these are the people who you hired to be experts in their fields, and are therefore able to provide the best inputs and the right context in creating accurate forecasts. The modular nature also allows you to provide only the information your leaders need to create their forecasts. For example, your marketing leader may not need access to everyone’s salaries, and yet they should be the person owning your marketing funnel driving the new customer forecast.
Before getting started, make a copy of the Google Sheets template to follow along.
For any Excel purists out there, yes, Excel is better for hardcore modeling. However, even with its’ new embedding capabilities, it doesn’t come close to Google Sheets in team collaboration.
Set up the Model Structure
The model consists of four types of templates:
- Operating Model
- Forecasting Model
- Reporting Model
- Data Export
The only required template is the Operating Model. This is a monthly view of three financial statements of your Profit and Loss, Balance Sheet and Cash Flow Statement. These three statements are a standard way to represent financials of any business from a mom-and-pop shop to a Fortune 500 company, and there’s no reason to reinvent the wheel for tech startups, either.
The remaining three models are optional, but you’re likely want to include at least a few of them. As their name suggests, Forecasting Models are used to forecast out a specific area of your business, such as revenue or payroll. These models feed or push data into the Operating Model.
In contrast, Reporting Models pull data from the Operating Model – or other models – to display the data in an easy-to-digest format. After all, SaaS has many unique metrics and KPIs that can’t be communicated using only a three statement structure.
Finally, Data Exports are really more like sub-templates, as their only function is to provide data for your other models. These are exports from your accounting, billing and other systems to bring in actual data to use in your models. Maintaining the same export structure month after month will offer significant time-savings and increased accuracy as you update your model.
The Operating Model contains Profit and Loss, Balance Sheet and Cash Flow statements, all displayed on top of each other in a monthly format.
Structure of a simplified Operating Model
Seeing actuals and forecasts side-by-side helps to ground your assumptions in reality. I’ve seen models where founders enter what they think happened, but also cases where someone manually types in every single line-item from their accounting. The first method is error-prone to say the least, and the second is just too time-consuming (and still error-prone).
A better way to accomplish this is to utilize a data export tab to pull in data from your accounting in an always-consistent format.
Prepare Data Export Tabs
In the following examples, let’s follow a company called Southeast Inc, which sells some obscure SaaS to airports around the world. They’re making about $700k in MRR, still make net losses, but work on becoming cash flow positive in the coming months.
In the examples below, I’m using Quickbooks Online (QBO), but you can pull similar exports out of Xero as well. The structure in our example books follows SaaS best practices for expense categorization, but the template will work fine even if your books look different.
In QBO, navigate to Reports on the left and select Profit and Loss. Select All Dates for the report period, and make sure to display columns by month. This structure ensures your historical export structure doesn’t change from month to month, and only new months are added as new data comes in. Next, export the report:
Profit and Loss Export from Quickbooks Online
Open the export in Google Sheets or Excel, and copy and paste the contents into the Profit and Loss Export worksheet. Repeat the same process for Balance Sheet and Statement of Cash Flows (=Cash Flow Statement) in their respective tabs.
Fill in Actuals
You’ll want to pull these three exports into the Operating Model, with a goal of, after the initial setup, you’re able to just drop in the exports from Quickbooks on a monthly basis with minimal effort. We’ll do that by using Named Ranges.
Let’s start with the Profit and Loss, or PnL.
In the example tab of Profit and Loss Export, I’ve named the spreadsheet column A (the “range”) with the PnL account names as PnL_Accounts. The month columns have named ranges following a syntax of statementName_mmm_yyyy. Say, PnL_Jan_2019. I have named the months from January 2018 to September 2019 to give you a fast start.
You’ll have to only name the months that I haven’t named already. For example, if you begin building your model in October 2019 and you have data since January 2018, you’ll only need to add a named range for October. If you have more historical data for example, you’ll need to add named ranges for each month you want to pull into the Operating Model. Again, make sure your columns match mine exactly, i.e. September 2019 is in Column Y and not any other column.
Repeat for Balance Sheet (BS) and Cash Flow Statement (CFS) tabs. The idea of this exercise is to be able to use a consistent formula on the Operating Model, without worrying about an error in how we link to the data export tabs.
Copy and Paste Account Names Into Operating Model
After naming your exports ranges, we need to tell the Operating Model what exactly we want to pull in each month.
Start by copying each account name from your PnL tab into the Operating Model, followed by BS and CFS. You can either clear out the Operating Model from the account names I use (pictured below), or rename the accounts to fit what’s in your books. Feel free to add more rows as needed.
This takes a good 20-30 minutes, but don’t worry. You’re doing this just once—with the rare exception when your accountant adds more accounts to your books. (Once you have a solid Chart of Accounts, this really shouldn’t happen too often).
Use the Formula to Pull in the Export Data
Now, we finally get to pull in data. The formula I use appears a little hard to read, but what it does is actually quite simple. The first part of the formula looks up the current month from the export, and the second part ensures we’re pulling the exact account name from column A.
Drag this formula to cover all the actual months you want to pull into the Operating Model. I recommend pulling at least the current year and the previous one:
Repeat the process for Balance Sheet, but remember to use the formula from the Balance Sheet section, as it changes the formula prefix from PnL to BS.
Next, make sure the “Total” formulas include everything you just modified.
The green sanity checks for the totals are extremely useful as I can immediately see if my Operating Model is missing an account that’s present in the PnL.
Note that the formula structure breaks if you don’t have unique account names in your Quickbooks. For example, if you have two “salary” accounts. The way to get around it is to give unique names to each of your accounts, which is a good practice anyway.
Connect Cash Flow Statement to Balance Sheet
Finally, one last time-consuming part is to finalize the Cash Flow Statement. The good news is that this pays off in spades once you begin to forecast your cash—say, from annual prepays.
The CFS doesn’t do anything on its own. It just looks at the differences in monthly values from your Balance Sheet and presents them in a separate statement. An increase in assets decreases the cash because you are buying an asset—say, a laptop. Whereas an increase in liabilities or equity (e.g. a loan disbursement or an equity investment) will increase your cash.
Create an Autopilot Forecast
Now we can begin forecasting. The first step is to create a forecast that’s just an average of your performance over the past three months. We like to call this “Autopilot,” since it’s a solid short-term forecast for your less-important accounts, and it updates itself every month when new data comes in. For example, forecasting your software expenses is usually not critical to your business. As a whole however, your smaller expenses add up—meaning you need to account for them somehow.
I like to use the Autopilot Input column to automate the use of the averages. The column looks up the most recently closed month from the Dashboard, and looks back three months to calculate the desired averages. Initially, all forecasts in the Operating Model are going to reference this Autopilot Input column.
Profit and Loss
Start by making sure all your forecasts are pulling in values from the Autopilot Input column (Column B).
Next, override any changes where the simple Autopilot doesn’t make sense. For example, if your future rent is expected to go up significantly as you move into a new office, change the value in the Autopilot column.
If the forecast varies from month-to-month, feel free to edit the future values directly. If you do that, I recommend using blue background for any overrides so you will always know what’s forecasted automatically and what is entered manually.
Finally, you could increase the accuracy of the Autopilot by making your Cost of Revenue (COR / COGS) section to be calculated as a percentage of revenue. Because costs such as hosting scale alongside your revenue, using the modified Autopilot will improve the accuracy of your forecasts. See the hosting costs section in the model as an example.
Note that Autopilot is a slightly different beast from the Last 4 Months (L4M) model, popularized by Jason Lemkin in a sense that we don’t add any growth assumptions quite yet. While L4M can be really useful to look at as a separate scenario, Autopilot serves a different purpose: quickly building out the structure of your forward-looking financial model before diving deeper with custom forecasts.
For Balance Sheet Autopilot, I recommend using the last month’s value to avoid adding any unnecessary noise to your cash forecast before we actually understand what are the drivers in your business. I modified the Autopilot Input formula to pull only the most recent month.
Cash Flow Statement
There is no Autopilot needed for the Cash Flow Statement since this is an automatic calculation. Make sure the formulas you create above extend to the forecasted months as well.
After implementing these Autopilot setups, you should have much better visibility which line-items deserve a custom take on their forecasts. For most businesses, this means at least their revenue and hiring plans. We’re going to build examples for both of these forecasts.
While you could continue to forecast your payroll spend as an average of the past few months, creating a Hiring Plan on an employee-by-employee level will significantly increase the accuracy of your projections. This is where you’d benefit from bringing in someone from your leadership team to keep things up-to-date.
On the Hiring Plan tab, add each of your current team members with their salaries, benefits, and other information. If you have recurring contractors that act as an extension to your team, add those as well with a contractor status. For better readability, I recommend adding Headings for each team, e.g. “Engineering” and “Marketing”.
Scroll down to the Teams section, and verify if the numbers make sense for the past few months. You don’t need to make the hiring plan accurate since the beginning of time, since the values from your accounting system will override data in the past.
Finally, we will pull the output rows of the Hiring Plan into the Operating Model. Each total is a named range, and they’re already being pulled into the Operating Model.
Note: There’s nothing preventing you from using data exports to pull employee data into the Hiring Plan, but the time savings aren’t that significant until you have 50+ employees.
Now all you need to do is go into the Operating Model and copy and paste the green hiring plan formulas under their respective payroll accounts. These formulas pull the salaries, benefits and payroll tax information from the Hiring Plan.
Pay careful attention to the formula name! If the named range says it’s pulling Hiring_Plan_Marketing _Salaries, it’ll only pull marketing salaries. Thus, you can’t use the same formula and expect it to pull Sales Salaries as well.
That’s it for the Hiring Plan! With adding only one custom forecast to your financial model, you’ve markedly improved the accuracy of your expense forecast.
Revenue is usually the most important item (and the most difficult) to forecast in any business. To forecast effectively, we will first want to see what the history looks like.
Prepare Data Export Tabs
To get started, we need data about your customers. The easiest way to see this is to pull a handful of reports from a SaaS metrics system such as Baremetrics. You can also enter these manually, or use an export from your billing system.
Head over to Monthly Recurring Revenue in your Baremetrics dashboard.
First, select “All time” as the time period from the dropdown on the top right. The chart should automatically switch to display data by month.
Export both Graph and Breakout from the top right, and repeat for the following reports:
Copy and paste each of these into the MRR Export tab in the financial model. You can ignore copy-pasting the dates from all exports except the first one for MRR breakout.
Six exports from Baremetrics color coded to denote where to paste each export.
Fill in Actuals
Now that you’ve exported the data, you’ll need to tell the Revenue Model to retrieve it from the exports. I’ve named the columns in the template already, so if you exported the values from Baremetrics, you can navigate to the Revenue Model tab to copy the formulas across.
Create an Autopilot Forecast
If you don’t have a marketing funnel yet, using an Autopilot forecast here is a great way to get started. Just replace the row 7 formula with something like a median from the past three months:
The company has been getting 26-32 new customers a month at an average size of $1,041/mo each. The Autopilot continues to project out 30 customers each month, bringing in over $31,000 in new MRR. While this obviously needs to change to something more accurate, you’ll immediately get an idea what is the baseline to beat.
Next, use Autopilot to project out your expansion, contraction and churn. As you sum these with the new customer MRR, you end up with net new MRR for a given month.
Add net new revenue to your previous month’s total MRR, and you have your revenue forecast for the month.
Finally, we need to take the revenue forecast and make sure it’s reflected in the Operating Model. Similar to the Hiring Plan, the yellow MRR row is the output we want to pull in. Navigate to the Operating Model tab, and make sure the green formula is pulling values from the Revenue Forecast Model.
Create a Marketing Funnel
The biggest remaining flaw in your Autopilot forecast is that your new customers are coming in at a flat rate, when you’d likely want to see growth. In this example, we’re improving this forecast by bringing in our imaginary marketing leader. They own the process of tracking the conversions from visitors to leads to paid customers, and have the best insights as to what the future might look like from a marketing perspective.
Since we are talking about the future, this would normally mean adding another Forecast Model. This time, the marketing funnel lives in another workbook updated by your marketing leader, which means we will need just another data export to pull in the outputs in.
Here’s the example SaaS marketing funnel template. Again, create a copy of this to follow along.
The funnel we are building is simple, but realistic. Visitors to the site come from two sources: paid advertising and organic search. Paid ads are driven by the spend in a given marketing channel, whereas organic traffic is expected to grow as a result of content marketing efforts.
Start by pulling in the AdWords spend into the AdWords tab of the Marketing Funnel. Copy and Paste the SaaS Financial Model URL into this section, and define from which row you want to pull data from. Given you have created a copies of both template, you’ll have to update this URL.
Next, modify the template to fit your needs. Enter how many visitors convert to leads, to marketing qualified leads and ultimately, to new customers.
For modeling purposes, it’s the new customers we are interested in, but having the steps in between enables us to move away from an educated guess to something more systematic.
On the tab of Marketing Funnel – Summary, we can see how new customers are summed up from paid and organic sources, only to be pulled into the tab with the same name in the master financial model. Although you could copy and paste this section into the master model and pretend it’s an export, I recommend using the same IMPORTRANGE formula to bring over the summary automatically.
Our example company Southeast Inc is planning to introduce prepaid annual plans. Thus, we want the Revenue Forecast Model is to split these newly projected new customers into monthly and annual customers. After all, the billing cycle will have a wildly different impact on your cash balance. More on that in the next section.
You should now have an idea of how to add in additional forecast models to your financial model, and have your respective team leads own them.
Say that your larger deals aren’t driven by marketing, but by your sales pipeline. In this case you may want to add a completely new revenue forecast model to better reflect the larger customer size, your existing sales pipeline, and other characteristics.
Most of our SaaS clients have mix of customers paying either monthly or annually. The cash impact of annual prepayments can be particularly challenging to forecast, even though prepayments can be critical in funding the growth in bootstrapped or fundstrapped companies.
In our example company Southeast Inc, their customers have been paying only on a monthly basis so far. Therefore, the Net Loss and Net Cash Decrease are very similar.
Before introducing annual plans, the company’s Net Income and
Net Cash Flow are almost identical.
As discussed in the marketing funnel example, Southeast Inc. is planning to offer annual plans, and expects to see their share grow over time. As you can see, such a change would transform the company from making losses to becoming cash flow positive!
After introducing annual plans, the company’s
Net Cash Flow becomes positive.
Let’s walk through a more concrete example.
Imagine that, of the 30 new customers a month, three are pre-paying for their annual plan in October. This is now $63,000 (27 x $1,000 + 3 x $12,000) cash coming in. If this continues with six new annual customers in November, and nine in December – the company is suddenly cash flow positive even though they are still making losses on their Profit and Loss statement.
Many companies I’ve seen attempt some sort of of internal, custom calculation for figuring out the cash impact on their business. Even the 1.0 financial model used a similar, simplified way, but this is really only suited for cash-based accounting for very small companies.
For more established companies, the standard and widely-understood method for forecasting cash from annual payments is to forecast Deferred Revenue. The challenge is that I have never met a CEO or a founder who “gets” the deferred revenue upon first walk-through. This isn’t to say startup finance folks are some kind of geniuses, far from it, but rather to highlight that there are many moving pieces you need to keep tabs on.
Here’s another go. Keep in mind this is still a simplified example, as Southeast Inc. is only beginning to sell annual plans – they don’t have any renewals or existing annual customers to worry about. If this still feels confusing, rest assured that if you have followed the instructions, the model calculates the cash impact for you automatically (from new customers, that is).
Please let me know in the comments or on Twitter what’s unclear, and I’ll attempt to clarify.
First, forget debits and credits, and let’s remind ourselves what we know already: revenue doesn’t equal the amount of cash coming in each from October onwards (that’s when the annual plans are introduced).
We already know what the revenue forecast is. So to get to the cash, we should think of it as making adjustments to the revenue. And to get to these adjustments, we first need to forecast our deferred revenue balance.
Deferred Revenue represents the balance of customer prepayments that are not fully earned yet. Imagine a business where a customer paid $12,000 for a 12 month subscription on January 1st, but the business went out of business in March 31. The business still owes the customer nine months of service, or 9 x $1,000 = $9,000, which is also the deferred revenue balance.
Let’s keep looking at the simple example for a bit longer. One customer signs up for a $12,000 prepaid plan on January 1st and continues to subscribe to the service for the rest of the year. There are no other customers, renewals, or any other activity. We know that we need to recognize the payment over the service period, which conveniently for us, is a calendar year.
As a reminder, we want to figure out what is the adjustment to revenue that gives us the cash impact on the business. Given this is an example with one customer, we already know the outcome—the $12,000. But repeated across hundreds or thousands of customers, we have no idea what the outcome would look like unless we have iron-tight understanding of what the adjustment process should look like.
To create the adjustments, we need to figure out what’s our Deferred Revenue. You add balance to the Deferred Revenue with every new customer prepayment, and deduct the balance once the revenue is earned or “recognized” over time. Here’s what the additions and deductions would look like for the one customer signing up in January:
Still, while we now begin to understand how this works in theory, the balance sheet will only accept this in one format only: a balance. So we’ll sum up all of these additions and subtractions to get to the month-end balance of Deferred Revenue:
The thing is, the monthly difference in the Deferred Revenue balance is the adjustment we are looking for. Given that this company had no previous deferred revenue, the first month’s difference is $11,000 minus the previous month’s balance (zero) which equals $11,000. For the following month, the equation is $10,000 minus $11,000, which equals a negative ($1,000).
If we now sum Revenue and Deferred Revenue Adjustment, we will finally understand what is the cash impact.
The problem is that the cash impact isn’t directly visible anywhere in your books, and that’s where most confusion with CEOs/Founders seems to arise. The main difference is that your books deduct costs and expenses from your Revenue, resulting in Net Income. This is then adjusted with Deferred Revenue, and everything else from Accounts Receivable to credit cards. The end result is Net Cash Increase, which is the amount that gets added to (or deducted from) your bank every month.
Given the example company has no other activity, the outcome would still be the same:
The good news is that as long as you actively project our future revenue in the Revenue Forecast Model, the financial model template will automatically calculate the Deferred Revenue adjustment for you. Your labor with the Cash Flow Statement will also come to fruition, since it will automatically calculate the adjustments for us from the balance sheet Deferred Revenue balance differences.
When you think about it, your Operating Model is actually a huge adjustment table, where you have Revenue at the top, followed by all sorts of expenses, balance sheet changes, and the Net Cash Increase/Decrease at the very bottom.
If you still have questions, I have a more comprehensive post coming up in Flightpath’s blog on how to forecast cash from annual plans in SaaS.
As the name suggests, Reporting Models pull data from elsewhere in your model and present it in a more digestible format. The goal is to create summary tabs that give you a high-level overview of what’s happening with the business, enabling you to do deeper dives on areas that require attention.
You could even create a reporting tab specific to your team or investors, and share that on a separate workbook using the IMPORTRANGE functionality we discussed earlier.
I like to think of metrics as sanity checks on the sustainability of the business. Is my Customer Lifetime Value high enough compared to my Customer Acquisition Cost to continue investing in Sales & Marketing? If not, what needs to change? Or if I’m making 80% gross margins right now, but the forecast says it’s decreasing to 70% in six months, do I understand why?
In contrast, I’m not a huge fan of using metrics to benchmark your company against the average startup. I often get asked questions along the lines of “What’s a good CAC payback time?” and my go-to response is the ever-boring “it depends.” For this specific question, it depends on the company’s capital position and appetite for risk. A VC-funded, well-capitalized startup selling large, prepaid contracts might be comfortable with 12-18 month payback times. On the other hand, a bootstrapped, profitable company selling monthly plans – and without a massive cash cushion – usually can’t wait that long. Definitely not if they don’t get paid in advance.
In this example template, we’re pulling data from the Revenue Forecast Model and the Operating Model to calculate some of the most common SaaS metrics.
Let’s begin with Customer Acquisition Cost, or CAC.
First, pull your total Sales & Marketing expenses from your Operating Model, and the number of new customers per month from your Revenue Forecast Model.
Now, this is just the company-wide or “Blended” CAC. While useful, given that paid acquisition is a large part of the Southeast Inc’s marketing strategy, we’ll also want to calculate another metric: a Paid CAC (or CAC per Marketing Channel).
We know the breakdown of new customers from paid and organic sources. Thus, we can pull your Advertising (=AdWords Spend) account from the Operating Model, and pull new customers originating from AdWords from your Marketing Funnel.
Next, let’s calculate the customer Lifetime Value (LTV). This is calculated by multiplying gross-margin adjusted Average Revenue Per Customer (ARPC or ARPU) by the expected customer lifetime. A common mistake is to use just the revenue-based figures and ignore the gross margin. I don’t recommend such an approach, as the result is something between overestimating your metrics and lying to yourself.
To make anything gross-margin adjusted, we’ll first need to calculate the gross margin. Pull your Revenue and Cost of Revenue from the Operating Model, and divide the second value by the first. That’s your gross margin for each month. For SaaS businesses, this is usually between 70-90%.
Next, pull the number of customers from the Revenue Forecast Model. Divide Revenue by the number of Customers to get to ARPC. Now, multiply ARPC by the Gross Margin, and you have your gross-margin adjusted figure!
Finally, pull in your customer churn (logo churn). Calculate the customer churn % by dividing the current month’s customer churn by the previous month’s total customers. The expected lifetime is often estimated as 1/churn, which is what we’re going to use here.
Now we have all the elements we need to calculate the LTV. You can even add the CAC to LTV ratio at the bottom.
While you could leave it here, I would also recommend creating quarterly views for all of these metrics. Your business goes through ups and downs each month, and monthly metrics aren’t always the best representation of the overall state of your business. For example, both CAC Paybacks appear to jump from 6-month payback in April to 12-14 in May, whereas the quarterly view paints a much more stable of a picture.
Last but not least, a financial model dashboard is a great place to review the most important areas of your business on a monthly basis. The goal is to include several high-level tables and charts, which enable you to do deeper dives into the drivers of your business. It will also help you spot any potential problem areas.
Personally, I prefer starting my monthly review by looking at a financial summary table, like this:
You should include the most recent month, and either a few forward-looking months or perhaps a forecast to actuals comparison. Profit and Loss tells you only so much, so I also include a summary of Cash Flows and Bank Balance.
Next, decide which charts you want to look at. I tend to include one each for Revenue, Bank Balance, Expense Breakdown and Cash Flows, sometimes metrics as well.
While you could pull these charts directly from other tabs or the Operating Model, I recommend creating another reporting model called Chartbuilding. This makes it easy to quickly create and update charts, without having to worry about something breaks in the model.
Name the data ranges you want to look at, and pull them into the Chartbuilding tab. This could be metrics, revenue, accounts from the Operating Model—anything at all that lives inside your financial model.
As a result, we expect to see a Dashboard that looks something like this:
The biggest gotcha in repurposing the SaaS Financial Model 2.0 to your needs are the named ranges. While named ranges bring much-needed clarity into your formulas describing in plain English where your data is coming from, this requires setting them up correctly.
For example, your new customers from paid sources might be located in a different row than the named range in this model. That said, whether or not you use named ranges, any multi-tab financial model will require you to verify the accuracy of the data you’re pulling in.
The goal of updating your financial model on a monthly basis is to give you the tools you need to make operational decisions about your SaaS company. You should be able to able to answer questions like “How are my marketing channels driving my top-line growth”, or “What are our capital requirements until we become cash flow positive?” All in all, you’re able to get to actionable takeaways behind the numbers.
Building a comprehensive, operationally-focused financial model requires a lot of work to set up, but the benefits are critical to grow your business, and are absolutely worth the effort.