Financial Waterfall Chart Guide: How to Create Different Waterfall Charts and Models

Strategic finance functions strike a perfect balance between two overarching responsibilities — presenting what happened in the past in a clear, digestible way for stakeholders and forecasting plans for the future in a way that surfaces growth opportunities.

Financial waterfall charts and models are critical tools for both of these responsibilities. But building them can often be difficult and time consuming.

You shouldn’t have to spend so much of your time chasing waterfalls. Streamline the process (or avoid it altogether) by skipping the complicated formulas and formatting challenges so you can focus more of your time on strategic tasks that drive business growth.

What Is a Waterfall Chart?

A financial waterfall chart, sometimes referred to as a cascade chart, is one tool for visualizing the positive and negative changes between a starting value and an ending value.

Regardless of use case, the left-most column in a waterfall chart is the starting value, the right-most column is the ending value, and each of the floating columns in between shows how positive and negative values impact the ending balance.

The floating values in between are typically segmented into more granular categories to help you understand the different components that are driving the changes over time.

Waterfall charts are most often used in financial reporting to provide a high-level overview of how KPIs changed over a certain period of time (monthly, quarterly, or annually). The standard waterfall format applies to a number of use cases, including ARR growth analysis, cash burn analysis, and sales pipeline analysis.

How to Create a Waterfall Chart

The process to create a financial waterfall chart is essentially the same regardless of use case. If you’re building in a spreadsheet, there are options in both Excel and Sheets to create a “Stacked Column Chart” that will help you format the stages of the waterfall based on the data table you create.

Take a standard ARR bridge as an example. To build the waterfall chart, you need to build a table with sections for Starting ARR, New ARR, Expansion ARR, Churn ARR and Ending ARR. Then, you’d create columns for Base, Fall, Rise, and Change values. It’ll look like this:

Then, you add a stacked column chart or bar chart to the spreadsheet and list the data ranges as your different table columns. Set your X-axis to break out according to the left-most column and add different series for the Base, Fall, Rise, and Change columns. The Y-axis represents the total dollar amounts from your table. (Check out the specific use case chapters of this guide for more in-depth overviews of building the data table itself.)

An Excel waterfall chart gives you options to update formatting according to business needs. Customize your waterfall with the following variables:

• Chart titles
• Format data series as necessary
• Connector lines
• Section headers
• Options like adding running totals to bars
• Data labels
• Formatting for negative numbers

In the example above, the horizontal axis breaks up into the changes in ARR and the vertical axis displays the cumulative total ARR. You can see that the starting point is $1 million in ARR. Positive values for new deals, reactivations, and upsells then increase the total. And negative values for churn and downsells result in the ending value of $1.25 million.

Financial Waterfall Charts vs. Models

The primary difference between a waterfall chart and a waterfall model is that charts are used as a visual reporting tool to present historical performance and/or forward-looking projections from a model. Waterfall models are used to build those forward-looking forecasts that are often incorporated into waterfall charts for reporting purposes.

Whereas creating waterfall charts is an exercise in creating visual presentations of data, waterfall modeling is an exercise in generating the forecasts themselves, using complex formulas that predict how different groups and categories will perform over time. Example use cases include modeling sales rep ramp, bookings to revenue, bookings to cash, and customer retention.

There are three general types of cohort waterfall models that you can build:

• Cohort. Present months of the forecast period across the columns and monthly cohorts down the rows. This type of waterfall shows how each monthly cohort performs over time, creating a cascading visual effect across time as you layer in more cohorts down the rows.
• Spread. A spread waterfall works exactly the same way as a cohort waterfall, with one difference: A spread waterfall only calculates totals for a defined period of time and then stops (i.e. 12 months for a 12-month contract), whereas cohort waterfalls continue in perpetuity.
• Aging. Similar in nature to a cohort or spread, but aging waterfalls pivot the data in a slightly different view. An aging waterfall displays months down the rows and shows how much all cohorts combined produce in each of those months. For example, the Month 1 row in a sales rep ramp aging waterfall model would show the total of how much all new reps produced in their first month.

