There are plenty of elaborate financial model templates out there, but the ones I’ve seen come with a key problem: it’s a significant undertaking to update them.

In fact, inaccurate financial modeling and the hassle to update that model is what nearly put Baremetrics out of business last year before we worked with them to get a sustainable model in place.

In this blog post, we are going to walk through a SaaS financial model that can be quickly updated by using exports from Quickbooks and Baremetrics. This should help founders and CEOs of companies with up to $500,000 in ARR* get:

  • Visibility into the key drivers of your revenue
  • Visibility into different scenarios of how the business might progress going forward
  • A solid financial model to support your decision-making, without spending days constructing the perfect model and hours upon hours updating it

The model will take a couple of hours to set up, but the monthly updates should take just 20-30 minutes. Everything in the model is modular, so whenever you grow to a point of needing more customized forecasts, you or your future CFO can expand on the area you find lacking.

*Companies nearing $1MM ARR typically have a model that’s at least somewhat customized for their business. If you don’t, I’d like to know how you pull it off.

Before you do anything else, you’ll want to download the Excel file so you can follow along.

You can also view and use a Google Sheets version! Just make a copy of it and save it to your own Google Drive.

Getting Started — Go Get the Data

In this section, we look at pulling exports from Quickbooks and Baremetrics to your financial model. The goal is that you can use the same exports each month with minimal changes, allowing you to focus on forecasts and actually building your business.

Before you start, make sure your bookkeeping separates cost of sales and expenses, enabling you to calculate a gross profit. If you aren’t doing this, you should probably get started. You need to understand your gross margins in order to figure out if your business will ever become sustainable.

Accounting

First, pull up your Profit and Loss report for the current year, separated by month. In the examples below, I’m using Quickbooks Online, but you should be able to use an accounting software of your choice.

Profit and Loss settings for Quickbooks Online

Next, copy and paste the export into the Profit and Loss Export worksheet of your financial model. Make sure to paint the area from above the Income all the way to the last month’s Net Income. Make sure to delete any rows that are left in from the example model.

Your export looks something like this — with many more expense categories.

Lastly, fill in the Historicals Start Date and Forecast Start Date in the Controlsworksheet. The first is most likely January 1, and the latter the 1st of the current month. Add your current cash balance as well.

Baremetrics

Next, we need data about your customers. The easiest way to do this is to pull a handful of quick reports from Baremetrics, but you can also enter these in manually.

Get started by heading over to Monthly Recurring Revenue in your Baremetrics dashboard. From the dropdown, select the time period for your historicals you specified above. For example, Jan 1 — Mar 31. Change the chart dropdown to display “Months”, and use the “Download Breakout” button to pull the first export.

Repeat the same for the following reports.

Once you have all the forecasts, drop in the MRR export to MRR Export worksheet and Forecasts to Forecast Export worksheet. Please note that Customer Export worksheet includes four exports — Active Customers Breakout, Upgrades, Downgrades, and the Active Customers Graph. I’ve separated these by a border line for clarity.

Set up the Operating Model

Now that you have exported the data, you’ll need to tell the model how to pull it from the export going forward.

There are five major sections inside your Profit and Loss export: Income, Cost of Goods Sold, Expenses, Other Income and Other Expenses. You’ll need to copy and paste these categories — without the actual numbers or the totals — to the operating model.

Start by copying Income “Section” in the Profit and Loss export. Make sure to exclude the “Total” category.

Start by painting the category and all its’ subcategories, and paste it to the Operating Model in the same section. I usually like to use “Paste Values” to preserve formatting.

Paste the categories of the copied section to the corresponding section in the Operating Model.

Repeat for each category, and pay extra attention not to include any line item with a “Total” in front of it, otherwise you end up counting the expense twice.

For the Baremetrics export, we have already told the model what belongs where by pasting everything using the pre-set format available in the csv export.

Set up Payroll

Next, move to the Payroll Model worksheet and add each of your employees with their role, annual salary, benefits, and whether they are an employee or a contractor. Also, add start dates and end dates if they are in the future. While roles currently include Support, Sales, Founder, and Engineering, you can certainly add more if needed.

Add your current payroll and a future hiring plan.

Map each role and contractor back to the Operating Model from the dropdown below. Doing this tells the model which of the Profit and Loss line-items each payroll category goes to.

Mapping compensation components back to the Profit and Loss

The dropdown gets a little clunky, but you should find each of your Profit and Loss Categories to choose from. Note that you can “push” multiple roles and Compensation Components to the same line item.

Lastly, scroll to the bottom of the payroll page to make sure the assumptions are to your liking.

Begin Forecasting

At last, we can begin forecasting. Go to the Operating model, and start selecting the type of forecast you’re going to do for each line item from the “Input Type” column. The purpose of this is to tell the model if it should pull a forecast from another worksheet, use previous month’s figures, or do something else.

Currently, there are two input types pulling from another worksheet — Revenue Model and Payroll Model. Start by changing your primary revenue line-item’s type to Revenue Model, followed by changing each payroll category to Payroll Model. The first is probably something along the lines of Subscription Revenue, and the latter categories you got familiar with already when mapping out your payroll categories.

Note: If the revenue seems all messed up or results in errors, skip to the Revenue Model section and come back here once finished. Unless you know what you’re doing, make sure to select only one Revenue Model as an Input Type.

