Cash Flow Simulation vs. Spreadsheets

I have seen many spreadsheets used for modeling and projecting cashflows for early stage companies, and they are all very interesting. But one after another I keep finding errors, estimates without basis, and a failure to predict the real bottom and how much cash will be needed when.

Some years ago, I started to seek a simulation model for cash flows, and have been running various version of this approach since the early 1990s at least. The challenge in modeling and simulation is two-fold; (1) getting a good model, and (2) doing a good job of simulating it.

There are many open source and commercial simulation systems of various sorts, and the sort used for cash flow analysis usually involves an event driven model with an association of cash (in and out), likelihoods of transitions of various sorts, time frames associated with the transactions, and the ability to analyze one thing from others.

Spreadsheets are great for annotating and calculating the effect of one thing on another. For example, if I sell 3 boxes of items a day, and it takes $1.50/box to make and ship, then I can readily formulate this in a spreadsheet. But time in spreadsheets is harder to model, and typically involves a monthly and annual model where we capture a month of effort at a time, and ignore the delays associated with activities. As we start to codify delays, things get tricky, and the models start to break down for two reasons. (1) It's hard to get all the formulas right and (2) Changes tend to end up hard to understand so that you can never be sure that you still have a right answer.

Simulators are great for doing step by step at any desired granularity, and give realistic depictions of event sequences, but it's hard to do conditionals and back calculate things like when you have to manufacture something to get it there in time for the order to be filled. That's because it requires time going backwards, while in a spreadsheet you merely put the cost of manufacture a few months earlier and calculate it back from the future.

The Sales Sieve

I've discussed the sales sieve before, and there is even one of our videos describing it, so I don't want to go into details here. But as a basic notion, the idea of a sales sieve is to describe the sequence of events associated with making a sale, including timing, resources consumed, costs, how you go about it, sales volumes, amounts, and costs, etc.

We generally start with the sales sieve, even if you already have a spreadsheet. We do that because the spreadsheet almost always disagrees with the sieve once you get the sieve right, and then you can update the spreadsheet to match. One of the things we find quite often is that the spreadsheet under-estimates the growth that can be achieved for a given available amount of cash. That's because the sieve provides a roadmap to a turn-the-crank sales process, and as the process pays for itself, you can calculate when you can increase sales without needing more cash.

Adding overhead to the sieve

One of the problems with the sieve approach is that it's really unit economics with time layers in, and as such, things like overhead are hard to add in without explicitly figuring things out. We like our automation to figure things out for us, rather than us figuring things out and telling the automation about it so it displays right.

In growing companies and businesses within larger companies, overhead tends to grow with sales, among other things, because things like after-market services, management, R&D, and so forth, tend to follow from sales rather than always lead them. Before you start to sell things, you have to do these things in order to get to the point where you can sell, but after you are selling and growing, these overhead functions tend to grow and become part of the ore mature entity that gets formed for longer-term success.

Calculating this in some sort of automatic way is rare, and normally, the person building the model, just says that at some point, we add overhead. A more scientific approach would be to backtrack from the goals and determine when the overhead has to grow in order to meet the anticipated needs and maturation.

All this projection never survives contact with reality

Exactly so. If all you are doing it projections for the purpose of getting investments, you are wasting your time and mine. The point of projection is measurement and adaptation. The real deal is that as you project, you are setting out the things you will bneed to measure in order to see how you are doing. A better projection model means the projection might be closer to reality, but more importantly, when the projection and reality don't hit the same numbers, the model can tell you why, and if done right, can tell you what's going wrong before it gets too bad to fix.

As a good example, you may not be making the sales numbers you anticiapted. Yes, we know we need to up our game, but when will it become a real problem? When will we run out of cash? If we adapt orders in our suply chain or renegotiate a price, will that solve the problem? Maybe we have to delay entry into the next market, or maybe we need to move it up. All these can be understood by running different scnearios through the model and simulating the future results. And if you have the model and simulator in place, you can do this in a few minutes rather than a few hours or days, or worse yet, not knowing.

Models are just that. They are inherently imperfect. But as they are adapted over time, they tend to get better at modeling the envelope of anticiapted futures and the result envelopes of different decisions. The unanticipated still happens, but less often, and to less severe unanticipated consequences. Or at least that's the hope and objective of the effort.

Try it out?

Want to try some simulations? There are some free trial offers online. If you look up "free cash flow simulation software" you will likley find what I did. Some templates, a few spreadsheets and a few free trial offers for software. Most of them integrate with spreadsheets and are pretty much spreadsheet-like. In fact, that's just what they are. They aren't simulators at all. They are mostly just ways to input to a spreadsheet for cash flow analysis. Some are able to take simple information and project it forward, but none really allow you to model at a reasonably fine granularity... until you tell Google (or your search engine) not to ignore the word "simulation" in the search.

I did manage to find some actual cash flow simulation systems, including (apparently) Growthpath, Anylogic, and Silico.

All of these solutions, and all of the others I have found in the market, require that the user knows what they are doing, both in terms of the business and the model. The simple examples are usually not instructive to the specific needs of the company, and modeling for the needs of the executive depends on the executive, the business, and the tool being used.

A call to action

Want to learn more? We'd love to discuss it with you - in a follow-on call after your presentation at...

Go To Angel

In summary

Understanding your cash flow going forward is critical to making good projections and decisions, and then tracking and adapting your progress.

Copyright(c) Fred Cohen, 2021 - All Rights Reserved