The theme of this ExcelZone tutorial series by Simon Hurst on Excel financial reports is, "Could do better."

So much effort goes into Excel: acquiring the data in the first place, working out and performing all the calculations, making sure everything is correct. It seems a shame if all this good work goes to waste because the end result - the presentation of the key information that a spreadsheet reveals - isn't as good as it could be.

Basic number, text and layout formatting is dealt with in the formatting section [1] of the Excel Compendium so, in this three-part series we'll be concentrating on more graphical methods of presenting financial information as effectively and efficiently as possible. To start with we'll look at basic Excel charting and consider just what it is that makes a chart effective. Part 2 will look at some more advanced charting features and putting some of the graphics lessons learned into practice, including incorporating data into a dashboard. In Part 3 we will consider ways of distributing our financial information whether by presentation, printed report or using SharePoint and web browsers.

Before charging into Excel and its graphic tools, I should first thank Dave Woolcock, IT Manager at North-West based accountancy firm Moore and Smalley, for introducing me to several important graphical presentation resources and, in particular, the works of Edward Tufte [2]. Tufte has published several books on the subject of how best to present information, particularly quantitative information. His book 'The Visual Display of Quantitative Information' is commendably short at only 190 pages but filled with remarkable charts and diagrams. Whether you agree with the conclusions he reaches or not, if you need to present financial information, finding a couple of hours to read this book could revolutionise the way you think about presenting data to colleagues and clients.

A picture's worth a thousand lies
I suppose we should first of all decide whether there is any need to go further than presenting financial data as simple text and numbers. Although it's distinctly possible that many of the Excel charts that are produced serve to confuse rather than elucidate, graphs can be superior to tables of figures in many circumstances. Graphs can better portray trends and identify correlations and can also display a very large number of values in a small space, making it much easier to understand the information that those values represent. On the other hand, graphs can not only confuse but also deliberately mislead:

Which company has the best results? Of course, both graphs are based on exactly the same numbers, Company B has just been a bit more creative in positioning the baseline at -20,000 rather than 0.

Which results have been growing more quickly – again the same figures, just a change in the aspect ratio.
Tufte includes examples of some slightly more sophisticated attempts to mislead – many using perspective and the representation of values using 3D rather than 2D shapes. For example, if you portray a value of 10 in a box sized 10x10, and a value of 20 in a box 20x20 the impression is given that the second value is four times the size of the first:

Focusing attention
A key consideration is what the user focuses on when they look at the chart. Do they focus on the substance of the data that it represents, or on the techniques used to create the chart? I must admit my own guilt in this area. The 'finale' of many of my Excel seminars is to show how to change this:

Into this:

I think it would be fair to say that this presentation risks diverting attention from the important substance of the data.

Data ink
It's easy to see what makes a poor chart, but what are the guidelines to creating charts that do their job of portraying the substance of the data as effectively as possible? One of Tufte's main principles is the idea of 'data ink'. This is based on examining the chart and deciding how much of the 'ink' used to create it is essential to represent the data, and how much is just decoration. The ideal being to get close to a ratio of data ink to total ink of 1:1.

At the risk of oversimplifying Tufte's approach, let's take a 'default' Excel chart and gradually reduce all the 'non-data' ink and see if what we end up with is clearer and better. The basic techniques of creating Excel charts are covered in the 'graphs and charts' section [3] of the Excel compendium. Here we've created an Excel 2007 3D column chart based on a very simple set of sales figures:

The ink required to produce the 3D effect is not 'data ink' and it actually makes the value behind the columns less easy to determine, so step 1 would be to go from 3D to 2D:

The legend is unnecessary with only one series being shown and we could also get rid of the grid lines:

We could go further, and make our chart values easier to assimilate by replacing the Y axis with data labels. The lines for the x axis aren't necessary and the white space is now a bit excessive so we could reduce the gap between our columns. We can 'de-emphasise' the heading by making it smaller (less ink) and less bold. We could then shrink our chart substantially without making it any harder to understand the data.

It's worth going back and comparing our end result with the original chart to judge which is the clearer and which achieves its objective of revealing the underlying data most successfully. In fact in this case, the data is so simple it could well be argued that the best presentation of the data would simply be:
Sales
South 12
North 9
West 7
East 5

The importance of charts
One of the major tasks for many accountants is to present financial information to others – whether they be within their own organisation or clients of a practising firm. Properly used, graphics can not only make that information quicker and easier to understand, but can show vital trends, patterns and links that would otherwise be indiscernible in a purely text and numbers based report. Whilst many accountants use graphics effectively and creatively, there is a great deal more that we might be able to achieve with a better understanding of what works in the graphical representation of financial data. While I was preparing this series, Dennis Howlett posted an article on the IT Counts site [4] wondering whether a new graphics-based service for smaller companies ventured into 'our' territory and whether we could take on MyCake and beat it. For some more inspiration on how we might be able to make the information we provide to our colleagues and clients more valuable, here are some links to other uses of graphics, courtesy of the aforementioned Dave Woolcock:

Gapminder [5], an interactive and animated charting tool that helps you 'explore the world',
What happened to the passengers on the Titanic [6]
Sankey diagrams [7]

Coming up

Further reading

Presenting financial figures with Excel: Part 2 - Charting choices [8]
Presenting financial figures with Excel: Part 3 - Delivery techniques [9]
ExcelZone Compendium: Working with accounts data [10]

Subscribe to the ExcelZone newswire
[11]To keep up with spreadsheet issues and regular tutorials, click the button below to subscribe to the free monthly ExcelZone newswire. The subscribe function [12] will take you back to the AccountingWEB.co.uk home page after it adds your name to the subscription list.