Financial Forecasting When Modeling With Missing Data

The partner at my previous venture capital firm used to tell me that financial forecasting for startups should start at a granular level. Coming from a macro hedge fund background, I initially did not understand what that meant, as I was used to forecasting based on trends, recurring revenue, and benchmarks. But these are mostly unavailable for startups. Often, it is even unclear what the target market is, let alone the revenue that it can capture.

It is difficult to convince someone of something that isn’t there. Founders usually sleep on an idea for six or more months before approaching their first investor. By this point, they would have simulated more than a thousand different scenarios and models in their minds with a grand vision of what they will achieve in 10 years’ time—only to get frustrated that investors do not conclude on the same vision. They often forget that they need to handhold investors through the same journey that they have been through themselves, albeit via a shortcut, to reach the same grand vision. The only way to achieve that is through facts and logic.

It is true that investors do not have the time to look through too much detail in the first meeting. That’s why techniques are created to capture their attention - from an elevator pitch, a 15-minute teaser, to an hour-long presentation. Eventually, investors still need to scrutinize the plan to ensure that the founder’s vision is real and the underlying assumptions are plausible.

A financial forecast is like a map that leads investors to the end goal. Most forecasts fail because they automatically assume the ability to capture one or more percent of the market or a 100-percent monthly growth rate without detailing the strategies and assumptions that will get them there. A financial model for startups needs to be logical and plausible; it needs to align with strategy and it needs to be granular with no missing steps from points A to Z.

Granular Financial Forecasting Methods for Revenue

A granular financial forecast should start with the revenue, as it’s usually the most important and the most uncertain item to forecast. Like a business, a revenue forecast should start with the customer in mind. After identifying the target market, the startup will need to determine how it will acquire customers. There are generally three sources: sales, marketing, and organic.

Sales Team

The sales team is a direct channel that converts leads into customers. Key drivers for sales are the size of the sales team and average sales per month. Average sales could be broken down into qualified leads per month, a leads-to-sales conversion rate, and average sales cycle length. For example, if a startup had a sales team of three, 100 qualified leads generated a month per salesperson, a leads-to-sales conversion rate of 10 percent, and an average sales cycle of two months, the startup would generate 15 sales a month (3 * 100 * 10% *0.5). For some business models, leads are generated through marketing, which results in a two-staged acquisition process.

Example of a Two-staged Acquisition Process

Marketing Strategy

Marketing does not target customers as accurately as sales but is better at reaching a wider audience. Forecasting sales from marketing starts with identifying the marketing strategies (e.g., pay-per-click, social media, direct mail, billboards, referrals), the budget allocated to each strategy, and the cost per acquisition (CPA) for each strategy. For many digital marketers, CPA could also be expressed as the cost per thousand (CPT or CPM) impressions multiplied by the click-through rate (CTR) and the conversion rate (CVR).

For example, if a startup employs three marketing strategies—pay-per-click, social media, and referrals—with CPAs of $50, $80, and $100, respectively, and is allocating $10,000 to each strategy, it is expected to generate a total customer acquisition of 212.5 (10,000/50 + 10,000/80 + 10,000/10). Depending on the business model, these acquisitions could be paying customers or leads; if they are leads, the acquisitions are added to the qualified leads generated in the sales formula above. If the leads are added on top of the leads that the sales team originated, a check is required to ensure the sales team has enough capacity to handle these additional leads.

Forecasting Lead Generation Through Marketing Channels

Organic Sales

While sales and marketing strategies have actions that pull customers in, organic sales generally arise from customers discovering the business—accidentally or strategically planned. Examples of organic sales include footfall, word of mouth, SEO, and by being part of a marketplace. It is still possible to calculate the cost of acquisition for these channels, but the business usually does not have much control, other than setting up the infrastructure for organic sales to occur. To estimate organic sales, start with estimating the number of exposures multiplied by the conversion rate. For word of mouth, the exposures could be estimated based on the number of current active customers, the percentage of likely “referrals,” and the reach per referral.

Forecasting Organic Sales Conversion by Channel

Customer Value

After forecasting the number of sales, the next step is to calculate the average revenue that each customer will bring, aka the customer lifetime value (LTV). To determine the LTV, estimate the average purchase value, frequency of repeat purchases, and the churn rate (which is 1/customer lifespan). Customers are divided into three categories: new customers, repeat customers, and lost customers. For each period, the forecasted sales are multiplied by the average purchase value to arrive at new customer purchases. Repeat customers are calculated as the active customers from the last period multiplied by the average purchase frequency (e.g., once a month or 0.5 times a month). Lost customers are calculated by multiplying the churn rate by the sum of new customers and active customers from the last period. New customers + last period’s active customers - lost customers = the active customers for this period.

Steps for Calculating Customer Lifetime Value (LTV)

