Adding
Insight to Financial Reporting through In-cell Charting
Summary
Financial
reporting differs in its breadth and scope from organisation
to organisation. Everyone needs Balance Sheets, Profit
& Loss statements, Cash Flow statements, and often multiple versions of each for
different entities in the business. Beyond the statutory requirements are a
series of additional reports which have built up over the years through
differing FD / CFO directives in order to accurately gauge performance and for
decision support.
While the suite of current reports may ‘do the job’, that
doesn’t mean they can’t be improved. Recent advances in visualization techniques
and technology can add huge context and depth to existing financial reports. How
many times have you heard variations of ‘a picture is worth a thousand words’?
For ‘picture’ read chart, and for ‘word’ read number. Of course it’s only true
if the chart in question is clear, concise and focused. I’m not for a second
suggesting that you start throwing away the numbers and start replacing them
with lots of pretty charts, I expect you’d have some issues with your auditors.
In many cases it’s more a question of extending what you already have, but the
extension can add both clarity and depth. Take a look at the case in point
below:
The original data only table has been transformed into something which now
shows the headline numbers, but graphically depicts trend and target on a row by
row basis. Significantly more data is being conveyed, and it can be assimilated
much more quickly. Almost at a glance, I’m concerned by the revenue plummet in
early 05, yet pleased by the strongly over target ytd profit.
This is a fairly dramatic departure from the original, but any one of the
charting approaches shown could be used in isolation to extend the information
conveyed in the original, and reduce the time required to assimilate it.
Background and the Barriers to Adoption
The approach taken in the previous example borrows from the work of
visualization luminaries such as Edward Tufte
and Stephen Few, who are both
exponents of the use of many small but tightly focused charts alongside the
headline numbers to quickly convey the overall picture. The term ‘visual tables’
has been coined around this work, to describe the ability to add small chart, so
called
sparklines, into an otherwise tabular layout. The vast majority of
Financial reports are highly tabular in nature, and as such lend themselves to
enhancement, or indeed transformation through this approach exceptionally well.
The ‘visual table’ approach has been around for a number of years, and is
something you’re likely to have seen in various business journals. The problem
until recently was that it hasn’t been possible to produce these chart in Excel
with the ‘standard’ toolsets available to most business users.
MicroCharts allows the creation of very small charts in Excel at a cell
level. MicroCharts enables exactly the type of reporting discussed above, and
for simple extension of existing in-Excel reports. The only prerequisite for
MicroCharts is Excel itself.
Key
aspects about MicroCharts and how they are implemented:
- Displayed within 1 Excel Cell (yes, that small!)
- Implemented through a font (can be aligned & resized in the same way as
text)
- Controlled & configurable via Excel Formulae
- One Formula can control multiple charts (potentially 000’s of rows)
Areas of focus for Financial Reporting
Aside from their tabular nature, there are a few other
common traits of many financial reports which have specific relevance to a
MicroCharts enhanced reporting environment.
1. Focused on the current situation, but weak on historical trend
2. Actual vs.
Budget / Forecast
3.
Ranking and Comparative performance
4.
Well understood within Finance, less so in the other functions
1. Focused on the current situation, but weak on
historical trend
For bottom line it’s the here and now which matters, but
past trends show how things are changing over time, and visualizing this can
enable early identification of both opportunities and issues. On a P&L for
example, if ytd profit is at or near target that could be masking strong start
of year performance, which has been slowly eroding throughout the year. An
in-cell ‘sparkline’ alongside the headline number will quickly identify the
problem.
Despite their diminutive size, sparklines support a large number of formatting
options including the normal band shown below, and a host of others.
Row by row, over whatever period range you choose, it’s possible to show a
huge volume of data in a tiny screen real estate, alongside the current position
to give you a real picture of the business by any of the metrics.
Sparklines: “data-intense, design-simple, word-sized graphics”. Whereas
the typical chart is designed to show as much data as possible, and is set
off from the flow of text, sparklines are intended to be succinct,
memorable, and located precisely where appropriate." Edward Tufte
2. Actual vs. Budget / Forecast
We are constantly comparing the actual numbers with our projections. Doing
this over a report of even 10 lines requires row by row number comparison, and
is not something which can be easily & quickly scanned. Differential formulae
and conditional formatting can help to a degree, but it’s still not ideal. Enter
the Bullet graphs,
invented by Stephen Few. It’s a compact, data rich and efficient alternative to
space hungry gauges. Bullet graphs can be vertical or horizontal, but are
often of most benefit when used horizontally, on a row basis to clearly display
performance against target.
The bands shown are user configurable in terms of their range and color, as
is the scale used. It’s worthwhile including a chart key, such as the above to
ensure the distribution can instantly familiarize themselves with the display.
It’s in a tabular form like the above where Bullet graphs really come into
their own. In this case a cost extract from a P&L, where being under target is
the goal. Here a classic traffic lighting approach has been taken to highlight
the fact we’re aiming for under rather than over target.
In this environment it becomes extremely easy to quickly scan the table to
identify areas requiring further investigation.
3. Ranking and Comparative performance
What’s performing well, and how well, how does ‘a’ compare to ‘b’, and to ‘c’
for that matter. All questions which arise every day with respect to unit sales,
profit, contribution, costs and a host of others. It’s easy in Excel to run a
quick data sort, add a percentage, and you have a ranked list. It still remains
difficult to see the relative contribution quickly though. The addition of
in-cell bars, as in the example below can address that easily. To extend the
visualization further, in this example the bars are color coded to show the top
30%, next 50%) and the bottom 20%, in classic pareto analysis fashion.
This has both added value, and made the table much quicker to read. A
standard alternative approach in Excel would be the pie chart, as shown below.
Pie charts can struggle when there are more than four or five splits to display,
your eye is constantly travelling between the chart and the legend to identify
the differing colors. Secondly, it’s difficult to gauge relative performance. As
it’s a ranked list it’s easy to see USA is largest, but after Thailand,
everything starts to blur together. -Another case where a picture is worth a
thousand words!
4. Well understood within Finance, less so in the other functions
This is a generalization, but one which we hear fairly frequently. Not all
managers and report consumers in the business have a solid grounding in the
nuances of a P&L statement, or the other ‘basic’ financial reports. In some
cases there is a degree of fear of the unknown, in others the complaint is that
the reports are too dry and lack insight. With the techniques now available,
it’s possible to extend existing reports, but make the pertinent aspects all but
announce themselves by tannoy. Making the reports more visual in nature gives
more insight for all, and can also help engage the non finance report consumers.
