Intro
Cash Flow Modeling: one of the more cryptic and misunderstood concepts in Financial Modeling.
It’s time to simplify this whole exercise, once and for all.
Let’s dive in…
Can I be Direct for a Second?
Forget accounting, forget the matching principle, forget the indirect method.
We’re going direct.
I stare at my calendar and ask myself, “what days will cash come in, and what days will cash go out?”
That’s the fundamental driver here. Timing and specific amounts.
To keep it simple, let’s start with a personal cash flow forecast (this is the best way to learn):
I’m just punching in numbers. There’s nothing fancy here.
We start with our personal lives because it’s so much more relatable.
Instead of “Receivables” and “Payables,” you’ve got:
- Paychecks
- Rent
- Utilities
- Groceries
- Etc.
You’d list those items down the left side of the sheet (see image).
Along the top you list the dates.
👉Side note: In the image, I’m building a weekly forecast (which is typical), but very often I prefer to build it by day.
After all, I might have $10k going out on Monday and $20k coming in on Friday. My overall week looks good (+$10k net), but Tuesday through Thursday I could be hurting!
(you can always roll-up your daily model into a weekly summary)
Okay so now that I’ve got the basic layout, I just ask myself, “when will this stuff actually happen?”
And my model formulaically works like this:
- Cash I have in the bank (aka “Beginning Cash”)
- (+) Plus: cash I expect will come in
- (-) Minus: cash I expect will go out
- = Equal: cash I have leftover (aka “Ending Cash”)
This “BASE” or “Corkscrew” layout (which we’ve discussed before) gets rolled forward every week.
Upgrade to Business Class
Once you’ve got the personal understanding down, it’s much easier to approach cash flow modeling the business setting.
In the picture below I’ve definitely upgrading things a bit, but it operates under the same “BASE” structure.
The additions are:
- Using SUMIFS (or SUMPRODUCT) to calculate the past results (under the blue “Actual” section)
- Using sub-schedules to forecast the timing of my Receivables and Payables (the green font)
The rest of it is pretty much the same: manual estimates (guesses) as to when cash will come in or out of the business.
Could I use fancier formulas for each line? Sure. And I’ve done that a bunch of times.
But sometimes we let the tool over-complicate the analysis.
For example, look at the “Rent” line in the image.
In a more “robust” model, I would probably build a formula that says “IF(the month end dates are different, then show $5,000 (driven by an input cell), else show $0).”
But, I don’t need that to accomplish my objective. I can also take 7 seconds and just punch the numbers in.
The goal here isn’t perfect formulas or automation. It’s estimating the ending cash. Finding the areas where we’ll be tight.
And then we start thinking:
- “Can we call up Customer X and have them pay sooner?”
- “Can we extend payment on Supplier Y?”
- “Perhaps we can look into switching when our credit card is due.”
- “We may need to talk to Investor about extending terms.”
Notice how everything above is about operations, and not finance.
The model serves as a playbook for our operations team, but kept as a scorecard in a finance format.
Action Item for You
Here’s what I’d suggest: build a cash flow forecast like this right now.
I can’t tell you how many times I’ve looked at a “turnaround situation,” and the very first thing needed is a cash flow forecast.
I’ve also heard tons of big corporate types tell me, “oh we don’t need that kind of analysis.” Well you don’t, until you do.
When I worked at Hilton (a company that does nearly $10 billion in Revenue), we had a cash flow forecast. So don’t flatter yourself. Get this process implemented.
It can save you from a major headache down the road when things don’t go as planned.
That’s it for today. See you next time.
— Chris
p.s., if you enjoyed this post, then please consider checking out my Financial Modeling Courses.
As featured by the Wharton Online Private Equity Certificate Program, Wall Street Prep, LinkedIn Learning (and more), you'll learn to build the exact models I use with Private Equity and FP&A teams around the world.
Thanks for reading.