Creating Bridge Chart with ARR vs AOP

In corporate finance, bridge chart is often used to visualize itemized sales/revenue performance during a particular period, such as a quarter or a year.

Bridge chart can be designed using waterfall chart. But we will use a different approach. Here we add itemized AOPs to the mix so that we can visualize the performance against goals for each item.

AOP means annual operation plan which is equivalent to the target in annual financial planning, usually on a quarterly basis.

Another acronym that is going to be used in this post is ARR, annual recurring revenue. It is often regarding the revenue on a quarterly basis and for each item.

The Data Set

We made up a data set for an example, which is very simple. The BeginningARR is the revenue of the previous period or the previous quarter.

There is no AOP for the beginning of the period. We just put the same ARR number there for the convenience of the calculations that will follow.

We do not need to specify the Ending ARR and AOP because both can be derived from the itemized numbers in the table.

Creating the Bridge Chart

First let's create the chart with ARR values. Because we are going to overlap the ARR values with AOP values, we won't use the waterfall chart approach where the bars are downwardly drawn. 

We will first create a gantt bar chart based on the following values:

This formula may look a bit complex. It comprises a few elements as follows:

• Running_Sum() of ARR values are the cumulative result of ARR leading to total ARR.
• Lookup() with offset -1 will shift the cumulative result to the right by one position.
• ZN() will fill the first position with 0 after the shift towards right.
• MIN(Item)=MAX(Item) is the condition for each item and ELSE is for the total which we force it to be 0 in this case.

For the ARRs, we use Sum(Value) as Size. For the AOPs we use Sum(AOP) as Size. These are all logical following the gantt bar setup. In contrast, in waterfall chart, we have to use negative values for sizes.

A few details

For the AOP label, we don't need it for the BeginningARR because there is none. So the calculation is as follows:

It defines the total and the value per item except the first one (BeginningARR). Note that in the formatting, its prefix is '/ $'.

For Item+, we label the total as EndingARR in the tooltips. Otherwise, it will be shown as All.

Here is the resulting view:

Voila. Feel free to download the companion workbook. 

Leave comments below or reach me at twitter @aleksoft if you have any questions.