Financial Modeling: How to Model a SaaS Company with ClearFactr

By Dean Zarras

The testing potential financial results of Software as a Service ("SaaS") company are alluring: A product that is delivered directly to the user at a near-zero marginal cost. Nonetheless, in today's hyper-stimulating world of jam-packed app stores and incessant advertising, getting users to actually find the product, try it, and ultimately buy it remains the biggest challenge. How should one go about modeling something like this?

It's obvious that at some point, a growth rate applied to existing customers will likely overwhelm any marginal costs and lead to the promised land of "hockey stick" profitability. We can use several of the features in ClearFactr to estimate where that point in the future may lie. And as you'll see, the growth rate in customers will come from a combination of several forces:

  • A conversion rate, where people first exposed to the product actually sign up to use it.
  • A social factor, which influences the likelihood of conversion.
  • A growth factor for the number of third-party links that direct people to the product's website.

This post is a companion to the Template that all ClearFactr users have access to: "Simple SaaS Company", which is a 3-year quarterly plan. We're going to describe the template, and demonstrate the benefits of approaching the problem with some of ClearFactr's unique features. To best follow along, get into ClearFactr (sign up for free as needed) and click the 'Templates...' button on the Plans section of the Dashboard. Then find the "Simple SaaS Company" plan in the resulting Templates folder and open it.

Template Overview

The template contains three Tabs, each of which will be described in detail, below:

  1. 'Revenue': starts with the assumption that views of the product's web page drive one of several types of conversion events.
  2. 'Expenses': contains several sections describing various types of expenses, including compensation and infrastructure expenses.
  3. 'Summary': serves as an overview of some of the main messages of the Plan.

All the numbers are signed with expenditures being negatives and revenues being positives. This allows for always adding numbers within formulas, rather than possibly combining additions and subtractions (a common source of formula errors in spreadsheets).

We should of course note that this is simply one approach to this modeling problem. You may have a completely different approach. Logic9s encourages your feedback at the end of this post.

Revenue Tab

In this model we assume that the product has three pricing plan that might result from differing combinations of features and/or performance:

  • Bronze
  • Silver
  • Gold

There are also three broad sources of lead generation for page visitors:

  • Traditional "Digital Marketing" methods, which include the purchase of web advertising
  • Social Media methods, where a person is introduced to the product through a "friend"
  • Inbound links from third parties representing "buzz" about the product

The basic premise of the revenue model is as follows:

  1. A certain percentage of page visitors, regardless of source, will become trial customers, where they can use the product for free for some period of time.
  2. Of that group of people, three different conversion percentages allow for individual control over how many of those trial people will become paying customers.
  3. For a given period, a certain percentage of existing users will expose their "friends" to the product through social media or other means. This will lead to another round of exposures and conversions, per product tier.
  4. A 'Social Driven Conversion Advantage' allows us to leverage the three aforementioned conversion factors, but with the assumption that conversions are likely to be higher when the person has been exposed to the product from a trusted friend.
  5. A certain percentage of the total number of customers will purchase a support contract, which produces an additional revenue stream.

Items 1 and 2 are represented within the ' Marketing Driven Lead Generation' section. Items 3 and 4 are shown in the 'Social Driven Lead Generation' section. Both of these feed the Customer Acquisition Analysis and Customer Counts sections. The latter, combined with the 'Pricing' assumptions section drive the 'Revenue Distribution' section, which also includes the Support Contract revenue stream.

The numbers of customers will affect some of the items on the Expense tab, like the number of required support people and the number of cloud-based servers required to handle the various types of users.

Expenses Tab

As is typical with any product, there is a prolonged period of development where revenue is not possible. This template starts with the product and associated website being developed in parallel while various legal costs are also being incurred. The latter might include things like incorporation filings, establishment of various types of contracts and so on. The model allows for some patent expenditures as well.

The base-case of the template envisions a sole employee, a software developer, working on the product for some period of time while the website is being done externally. A second software developer is added as a marketing campaign is started to get the product in front of the target audience and to begin to generate buzz and sales leads. A salesperson is also added at that time. No attempts are made to account for sales commissions, which would be a good extension of the template and might want to be tied into equity of the company as well. Lastly, the staffing section models support personnel as a function of the number of customers having support contracts and an attribute controlling a support person's capacity for dealing with them, like this:

@max(@round(Revenue: Number of Support Contracts / Support Contract Capacity,0), 1)

Note that the 'Number of Support Contracts' is coming from the 'Revenue' tab. The 'Support Contract Capacity' allows you to model how many support contracts a given Support person can manage. Note the formula result is always one or greater.

Other expense assumptions are that office space is only acquired with the addition of the second employee, minimal web-site development expenses are ongoing, and there is an allowance for travel on a regular basis.

The Product Infrastructure section makes the sweeping assumption that the product runs entirely at Amazon Web Services and that different classes of servers will be used for each of the three different product offerings. Amazon has its own pricing calculator -- no attempt is made to replicate that. The various attributes in this section are designed to determine the number of servers required to support the customer load (again coming from the Revenue tab). Combining the number of servers with the pricing data per server yields the various server expenses. There are also allowances for external consulting work, and a variable per-user cost that covers additional infrastructure requirements. This might reflect the monthly marginal storage costs for any given user, for example.

