Data Visualization for Accounting and Unicorns

Recently, I’ve been reviewing the financial analysis part of my new book and I’m reminded how hard it is to create data visualization for an area obsessed with calculating every amount to the exact cent.  When we develop our visual analysis it is so easy to get trapped in this labyrinth of detailed tables and numbers.  We hit our heads against the wall and try to imagine one omniscient visualization that captures every detail and we only succeed in creating something more impenetrable than the table we were trying to replace.  So, let’s step back and think about what we need to do to create a successful visual analysis for an accounting department.

What you need to know about accounting

First things first, modern accounting has been around for more than 500 years.  To put this in perspective, Luca Pacioli invented modern accounting about 300 years before William Playfair invented the bar chart in the 1780’s.  Unlike all the impromptu reports of other departments, several reports that are required by the accounting department are based on well-established rules that have long made them useful to external parties like banks, investors and the government.

When we create our first analytical application for an accounting department, we have to take all this in with the gravitas it deserves and not try to supplant their requirements with cheeky charts.  You might not like it, but we should create a well-formatted income statement, balance sheet and cash flow statement that accountants can easily export to Excel.  If we do this without denigrating their complete lack of modern data visualization techniques, we might have just gained the accountant’s respect and the opportunity to invite the department into the 21st century of data visualization.

Data visualization for accounting

There’s no doubt that the first steps to create valuable data visualization for accounting can be quite hazardous. Our task now is to persuade accountants to take data visualization seriously.  We have to convince them that data visualization is more than just eye candy that makes quarterly financial reports less dense.  Sadly, shallow visualizations abound in these reports and make our job an uphill battle.  The following example is from Thomson Reuters’s third quarter earnings presentation which just happened to be the first result returned from a Google.

Accountants have to understand that data visualization can also help them analyze and explain complex problems.  However, at the same time that we want to introduce visualization with more substance, we also must avoid going to the other extreme.  We must avoid the trap of trying to add every element of a financial report into one visualization.

One financial report is more than one visualization.  Take a second to step back to reflect on the number of visualizations we use to analyze sales.  Now, take note that sales is only a few line items in an income statement.  Although we don’t usually store as much detail about assets, liabilities, costs and expenses, we should dedicate a series of charts, if not a few tabs, that help us investigate each of those concepts.

Data abstraction

Surely, inventing ways to visually analyze data is not only a problem for financial reports.  We follow some process of abstraction in all departments to create effective data visualization.  If we want to look up an exact number we use a table.  If we want to look for general patterns at a glance we use an abstract visualization.  In order to do this we have to make the difficult decision to focus on a few variables.  Only then can we take advantage of our uncanny ability to discover patterns.

In the accounting department, we have to take the following table packed with variables and convert it into something that takes advantage of our preattentive processing.

In order to do that, I repeat, we first have to stop chasing unicorns.  In other words, we have to stop looking for one magical chart that is going to handle every variable in a comprehensible way.  It doesn’t exist.

Second, we attack this from two directions.  On one side, we create concise user stories, and on the other, we create prototypes train the user’s imagination.

In order to aid the abstraction process, the user stories must be clear and concise.  We shouldn’t allow run-on sentences like the following user story.

As a financial analyst, I want to be able to analyze the trend of all sales, costs, and expenses over the last two years along with their percentage relationship to sales and have a way to see both year-to-date values alongside monthly values with some indication whether the numbers are good or bad against budget so that I can detect areas of improvement.

Like a sentence, an excellent chart is focused and concise.  The previous epic user story can be broken down into several user stories that each represent an individual chart.  Together, the separate charts make up a clear, insightful analysis.  The following user stories are only a couple of the many examples that can be created out of the previous epic.

As a financial analyst, I want to be able to analyze the trend of all monthly sales and sales-related costs over the last two years so that I can have an idea what to expect over the next year.

As a controller, I want to be able to compare detailed expenses to their budget so that I can control future spending.

These short user stories and our proposals to visualize them should bring us closer to creating an analytical application as effective as those we’ve created for other departments like sales.  Before we conclude, let’s take one more look how we might refurbish those old financial reports with more advanced visualizations.

Financial reports dashboards

What did a business use before the modern information dashboard to determine the health of their company?  If I had to guess, I would say that income statements and balance sheets played an even more vital role than they do today to monitor a company at a glance.  In a way, many still use them for that purpose today.

What can we do to those old financial reports to take advantage of what we know about data visualization today?  Like any dashboard, we can add sparklines (mini-charts), bullet charts, and alerts to its columns.  We may also be able to replace the numbers by color and make it into a heat map.

I introduced the heat map as a possible way to do multi-variate analysis in Learning QlikView Data Visualization, and recently, Rob Wunderlich talked about a similar pivot table grid.  Both methods can be a great way to abstract data, and I think it can work well for some financial reports.

The heat map based on monthly amounts.  It shows both temporal and related account patterns in a traditional report structure familiar to accountants.  We also add arrows that make year-over-year comparisons easier to see.  In our process to make the report more abstract, and therefore, more understandable, we sacrifice using all other variables and the exact numbers.

No doubt there are numerous ways to visualize the same data, but we should take care not to over-complicate the matter.  The temptation to look for one magical chart that solves everything is sometimes hard to resist.  Everyday we should ask ourselves, “Am I chasing any unicorns today?”

See you around,

Karl

 

Author: Karl Pover

Owner of Evolution Consulting, which provides Qlik consulting services throughout Mexico. Author of "Learning QlikView Data Visualization" and "Mastering QlikView Data Visualization." Qlik Luminary since 2014.

1 thought on “Data Visualization for Accounting and Unicorns”

Leave a Reply