How to Build a Bookings to Cash Waterfall

Top-line growth is the flashy benchmark that every startup and mid-size company is judged by. But while hitting hypergrowth on the top line is great, having a deep understanding of how booked revenue will turn into cash is what will keep your business alive.

For SaaS businesses with subscription revenue, a strong bookings to cash waterfall model is crucial to accurately forecasting cash flow.

However, as high-growth companies evolve and become more complex, maintaining a bookings to cash waterfall gets increasingly difficult. Here’s how to build one with the flexibility to keep pace with business change.

What Is a Bookings to Cash Waterfall Model?

A bookings to cash waterfall model is a forecasting tool that shows how booked revenue turns into billings and, eventually, cash collections.

To bridge the gap between bookings and cash collections, this type of waterfall model is actually a combination of two different waterfalls.

The first is a billings waterfall that applies your different invoicing assumptions to your bookings forecast. You can see part of an example below.

The second is a cash collections waterfall that calculates total cash inflow according to your payment term assumptions, as you see in the example below.

The combination of these two waterfalls results in a forecast for total cash collections by month over the course of the forecast period.

The Importance of a Bookings to Cash Waterfall Model

Like any cohort-based waterfall model, building and maintaining a bookings to cash waterfall can be time consuming and complex. You’re dealing with a large quantity of interconnected formulas and pushing the structural limits of spreadsheets.

But getting a cohorted view of how your booked revenue turns into cash collections is worth the effort. When you get this type of waterfall model right, you unlock a number of benefits.

• Optimize cash flow. Having a detailed view of bookings to cash movement helps you identify opportunities to optimize cash flow. You’ll see exactly how much cash is coming in month to month and can proactively address issues and identify opportunities to accelerate collections.
• Test billings and collections policy changes. The process of nailing down billings and collections policies is often loaded with experimentation. A bookings to cash waterfall can help you sense-check planned policy changes by showing how they’ll ultimately impact cash flow.
• Run sensitivity analysis on billings assumptions. An effective bookings to cash waterfall allows you to pull levers for key assumptions and better understand which drivers your business is most sensitive to. You might find that changing from Net 30 to Net 60 payment terms has a marginal impact on the business, but charging more customers upfront has a massive impact.
• Show stakeholders your business is healthy and sustainable. A top-line forecast that includes the movement from bookings to cash collections will help you show executive stakeholders and investors that your business isn’t just growing — it’s doing so sustainably.

These benefits make building and maintaining a bookings to cash waterfall worth the time and energy. If you take the time to build the model with as much flexibility as possible, you’ll have an easier time maintaining it through each new planning cycle.

How to Build a Bookings to Cash Waterfall

If you want to skip the process of building a bookings to cash waterfall altogether, download our pre-built template so you can spend more time focusing on strategic tasks.

But if you’d rather build one from scratch, these are the high-level steps to do it.

1. Outline Your Billings and Collections Assumptions

Financial modeling starts with an understanding of the different drivers and assumptions that will make up the calculations — and the process of building a bookings to cash waterfall model is no different.

There are three main assumptions to outline in your waterfall model:

• Billing frequency. This determines h0w often you bill your customer over the life of the contract. The most common options are upfront, half-year, quarterly, and yearly. In your model, build out your billing schedule by assigning percentages in the corresponding months in your assumption table. Then assign a percentage split across your different billing frequency types to define your frequency mix.
• Payment terms. This dictates how many days after the invoice is issued the customer is obligated to submit payment. The common payment terms are Net 30, Net 60, and Net 90. For example, Net 60 payment terms mean the customer is obligated to pay the invoice 60 days after receiving it. In your model, assign a percentage split across your different payment term types to define your payment term mix.
• Late billings. This takes into account whether bills are sent on time or not. Options could include “on time,” “30 days late,” and “60 days late.” In your model, assign a percentage split across your different late billings categories to define your late billings mix. If you want to assume that all billings are sent on time, you can set this assumption to 100% on time.

These assumptions drive the calculations for your billings and collections throughout the waterfall model.

2. List Out Your Total Bookings per Period

If you’re creating a standalone bookings to cash waterfall, you need to hardcode a row for total bookings per period. In this situation, you’d go through and manually enter bookings data from your CRM and forecasted bookings data from your top-line model.

The other option is to build the bookings to cash waterfall as part of a larger top-line financial model like an ARR snowball. When you build the model this way, you can pull historical and forecasted bookings data directly from the top-line model tab.

3. Create Formulas for Monthly Cohorts

For both the billings waterfall and collections waterfalls, you need to build a time-based cohort schedule that lists the months of the forecast period down the rows of your model. Depending on your billing terms, you’ll need upwards of four components to each monthly cohort, like in the example below.

The bookings column is where you start hitting some formula complexity.

In our template, we used a combination of IFERROR, SUMIFS, and VLOOKUPs to bring the billing frequency assumptions into the cohort waterfall. Each row uses the billing frequency assumptions described above to calculate a percentage of total bookings for each bucket of invoiced revenue.

The IFERROR function assures the necessary cells remain blank in the waterfall. Then the SUMIF pulls the proper bookings value and multiplies it by the correct billing frequency mix percentage, which is pulled with the VLOOKUP. In our example the formula looks like this:

IFERROR(SUMIFS($31:$31,$4:$4,C37)*vlookup(D37,$B$13:$N$16,columns($B$13:$N$13),0))

Repeat this cohort view for the collections waterfall as well.

4. Calculate Monthly Billings and Cash Collections

Once you have the cohorts set up properly, you can build out the formulas to calculate each period’s billings and cash collections according to your original assumptions.

In the billings waterfall, your formulas need to multiply the segmented bookings total by the “billing frequency schedule” and the variable for late billing. This cascades out for each monthly cohort.

The cash collections waterfall works similarly, but multiplies the corresponding billings value with the payment terms assumptions.

Don’t Go Chasing Waterfalls — Use our Bookings to Cash Waterfall Template

It could take you hours or even days to build out an effective bookings to cash waterfall model — and that’s just one piece of a larger revenue forecasting puzzle.

Download our spreadsheet-based template to get started faster. And streamline your other waterfall use cases by downloading our other templates:

• Bookings to revenue waterfall model
• Revenue bridge chart
• P&L waterfall chart
• Sales pipeline waterfall chart

But don’t stop short at just using a spreadsheet template. If you’re ready to unlock your true strategic value, Mosaic can help you go beyond spreadsheet-based waterfalls.

Mosaic’s analytics tools give you 125+ out-of-the-box metrics and KPIs as well as custom reporting features to easily show period-over-period changes and variances with real-time actuals. And the Topline Planner provides pre-built cohort waterfall model methods that completely automate these complex waterfall modeling calculations.

Ready for a demo of Mosaic? Reach out and schedule time for a personalized walkthrough.