Dr. Winston's Excel Tip: How to Use Waterfall Charts in Excel
In 2016 Excel added many new charts, including the Waterfall chart. Waterfall Charts are great for telling the story of how a quantity of interest (for example, cash position) changes over time. Waterfall charts also allow the finance professional to chart for a single point in time how a quantity (such as total revenue) “steps down” into a final quantity such as Before Tax Profit.
With Excel 2016, you can quickly create a Waterfall Chart that summarizes situations such as changes in cash flow position and the stepping down of revenue into profit. Our work is in the file Beckeroct19.xlsx. In the worksheet Cash Flow (see Figure 1) we are given a company’s beginning of the year cash position ($110,000), monthly changes in cash position, and the end of year cash position ($168,000.)
A Waterfall chart makes it easy to summarize the ebb and flow of the company’s cash position. To begin, select the rangeA1:B14. Then, from the Insert Tab choose the last chart icon in the first row and then choose the Waterfall Chart Icon (shown in Figure 2.)
You will now see the Waterfall Chart shown in Figure 3.
Note that positive cash flows are shown in blue and negative cash flows are shown in orange. The problem with this chart is that we would like the Opening and Closing Cash Positions to be in a different color (say, gray) and anchor the Closing Cash Position to 0. To accomplish this goal, first put the cursor on the Opening bar and click until the bar is selected. Then, right click and choose Set as Total. Then, the first bar will turn gray. In a similar fashion, we select the Closing Cash Position bar and choose Set as Total. The Closing Cash Position is now in gray and anchored to $0. Our final Waterfall Chart is shown in Figure 4.
The worksheet Profit and Loss (see Figure 5) and the worksheet Profit and Loss contain a Waterfall Chart that show how a company’s Revenue is reduced to Profit. The chart shows that COGS has much more impact on profit than SG and A costs.
In this chart, we chose to leave the first bar colored blue but we anchored the Gross Margin and Profit bars to $0 and changed their color to gray.
I hope you will utilize (now!) easy to create Waterfall charts to gain insights into your organization’s financial data.
Treemap and sunburst charts are also useful tools in Excel, helping you to visualize hierarchical data. Here’s how to summarize data with treemap and sunburst charts.