In Excel 2016, Microsoft introduced the waterfall chart, which shows how values increase and decrease. In this video, learn how to create this very useful type of chart.
- [Instructor] Business people, at least the successful ones, tended to be good with numbers. Of course it can be difficult to visualize cash flows if all you have to work with are the raw numbers. In Excel 2016, Microsoft introduced the waterfall chart, which shows how values increase and decrease. In this movie I'll show you how to create this very useful chart type. My sample file is the waterfall workbook and you can find it in the chapter three folder of the exercise files collection. This workbook contains an Excel table that has a summary of a company's income statement.
So here we have a description and an amount column, we have revenue, which we assume would be from sales, of $150,000. Then an expense for advertising, another for web services, and then SG and A, which is selling as well as general and administrative. Next we have an entry for operating income, which is 150,000 minus all of the advertising, web services, and SG and A expenses. So operating income is actually a total as opposed to a separate income level.
From $42,000, we paid 30% in taxes for 12,600, and we have a net income of 29,400. And my goal is to create a chart that shows the revenue as income, advertising, web services, and SG and A as expenses, then show operating income as a cash position at one moment, subtract taxes, and then have net income as our last item. And to do that, I will create a waterfall chart.
So with any cell in my Excel table selected, I will go to the insert tab or the ribbon, and then in the charts group I will click the waterfall or stock chart button and click waterfall. And here I have my chart. I'll drag it over to the side a bit and decrease its size, although I will make it a little taller. Sometimes playing with screen resolution can make these charts easier or harder to read.
I'll change the chart title by clicking it and then selecting the text to income statement summary. And you can see in the body of the chart that we have increases in blue, so that's 150,000, then we have decreases in orange, so we have 15, 75, and 18, and any totals would be displayed in gray, although we haven't identified any yet. And in fact you can see that the chart sees the $42,000 result of operating income as an increase, as opposed to a cash position, or what's called a total.
To identify operating income as a total, I will click the series, or click any item in the series, and then click the item for operating income. That's 42,000. Then I will right click it, and in the shortcut menu, because this is a waterfall chart, I get set as total. And you can see there that I have my cash position of $42,000, so instead of a 42,000 increase, that is an indication of where we are at the moment. Now I can do the same thing for net income, which has 29,400.
So I'll go over to the right side and click the column for 29,400, and I'll do the same thing, set as total. There we go. I'll click anywhere else in the chart area and there I see the net income as a cash position. So once again increases are displayed in blue, decreases are displayed in orange, and totals, what I've been calling cash positions, are displayed in gray. Waterfall charts are extremely useful and they're a relatively recent tool that allows you to visualize cash flows in an interesting and informative way.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart