Squared Sankey P&Ls in Tableau
Aug 13, 2020Klaus Schulte
Squared Sankey P&Ls in Tableau is a joint work of Liam Spencer & Klaus Schulte.
Earlier this year, I (Klaus) published a P&L visualization, that grabbeb some attention on Twitter:
It shows a P&L flowing from the revenues at the top to the net profit at the bottom of the chart and was inspired by a chart originally created by Nadieh Bremer.
P&Ls are probably the most common and the most boring data visualizations around. There has been some content on this blog dealing with the topic recently. Visualizing P&L Statements discusses some general reflections on how to visualize P&Ls. Drill-Down and Drill-Up P&Ls presented a technique to foster interactivity in the context of P&Ls.
As you can imagine, I was quite excited, when I saw Liam Spencer posting a rebuild of my sankey style P&L some weeks ago.
When I was chatting with Liam about the huge efforts it took me and him to create this kind of chart in Tableau, a new collaboration was born: Let’s make this an easy to use template available for everyone in the community!
And today — a few weeks later — we can present our Squared Sankey P&L Template. Feel free to call it an Allen Wrench Chart (suggested by Steve Wexler) or a Tetris Chart (by Rodrigo Calloni).
We got rid of some complexity of the original charts to make this work. We dropped the gradients and the curvy design of the inflows and outflows to get a simplified view.
This post will explain the usage of the template and will focus on how to interpret the chart first. Second part will be a step-by-step tutorial on how to upload your own P&L data and how to update the workbook.
A P&L is actually a very simple document, there are only really two things happening: 1. Some things (revenues, income) coming in and 2. Some things (costs, expenses) going out. The perceived complexity (at least for people who do not work in finance) is that these two ‘flows’ get attributed at different stages or levels within a P&L. The chart aids in visualizing this hierarchy. Further to this, an important aspect of reviewing any companies performance is the relative size of any income or cost line. In the chart, we can easily see where and by what proportion of total revenues companies costs/expenses are going.
The chart is split into multiple levels – as few or as many as you like with the custom template. Each level shows all the necessary information to go from the total of the preceding level to the total of the current level. The width of level one will always represent the total revenues at 100%. The width of any subsequent component is then always relative to level one. Note: the width as described excludes the ‘kickout’ portion of any component as this can be adjusted to preference.
The requirement for varying levels will be understandable to those working with any financial data sources. For all others, I (Liam) use a more personal example: I am paid 100 euros a month, this would be my level one (income). I am then interested to see how much money I have left after shopping for necessities, this is my level two. I could then continue this exercise showing how much money I have remaining after multiple stages (transport, entertainment, household bills) to gain a greater understanding of the overall health of my finances.
Flows in represent any value being added to the chart, the easiest example here is revenue at the top of the chart. Total revenues for any business are always the starting point for a P&L and all subsequent information is measured against (“percent to sell”).
Flows in can be added at any level of the chart to signify income being generated, that must not be included in level one. Examples are Other Operating Income or Interest Income. Again, using my personal finances, my income may be 100 euros, but on occasion my mother sends me 10 euros in a card. I would not class this as income according to level 1. I may place this at level 2 or 3 – to signify additional income without attributing it to my usual source of income.
In terms of the composition of the chart flows out are simply the reverse of flows in. These are the values which are removed from the preceding level, and when coupled with any relevant flows in, create the new total for the current level.
The total of each level represents the prior levels total, minus all flows out and plus all flows in. In a P&L, these totals are pre-defined and as standardized as possible (variances can occur across countries/accounting principles etc.) to allow comparisons across companies and years.
The key takeaway here is that it is not just important to know how large a companies revenues and final profit are, but it is important to know how they generate that profit.
For example, let’s imagine two companies with equal sales and profitability:
If there was a significant rise in the minimum salary, the risk to Company A is much greater than B, our totals allow us to see this.
The remainders do not have a specific relationship to a normal P&L. We’ve created them to aid the design and aesthetic of the overall chart. Each remainder represents the total value of the level above minus any current outflows.
Labels at the horizontal lines indicate the current year value (bold) and the prior year value for comparison. Vertical lines at the top and at the bottom of the chart show the margins at (sub-) total level. Feel free to add more information like position names as needed.
In the next chapter, we will describe the usage of the template.
It contains four sheets, please do not delete or rename any of these sheets.
You can add your P&L data in the Values sheet. Again, please do not delete or rename any of the column headers. Add additional information as needed.
Costs/expenses must include negative signs. Otherwise the workbook wouldn’t identify them as outflows. If you want to upload disaggregated data, you have to update the formulas in the workbook.
Lastly, edit the data source and set the path to the updated excel file on your local machine or an data source on your Tableau server or whatever. Then, set the Entity and the Year filter accordingly, and the view should update.
After uploading your data, the view will look different from what it looked like with the sample data. There is a bunch of parameters that will help you to get the disired look of your view.
Last thing you want to do is to update the reference line custom tooltip according to your naming conventions for the resprective lines.
That’s pretty much it!