If you’re using Excel 2016 there is 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.
We know 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. Creating a waterfall chart can help.
To create a waterfall chart in Excel 2016, you must first create a summary version of your income statement that should only include major totals or subtotals. Next, as illustrated in Figure 1:
Click on any cell within your summarized income statement.
Select the Insert menu in Excel 2016.
Select Recommended Charts.
Select the All Charts tab within the Insert Chart dialog box.
A waterfall chart will appear within your worksheet. Initially your waterfall chart will flow uphill, which is a physical impossibility. This is the nuance that you’ll need to overcome.
Figure 1: Follow these steps to create a waterfall chart in Excel 2016.
As shown in Figure 2, slowly click twice on the Gross Profit column; meaning click, pause, and then click again to select that single column.
Right-click on the Gross Profit column.
In the context menu, select Set as Total.
Repeat steps 8 through 10 for all subtotal columns.
As shown in Figure 2, a properly completed waterfall chart will flow downhill from left to right, and provide context to how the initial figure at the left has been broken down.
Figure 2: You must demarcate subtotals within the chart to ensure the trend flows downward.
As shown in Figure 3, there’s an additional catch to bear in mind with regard to waterfall charts. If you create such a chart in Excel 2016, you won’t be able to share the workbook with anyone using an earlier version of Excel without running the risk of losing your waterfall chart. One way that you can share a waterfall chart with someone using an earlier version of Excel is to copy your chart and paste it as a picture, as shown in Figure 4. The picture is not editable, but at least it can be used to communicate financial information in chart form.
Figure 3: Waterfall charts will be destroyed if someone using Excel 2013 or earlier saves the workbook.
Figure 4: Pasting a picture of a waterfall chart into a new workbook is a safe means of sharing waterfall charts with users who do not have Excel 2016.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.