Ask a VC: how to model a P&L — Part 1/6: Revenue
Every VC will ask you at some point to have a look at your financial projections. Many entrepreneurs refer to it as a business plan, but this is incorrect: a business plan includes of course the financial plan (or projections), but also the marketing plan, the technical roadmap, the HR plan, etc. The business plan should exactly be this: a tool to help you, the CEO, plan your business. The financial projections are then just a tool among others to help you get there; and it’s like writing your budget for next year over several periods, and I like to use 5 years.
Another mistake that I see often, is when the CEO delegates the financial model to his co-founder, the CFO, or worse to an external advisor; or when they say I can’t discuss it myself, let me call my cofounder/advisor into the call/meeting. Big red flag. A financial model is not something you build for your investor: it’s a tool you build for yourself, to plan your business, understand how you are going to make money, write down your business assumptions, showcase your ambition, and structure your ideas. How can you claim to be the CEO if you don’t have this clear in your head, and can’t explain it to a third party easily? We all know that business plans and financial models are a sub-genre of science-fiction, but modeling them regularly, and keeping track of accuracy is like your morning yoga: it keeps your business on a healthy track of execution.
I also have often heard “You’re such a VC from Europe. In the USA they don’t require a financial model”. I don’t know about the American VCs, but someone who doesn’t want to share how he thinks about his business and the growth is not inspiring me confidence, and I ask myself immediately whether I should entrust his team with my money or the money of my investors (LPs)…
Luckily, you don’t have to have a degree in accounting to create a P&L (Profit & losses). And, you don’t have to make it 100% compatible with accounting standards, as what we care about is cash-flow projections, not accounting measurements. You can then of course ask your accounting firm to produce your accurate yearly statements a posteriori.
I often say that startup P&Ls are like war-time medicine: ugly, back-of-the-enveloppe, but they work well enough.
I’m going to base my model here on the widely accepted US GAAP (Generally Accepted Accounting Principles). These are the big items:
Revenues
- Direct Costs / Costs of Goods Sold
= Gross Margin
Fixed Costs :
- Sales & Marketing
- HR
- Technology
- G&A (General and Administration)
= EBITDA
(we’ll ignore Depreciation and Amortization)
Investments :
- CAPEX
(we’ll ignore Variation of WCR for now)
= FCF
I’ll discuss all this in 6 articles:
- In Part 1 here, I’ll show how to model revenue.
- In Part 2, I’ll show how to model direct (variable) costs.
- In Part 3, I’ll show how to model fixed costs.
- In Part 4, I’ll bring it all together, and calculate the J-Curve, therefore the need for cash, coming full circle with my article “how much to raise”.
- In Part 5, I’ll show you a quick trick, to check the consistency of a model, that can also be used for financial due diligence.
- In Part 6, I’ll go into some sensitivity analysis.
First how to build a model?
Open Excel. First, create 60 columns (12 months x 5 years). Make a header row (Month 1… Month 60).
Then add 5 columns before, and add headers (Year 1.. Year 5). Add for each year the sum of 12 months with =SUM(..). We are mainly going to read just these 5 columns when presenting but you need the 60 months to create the data. You will then be able to drag down this formula on these yearly columns. You can freeze the panes after Year 5, and under the headers.
I myself always use a yellow background for a variable (assumption we are testing, and blue for a constant, like VAT rate for example). Most t0 should be yellow. I format in italic rows what are just calculations.
I like to number the main rows and to create a S/T name for each section. Makes it easy to read. You can group rows together with the add command, and show/hide details easily like this.
The beauty of a model is that first you build a very simple one, and slowly you can start adding complexity. Often you can open a new sheet for the details of the calculation of each row, and just copy the results to the main sheet.
- Revenue.
It’s a simple formula: Revenue = Price x quantity. So add 2 rows.
If you know the price (one row) of what you are selling, just add it to the 60 rows. If there is a seasonality like in summer, make sure you adjust for that. One way to make this even simpler to do is to add 3 columns before the year columns. On the first column add the start value (header t0), and add a growth factor on the 2nd column (like 1%). Then in the 60 columns, add a formula like t+1=t * (1+ growth) with the first t=t0. This will create a line (y=ax+b), so that you can increase the price regularly each month. Don’t forget to “block” the value in t0, etc. with the column preceded by a $.
This is a technique you can adjust to whatever start month you want. Or decide to grow in steps, like every year add a % increase (like inflation); this will create a step-like curve. It is easy to make a step curve in a model, by using the modulo formula =MOD(). It usually is a function of another value like the number of users, servers, salespeople, volume discounts, etc.
The idea is that you don’t modify the values in the 60 columns once you’ve “coded” the formula”, and only change the first t0 value, and growth value. Remember this is a model, you are trying to approximate reality, not describe it accurately. You can always adjust any value later.
You can make this slightly more accurate/complicated, by adding a third column (acceleration). Create the same formula as for growth in the row below, using growth for t0. This will adjust the growth rate each month. Basically, this creates a 2nd degree equation, and a curve that goes up then down, or down then up. For example, if you first start with low prices, then increase, and when you reach critical mass, you can decrease your prices. Again this is a technique to model values for 60 columns in a very fast way, playing only with t0, growth, and acceleration. You should graph this line to see how it changes in real time and allow you to play with assumptions. In my example here, the price starts at 20€, and increases slowly over 2 years to plateau around 50€. Quick and dirty, but effective.
Format Revenue and price to your favorite currency (just format the Row). Quantity should be formatted to numbers with no decimals.
Now quantity. You can use either a linear growth curve or a parabolic (growing faster) using the acceleration technique.
As this is a quantity, you should not have decimals, so add a =ROUND(x;0) in the formula. Drag the formula to month 60.
Now you only need to multiply quantity x Unit price to get revenue for that month. And automatically per year once you copy down the sum() formulas here. Science fiction is beautiful no? Don’t forget to add data labels next to the histogram on the graph.
In this model until the end, I’ll ignore the impact of VAT (for EU countries). All prices in the revenue section should be without tax. We can model them (optional) after the model is finished, with the variation of WCR.
You should move the graphs to the relevant sheets to double-check check consistency of the model. Here we see that revenue growth is slowing down; not what you want to show a VC. Obviously in my model, it’s because the price growth has stopped at around 50€, and the quantity of sales is not growing fast enough. Here is where you see the beauty of a model. Modify the values in the yellow cells and you can adjust the model to what you want to achieve, or to your ambition.
—
I consult with corporates and startups and help them address issues like this and many more. Don’t hesitate to reach out at www.rodrigosepulveda.com or book a video call with me on intro.co.
