This post is directional to help someone get up to speed with the SaaS financial model template you can download at the bottom. Just know this is a really boring post unless you’re working with the model! Once you’re done with this post, continue to play with the numbers and really understand what’s happening in every cell. Feel free to reach out to me with any questions through the comments, twitter, or email. I can dive into more theory and the approach if asked.
It’s best to understand your high-level business model to talk intelligently about how you grow over the next 12-18 months and reach a Series A. For Seed stage startups and investors, it’s all about reaching Series A and having a real business to attempt scaling.
Finally, the real key to doing this right and preparing for an A round is to input the actuals in every month. Keep yourself honest with projections, understand how the business is trending, and show a healthy graph that extends from healthy actuals. This builds your investor collateral and puts you in an even better position to raise.
Before I walk through each of the 4 tabs in this model, a few items and tips:
- The blue cells are the only cells you should change. Anything white is a formula and is ultimately calculated from blue cells. More power to you if you want to change blue input cells into formulas! I’d suggest changing the color of those to white so you won’t forget they’re no longer inputs.
- The model can be extended infinitely and I’ll explain how to do that at the very end. For a pre-A startup, 12-18 months is the max you should project because that’s about how long it should take to reach Series A.
- You can use Excel’s ‘Trace Precedents’ or ‘Trace Dependents’ in the ‘Formulas’ tab to understand what that cell is tied to. This is key to understanding the calculations and what’s happening.
- Create graphs from the financial statements (or whatever rows you’re affecting by changing inputs) and place them under the inputs you’re working on. This is a much easier way to watch the changes as you make them instead of scrolling up/down or going from tab-to-tab.
Tab 1: Customer Segment Drivers
First, notice that there are three variables for every single input. Think through these three scenarios for every customer segment you populate because there is a key purpose to having these.
Start by renaming the workbook’s header on row 2. That will populate through to each tab. Rename each customer segment in row 4 to whatever you’d like.
Notice that row 7 allows you to switch between MRR (monthly recurring revenue) and ARR (annual recurring revenue). I’ll come back to this.
- Use cell C9 to rename the tracked metric that doesn’t actually control anything other than a metric you define. Think about it as the main qualifier you would explain to an investor when detailing a customer’s profile. This could be the avg. number of licenses you’re selling into each customer, the rows of data each customer uploads into your software, or the amount of revenue each customer does per year. Again, this doesn’t affect anything other than a metric to be tracked.
- Cells E10:E12 name your 3 scenarios to cycle through as variables. These only affect the words on this tab and are insignificant.
- Row 14: ACV (Annual Contract Value) will control revenue per customer and the variable on row 7 will decide whether they pay monthly (1) or yearly (2).
- Row 19: Sales Commission is calculated as a % of the ACV, put ‘0’ if you aren’t paying this out.
- Row 24: Implementation Revenue is a one-time fee in the first month you acquire a customer, put ‘0’ if you aren’t receiving this.
- Row 29: Operational Costs are also a % of the ACV and are charged monthly, regardless of MRR and ARR. Think of hosting and other support directly tied to the software you’re providing.
- Row 34: Renewal commission is also a % of the ACV and will be paid out with each renewal. I’ll discuss renewals and controlling the timing below. Put ‘0’ if you aren’t paying this out.
Tab 2: Model Drivers
This is where the magic happens. Once you’ve populated all of the drivers in the first tab you can basically forget about it and simply cycle through each scenario for each customer segment. More importantly, investors can do the same and see how you’re forecasting growth based on the three different scenarios without extra questions or building multiple models. This is why taking your time matters on the first tab and being thoughtful with those inputs.
If you want the variables to work together and change together, simply create an input somewhere that those blue cells equal. You can then put a ‘2’ and everything will go to that scenario.
The Customer Acquisition Box (Rows 4:33)
The dates are more important than you think! Cell G7 is the only input for the date. Enter the last day of the month you want to use (i.e. 8/31/2015) and it will populate the date through the entire workbook. Don’t send a model to someone with previous months having projections in them. You’ll potentially come off as lazy, or maybe even provide proof that you missed targets. Those should be filled with actuals or the date should be changed.
- Rows 14:17 are where you input the number of customers starting that month. If you want to create a formula to drive these, even better! Just remember to switch the color to white so you know they’re no longer inputs.
- Rows 8:11 are calculations that tell you how many new customers are starting that month. They mimic the blue inputs and help to drive the active customer numbers after churn. It seems redundant, but you can always hide this section if you only want the blue showing, or vice versa.
- Rows 20:23 work the same way as 14:17, with integers, except they’re subtracting from the active customer count.
- The rest of the ‘Customer Acquisition’ section is self explanatory.
The Customer Drivers Box (Rows 36:129)
To the left are small plus signs to expand each customer segment’s drivers. This will keep the workbook smaller to quickly look through. Remember my tip to use graphs while working with various inputs.
Start by expanding the first customer segment driver box at row 38. Here are those various scenarios you created on the ‘Customer Segment Drivers’ tab. Simply change the numbers in column D between 1, 2, and 3 to cycle through your scenarios. You’ll also see some very basic unit economics for each customer segment below the scenario inputs; customize those for whatever is most helpful and relevant. It’s identical for each segment.
The Revenue and Costs Box (Rows 132:166)
This is where the acquisition and customer drivers come together. Work through each section here (they’re all formulas, no inputs) and use the trace buttons I mentioned to understand what’s going on here.
The Renewals Box (Rows 170:198)
This is where you control how often your customers renew via cell F174. Put in the number of months to renewal and they’ll automatically renew (churn is accounted for like FIFO). It’ll also create a renewal commission if you’ve set that to more than 0% but not create more implementation revenue.
If you want customers paying for services every 6 months, you can do that. Set the ACV on tab 1 to whatever the 6 month contract value is, set the revenue structure on tab 1 to ‘2’ (ARR), and set the renewal to every 6 months. You can do this with any amount of time.
If you’re interested in the formulas that create the renewals, click the plus signs and trace the cells. If you don’t care and are happy with it happening, ignore those :)
The Sums for Financial Statements Box (Rows 201:257)
This is exactly what it sounds like: all of your totals that are used to populate the income statement. Run through all of this, use the arrows to trace what it’s summing from, and that’s the Model Drivers tab!
Tab 3: Headcount
This tab is straightforward. Input the title, name, and whatever into column E, the salary into column F, the month they’re starting in column G, and the year they’re starting in column H. Everything else will populate through from that.
If you need more fields to add headcount you have two options:
- Understand how I created those and create more
- Add multiple people in each field and multiply the salary by that headcount
Tab 4: Financial Statements
This is already an incredibly long post so I’m not going to dive into the income statement line-for-line. If it’s not automatically populated you can use the blue input box between the row label and numbers to populate the entire row. Change those formulas however you need to make the expense calculations work. The best way to understand this is to start throwing numbers in those blue cells and seeing how they work.
The Cash Flow box isn’t a true cash flow statement because it isn’t pulling from the balance sheet. I did my best to consolidate the balance sheet items and make something that is helpful to an early stage company, and this is what made sense. Input your starting cash position in cell G83. Cells G87:88 and G92:96 are simply starting inputs that populate through to each field. The exception is row 95, which is calculating using COGS from the income statement.
The key piece here is row 101 to input when your receive investment and not have an insolvent (read: no monies) business through your projections. I already wrote a post on thinking about this and how to work through that process so read Gunpowder in a Startup Financial Model and be a pirate.
Below that cash flow calculator is a small box that’s calculating burn with zero revenue, regardless of what you’re projecting.
Done! That’s that. The better you know and understand your model, the easier a lot of investor conversations will be. Not every angel or VC needs these or wants to deep-dive into them, but I think it’s a crucial exercise for any CEO. Upselling is a huge piece of growing revenue in a nonlinear way for SaaS and especially at the time of renewal. Investors also love understanding how you can upsell in the future and what it will take. Unfortunately I don’t have that in here for the sake of simplicity, but don’t forget that and let me know if you create a simple way to build that in!
How to Extend the Model’s Dates
It’s pretty simple to extend the model, but an early stage company should only be concerned with the next 12-18 months at a very high level. Anything beyond that is even more BS than 12 month projections, but sometimes it’s necessary for certain investors or angel groups.
Save before you try this!
Start with the Model Drivers tab and select the entire skinny column at the end of the model. If you opened up the downloadable file from this post, it’ll be column V. Insert columns to the left of this and always keep that skinny column. You’ll break calculations if you do not.
Highlight the last two populated columns (‘T’ and ‘U’ if it’s the original file) and click and drag those over through the new columns. Again, leave the skinny column blank. To click and drag, you will see a small, blue plus sign just under the letter in the top bar. Click and hold this while dragging to extend. You must grab the two populated columns; only grabbing one will create some errors when copying blank cells.
Now do that for the Headcount and Financial Statements tab and in that order. Everything should populate through properly, and you can do this for as far out as you wish.