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.