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.
Dr. Wayne Winston is a Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in Mathematics from M.I.T. and a PhD in operations research from Yale. He won over 40 teaching awards at Indiana University. He has written over a dozen books including "Marketing Analytics", "Data Analysis and Decision Making", "Operations Research", "Practical Management Science", "Excel 2019 Data Analysis and Business Modeling", and "Mathletics." Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! Champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks.