Giving a new meaning to Financial Engineering, when I worked in Corporate Finance I was sometimes asked to build bridges. After the initial confusion, I found out that a bridge is a graph that is useful to explain differences between two periods of time in an account.
The usual example (and application) is creating a graph for a budget discussion. A bridge like the one depicted below can quickly show the reasons for the differences between the budget in 2011 and the budget in 2012.
I build these graphs with the Stock (Volume - Open - High - Low - Close) chart in Excel.
To build the graph yourself, you'll need to know N+2 numbers, where N is the number of "buckets" that explain the transition between the two periods. In our example, we have four buckets, and therefore we'll need N+4 = 6 rows, one for each number.
The N=4 buckets in our ficticious case are:
- New customers
- Outsourcing savings
- Production efficiency
- Cost of capital.
The N+2 = 6 numbers are:
- Value for the budget in 2011 = 3.4M
- Additional expenses due to new customers (a positive number) = 7.5M
- Savings due to outsourcing (a negative number) = -2.2M
- Savings due to production efficiencies (a negative number) = -2.9M
- Additional cost of capital (a positive number) = 0.8 M
- Value for the budget in 2012 = 6.6M (the sum of the five numbers above)
To build the chart, you'll need a table with N+2 rows and 7 columns. The number of columns is the same regardless of the number of rows. In our example, we need N+2 = 6 rows (one for each number) and 7 columns.
For simplicity, I will assume that your table will begin in Cell A1, and therefore will be delimited by cells A1 and G(N+2). In our example, the table will be delimited by cells A1 and G6.
The recipe has four steps. Ultimately, we're going to enter data in only a few cells, as shown in the picture below.
The step-by-step guide on how to determine the formulas to enter in each cell is below.
Step 1 - The first column
Column A will have the names of all the buckets, including the starting and ending values.
In our example:
- A1: Budget 2011
- A2: New Customers
- A3: Outsourcing Savings
- A4: Production Efficiencies
- A5: Cost of Capital
- A6: Budget 2012
Step 2 - The last column
Column G will have the values for each one of the buckets (numbers 1-6 in our example).
- G1: Value for Budget 2011 = 3.4
- G2: Value for New Customers = 7.5
- G3: Value for Outsourcing Savings = -2.2
- G4: Value for Production Efficiencies = -2.9
- G5: Value for Cost of Capital = 0.8
- G6: Value for Budget 2012 = 6.6
Step 3 - Row formulas
- 3a) First row: cell B1 should point to G1
- 3b) Second row: cell C2 should point to B1, cell F2 = C2+G2
- 3c) Rows 3 to N-1:
Cell C(row) points to cell F(row-1). For example, C3 = F2.
Cell F(row) = C(row) + G(row). For example, F3 = C3+G3.
- 3d) Last row: cell B(N) should point to G(N). In our example, B6=N6. Another alternative is to have it point to F(N-1), or F5 in our example.
Step 4 - Build the graph
- The graph created by this procedure has a meaningless legend. Right-click it and delete it.
- The graph also has a secondary axis that is meaningless in this context. Right-click the secondary axis (the numbers on the right of the graph) and delete it.
- If you don't like the colors of the up and down bars, you should right-click one of them and select "Format Up/Down Bars". I usually format my up bars as red (increasing budget is usually bad) and the down bars as green (savings!). If your graph is about revenue, the opposite makes sense.
- It is always a good idea to add a descriptive title.
There are a few other ways of building this type of chart. Most of them are far more involved. For example, you can check Chandoo.org, PeltierTech and even one from Microsoft (provided by a third party).