The story so far...

In the first part of the series [1] we looked at some general principles of presenting financial information graphically including the creation and simplification of basic Excel charts. In part two [2] we looked at some more advanced charting features including more advanced chart types, and charts suitable for use in an Excel dashboard. In this article, we'll be considering the distribution of our financial information whether by presentation, printed report or using SharePoint and web browsers.

Within Excel

Let's start with the simple option – presenting our information within an Excel spreadsheet. We've already looked at some different ways of presenting financial information including the use of charts. We have also considered the benefits of focusing attention on the information that we are seeking to communicate rather than on our prowess in the presentation techniques involved. There are a few other pointers that come out of Tufte's The Visual Display of Quantitative Information and Few's Information Dashboard Design.

Use colour carefully: properly used, colour can help highlight important information and show the relationship between similar items in different areas of a report. Used carelessly it is distracting or worse still, it can show apparent connections that are not intended to be there.

It's also worth bearing in mind that a significant proportion of the population is colour blind and that green = good, red = bad is not a universal concept. In China for example, red is seen as good. The combination of colour and shade can be preferable to colour alone as it helps make the difference equally visible to those who have difficulty telling hues apart.

Here's an example comparing the use of colours and shading. It could be argued that the bright colours add little except for possible confusion with the (rather heavy-handed) use of green and red to highlight expenses that have increased above budget. A quick glance might give the idea that rent and rates are to do with the North, whereas financial costs relate to the South.

Tufte suggests that the use of "small multiples" can be an effective way of showing comparisons between multi-dimensional sets of data. Here is an example that contrasts the use of a single chart to compare sales by product and region with using a series of separate charts. In this case there's probably no great advantage, and the separate charts certainly take more work to produce, but there may be situations when small multiples convey the message of the data much more clearly:

Dealing with information that needs to be displayed by different categories – such as our very simple example of region and product – leads to another choice of methods. It is reasonably easy to build interactivity into the display of Excel figures and charts, so that a user could select a region from a drop down, and all the figures and charts change to display the figures for that region. There are situations where this may be the best way to give someone easy access to all the data they need, but a point that Few makes is the power of the dashboard approach to reveal comparisons and correlations that might otherwise go unnoticed.

The more data that can be effectively displayed within a single static screen, the more likely such comparisons and correlations are to appear. Switching between different displays may not achieve this, so it may be worth considering how to condense the charts to their minimum content and size so that the figures for all regions can be assimilated at one glance, rather than forcing the user to view the data sequentially.

This is far from saying that there is no place for building interactivity into an Excel display or dashboard – Excel has a comprehensive set of tools to help you achieve this. View-Toolbars-Forms (Excel 2007: Developer ribbon tab, Controls group, Insert, Form Controls) gives you access to lists and combo boxes to select items, sliders and spinners to increase or decrease values – such as the month number - and simple buttons and yes/no check boxes. In addition, you can insert hyperlinks into cells or assign them to pictures and objects in order to aid navigation or allow drill down to further detailed analysis.

From Excel to PowerPoint – and back again

It's difficult to avoid the mention of PowerPoint when it comes to presenting any sort of financial information. Of course PowerPoint includes the ability to insert its own charts and tables, but if you've used Excel to produce the underlying information you will probably want to at least copy and paste details into PowerPoint from Excel and maybe create a link from PowerPoint back to Excel so that updated information is automatically reflected in the presentation.

There are a few issues with including linked information. The first relates just to PowerPoint. You can animate the individual elements of a PowerPoint chart, or an Excel chart that has been pasted into PowerPoint without being linked. This is achieved via an additional Tab that appears on the object's Effect options screens in the custom animation drop down:

However, this option is not available if you have used Paste Special-Paste link to copy your chart from Excel as a linked object.

A wider issue that applies to PowerPoint and other applications such as Word is the danger of a change in the structure of the source worksheet leaving the linking application including information from the wrong set of cells. This is usually an issue when linking to a block of cells rather than an object such as a chart. If you just create a link to cells B6:D15 for example, PowerPoint or Word will do what they are told and display the values in those cells. If you insert rows and columns that change the location of that block of cells, your linking application will be looking at the new contents of cells B6:D15 which may well not be what you intended.

Consequently, it's usually a good idea to name the range [3] that you're linking to in order to enable the linking application to keep track of the current whereabouts of the range. It's an even better idea to always make sure that you check that linked data has been correctly updated before distributing or presenting the report. Incidentally, there is an issue in Office 2003 Service Pack 3 that causes the linking application to ignore the name even if there is one, requiring you to manually change the link (ALT-F9 displays the link information) if you want to use the name. There is a little more detail on this in this article [4] on IT Counts.

It's Excel Jim, but not as we show it

While on the subject of IT Counts, the ICAEW IT Faculty has recently been promoting the Business Analytics and Reporting [5] course (link to March course - another being pencilled in for July). I attended one day of the four-day course as a faculty observer, and among many interesting and useful ideas, came across the idea of using Excel as PowerPoint. By clearing the headings and gridlines, using pictures or sheet backgrounds and Excel's View, Full Screen option, Excel can be at least as an effective a means of presenting financial information as PowerPoint. The aforementioned hyperlinks and interactive controls can be used to provide for navigation and interactivity in a way that it would be hard for PowerPoint to match:

Alternatives

Access - If your end result is to be a printed report pack incorporating mainly charts and figures rather than text, an alternative to using Excel, PowerPoint or Word would be to consider Microsoft Access. If you can create the reports that you need using Access – and you can include charts in an Access report – then why go to the effort, and increase the chances of introducing errors, by exporting the data to Excel? Access queries are likely to be an easier and more flexible method of getting at the data you need than using the various Excel filter, sort and lookup and reference capabilities. Access reports may also be able to completely automate the reporting process.

Excel web services and SharePoint If you need to disseminate your finished reports widely, particularly if you wish to allow for collaboration and interactivity, then a browser-based solution might be worth considering. If you are using Microsoft SharePoint Server, then the answer may well be the use of Excel Web Services. The combination of Excel Services and SharePoint Server allows not only the sharing of the workbook, but also the use of Excel functionality via a browser. For some examples of the use this technology applied to the areas we have been discussing, it's worth having a look at one of the Microsoft technical articles on the subject: Business Intelligence Reporting and Dashboards [6].

Further reading

Presenting financial figures with Excel: Part 1 - an introduction [7]
Presenting financial figures with Excel: Part 2 - Charting choices [8]
ExcelZone Compendium: Working with accounts data [9]

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