A Step-by-step Guide to Building an Accurate Financial Model

A Financial Model is a Compass, Not a Crystal Ball

As an investor and advisor to early-stage companies, nearly every financial model I’ve seen from new ventures has shown exponential growth at some stage. Assumptions around customer acquisition costs trend down, Average Revenue per User (ARPU) trends up, costs are forecasted to grow more slowly than revenues, sales cycles shorten, prices rise, and revenues explode.

In 12-18 months, most companies are growing so fast that they will either (a) raise capital to fund the massive growth opportunity they’ve proven out, or (b) pull back on the marketing spend and harvest the free cash flow that results.

Similarly, the COVID-19 models being used by governments around the world produced forecasts considerably disconnected from the results. This shouldn’t be a surprise given the vast uncertainty inherent in a novel virus. This is meant to be a realistic—not cynical—assessment. All models are wrong by design, but there’s a six-stage process for maximizing their accuracy over time.

Stage 1: The Hatchet

The first step in the financial modeling process is to push back on every assumption:

• Why are your customer acquisition costs comparable to the billion-dollar competitor you are trying to take down?
• Do you really expect to go from cold call to sale in four months? What is your experience selling seven-figure contracts to Fortune 500 enterprises?
• How are all your sales hires going to be productive from day 1?
• You have nothing in the budget for customer success yet still assume extremely low churn - why?
• Is your product sufficiently differentiated from your main competitors that you can charge 50% more?

I work with a lot of SaaS (Software as a Service) companies, and three of the major Key Performance Indicators (KPIs) we focus on are Customer Acquisition Cost (CAC), Customer Churn, and Customer Lifetime Value (LTV). Each of those is driven by other assumptions: CAC is a function of how much you spend on marketing and promotion, how many people are clicking on your ads, how many people are taking action on those ads, and how many of them are converting to free and ultimately paid customers, and when.

Before making any business decisions, all of the assumptions are assigned to KPI families. The CAC KPI includes the marketing funnel assumptions but also sales assumptions. How many prospects can your salespeople manage at one time? What is the close rate? How long does the typical sale take? Only after we find sufficient support for all of those assumptions do we move on to implementing the business plan.

This step can be equated to the COVID-19 pandemic we’re living through now. Those models are also built around assumptions. One is R0, which is the assumed rate at which the SARS2 CoV-19 virus naturally spreads through a susceptible population. Other assumptions are around the impact of COVID-19 after infection.

Preliminary investigations conducted by the Chinese authorities have found no clear evidence of human-to-human transmission of the novel #coronavirus (2019-nCoV) identified in #Wuhan, #China🇨🇳. pic.twitter.com/Fnl5P877VG

— World Health Organization (WHO) (@WHO) January 14, 2020

None of those assumptions were solidly understood from the outset, and, ideally, Western health officials challenged all of them before making their final decisions to mandate the various forms of social distancing.

Stage 2: The Known Unknowns

The less certain variables in a financial model are the Known Unknowns. When I work with clients to figure out the assumptions to focus most closely on, we get in a room with a whiteboard (or a Zoom room with a blank Google document) and sort them all by degree of certainty as well as importance within the model.

For a consumer SaaS company, conversion rate to sale (Conversion Rate) is a very important assumption. One of my clients is a healthcare app - they have a free product, a paid subscription product, and they earn commissions for referring customers to service providers. They assumed that 50 of every 1,000 people who sign up for the free app eventually upgrade to paid subscribers. This was based on recent data but could have also been based on the experience of competitors or their previous experience with another company. Every assumption in a model is the result of a team’s view that those particular assumptions make the most sense with the information available at the time.

Since we knew that the Conversion Rate rate has a large impact on sales growth and profit margins, and since we were less certain of our assumptions around that rate, this variable was our prime focus. If you’re running a consumer SaaS business with a relatively high volume of sales and a short sales cycle, you can see these results fairly quickly. When enough data comes in, we can either be satisfied that our assumptions were accurate or see there is a problem to be addressed because we overestimated the Conversion Rate.

The real question that we’re trying to sort out, because we’re getting anecdotal reports, is can you transmit it efficiently when you are asymptomatic. Anthony S. Fauci, M.D., NIAID Director (February 19, 2020)

