5️⃣ MINUTE⏱️EBITDA BRIDGE
In the next five minutes or so, you will build an EBITDA bridge from scratch.
1 - GET DATA
We use Tableau's Superstore dataset you can download here. Open it and save it as a xlsx Excel workbook. The first sheet is the one we are interested in. You can leave the other two in the workbook.
Alternatively you can jump to section 4 and directly open the file in the app by selecting "Tableau Superstore" in the drop down menu.
2 - INSERT CALCULATED COGS COLUMN
Our file looks like this. There is a profit column. The app, however, needs a COGS cost column to calculate Gross Margin.
Insert a calculated column, called COGS, equal to Sales minus Discount minus Profit.
3 - LOAD DATASET ON 'MPARANZA.COM APP
Open the upload widget and load the file.
We will compare YTD periods. You can also compare calendar years of rolling 12 month periods. By the way, the "_2016" (underscore + year) notation you will see in the charts below is the standard IBCS notation for YTD.
To build our EBITDA bridge, we need make up the value of indirect costs in each of the two periods. To do so, we need to know the gross margin value: it is 81k in 2017 and 92k in 2018.
Based on this information we make up a set of reasonable values for indirect costs in the two years .
Open the Excel file and insert a column called "Indirect Costs". Insert two rows at the top and type in the indirect costs for each year in the Indirect Costs column cells. In the Date column cells type in a date for each year. In the Quantity and Amount column cells type in a very small number such as "0.000001" so that the row will not be mistaken by the app for an "all zero" row that can be discarded. Leave all other cells blank. You should have something like this.
4 - GET YOUR EBITDA BRIDGE
Set the 🏃🏻♀️Run widget to "Fix dimension bridge" and the 🎦 Show variance widget to "After COGS". Hit the 🚀 Submit button. You should get this basic EBITDA bridge.
You can plot the same chart in % of sales.
To plot percentages, set the Variance as % of revenues widget to True.
Set the 🎦 Show variance widget to "price, volume & mix, costs" to see if prices or unit costs have changed. They apparently have not, but the labels in this second bridge now show the variance driver.
Now lets split the volume variance impact by Category. Each row of our EBITDA bridge shows the impact of a Category on a given variance driver. To do that just set the "Choose dimension for small multiples" widget to "Category" and the "Plot small multiples" widget to False.
You should get this.
You can get the same chart with the % on revenues impact by Category.
To finish, set the the 🏃🏻♀️Run widget to "variable dimension bridge" and hit 🚀 Submit. You get a more nuanced and informative picture of the combinations of dimensions that impacted on the change of margins.