Ask a VC: how to model a P&L — Part 6/6 — Sensitivity analysis
Note: the content of this post was taught in a 7-hour class at HEC Paris Business School in May 2024.
Now that you have built a sound P&L (part 1 — Revenue, Part 2 — Variable Costs, Part 3 — Fixed Costs), understood your cash needs (part 4) and checked the consistency of your model (part 5), you’re all set to go raise money?
Actually no, because your model is based on a very specific set of assumptions that you think are true or will be true, that gives you a specific result: the moment you will not need to consume more cash (amount and date).
What if something goes wrong? What if the sales you expected don’t materialize? What if your employees demand a higher salary? What if your sales explode, implying more working capital requirements (this means you need more cash to finance faster growth ?).
I use 3 methods to deal with this uncertainty.
Method 1: just add an amount.
The first way to deal with this uncertainty is to just add a buffer of money. A rule of thumb (based on ZERO reality) would be to round up your required venture money by a certain amount. Different techniques can be used:
- add 20–30% to your requirement. In part 4, I showed the model said I needed 1.073m€ to finance the business. let’s assume you raise 300K€ for safety in the model. That’s 1.3/1.073–1=21,2% more money.
Let’s assume you were raising 1.073m€ on a 4m€ pre-money valuation, the dilution for this round would be 1.073/(4+1.073) = 21,15%. But now if you raise the buffer amount, your dilution would be 1.3/(4+1.3) = 24,53%.
- another technique is to calculate your burn rate at the time of cash-flow breakeven, and add an arbitrary few months (between 3 and 6) of cash burn to the amount you are raising, just in case things go wrong.
You’d better be sure that this extra money is worth it and is not too expensive as it would be cheaper to raise more at a higher valuation later. Nevertheless, it’s a quick and dirty technique to have some buffer.
Method 2: scenario analysis
A better way would be to build 3 models for best-case, worst-case, and target-case situations (I’ve seen entrepreneurs build even more P&Ls, according to different scenarios related to different strategic choices in time). Ideally, reality will be somewhere in between the worst-case and best-case scenarios.
In all situations, you have to get to a J-curve on a worst-case scenario: if you keep bleeding money with no short-term (below 5 years) visibility for your cash consumption recovery, then your business is probably not VC-compatible.
The best way to do this is to make 2 new copies of your P&L, to work with 3 Excel files. Then just change the assumptions on each model. I would use your existing model as your target case.
Best case: now imagine if everything went wonderfully well. Imagine you get more market share. Imagine you can improve your pricing, or design a product mix where you move sales towards up-selling customers. See how these new sales impact your COGS. How they impact your cost structure (as an example, if you are hiring double the amount of people, that means a new office building, which will impact G&A). Maybe you will need more money to finance this business, or maybe you will reach cash break-even faster.
Worst case: imagine your product launch is delayed. Your sales start later. Demand is lower. Competition kicks in and you have to lower your sales targets. Maybe you need fewer people to serve your customers. Maybe you need less infrastructure. Everything else remaining equal, the intuition here is that you might hit cash break-even much later. Sometimes it is counter-intuitive, but you might reach profitability sooner but with a much smaller business.
The only way to know is to model the 3 scenarios. Then you can plot the 3 cash consumption lines (the 3 J-curves) easily by creating a 4th file, copying the data from the cash consumption line from each file into the 4th file, and plotting the resulting 3 lines.
This will show you the sensitivity in terms of cash needs, and the timeframes (at which month) you will hit the lowest point in cash in the company. This is much more accurate, and you can explain why you built each scenario. The truth will be somewhere in between unless an act of God hits.
You can go a step further, but this is usually reserved for investors trying to understand your business better and approximate it empirically. They would rebuild your 3 scenarios by assuming hypotheses such as:
- they believe your best case is more or less your target case, with a bit of upside (let’s say 80% of your target case and 20% of your best case).
- they believe your target case is overestimated and will build a mix of target and worst case (let’s say 70% and 30% respectively)
- and that your worst case is not gloomy enough. Let’s say they degrade sales to 75% of your worst case.
This will create new curves, that show a much dire picture of the future: with a longer time to cash breakeven, and a greater need for financing.
Of course, we’re stepping into science fiction here again, using arbitrary numbers, but it allows us to play with sensitivity and is a basis for discussion about contingency planning between the investor and the entrepreneur.
As you see here, the cash need is much higher (moving from 3.3m€ to 5.6m€), and cash breakeven moving from month 25 to month 55 (almost 3 years later!), and much different than the 2.3–2.7m € requirement shown by the entrepreneur in the previous chart.
Method 3: Monte Carlo simulation
There is a more elegant and more accurate way to predict a P&L, but only some people use it. It requires adding a software add-on to Excel. As it uses Visual Basic, it only works on Windows. For Mac users, you can install a Parallels virtual machine with Windows, Excel for Windows, and the Addon. It’s called Crystal Ball, and was acquired by Oracle many years ago. It is pricey (in the $1000 range), but will be extremely helpful in many situations. I used it for example in one of my startups many years ago to define the optimal level of traffic to acquire on the homepage of Yahoo!. Having too much traffic was a waste of money, and not enough was not effective enough.
How does a Monte Carlo simulation work? Let’s assume I want to calculate the number Pi (we all know it’s about 3,14). Let’s assume I create a square of 4 meters by 4 meters. The area of the square is side (2R) times itself = (2R)² = 4R². In this case 16 square meters. The area of a circle inscribed within the square would be Pi x R², R is the Radius of the circle.
If I put a certain number A of basketballs inside the square, I can count how many B basketballs are inside the circle. And therefore that A/B = 4R² / Pi x R², or said differently, that A/B= 4 / Pi, or that Pi=4 x B/A. I’ll get a certain amount. If I now repeat the exercise with tennis balls (smaller balls), the amounts A’ and B’ are more precise, and the result is more precise for Pi. I can do it again with golf balls, or marbles, and get even closer to Pi. Or with sand leveled exactly, and I can calculate a ratio of the weight, or with water.
The overall idea is that with each iteration of a small quantity, I can approximate reality better. It works the same for defining certain target numbers in a P&L.
Let’s assume I can model some key parameters with a distribution (triangular or normal). Example: I’m assuming my customer acquisition cost is 17€. But at times it could be as low as 13€ and as high as 25€. I can tell my model that I want a triangular distribution centered at 17 with boundaries 13 and 25.
And then I can run the model that will iterate a few thousand times (usually up to 5,000 is enough). It will use 5,000 different values for this CAC that match this triangular distribution, and of course, it will generate a distribution of 5,000 data points for the final result, for example, the lowest point in my J-curve. It usually always looks like a normal curve.
I can also model the quantity of customers acquired every month, maybe with a Poisson law, or a normal curve. Let’s assume the latter with a medium at 10,000 new subscribers per month, but with a standard deviation of 2000 (you remember that 1 standard deviation +/- the mean is 68% of reality, 2 standard deviations is 95%, and 3 standard deviations go to 99,7% right? ;) Of course, you need some real data to model these assumptions.
Then the model will run 5,000 times taking a value for new subscribers that will approximate the normal curve you specified above. Combined with the input distribution for CAC, the resulting normal curve will look very much like reality.
The mean of the resulting normal curve will be the lowest point of your J-curve, therefore the amount of money you need to raise.
Is it beyond this post to describe how to use Crystal ball, but you get the idea.
—
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.