There are as many business models as there are businesses so the exact equation may not apply to every startup. Yet, the same logic applies to every business. A good place to start is to map out the customer journey and examine the strategy, cost, and revenue associated with each step. For a more realistic, albeit more complicated forecast, also consider the lag between conversions and initial marketing or organic exposures.

Run the resulting revenue forecast through a sniff test (no, not a diaphragm fluoroscopy) to make sure it makes sense. This is the typical top-down approach starting at the total market size (TAM) and narrowing it down to the serviceable available market (SAM). Obviously, the ability to capture 80 percent of a large market on day one is not reasonable, no matter how much money is thrown at marketing. Also, sales should not exceed the physical capacity of the business unless the possibility of capacity expansion is modeled in.

Granular Financial Forecasting Techniques for Costs

Forecasting cost is much easier compared to revenue. Cost should be a function of revenue; it is the total resources required for the business to generate and continue generating the forecasted revenue. Some costs are more direct (e.g., COGS) while others could be indirect (e.g., rental).

Cost of Goods Sold (COGS)

First, start with COGS, which is the cost directly related to the production, acquisition, or delivery of the company’s products or services. COGS could be variable or semi-fixed costs. Variable costs could be linked to total sales (e.g., transaction fees) or customers (e.g., account managers). Semi-fixed costs are loosely linked to sales or customers; these are costs that suffice for certain sales - or customers - but require an upgrade after a certain volume (e.g., a server upgrade to cope with more web traffic or the number of customer service reps).

Operating Expenses

Second, determine the operating expenses. Broadly speaking, the main categories are sales and marketing, general and administrative, and research and development expenses.

Sales and marketing costs should link to the sales and marketing budget and customer acquisition strategy, as defined in the sales forecast.

General and administrative costs are the business operating costs required to support a company of the forecasted size. This varies from business to business but some of the key questions are: What is the staffing requirement? How much are the office rent and associated costs? What would the IT infrastructure cost? How much are the insurance and professional fees? An experienced accountant should be able to answer these questions.

Research and development costs are costs incurred in the discovery of new knowledge and products.

Capital Expenditures

Finally, determine the capital expenditure required for the business. Not only would this significantly impact cash flow, but each capital expenditure would also have its own depreciation rate which would impact the profit and loss.

Most startups do not have financing cost, but if there is, it also needs to be added.

After forecasting the profit and loss of the company, the cash flow statement and balance sheet are simple extrapolations with a few additional assumptions. A finance expert would be able to extrapolate a three-statement model from the forecasts and assumptions above.

Although forecasting on a granular level results in more unknowns, rest assured, no investors expect startups to know precisely what their CAC, LTV, or other variables are. In fact, most investors are not interested in the accuracy of the revenue forecast; they know that it would not be. They are more interested in the thinking process and the ability to identify the underlying unknowns as they reveal whether the founders are logical, pragmatic, and understand their business. Methods to estimate these unknowns include analyzing past performances, conducting desktop research, sifting through prospectuses/annual reports of listed companies, and talking to other companies and marketing agencies in similar industries.

What Are the Benefits of a Granular Financial Forecast?

The revenue and cost forecast should be an iterative process for startups to come up with a feasible business model. A good financial model will help founders make key decisions about their operation and determine their funding requirements.

A granular financial forecast aligns the expectations of investors and founders. It shifts the discussion from what is achievable—a subjective matter—to strategy and its underlying assumptions. If the investors and founders disagree with an assumption, they just need to prove it with data or by conducting small experiments (e.g., A/B testing). The underlying assumptions, especially the ones that are most sensitive, also become the KPI for the management team so that they are clear about what needs to be achieved. If the revenue forecast did not eventuate, rather than arguing and blaming each other, it is possible to pinpoint the assumption that is out of line and focus on finding the root cause of the problem. If the assumptions turn out to be wrong, startups can quickly update the model with new assumptions to arrive at a new set of financial forecasts which will help them make rapid decisions as to whether to persevere or pivot the current business model.

So, while it takes longer to build a granular financial forecast, it saves a lot of time down the track and reduces conflicts between founders and investors.

Another benefit of a granular financial forecast is that it allows startups to test the sensitivity of various assumptions and find the ones that are most sensitive and critical for the business success—commonly known as the “leap of faith” assumptions. Startups can design small experiments or MVPs to test these assumptions to prove or disprove them. Startups that can prove these assumptions early on gain more credibility with investors and come across as being more pragmatic.

While it may seem like the financial model may be the last thing investors look at, it is also one of the most important documents as it helps to align investors and guide the startup to success. Founders should not be afraid of the extra effort it takes to conduct a granular financial forecast because it will save them time and pain in the future; sometimes, it is the key to the startups’ success because it helps them make rapid decisions based on facts. Furthermore, a good financial forecast will focus the founders on addressing key strategic questions such as sales and marketing strategy and resource allocation. These are important questions to answer for any startup. The financial model merely summarizes the strategy and the assumptions in a single document.