How to Create a Sales Pipeline Waterfall Chart

There are few business partnerships more important than the one between finance and sales leaders. When these two sides are working together, the sales motion becomes more data driven, top-line plans become more aligned to business goals, and pipeline metrics become more predictable.

But there’s often a language barrier between finance and sales. While both sides want the same goal of driving top-line growth, sales leaders may not be well-versed in the kinds of spreadsheets, charts, and financial data sets that you live in on a daily basis.

One way you can visualize data in a more digestible way is with a sales pipeline waterfall chart. Here’s how you can build your own sales pipeline waterfall — and how to go beyond this basic analysis tool for more actionable, strategic insights.

What Is a Waterfall in Sales?

A sales pipeline waterfall chart is a type of data visualization that shows the difference between a starting pipeline value and ending pipeline value for a given period of time, broken down into different stages of positive and negative values.

In this type of bridge chart, the Y-axis represents the total ARR bookings value and the X-axis represents the different ways that pipeline value can increase or decrease in a given period.

As with any other waterfall chart or bridge chart, the goal here is to visualize the differences between a starting value and ending value. It shouldn’t be confused with other types of pipeline breakdowns that might show how total pipeline breaks out into different stages of the sales cycle (discovery, demo, price negotiation, legal, etc.).

What Does a Sales Pipeline Waterfall Chart Show?

Sales pipeline waterfall charts show the high-level overview of how potential bookings value moves into and out of your pipeline month-to-month or quarter-to-quarter.

The inputs and movements to track in a sales pipeline waterfall chart include:

• Starting pipeline. The total value of bookings in pipeline at the start of the period you’re analyzing.
• New pipeline. Bookings value added during the period to the pipeline either through inbound or outbound activity.
• Reopens. The bookings value of pipeline opportunities that were once closed-lost and are now reopened in the sales cycle.
• Expansions. Potential bookings value from upsells with existing customers either in renewal opportunities or mid-contract.
• Reductions. Opportunity value that has decreased given new context from sales reps.
• Moved out. Bookings value that is no longer in pipeline compared to the starting period but is not considered a closed-lost deal. These could be deals that were delayed and moved out of the period you’re analyzing.
• Lost. The bookings value lost from deals that didn’t end in a signed contract.
• Ending pipeline. The final value of bookings once all additions and deductions are made compared to the starting period.

How to Build a Sales Pipeline Waterfall Chart

The steps to build a sales pipeline waterfall chart aren’t necessarily complicated. However, the process of formatting data tables and charts can get tedious monthly and quarterly.

Skip the setup and formatting process altogether by downloading our sales pipeline waterfall template.

Or, take a look at the simple steps below to get started with building your own.

1. Create the Sales Pipeline Data Table

The first step to building any waterfall chart is creating a data table with all of your necessary inputs.

In the case of a sales pipeline waterfall chart, that means pulling all key data points from your CRM and adding rows for the key inputs listed above. The data table in our template looks like this:

The formulas for calculating the Base column are simple: previous Base value + Rise value – Fall value.

The biggest challenge here is having reliable pipeline data to pull. If your CRM setup is clean, you should be able to pull amount, close dates, weighted pipeline, and opportunity history fields to populate your waterfall chart data table. Building a waterfall chart becomes more complicated if your Salesforce instance or other CRM isn’t a reliable source of truth.

Want to get your CRM in better shape? Check out our complete guide to CRM hygiene for finance teams.

2. Create a Waterfall Chart or Stacked Column Chart

Use the organized data table to create the right chart type. If you’re in Excel, you can use the waterfall chart feature to streamline some of the formatting steps. In Sheets, a stacked column chart option can create a cascade chart that mimics waterfall visualizations, but a bar chart will work as well.

A few basic setup options you’ll have to address are:

• Data range. This should cover the entirety of your data table and break out across columns. In our example, the data range includes B2:B10, C2:C10, D2:D10, E2:10, and F2:F10.
• Combine ranges. Set this to “horizontally” to get the standard waterfall chart flow in place.
• Series. Create a series for each of your variable columns — Base, Fall, Rise, and Change.

These settings ensure you’re pulling the dataset from your table into the chart properly. Then, creating the waterfall chart is more about format customization.

3. Customize Formatting and Visualization Options

Once you have the data pulling into a stacked column chart/waterfall chart, there’s no real one-size-fits-all way to build the sales pipeline visualization.

Add data labels to make subtotals easier to read, change colors to match your branding, apply formatting options to specific series of data, and more. Adjust the settings until the chart is ready to present to your executive team or board.

How to Take Pipeline Analysis Further Than a Waterfall Chart

The waterfall chart is one of the most traditional ways to visualize pipeline data. But that doesn’t mean a sales pipeline chart will bring the most strategic value to your company.

Waterfall charts are good at conveying numbers in a digestible way for people outside of finance. But they stop short at the “what.” Adding more strategic value to sales leaders and your executive team requires a deeper understanding of the “why” behind your numbers.

Knowing the “why” behind pipeline data starts with opportunity-level insights as well as trended data that you can’t see out of the box with CRM reporting features. Beyond the basic Point A to Point B insight a waterfall chart can give you, you also want to be able to:

• Understand weighted pipeline amounts by sales rep to see team performance on an individual level.
• Get an account-by-account overview of stale pipeline, closed-lost, and closed-won deals.
• See bookings data by product line to better understand growth.
• View the top opportunities in the pipeline to help reps prioritize their time.

Your goal isn’t just to visualize your data with a waterfall chart — it’s to surface strategic insights from the numbers in a way that the entire business can understand. And a big part of that is the speed with which you can present key insights. Whether you’re building in Microsoft Excel, Google Sheets, or something more powerful like Power BI, speed is going to be an issue.

Mosaic integrates with your CRM and other source systems to help you go beyond traditional data visualizations and get drill-down visibility into real-time data. Here’s a quick preview of our pre-built pipeline analysis template.

Don’t Go Chasing Waterfalls — Start with Our Sales Pipeline Waterfall Template

If you still want to use waterfall charts to share sales pipeline data with your business partners, you shouldn’t have to waste hours every month updating your spreadsheets.

Download our spreadsheet-based sales pipeline waterfall chart to get a head start. And streamline your other waterfall use cases by downloading our other templates:

• Bookings to cash waterfall model
• Bookings to revenue waterfall model
• P&L waterfall chart
• Revenue bridge 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 take financial analysis much further than possible in spreadsheets.

Mosaic dashboards 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.

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