Power BI: 1, 2, 3, 4, big data
Hugh Johnson explains why data visualisation is important for your monthly board pack.
The human brain struggles to hold more than about seven digits in short-term memory, but this number drops to about four if we need to perform some mental arithmetic on them. Beyond that we go into overload and it all becomes “Big Data” to our humble minds.
We can, however, process complex visual information really quickly and easily. This has huge implications for how we should present our monthly reports. It may seem obvious that charts and graphs are easier to understand than tables of numbers, but I think it is worth reminding ourselves of why.
Content seriesView full content series
As a financial controller or financial director, you are probably charged with preparing a monthly board financials report. Getting this right can be tricky. On the one hand you need to be able to present the big picture, very quickly. On the other hand you need to underpin this with detail, for completeness and to answer almost any question that may be raised.
It can be tempting just to churn out page after page of tables and numbers and leave the board to it, but you know that is not the right way to go. It is your duty not just to deliver the numbers, but to make them understandable.
It may also be tempting just to present a standard set of charts that come from your accounting software, but you know that this is also wrong. This approach most likely misses the fact that every business is different, and therefore the things that you need to track month-on-month are unique to your business.
In this article I review, with an example, some of the difficulties that we have interpreting numbers and why data visualisation can help. By understanding the difficulties, along with what we are are trying to achieve, we can come up with a much more useful monthly pack.
We humans are not that numerate
Although, as a species, we think of ourselves as very clever, we are actually not very numerate.
Let me illustrate this with a simple example. Below is a table of sales data from a profit & loss statement. Without using a calculator or pen and paper, look at the table and try to interpret what you can. While you are doing it, try to focus on what your mind is doing.
Now look away and try to recall what the table was telling you. You probably concluded fairly easily that:
Sales YTD are around £3.4m
Sales YTD are up by about 50%
Sales are heavily concentrated in 2 or 3 nominal codes.
You probably found it quite difficult to:
Estimate what proportion of sales are concentrated in the top three nominal codes
Determine whether sales are more or less concentrated this year versus last
Remember any significant sales values to the nearest £1k
Recall which seven nominal codes have lower sales this year vs last
Understand just how concentrated the sales are in the top three nominal codes.
In the table, there are 17 nominal codes and 34 values comprising 213 digits. In order to get some meaning from the table, we first do some mental pre-processing on the numbers to simplify things. As a trained accountant, you probably looked for highs and lows, rankings, proportions and approximations. When you scanned the numbers, you did a kind of mental rounding, identified the significant numbers and put them into your working memory for use in some rough mental arithmetic.
After this initial mental scanning for significant numbers maybe you were left with eight numbers to focus on; the three most significant nominal codes and the total sales figure for the two periods. Combined, these eight numbers have 68 digits so you had to do some mental rounding.
When you saw £3,338,115.79 (a number that I am sure you don’t recall precisely) you did a quick mental conversion to £3.4m. You were probably looking for relative values and tried to interpret some trends. Maybe you converted sales last year of £2,379,504.53 to £2.3m or £2.4m, compared this with £3.4m and estimated a year-on-year growth rate of about 50%.
After doing this rounding, it was still quite a serious mental effort to analyse these numbers; the actual growth rate is 42%. In some sample tests that I did on colleagues, most estimated that the top three nominal codes accounted for around 80% of sales whereas the actual is 96%. As I mentioned at the start of this article,without specific training, we struggle to hold more than about four digits (or very round numbers) in our working memory.
This has huge implications for how we can, or rather cannot, interpret and digest sets of financial numbers. Just presenting a table of numbers, that may be accurate, leaves us to do significant mental work to summarise and interpret the numbers while we mentally discard the actuals. We need to reverse this. We need to present the big picture and important points first, backed up by the detail as necessary. This saves us from that mental effort to interpret the numbers, giving us more time and energy to concentrate on what they mean and what we might do about it. Good visualisations can really help with this process.
See what you mean
Unlike our very limited ability to juggle lots of numbers simultaneously in our minds, we are actually very good at processing visual information quickly. We can also recall images more easily than strings of numbers. This means that we can, with a bit of thought, present a Profit & Loss report visually, in a way that is so much more digestible. Take a look at the following charts.
The first bar chart simply shows sales YTD compared with the same period last year. Simple. If you want you can display the actual numbers and a YoY growth %.
The treemap below shows YTD sales by nominal code. The size of each square represents sales YTD and the colour represents % growth year-on-year.
Green is highest growth, yellow is no growth and red is negative growth.
In an instant you can see that the top three nominal codes account for nearly all sales (well north of 90%). You can also see that the nominal code with highest sales is also growing the fastest, so sales are becoming more concentrated.
The 3rd chart shows something a little more sophisticated. It is a waterfall chart that shows sales variance YTD by nominal code.
So, in a single chart we can see that sales are up YTD by £1.22m and how this growth is being driven by each nominal code. You can see immediately that three nominal codes are driving nearly all of the growth, but there are some nominal codes showing reductions. We can also see that more than half of the growth is driven by a single nominal code.
This makes it easier for the board to focus on the exceptions with the appropriate level of attention and make the monthly meeting more productive.
We struggle as humans to carry more than about four numbers in our head. Why are PIN codes just four digits, and why do we break down telephone numbers into groups of no more than four digits? Why is a credit card number four lots of four digits?
If detailed numbers are presented to us, we go through a mental process to arrive at approximations and discard (mentally) the actuals. So let’s present the approximations first, and let the reader examine the details if needed.
We then try to sort these approximations into “important” and “unimportant” - discarding what we consider unimportant so that our limited mental capacity can be used to look at three, four or five most significant values. So let’s pre-identify and present those few most significant values first and keep the less significant values in the background.
Good visualisations will save you from this mental effort and help you to grasp the big picture very quickly and focus on what needs attention. I am not suggesting that you dispense with the absolute numbers, rather have them as a detailed appendix as needed, or alongside a chart.
As you can imagine, there is no single standard set of visualisations that are best for all board packs. Beauty is, after all, in the eye of the beholder. What is beautiful to you will depend on the lifestage of your business, the industry you are in and any specific nuances faced by your company.
There are many tools available to help you produce good visualisations of your data. My tool of choice for accounting data is Power BI. How to create a really good visualisation is the subject for another day, but try to keep things simple.
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs,...