EBITDA BRIDGE

The tool helps break down the change in EBITDA between to periods into the specific factors driving change.

DATASET FORMAT

You need to have a column in your dataset with your indirect costs for the two periods you want to compare. This column should be called with one of these names: 'Indirect_Costs', 'Costi_indiretti', 'Coûts_indirects', 'Overhead', 'FP&A'.

There is no need to allocate your indirect costs at the line-by-line level.

EBITDA BRIDGE CHART FORMAT

The tool will return an EBITDA bridge with one item, labeled "Indirect costs" showing the change of the total value of indirect costs from one period to the other. Allocation is therefore not necessary.

You can toggle between the "with" and "without" total view of waterfall charts with this widget.

Set on False will show this.

An interesting approach is to analyze the factors behind the change of EBITDA along a particular dimension, here channel, aggregating the different EBITDA variance factors...

...or, alternatively, split variance in its volume, price and cost components. The balance value is tied to the fact that the EBITDA bridge walk was limited to five elements.

When the limit is lifted, the balance value goes to zero.

EBITDA BRIDGE ANALISIS ALONG CHANGING DIMENSIONS

The tool can also return a 'variable dimension' EBITDA bridge showing different dimensions, or different combinations of dimensions, that explain the change in EBITDA.

In this example, sales of black hair color products drove a positive impact on EBITDA of 12k, while poor sales of Permanent hair color products in shades different from black, in the Pharmacies channel, resulted in a negative 10k loss of margin.

This is the break down the 12k black margin reduction, that appears to be driven by the two following combinations of factors.

The change in margin can be plotted along any dimension, here color. Black is indeed doing well and has contributed 12k to the increase in margin, a 63% hike. Blonde unfortunately is not doing so good.

In variable dimension EBITDA bridge analysis, the dimension can change for each row result. Therefore there can, and probably will, be a number of different sets of combinations that explain the change in EBITDA. Each set is potentially an opportunity to see the problem from a different angle.

This "alternative" EBITDA bridge representation reveals that most of the positive impact of 'black' on EBITDA was due to the 'sachet' pack type.

EBITDA BRIDGE ANALYSIS IN % OF SALES

An EBITDA bridge can also be shown, instead of in absolute value, in % of sales. In this example, EBITDA fell from 49 to 26% mainly due to a COGS impact, while higher indirect costs contributed a negative 8%.

The same analysis of the impact on EBITDA in % can be done along a dimension, for instance channel.

INDIRECT COST ALLOCATION

In you can simply add two rows on the top of your dataset with the value of the indirect costs in the two periods. No need to specify the dimensions. Note that the Amount and Unit column has been set to 0.000001, because, to optimize performance, tool drops zero rows.

Note that if you want to calculate both yearly and the monthly variance, you will need to input 12 monthly indirect costs values for each year you want to consider.

Do not forget that the excel format of the number columns must be "general" or "number", otherwise the app will have problems parsing the CSV file.

Your data might have indirect costs allocated for each row, as in this example.

The tool will still not show the allocated portions of the indirect costs unless you filter the data. For instance the small multiple chart by "channel" will be returned at the "gross margin" level.

However if you filter, in this example, by "Channel" = "supermarkets", the chart will be returned at the "net margin" level, for that specific sub-set of data.

If you want to filter the dataset along different dimensions and get back the full EBITDA bridge, you will need to split the indirect cost values accordingly, allocating a portion to each row in your dataset. However if you are only interested in filtering along one dimensions you can simply enter the indirect costs of those dimensions.

For instance, say you want to filter the report by market, and you operate in two markets, Italy and France, with, say, 10.000 and 20.000 indirect costs respectively the first year and 40.000 and 60.000 the second.

You need to input these four values in four cells, two corresponding to rows where "Italy" is referenced and two corresponding to rows where "France" is referenced. Don't forget to make sure your rows correspond to the right periods and don't forget the "0.000001"s in the amount and in the units columns to avoid your rows being dropped.

Now you filter for Country = Italy...

and run the analysis

Once you filter your analysis by Country - Italy, the fixed dimension bridge report will show the indirect cost variance for Italy only.

Works the same way with variable dimension bridge analysis.

ADDING MESSAGES TO THE EBITDA BRIDGE PLOT

It helps to enrich the EBITDA bridge plot with an explanatory message...

...and to add shapes to highlight important information.

TRY IT OUT!

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

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.

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.

LOAD DATASET ON 'MPARANZA.COM APP

Open the "get started" page.

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.

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.