Skip to content

5️⃣ MINUTE⏱️EBITDA BRIDGE

In the next five minutes or so, you will build an EBITDA bridge from scratch.

waterfall (1)

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.

image-20210917152839110

Insert a calculated column, called COGS, equal to Sales minus Discount minus Profit.

image-20210917153357488

3 - LOAD DATASET ON 'MPARANZA.COM APP

Open 'Mparanza.com, and click on "Open Beta".

Open the upload widget and load the file.

image-20210917153619280

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.

image-20220105165206731

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.

image-20220106092903682

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.

image-20210917181949627

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.

waterfall (2)

You can plot the same chart in % of sales.

waterfall (3)

To plot percentages, set the Variance as % of revenues widget to True.

image-20210918174254689

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.

waterfall

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.

image-20210918175058749

You should get this.

waterfall (1)

You can get the same chart with the % on revenues impact by Category.

waterfall (3)

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.

waterfall (2)

image-20220106094605259