Excel 2016 Waterfall Chart Illustrates Financial Statements

It’s often difficult to get the measure of an income statement by simply glancing at it. Numbers quickly blur together, and there’s often very little frame of reference of how the numbers flow from Total Revenue down to Net Income. If you’re using Excel 2016 you now have a new way to visualize financial statements. The upside is these charts are easy to create—as long as you’re aware of a nuance—but the downside is that these charts can’t be shared with anyone using Excel 2013 or earlier.

To create a waterfall chart you must first create a summary version of your income statement which should only include major totals or subtotals. 

 
Download a sample Excel file. Requires Excel 2016.
 


Next, as illustrated:

  1. Click on any cell within your summarized income statement.
  2. Select the Insert menu in Excel 2016.
  3. Select Recommended Charts.
  4. Select the All Charts tab within the Insert Chart dialog box.
  5. Select Waterfall.
  6. Click OK.
  7. A Waterfall chart will appear within your worksheet. Initially your waterfall chart will flow up hill, which is a physical impossibility. This is the nuance that you’ll need to overcome.
  8. As shown, slowly click twice on the Gross Profit column; meaning click, pause, and then click again to select that single column.
  9. Right-click on the Gross Profit
  10. In the context menu, select Set as Total
  11. Repeat steps 8 through 10 for all subtotal column
  12. At this point the chart will flow on a downhill cascade, going from Total Revenue down to Net Income.

Figure 1

Figure 2

 

Download a sample Excel file. Requires Excel 2016.