If you are in a hurry, you can download an Excel template. For more ideas on how to present quantitative information, check Edward Tufte's books or his website.

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.

**The setup**

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.

**The recipe**

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**

**don't**select column G), and click Insert->Other Charts->Volume-Open-High-Low-Close, as shown in the picture below:

- 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.

**Other Sources**

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).

## Recent Comments

Ian Kaplan:Hmmm, this seems strangely familiar... Great w... | more »Sergeig888.wordpress.com:Do you mind sharing what else you cleaned out? ... | more »