Skip to content

FIVE MINUTE EBITDA BRIDGE

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

image-20210917185445869

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.

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

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.

image-20210917154205272

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.

image-20210917154510259

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.

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.

image-20210917182310463

You can plot the same chart in % of sales.

image-20210918174147183

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.

image-20210917182719307

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.

image-20210917183218792

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

image-20210918181806431

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.

image-20210917183527333