The story so far...

In the first part of the series we looked at some general principles of presenting financial information [1] graphically, including the creation and simplification of basic Excel charts.

In this second part we'll look at some more advanced charting features and put some of the graphics lessons learned into practice, including incorporating data into a dashboard. In part 3 we'll go on to consider how to distribute our financial information whether by presentation, printed report or using SharePoint and web browsers.

More charts than Cap'n Jack Sparrow

The only chart type we looked at in part one was a simple bar chart. I have always been drawn to the idea of keeping things as simple as possible – particularly when two chaotic worlds such as IT and finance overlap. Accordingly, the idea of using the simplest possible chart to reveal the substance of the data that it represents, seems to me a good one. Most of us will have come across - and probably used - the basic chart types: line, column/bar and scatter. As well as these, Excel can produce the following chart types for us:

• Pie




• Area




• Stock




• Surface




• Doughnut




• Bubble




• Radar

Some of these charts have specific purposes for which I'm sure they are well suited, but it might make sense to have an overall guiding principle of comparing the proposed chart type to the simplest of column/line/scatter charts, and only using one of the more esoteric types if it really makes a better job of delivering the underlying message of the data.

Easy as pie?

Let's look at some specific chart types. Pie charts come in for a fair amount of criticism, yet they are probably one of the most frequently used types of chart. The main criticism of a pie chart is the difficulty of judging the values that the slices represent. Edward Tufte argues that if you want to compare the parts of a whole, a column chart does a far better job:

I'm not sure I'm wholly convinced that pie charts are never useful. If you are comparing parts of a whole to each other, then the column chart indeed does a better and clearer job, but if you want to see the proportion of each part to the whole then the pie chart does a decent job.
3D – 50% better?

I find it harder to disagree with Tufte's aversion to 3D charts. If used for an essentially 2D presentation, all the extra dimension tends to do is to make it less easy to work out the values represented:

When they are used to cope with an extra dimension of data, perspective is again an issue, and it can be hard to avoid obscuring some of the data:

Like all good rules, there will be occasions when it's right to break them, and it's worth exploring not only the different chart types, but also the different sub-types or layouts. For example we could have used the "bar of pie" layout and dragged a particular slice for emphasis:

Dashboards

Dashboards are currently a popular method of displaying financial information. The underlying idea of a dashboard is generally to present a single-screen summary of all the key information you need to drive your organisation or department.

I found Stephen Few's book Information Dashboard Design useful although you need to take into account that he also finds Tufte's work persuasive and so focuses on simplicity over decoration. In his view, many dashboard designers have taken the dashboard analogy too literally and compromised the effectiveness of the data presentated through the use of too many visual gimmicks.

If you just can't resist those visual gimmicks then you can either invest in one of the many add-ons that let you add speedometers and gauges based on Excel data or, with a bit of creativity and lateral thinking, create them yourself.

For example, if you set one element of a pie chart to 50%, position it as the bottom half of the circle and hide it by setting outline and fill to none, set the angle of the first slice to 90 degrees and do a bit of formatting you end up with something like this (as described in John Walkenbach's book Excel 2007 Formulas:

The alternative approach is to go for simplicity. The argument being that, in order to display all the required information at a single glance, the key requirement is to condense the graphics as much as possible, rather than wasting screen space and user attention on unnecessary decoration.

An idea originated by Tufte is the use of 'Sparklines' – very small, very simple graphics that display a trend or a series of values. As regards their use in Excel, a sparkline could fit in a single cell. You won't be surprised to learn that third party add-ins are available to create sparklines or 'tiny charts' for you. Just as examples, TinyGraphs [2] is a free, open source add-in that enables the creation of miniature charts in individual cells or groups of cells. It's a fairly basic add-in but works well – it has been used to generate the charts for the expenses items in the first graphic in this article.

The BonaVista microcharts add-in [3] looks much more sophisticated and comprehensive, coping not only with sparklines but also with several other chart types specifically designed to show as much as possible, as clearly as possible, in a very small area. The website also includes several sample dashboards.

The graphic at the top of this page is an example of how some dashboard techniques could be applied to a 'traditional' set of accounts. In this case we have assumed that we are preparing an annual set of accounts and have at least five years' worth of data upon which to base our tiny charts. We've also used another technique from Stephen Few's book, the use of a highlight to draw attention to problem or important areas.

In our case we have inserted a circle symbol into column B for the expense item rows. The font colour is set to white, so ordinarily the circle will be invisible, however we've used conditional formatting to change the font colour to red if a certain condition is met. This could be based on an absolute and/or percentage value change since last year. Just to prove it can be done, we've created some perfectly normal Excel line charts for Sales, Cost of Sales and Gross Profit, stripped out all but the line itself and then shrunk them to single cell size. This isn't quite as straightforward as it sounds, as to fit the chart into a cell shape it's necessary to manipulate the minimum and maximum vertical axis values to avoid flattening the graph. Obviously the same techniques could be applied to monthly data.

So at last, a use for that colour laser printer sitting in the accounts and audit departments!

Coming up

Further reading

Presenting financial figures with Excel: Part 1 - An introduction [4]
Presenting financial figures with Excel: Part 3 - Delivery techniques [5]
ExcelZone Compendium: Working with accounts data [6]

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