FIVE 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.
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.
Set the "Compare with year before" widget to False and set the "Most recent period" widget to "n-1". We want to compare full calendar years, namely 2016 to 2017.
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 83k in 2016 and 92k in 2017.
We want our company to be profitable and leave a net margin of 43k in 2016 and of 47k in 2017: our indirect costs therefore will be 40k in 2016 and 45k in 2017.
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.