Similarly, public health officials knew several of their assumptions were uncertain at the time: R0, symptomatic rate, hospitalization rate, hospital capacity, fatality rate, etc. R0, for instance, is more complicated than it sounds. A good R0 assumption needed to take all the behavioral changes into account, which varied in application and effectiveness based on the characteristics of a region (e.g., urban: congested New York City vs. a rural town in South Dakota). The Effective Reproduction Number, Re, is used to describe the virus reproduction rate under various circumstances and at different times.

Both my SaaS clients and the public health officials must fully focus on achieving a greater understanding of the key variables in the model. Once they know what hard data to focus on, they move to the next step: Measurement.

Stage 3: Measurement

During Measurement, we will take a deep dive into the Conversion Rate assumption: Are people clicking on the ads and downloading the app at around the rate we anticipated? At what rate are they converting to paid customers? How long are they taking to convert to paid customers and how long are they staying on once they upgrade?

The issue now with this is that there’s a lot of unknowns... You know that, in the beginning, we were not sure if there were asymptomatic infections, which would make it a much broader outbreak than what we’re seeing. Now we know for sure that there are. Anthony S. Fauci, M.D., NIAID Director (January 31, 2020)

You can see this process playing out with the COVID saga as well. Early in the pandemic, experts like the WHO were estimating fatality rates for the virus at 3.4% worldwide, a very scary number considering the flu is estimated to be somewhere around 0.12% (but with a range of estimates around that figure). That was the number being reported in the press; behind the scenes, the models likely incorporated a range of possibilities. But it is pretty clear that the estimates were anchored around a higher figure than appears to be probable given current data.

With current data, it seems clear that the percent of persons infected with the virus that causes the symptoms we understand as COVID is much higher. Given that higher denominator, the fatality rate is necessarily lower, likely much closer to the 0.12% than the original 3.4% figure. It turns out that the models we relied on skewed pessimistic rather than optimistic, and we have had to adjust our estimates of hospitalizations and mortality down:

This is certainly a much better problem to have than the reverse and partially due to a prudent bias toward caution. Now what?

Stage 4: Tinkering

Right now, world political leaders are working with scientists to recalibrate their models. They are tinkering with which behavior-altering policy levers to use or relax. Some of the assumptions will be the same across the world, but many are unique to a certain region or environment.

With my clients, I go through every assumption within every KPI to understand how well-calibrated the model is. When an assumption is not matching reality, we brainstorm together to figure out why. Is the value proposition less than we thought and are we making it clear to the prospective customer? Is the product priced correctly? Do we have a call to action that catalyzes their buying decision? Have we made the sign-up process as frictionless as possible?

We may try a few different approaches to see if one works better at improving the underperforming metrics. Sometimes, there is an easy fix, but usually, we have to tinker a bit to get to build confidence in a particular path. Software companies use a process called A/B testing: You divide your targets into closely comparable groups and try one approach on the A group and another on the B group to see if either produces consistently superior results.

We must now consider all patients (including trauma call types) to be infected and all persons (even coworkers and family members) to have been exposed due to community spread. Memo to FDNY EMS staff (April 4, 2020)

While few A/B tests are likely occurring on policies to see which work best, there is a range of approaches being taken by different countries and different jurisdictions within countries that can be nearly as illuminating. Both municipalities and medical professionals throughout the world are also constantly tinkering with their approach to this disease, based on both feedback from their own patients and published research around how other patients reacted to certain treatments.

Stage 5: Recalibration

This is the stage where the rubber meets the road. You have identified your KPIs, measured them versus your prediction, identified those that are off, pinpointed the assumptions driving them, and tested certain hypotheses around how to improve them.

Governments around the world are in the process of recalibrating. Most are implementing plans for a phased return to normalcy driven by progress on rate of spread, hospital and testing capacity, and general community preparedness. You can bet that the results will be closely monitored.

In both businesses and public health, this stage is decision time - pick a path and push forward. Don’t just consider the payoff of success but also the probability and the cost of failure in terms of time and resources. Identify metrics for success, and when you expect to see the results, manifest. If your chosen path requires a significant change to your processes, assign ownership. This will prepare you for the final and most important stage…

Stage 6: Go Back to Step 2

There is no Stage 6 - do not pass Go, just go directly back to Stage 2. Every successful business in the world is regularly identifying, measuring, tinkering, and recalibrating their assumptions. CEOs should meet with their finance teams at least once a quarter to go over all the business KPIs and prioritize what needs to be improved upon. Creative solutions should be arrived at as a team, but ultimate responsibility should be assigned to the relevant business leaders. Those leaders should create a culture of constantly identifying, measuring, tinkering, recalibrating, and ultimately forging ahead.