An interesting development note, here -- as an earlier version of the template was being developed, with attributes being added and various relationships being wired up, the 'Highlight Terminal Cells' button revealed this:


Notice that these values are not connected to anything! These cells may be interesting, but ultimately they were not controlling anything in the plan. Some simple formula additions took care of that.

Summary Tab

This template's Summary tab serves as a convenient focal point for some of the main messages to the audience. We're highlighting:

  • The growth in users
  • Expenses in Quarterly and Accumulated terms. Individual quarterly values are useful to get a quick gauge on the monthly "cash burn". The Accumulated values allow us to quickly visualize the total capital required to support the business before it turns profitable.
  • Revenue in Quarterly and Accumulated terms.
  • Total Profit or Loss in Quarterly and Cumulative terms.

Using ClearFactr's one-click graphing feature, we can right-click any cell on the Cumulative Profit or Loss row and select 'Graph Attribute Over Plan.' That produces something like this:


Ensuring Referential Integrity

One of the biggest problems in developing a spreadsheet-based financial model is ensuring that the formulas are consistently applied through the model, or what we'll call "referential integrity." Dangling cells (like discussed above), or formulas that are inconsistently applied can be nearly impossible to find and can wreak havoc on the results, with potentially all kinds of nasty implications.

Using the Describe Plan feature, we can quickly verify that things are connected correctly. Here's an excerpt of that:


Any inconsistently applied formulas will pop out on this report as unexpected additional line items.

The ancestry highlighting feature was also instrumental in ensuring referential integrity as new formulas were being created and copied from place to place. In the picture below, we're tracing the parents and children of the Total Expenses value for 2015 Q1 -- the one with the dotted red border, i.e., the "current cell." At the far left, we have the level of ancestry set to 1. Notice that each of the section totals, and only those numbers, feed into the current cell. Moving to the right, we have the ancestry level at 3, which brings in various intermediate attributes. At level 5, all attributes of the tab have been accounted for, and notice that this month's Total Expenses affects the current month's plus the next four month's of Accumulated Expenses.


Modeling Considerations for the Simulator

ClearFactr's combination of the Self-Describing Spreadsheet and its built-in Monte Carlo simulator are designed to work in tandem to enable you to produce more realistic pictures of what may happen with your plan in the future. To get the best results, it makes sense to treat some attribute values essentially like constants (described as "fixed", below), and to allow others to be manipulated by the simulator ("floating"), with the results being displayed graphically in the simulator output.

Some other considerations for deciding the "fixed vs. floating" question are the following:

  • How realistic is it that the attribute in question will be fixed over time? Things like sales growth rates tend to be variable.
  • Is the value in question something artificial, done solely for modeling purposes, where having it vary over time might introduce unnecessary complexity?

With this in mind, plan attributes like the 'Growth Rate in Page Views' and 'Marketing Driven Visitors' on the Revenue Tab are good examples of things that will in reality change quarter to quarter (or whatever your plan's time unit is). Some quarters will be great, and others, not. The 'SD Conversion Advantage' is a more theoretical item, something one might debate conceptually. It's effectively a constant, since it can only be changed in one place (2015 Q1). All other quarters simply copy the prior quarter's value. Setting it to 1.00, removes its effect from the model (this says that "social" plays no special role in conversion).

Some Simulations and Scenarios

A typical scenario for this Template looks like the one below. We're varying two aspects of the plan on the Revenue tab that control the 'Total Page Views' of the site, the 'Growth Rate in Page Views' and 'Marketing Driven Visitors', and we're monitoring those effects to the 'Cumulative Profit or Loss' on the Summary Tab. The resulting graph looks like this:


Notice on the graph above that the crossover to accumulated break-even occurs at different points in time:

  • In the best case, it's Q4 of 2015
  • In the worst case, it's approximately one year later
  • In the median case, it's Q2 of 2016

The median case should be most interesting, as based on the chosen distributions for the input variables, this path is the most likely to actually happen. It is this combination of particular results with their associated probabilities that produce a better analysis, more insight, and a better dialog with your audience. This is described via the histograms that are available at each timepoint, like this, at 2016 Q4:


Drilling into those results, we can find the the "best of the best" scenario by using the Periodic tab on the summary results inspector. Moving to the 2016 Q4 timepoint, we can click on the #1 point on the "Best" curve and save the resulting values as a Scenario called "Best of Best." Repeating those steps also yielded the "Worst of Worst" and "Typical Case" scenarios as seen on via ClearFactr's Dashboard.

Special Case: Single Input vs. Single Output

When the simulator is configured to monitor a single attribute at a single moment in time as a function of a single input variable, it automatically produces a sensitivity graph. Here's an example:


Where to go next?

Like any Template in ClearFactr, this is only meant to be a jumpstart to your actual needs.

  • By taking a copy of the template, you'll be able to add additional tabs, sections and attributes, move things around, change the timepoints and change the formulas as needed.
  • You should consider adding a section for taxes.
  • You can add as many more attributes as you need to provide additional levels of detail. If you connect these to the existing section totals, things will flow through to the grand totals automatically.
  • A separate tab might be appropriate for valuation. The 'Company Valuation Demo' template provides a Discounted Cashflows approach, but there can certainly be others.
  • Hire more employees -- yes the hockey stick looks great, but the customer to employee ratio is getting up there!

Spending too much time worrying about spreadsheets...
and not enough about your business?