The type of forecast model you build will depend on the use case. But the cohort waterfall method is the most common — especially for top-line planning use cases.

How to Create a Waterfall Model

The process to create a waterfall model involves building complex formulas to a financial model to add cohorted data sets. Different use cases will require different steps and formulas, which you can find in the other chapters of this guide.

But this quick video shows how to think through the process of creating a cohort waterfall model (albeit without the complex steps you go through in spreadsheets):

If you’re looking to streamline the spreadsheet-based process, we have two financial waterfall model templates available:

• Bookings to revenue waterfall model template
• Bookings to cash waterfall model template

The Benefits of Using Waterfall Charts and Models

The benefits of using waterfall charts and models are that they allow you to analyze performance trends in different and more granular ways. This lets finance and business leaders discover insights that they wouldn’t have seen otherwise, and arms them with a new perspective on how to optimize business performance.

Cohorting data for forecasts and visualizing with standard financial waterfall charts helps you add more strategic value to the business.

While these benefits make creating waterfall charts and models worth the effort, there is a point of diminishing returns. If the time it takes to build the chart or model leaves you analyzing stale data, the insights will fall flat for the rest of the business.

The Problem with Building Waterfall Charts and Models in Excel

There’s a common refrain among modern finance leaders regarding spreadsheets — that even though spreadsheets are powerful, flexible tools for finance processes, they come with their fair share of headaches.

Sal Abdulla said as much on an episode of The Role Forward. “Spreadsheets are great for analysis, they are great as ad hoc tools,” he said. “But they’re not great as databases and they’re not great as integration tools.”

So, while you might be able to get away with creating ad hoc financial waterfall charts month in and month out when someone is looking for a quick visualization, relying on spreadsheets gets much harder when you’re building complex waterfall models. In that case, you’re spending all your time pulling data from source systems, aggregating it into a model, and building out complex formulas instead of discovering the strategic insights that will drive the business forward.

But even if you’re just building out a simple waterfall chart, there’s the potential for issues in a spreadsheet. Your spreadsheet-based waterfalls are:

• Prone to human error. Any time you’re manually entering formulas and data into a spreadsheet, there’s a chance for typos and errors that compromise the numbers or, worse, break the model altogether.
• Poor collaboration tools. Business stakeholders may be able to understand a spreadsheet-based waterfall chart. But then what? When stakeholders can’t drill down into the numbers or leave comments easily, you’re missing out on opportunities to strengthen your partnerships across the business.
• Unable to meet real-time demands. The time it takes to aggregate the financial and operational data necessary to create spreadsheet-based waterfalls makes it almost impossible to keep pace with business demands. Automating data aggregation and working with real-time data are major reasons to avoid spreadsheets for waterfall charts and models.

None of these disadvantages of spreadsheets are new — they’ve been around as long as spreadsheets have existed. And they’ve certainly been around since waterfall chart formatting first arrived in Microsoft Excel 2016.

But just because you can build waterfall charts ad hoc in Excel doesn’t mean it’s still the best way to present financial data. A new wave of modern strategic finance software is making it easier to drill down into the numbers, quickly create visually-appealing charts, keep stakeholders aligned, and automate the waterfall modeling process.

Don’t Go Chasing Waterfalls — Use These Financial Waterfall Templates Instead

Stop spending hours or days per month building out multiple waterfalls for forecasting and data visualization. We put together five different financial waterfall templates to streamline your analysis and planning processes:

• Bookings to revenue waterfall model
• 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 dashboards give you 125+ out-of-the-box metrics and custom reporting features to easily show period-over-period changes and variances. And the Topline Planner includes out-of-the-box cohort waterfall modeling tools that completely automate some of the most complex waterfall modeling use cases.

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