- What is a Waterfall Chart?
- Variants of the waterfall chart
- How does a waterfall chart work?
- Where can waterfall charts be used?
- Best practices for waterfall charts
- Pros and Cons of Waterfall Charts
- How to create a waterfall chart in Excel?
- Learning Resources
Waterfall charts are used to show how an initial value is increased and decreased by a series of intermediate values, leading to a final value. A waterfall chart is widely used in the Finance sector to exhibit how a net value is arrived at by breaking down the aggregate effect of positive and negative contributions.
Let's take a simple example to understand things better. The simplest example would be an inventory audit of men's t-shirts in a retail outlet. You need to find out how many salable t-shirts you have in hand to start the next month with. Typically, there will be some units in stock to start the month with. During the course of the t-shirt being on display and various people trying it out, some of the units will get damaged. Some of these damaged units could be refurbished to add to the stock, and finally, we arrive at the number of salable units.
So, in this waterfall chart (also called the bridge chart), the initial value of "Units in stock" goes through a series of ups and downs, one up and one down, to be precise, to get to the final value of Salable Units. This is also referred to as Waterfall Reporting.
What Is A Waterfall Chart?Waterfall charts, also known as bridge charts or cascade charts, are used to showcase the impact the initial value creates on intermediate values. The result could be positive or negative, and with the help of waterfall charts, one can analyze the data consecutively.
It can display all the traffic sources, leads, and blog views over a given period. This chart is the best possible visual solution to see the monthly increase and decrease in the data values. Although different charts can be used to showcase one’s data, this chart has an advantage over them all. They have the benefit of showing gains impacted by losses over time.
Variants Of The Waterfall ChartWaterfall charts come in a number of variants. While these variants might not do justice to the universally accepted waterfall definition, but they come in very handy in some situations.
Let's consider the situation in which you need to plot your company's annual profit. So you will show your various sources of revenue, add them up, and deduct all your costs to arrive at the total profit (or loss).
Another minor variant of the waterfall chart (also called the cascade chart) is possible, wherein you show intermediate sums along the way before showing the final cumulative sum. In the example above, we could show intermediate sums for total revenue and total costs before showing the total profit.
The intermediate sum becomes very useful when there is a lot of data to sum up before the final value in the chart. For example, instead of Product Revenue if we were to show revenues from Web-based products and Desktop-based products separately (price waterfall charts); and similarly Customization Service and Support instead of Services as a whole, the Total Revenue sum would have been particularly useful over there.
How Does A Waterfall Chart Work?Waterfall charts are a powerful visualization tool in business and finance to illustrate the cumulative effect of consecutively introduced positive or negative values. It represents how an initial value is impacted by a succession of intermediate positive or negative values, leading to an outcome. Here's a breakdown of how waterfall charts work:
Initiating Point: The Starting ValueAn initial value or baseline serves as the starting point at the beginning of this chart. This could represent anything, for example, the total revenue for a company, the project budget, or any other significant metric. This starting point is typically displayed as the first bar on the chart.
Positive and Negative Changes: Intermediate StepsFollowing the starting point, the chart showcases intermediate steps, each represented by a bar. These steps can be positive or negative, indicating increases or decreases in the measured value. Positive values extend upwards, while negative values extend downwards. These intermediate steps demonstrate the impact of various factors on the overall value.
Cumulative Effect: Building the WaterfallEach bar's length visually accumulates or subtracts from the initial value as you move across the chart. The cumulative effect is displayed by the running total at each step, helping viewers understand the overall impact of individual components on the final result. This cumulative nature makes it easy to identify which factors contribute the most to the overall change.
Final Result: Endpoint of the WaterfallThe last bar on the waterfall charts represents the final result or endpoint of the sequence of changes. This could be the net profit, total project cost, or other relevant metric. These charts provide a comprehensive and intuitive understanding of the factors influencing the outcome by visually connecting the starting point to the endpoint through the cumulative effect of intermediate steps.
Where Can Waterfall Charts Be Used?Cascade Charts bring a lot of benefits of waterfalls to people. Now that you have got a hang of the waterfall chart, let's take a look at some other places where the waterfall chart can be used effectively.
- How many contracts do you have in hand at the start of a year? If you are from the construction or the legal department, you will surely identify with this. Start off with the number of contracts carried forward from the previous year, add to that new contracts you earned during that year, and then reduce the contracts canceled and the tasks accomplished. Finally, you reach the number of contracts in hand at the end of the year.
- How much did you score on an exam? So you took the GRE. Wouldn't you like to see how many questions you answered correctly in the verbal section and how many you answered wrong? Ditto for the quantitative section as well. And you add the two of them up to get to your final score.
Best Practices For Waterfall ChartsFollowing is the list of a few best practices that individuals can make full use of to optimize their waterfall charts:
Use Full Bars At The First and LastThe total at both ends of the chart should be full columns, starting from the left and finishing at the right end. Bars that are in the middle are called the floating Mario-style blocks.
ColorsColors play a vital role in the graphs for ease of crafting them and for avoiding confusion. There are predominantly two schools of thought when it comes to water charts. Negative figures should be of a different color than the positive ones. Another thought is that colors should be chosen to indicate factors. Whatever point you choose, make sure the numbers are more straightforward to understand.
No Need to Start The Scale At ZeroMost charts start at zero, but the case is different in waterfall charts. For example, if a company had 120 employees at the end of the year, it might be less than that at the start of the year. So, the initial point will begin from that numeric value.
Pros And Cons Of Waterfall ChartsBefore deciding on whether or not to make use of waterfall charts, let's learn about their pros and cons:
Advantages of Waterfall ChartsIt is effective to display gradual changes in the quantitative value of something over time It uses a clear structure
Demonstrates ChangesThe purpose of this chart is to show changes in something over time. This is quite effective at performing tasks.
Provides Micro-storiesWaterfall charts can showcase a story that is way more compelling than what other types of charts offer.
Easier to UnderstandThe western part of the world reads the content from left to right, and waterfall charts understand this fact quite well. The first bar in the chart provides a visual comparison that fetches the reader's attention at once, helping them understand the whole chart at a glance.
See more examples of the waterfall chart here
Drawbacks of Waterfall ChartsWhile these charts are effective in many scenarios, they do have some drawbacks that users should be aware of:
Simplification of ComplexityOne of the disadvantages of waterfall charts lies in their inherent simplicity. While this simplicity aids clarity, it can also oversimplify complex datasets with numerous interdependencies and intricate relationships between data points. This chart can only capture part of the underlying information. Users might need to comprehend the true intricacies of the data.
Limited Forecasting CapabilityWaterfall charts primarily showcase historical data and the cumulative impact of sequential changes. There may be better choices for scenarios that require forecasting or predicting future trends. In dynamic environments with crucial future projections, other visualizations like trendlines or predictive models might be more suitable. These charts are retrospective, emphasizing past events rather than predicting future outcomes.
Inability to Handle Parallel ProcessesThese charts represent sequential processes, where each step builds upon the previous one. However, they are less effective in illustrating parallel processes or multiple factors influencing the outcome simultaneously. In cases where concurrent and interrelated elements affect the final result, waterfall charts may need help to provide a comprehensive overview. Alternative visualization methods, such as parallel coordinate plots or multi-axis charts, may be more suitable for depicting concurrent complex relationships.
How to Create a Waterfall Chart in Excel?Here are several steps on how to create a waterfall chart in Excel:
Step 1: Prepare Your DataThe first step for creating this chart in Excel is to prepare your data. You must ensure that your data is organized to facilitate the creation of the chart. It involves having a column for categories and another column for values. The first and the last values will show the starting and ending points, or the intermediate values will show the changes.
Step 2: Insert a Waterfall ChartThe second step is to insert the waterfall chart; for this, you'll need to select the data range. Then, go to the "Insert" tab on the Ribbon.
In the charts group, select "Waterfall" from the Chart options. The correct location might vary depending on your Excel version. Choose the "Waterfall Charts" type.
Step 3: Set the TotalsThe third step is to set the totals. Once the chart is inserted, you need to set the starting and ending totals to appear correctly. For this, you need to click on the chart and see a floating menu on the right. Then, click on the small drop-down arrow to access the "Chart Elements" menu.
Check the box for "Chart Title" and "Data Labels." This will help you see the starting and ending values on your chart. Then, click on the starting and ending bars of the bridge chart, and in the formula bar, enter the actual starting and ending values.
Step 4: Customize Your Waterfall ChartClick on different elements of the chart to select them. For example, click on the bars, axis labels, or legend. After this, right-click to access the context menu to choose different formatting options. You can also customize the colors of individual bars to represent positive or negative changes more clearly.
Adjust the axis labels, legend, and other formatting options to make your chart visually appealing and easily understood. Remember that the specific options and steps might vary slightly depending on your Excel version.
ConclusionWaterfall charts are powerful visual tools used in finance and business to represent the cumulative effect of sequential changes on an initial value. They showcase how increases and decreases impact the outcome, providing a clear understanding of contributing factors.
Their advantages include effectively displaying gradual changes, demonstrating micro-stories, and being easily grasped. However, comparisons can be challenging without labels.
Creating waterfall charts in Excel involves data preparation, inserting the chart, setting totals, and customization. With their strengths and overcoming limitations, these charts can effectively communicate complex data.
- Learn how to make a Waterfall Chart/Cascade Chart/ Bridge Graph on Excel
Frequently Asked Questions
What are waterfall charts used for?Waterfall charts are used to visually explain how an initial value gets transformed by a series of positive and negative changes, ultimately reaching a final value. They're great for showing things like how profit is calculated, how inventory changes over time, or how a website gets traffic.
When should you use a waterfall chart instead of another type of chart?A Waterfall chart is a good choice if you want to emphasize the step-by-step process of reaching a final value and show how individual factors contribute along the way. It's particularly helpful when you have both increases and decreases. For simple comparisons, a bar chart might be sufficient.
Where can you learn about creating waterfall charts?Many data visualization tools offer tutorials and templates for creating waterfall charts. Also, you can find online resources and articles with examples and best practices.
How do waterfall charts work?Imagine starting with a pool of water (your initial value). Waterfall charts add or remove water (positive or negative changes) using steps of different heights and colors. Each step shows the impact of a specific factor, and the final water level is your ending value.
Do waterfall charts have any drawbacks?Yes, they can get cluttered if there are too many steps or details. Make sure to keep it focused on the key factors and use clear labeling. Also, they may not be the best choice for massive datasets.