How to Illustrate Financial Statements in Excel 2016

Mar 8th 2016
Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

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:

  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 uphill, which is a physical impossibility. This is the nuance that you’ll need to overcome.

Waterfall Chart 1

Figure 1: Follow these steps to create a waterfall chart in Excel 2016.

  1. 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.
  2. Right-click on the Gross Profit column.
  3. In the context menu, select Set as Total.
  4. Repeat steps 8 through 10 for all subtotal columns.
  5. 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.

Waterfall Chart 2

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.

Waterfall Chart 3

Figure 3: Waterfall charts will be destroyed if someone using Excel 2013 or earlier saves the workbook.

Waterfall Chart 4

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.

Replies (3)

Please login or register to join the discussion.

Accounting
By JohnDoe
Jun 30th 2016 17:50

#Financial #Statements Books are available in pdf formate and free for Download
http://accountingbookspdf.com/pwc-guide-financial-statement-presentation...

Thanks (0)
avatar
By JJ Collins
Jul 6th 2016 16:59

Regarding Excel versions, where is the industry? Excel 2007, 2010, 2013 or 2016?! Personally I use 2010, OI only prefer 2010 because that is all I use, should I switch to 2016? It's bothersome that a waterfall chart would be lost when interfacing with users on previous versions; which is unavoidably inevitable.

Thanks (0)
Replying to JJ Collins:
avatar
By CharleyKyd
May 30th 2017 16:45

JJ,

The industry is moving to Excel 365, which gives us the current version of Excel 2016. That creates a problem for folks like David, because 2016 becomes a moving target to write about.

However, when you leave Excel 2010, you'll need to get used to Excel's Single Document Interface (SDI). That means you won't have one Excel environment that can contain multiple workbooks. Instead, each workbook now becomes its own container. Microsoft made that change so that if you have two monitors you can have a workbook in each monitor.

Charley

Thanks (0)