What is a waterfall chart?
A waterfall chart is a type of data visualization that helps to show the cumulative effect of sequentially introduced positive or negative values.
These charts are often used to depict how an initial value is influenced by a series of intermediate positive and negative values, leading to a final result.
Key Features
- Starting Point: The chart begins with an initial value.
- Intermediate Values: Bars representing intermediate values (both positive and negative) that affect the initial value.
- Subtotals: Points where the cumulative effect is calculated and displayed, often represented as columns with different formatting.
- Ending Point: The final value after all intermediate values have been applied.
Why is this chart so useful?
- Clarity: It visually breaks down the contribution of each component, making it easy to understand how different factors influence the total.
- Transparency: It clearly shows the individual impacts of various positive and negative values, providing transparency into the process or data being analyzed.
- Trend Analysis: It helps in identifying trends over a period of time, such as monthly or quarterly financial performance.
- Decision Making: It assists in making informed decisions by highlighting key areas of improvement or concern.
- Comparison: It allows for easy comparison of different scenarios or periods by showing the impact of sequential changes.
Now let’s dive into some practical application of the waterfall chart!
Scenario : Quarterly Financial Analysis
Suppose you have quarterly financial data for a company, including starting revenue, various expenses, earnings, and net income for each quarter as seen below. The goal is to create a waterfall chart to visualize the financial changes and derive insights across quarters.
Creating the waterfall chart in Excel
- Select the data range and go to ‘Insert’ tab and pick the waterfall option as below.
2. After tapping on the waterfall we get the following default plot. However, our intention is to compare and see trends in the net income across the quarters.
3. To achieve this, individually pick the net income bars and right click to ‘SET AS TOTAL’ as below:
Now we can easily see the components and their movements across Q1 / Q2 and derive some business insights as seen further.
What insights can be drawn?
Net income in Q2 was lower than in Q1 despite higher sales. This suggests that the increases in COGS and operating expenses outweighed the benefits from increased sales and other income.
Some more detailed insights can be drawn too across different parameters as illustrated below.
How does this drive decision-making?
By comparing the waterfall charts of Q1 and Q2, it’s clear that while revenue growth strategies were effective, managing costs and maintaining efficiency are critical for translating revenue growth into higher profits.
- Focus on Sales Growth: The increase in sales from Q1 to Q2 indicates that efforts to boost sales were effective. Continuing to invest in sales and marketing strategies that drive revenue growth is crucial.
- Cost Management: The rise in COGS and operating expenses in Q2 indicates a need for better cost management. Reviewing supplier contracts, improving production efficiency, and controlling operational costs could help maintain profit margins.
- Diversify Income Streams: The increase in other income in Q2 shows the benefits of diversifying revenue streams. Identifying and nurturing additional sources of income can provide a buffer against fluctuations in core business revenue.
- Balance Investment and Expense: While it is important to invest in growth, it is equally important to monitor expenses. Finding the right balance between investing in business growth and controlling costs is key to increasing net income.
Thus, we can clearly see how intuitive and informative a waterfall chart can be; and merely with a few clicks one can easily derive powerful insights!
