How to Create a SaaS Revenue Waterfall Model with Cohorts

Any planning process typically comes with the same cycle of tasks for finance teams — data aggregation and manipulation, hours of spreadsheet wrangling, and time spent tweaking formulas and models to accommodate stakeholder requests.

Those issues only amplify when you start adding cohort waterfalls to your models. While cohort waterfalls can give you a deeper understanding of performance drivers over time, building those waterfalls is a heavy lift.

One of the best examples is a SaaS revenue waterfall model. Adding a SaaS revenue waterfall to your ARR snowball model makes the bookings to revenue forecast clearer and more insightful — but it requires precise modeling and complex formulas to get it right. This often adds time and frustration to the process, which can leave you skipping the effort altogether and leaving valuable insights on the table as a result.

If you want to make your revenue forecasting process as accurate and insightful as possible, you need to be able to create and maintain a SaaS revenue waterfall that accurately reflects the business. And you need to be able to do it fast. Here’s what it takes to get it done.

What Is a SaaS Revenue Waterfall?

A SaaS revenue waterfall, also referred to as a bookings to revenue waterfall, is a model that shows how booked revenue turns into GAAP revenue according to accounting recognition rules.

These types of models use time-based cohorts to show how total bookings (including new, expansion, renewal, downgrade, and churn bookings) translate into total GAAP revenue by period. You can see the cohort months down the left-most column in the example below.

The resulting view adds more nuance to your revenue forecast, helping you understand how top-line growth will impact fully-recognized revenue in the months, quarters, and years ahead.

SaaS Revenue Waterfall Model vs. Deferred Revenue Waterfall vs. Revenue Waterfall Chart

SaaS revenue waterfall model, deferred revenue waterfall model, and revenue waterfall chart sound similar but actually have different meanings to finance teams. It’s important to recognize the differences as you use these tools for analyzing and optimizing revenue, retention, billing, and cash flow.

Whereas a SaaS bookings to revenue waterfall model uses cohorting to show how bookings turns into GAAP revenue, a more basic B2C or B2B revenue waterfall chart shows how a starting bookings balance turns into a final bookings balance after accounting for new, expansion, renewal, churn, and downgrades. You can see an example below.

A revenue waterfall chart, or an ARR bridge for SaaS companies, is a common way for CFOs and other finance leaders to visualize top-line growth for business partners. But it doesn’t show revenue recognition the way a SaaS waterfall model does.

In certain tools, you can make up for the limitations of an ARR bridge with a deferred revenue waterfall report. These reports are typically used by finance teams to compare the recognition schedule to the deferred revenue balance period over period. While it’s not a common tool for planning purposes, understanding your deferred revenue liabilities is critical to proper balance sheet forecasting.

How to Create a SaaS Bookings to Revenue Waterfall Model

The cohort modeling necessary to create a SaaS revenue waterfall and other types of waterfalls is among the most complicated modeling use cases for any finance team. Even if you’re an Excel master, calculations for cohort modeling are difficult to set up, hard to view for anyone outside of finance, and hard to troubleshoot and maintain after you’ve built the model.

Even though cohort modeling pushes Excel and Sheets to limits of their functionality, spreadsheets are still often the de facto tool for this kind of revenue forecasting.

If you’re ready to get out of spreadsheets and streamline the SaaS bookings to revenue waterfall modeling process, check out how you can tackle this complex use case in 3 minutes using Mosaic. (Reach out for a demo if you want to see it live.)

Whether you’re using a tool like Mosaic or using spreadsheets, there are a few key inputs that go into a SaaS revenue waterfall:

• New ARR bookings forecast. An accurate forecast of total bookings from new business, whether that’s through a sales capacity model, an ARR snowball, or other top-line planning technique.
• Bookings from retention and churn. Bookings from renewals, upsells, expansion, churn, and retention. These combine with new ARR bookings to give a total bookings number per period.
• Revenue recognition schedule. A revenue recognition schedule that outlines how you recognize that booked ARR over time. For example, if you sell one-year contracts, your revenue recognition schedule is typically going to be 8.33% per month for 12 months.

The real challenge when turning these inputs into a SaaS revenue waterfall in a spreadsheet is getting the SUMIF formulas right. With the revenue recognition schedule above, your SUMIF should look like this:

SUMIF(total bookings row, forecast period row, cohort month) / 12

This formula would turn total bookings (ACV) for one month into 12 equal periods of recognized revenue across a cohort row. It should look like this:

You’d then repeat this formula format for each cohort month for the entire forecast period.

One important note is that you typically wouldn’t build a SaaS revenue waterfall in a silo. It’s a common addition to top-line models, which means there are additional considerations to make sure you pull forecasted bookings numbers properly.

Don’t Go Chasing Waterfalls — Use our SaaS Revenue Waterfall Template

Don’t spend hours (or days) every month trying to recreate the waterfall model you need to translate ARR bookings into a recognized revenue forecast.

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

• Bookings to cash 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 reporting and analytics tools give you 125+ out-of-the-box SaaS 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 includes cohort model methods that can completely automate the most complex waterfall modeling use cases.

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