Building Waterfall Charts for Financial Analysis

Hello! Welcome to the final lesson in our module on Core Financial Charting in Excel.

In our previous lesson, we dove into the world of stock trading by creating candlestick charts to visualize daily price movements. We focused on how to represent Open, High, Low, and Close data to understand market sentiment for a single asset.

Today, we shift our focus from tracking a single price to understanding how a value changes from a starting point to an end point. Our goal is to create waterfall charts to illustrate sequential changes in financial values, such as profit and loss statements. This type of chart is incredibly powerful for telling a story about financial performance, making it a favorite in corporate finance and investment analysis.

1. What is a Waterfall Chart?

A waterfall chart, also known as a bridge chart, is a visualization that shows a running total as values are sequentially added or subtracted. It's perfect for explaining how an initial value (like last year's profit) is affected by a series of positive and negative factors (like revenue growth and increased costs) to arrive at a final value (this year's profit).

The key features are:

• Start and End Bars: These are typically grounded on the horizontal axis and show the initial and final values.
• Floating Columns: These "float" in between the start and end bars, showing the positive (increase) and negative (decrease) changes.
• Connector Lines: These are thin lines that visually connect the columns to show the sequential flow.

To get a quick and clear overview, let's start with a short video.

As you can see, waterfall charts excel at breaking down complex financial statements or variance reports into an intuitive visual story. This is highly relevant for your interest in analyzing financial markets, as they are frequently used in equity research reports and company presentations to explain performance.

2. Building a Waterfall Chart in Excel

Fortunately, creating a waterfall chart in modern versions of Excel (2016 and later) is straightforward because it's a built-in chart type. The process is much simpler than the manual workarounds required in older versions.

The process involves two main stages: inserting the chart and then configuring it to correctly interpret your data.

Step 1: Data Preparation and Chart Insertion

First, you need to structure your data. All you need is a simple two-column table: one for the labels (e.g., "Revenue," "COGS," "Net Profit") and one for the corresponding values. Note that for expenses or decreases, you'll use negative numbers.

Item	Value
Starting Value	100
Change 1	20
Change 2	-15
Change 3	30
Ending Value	135

Once your data is ready, you simply:

1. Select your data range.
2. Go to the Insert tab.
3. In the Charts group, click the icon for Waterfall, Funnel, Stock... charts.
4. Select Waterfall.

Step 2: Setting the Totals (The Crucial Step!)

When you first insert the chart, Excel will treat every value as a change (an increase or decrease). It won't automatically know that your first and last values are totals. You need to tell Excel this manually.

This is the most important step to get right.

As a summary of the key formatting steps:

1. Double-click on a bar that should be a total (like your starting value, "Revenue," or ending value, "Net Profit"). This will select just that single bar.
2. Right-click on the selected bar and choose Set as Total. The bar will now be "grounded" to the axis.
3. Repeat this for any other start, end, or subtotal columns.
4. You can then format the colors for "Increase," "Decrease," and "Total" by right-clicking the corresponding entry in the legend or by right-clicking a bar of that type.

Test your understanding!

You've created a waterfall chart to show the change in a company's cash balance over a year. Your data includes "Beginning Cash," several positive and negative cash flows, and "Ending Cash." When you insert the chart, the "Ending Cash" bar is shown as a floating green bar. What do you need to do to fix this?

Show answer

You need to double-click the "Ending Cash" bar to select it individually, then right-click and select "Set as Total." You would do the same for the "Beginning Cash" bar.

3. Creating a "Nice" Waterfall Chart

A default chart gets the job done, but to create truly "nice" and professional-looking financial visualizations, you need to pay attention to the details. This is where you can make your charts look like they came from a top-tier investment bank report.

Techniques include:

• Custom Number Formats: Adding "+" signs to positive numbers.
• Color & Opacity: Using more subtle or branded color palettes.
• Spacing: Adjusting the gap width between columns.
• Annotations: Adding vertical lines or custom text boxes for clarity.

These advanced formatting tips are what separate a basic chart from a polished, insightful visualization that commands attention.

Conclusion

Congratulations on completing the final lesson of this module! You've now added another essential financial chart to your toolkit.

Key Takeaways:

• Waterfall charts are ideal for showing how a series of positive and negative values contribute to a final result.
• They are commonly used in finance to visualize Profit & Loss statements, variance analysis, and changes in balance sheet items.
• The most critical step in creating a waterfall chart in modern Excel is to "Set as Total" for your starting, ending, and any intermediate subtotal bars.
• Advanced formatting, such as custom labels, refined colors, and strategic annotations, is key to creating professional-quality financial visualizations.

In our next lesson, we will begin Module 3: Introduction to Power BI for Financial Analysis. We will be moving beyond Excel to a more powerful, dedicated business intelligence tool. We'll start by getting familiar with the Power BI interface and seeing how it can help us build even more dynamic and interactive financial dashboards.