How to Illustrate Financial Statements in Excel 2016

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.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

About David Ringstrom, CPA

David Ringstrom

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.

Replies

Please login or register to join the discussion.

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
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)
avatar
to JJ Collins
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)