Select the type of forecast from Input Type column

For line-items other than revenue and payroll, I’ve included pre-fills for % of Sales, and Continue Last Month. The first one is useful for forecasting something not quite as demanding that requires a separate worksheet, but that still scales up with your revenue. Hosting costs are a good example. Continuing last month’s figure is a good idea when forecasting something simple and static such as near-term rent.

You can — and probably should — add some custom forecasting as well. I prefer to color code these with blue text and blue background, to clearly point out that there’s a forecast that’s not pulling from a model or a prefill.

Example of a custom forecast for rent increase in April 2017

Revenue Model

As David Skok points out in his SaaS 2.0 Metrics — the “Bible” — there are three things that really matter when running a SaaS business:

  1. Acquiring customers
  2. Retaining customers
  3. Monetizing your customers

Let’s start with #1 — new customers.

In the Revenue Model worksheet, we’re pulling your historical number of new customers and new revenue from the Baremetrics Revenue Export. This gives us the Average Revenue Per User (ARPU) for each month of historicals, which we can then use as a driver in our future forecasts. Another driver is naturally the number of new customers.

Baremetrics export pulls in historical data semi-automatically (Jan — Mar figures on blue), after which you can focus on setting the assumptions (blue on blue background)

I like to start by filling in something flat but realistic, and come back to fine-tune things once everything is built out and done. Your Baremetrics exports pre-fills your historical data, which should give you enough context to get started.

Next, let’s continue with #2 — the dreaded churn. As you can see, the formula I’m using for forecasting churn is slightly different. Even though we still pull the number of churned customers and dollars churned, we calculate both churn ARPU and churn %. Now, you could use the actual number of customers churned for your forecasts, but I’ve opted to use a %-based approach as it’s more common.

Instead of using a number of customers churned, we’re opting to use churn % array.

Lastly, repeat this with #3 — Monetizing your customers, or expansion revenue.

Next, take a quick look at the line “Recurring Revenue, Monthly Only.” Here you should see your forecasts, along with your existing monthly revenue, courtesy of your Baremetrics export.

If your customers are mostly monthly subscribers, that’s it! Your first pass of revenue forecast is done. If you have completed the previous section on mapping your revenue line-item to the Revenue Model, your revenue will be automatically flowing into the rest of the model.

Now, it’s worth repeating that the above model works only if your customers are mostly monthly recurring customers. If the majority of your customers are annual customers, you should build a near-copy of the above revenue model, where the revenue is recurring annually instead of monthly.

However, if you are like many early-stage SaaS companies I’ve seen, you have some annual clients but not enough to forecast with any degree of accuracy. In this case, I often like to include renewals from existing annual plans, and ignore any new annual additions altogether. Excluding all annual plans would ultimately understate your cash forecasts, as you would constantly bring in more than your forecast.

Monthly renewals for your annual plans are automatically pulled from the Baremetrics Forecast Export — all you need to do is to discount it with your best guess for annual churn. (After all, at this stage you probably won’t have an accurate picture of what the actual annual % churn would look like).

This results in your company’s revenue forecast!

Dashboard & Scenarios

At this point, it’s time to take a step back. Most founders we work with prefer to start their monthly review by looking at the big picture on the Dashboard worksheet, before diving deeper into individual forecast models and major expenses.

By now, you have a good understanding how the model works, so you can start to play with the assumptions and see how the effect on your business going forward. Use the table below in the Dashboard worksheet to create differing scenarios. Unless you modified the revenue model inputs in the previous section of the post, this table will pre-fill the input arrays for you.

The Base-Case column uses your last month’s figures, and you can start to insert numbers better than these for the Optimistic scenario, and worse for the Fallback / Pessimistic scenario. Typically, you’d want to plan for

  • What if revenue grows slower
  • What if expenses grow faster
  • Both

Once done, you can start to play with the “Scenario” dropdown to see the effects of your changes.

The resulting dashboard gives you a high level visibility to your business. You need to know what your revenues are vs. total cash outflow (which here I call Gross Burn), as well as how your runway looks in the coming months.

Given that we’re still working with drivers that don’t change over time, you might want to improve the accuracy of the model after a month or two of tracking results. At that point, go inside the Revenue Model worksheet, and start inserting the following formula to any forecast arrays:

Syntax: =CHOOSE(Scenario, Optimistic, Base-Case, Fallback)
Example for new monthly customers, April: =Choose(Scenario, Previous Month + 5, Previous Month + 2, Previous Month +1)
Example for Churn %, April: =Choose(Scenario, 3%, 4%, 6%)

If you end up using the array-based forecasting, use the dashboard real estate as a section of “What We Need to Believe?” In other words, instead of pre-filling your array results here, pull here averages and trends for your forecasts. For example, “Churn starts at 4% a month and declines to 2.5% by the end of the year.”

Summary

If you got this far, you don’t need convincing that the ability to glimpse into the future is a must for every growing business. It’s likely that none of your forecasts are 100% accurate — in fact, some of them won’t even be close — but you need a financial model to better understand which scenario you’re trending towards.

My goal is that the model we just built enables you to look at the most up-to-date plan to help you with your next steps forward as a company.


If you’re interested in having some work with your company to build a custom financial model and help you update it regularly, we’d love to hear from you at Flightpath!