Squared Sankey P&Ls in Tableau
Aug 13, 2020
Klaus 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.
Interpreting the Chart
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.
Levels and Width
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
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.
Flows Out
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.
(Sub-) Totals
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:
- Company A has extremely low Cost of Goods (the materials or products it buys in order to generate revenue) but significantly high Operating Costs due to payroll,
- Company B is the reverse.
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.
Remainder
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 & Margins
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.
Using the Template
Upload your P&L data
You can download the template workbook from Tableau Public. To be able to update the underlying input sheet, please also download the model sheet.
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.
Workbook settings
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.
- Kickout-side for inflows
Revenues can flow in from the left or from the ride. Set the parameter to your preferences. - Revenue as inflow
The no setting will show revenues on level 1 as simplified rectangles. - Height Line
Sets height for levels. (=remainder+subtotal) - Height Subtotal
Defines height for subtotals. - Gap Line
Height of the gap between two line. - Kickout-Length
This defines the length of the Allen Wrenchs/Tetris elements. - Kickout-Offset
Parameter sets the length of the shorter sides of the Allen Wrenchs/Tetris elements. - Length Lines horizontal
Sets Lengths of horizontal lines. - Length Lines vertical
Lenght of the vertical lines at top and at the bottom of the chart. - Gap Label
Defines a gap between the chart and the label lines. - Spacer Labels
Depending on what your labels show, adding or deleting blanks from this parameter will prevents overlapping of labels and lines. - Xaxis_fix_left
Controls a reference line to fix the x-axis on the left side. - Xaxis_fix_right
Does the same for the right side.
Last thing you want to do is to update the reference line custom tooltip according to your naming conventions for the resprective lines.
Wrap Up
That’s pretty much it!
We hope, you find good use cases for this. Please reach out to us with feedback in the comments below or on Twitter (@ProfDrKSchulte or @WilhelmsBrick).
2 COMMENTS
Matjaž Vidovič says:
Hi,
great example of how to be inovative with visualizing P&L. How do you show if there is Loss in your P&L. Is then this visualization changed or it is just the purpose for the visualization to show split of Revenue? Thanks
Klaus Schulte says:
Hi Matjaz, you can also show losses. Have you tried it out? The subtotals are show on the left side when there are negative subtotals or totals.