Top 4 Must-Know Investment Banking Charts (Free Download Template included)

I think the greatest gift of Dan Bricklin (“father” of the electronic spreadsheet) and Bill Gates to investment banking humanity is the Excel spreadsheet software. It allows the analyst to create rock star financial and valuation models and help present their analysis in some awesome pictorial format (graphs).

With this, I thought, why not a tutorial on the most popular investment banking, graphs vs. charts?. In this article, I will discuss the following set of graphs -

1. PE Chart
2. PE Band Chart
3. Football Field Graph
4. Scenario Graph (my favorite)

Download the spreadsheet templates for all the graphs here

This PE ratio is, in essence, a payback calculation. It states how many years’ earnings it will take for the investor to recover the price paid for the shares. PE (price to earnings) charts help us understand the multiple values over time. Other things being equal, when comparing the price of two stocks in the same sector, the investor should prefer the one with the lowest PE. If you are new to PE ratios, you may refer to this valuation primer article on relative valuations.

What is a PE Chart?

PE chart helps the investors visualize the trading valuation multiple of stock or index over time. For example, the below PE graph of Foodland Farsi is depicted for March 2002 until March 2007.

Interpretation of PE Charts

• Historically, Foodland Farsi has traded an average PE multiple of 8.6x.
• The standard deviation of the PE multiple signifies the volatility of the PE multiple.
• We note that Foodland Farsi has traded within the range formed by the Upper (defined as Average PE + 1 Std Dev = 12.2x) and the Lower (Average PE – 1 Std Dev = 4.9x).
• We note that the PE multiple for the period after June 2006 has crossed the upper standard deviation line signifying a higher valuation multiple.

Why is it useful?

• This chart is fairly useful because this provides the historical valuation details quickly and easily.

You may take not more than 30 seconds to interpret such a graph.

Dataset for PE Chart

Let us now prepare the PE chart as provided above. Please download the PE Chart dataset here. The dataset includes the following: -

• Date
• Historical Stock Prices
• EPS estimate (forward) – Please note that this data may not be available in the public forum. You may use Bloomberg, Factset, and Factiva (all paid versions) to access such data.

Building a PE Chart

Step 1 - Calculate the PE Ratio

Since we already know the stock price and the forward EPS, calculate the PE ratio of the stock for each date.

Step 2 - Calculate the Standard Deviation of PE

Calculating standard deviation is easy in Excel. You can use the formula STDDEV to calculate the standard deviation of the stock. Do not forget to use absolute references to display the same standard deviations across the dates.

Step 3 - Calculate the Average PE

Calculate the average PE of the stock using the formula AVERAGE and use absolute references as the average of the data should remain constant across all the dates.

Step 4 - Calculate the UPPER and the LOWER range.

Calculate the UPPER and the LOWER range by using the following formula: -

• UPPER = Average PE + Standard Deviation
• LOWER = Average PE - Standard Deviation

Step 5 - Plot the Graph using the following data -

1. Forward PE
2. Average PE
3. UPPER
4. LOWER

Step 6 - Format the Graph

It is very important as formatting can win if you highlight the important areas and make it more intuitive to comprehend.

Like the PE charts, you can also make a Price to Book Value Chart (P/BV), PEG Graph, Price to Sales, or Price to Cashflow Charts.

What is PE Band Chart?

Like the PE ratio graph, the PE band is also computed from the historical PE ratios for each stock/index. The line plotted from the average highest PE will form the upper PE band. At the same time, the average lowest PE will form the lower PE band. The middle PE band will be derived from the mean of the upper and lower band.

Interpretation of PE Band Chart

The above chart can be interpreted as follows: -

• The price line (colored in GREEN) touches the maximum PE band line of 20.2x. Therefore, it implies that the stock is trading at its maximum PE and may be overvalued.
• The upper band would reflect the stock's historical maximum price if it traded at its maximum PE. For example, If we trace back the maximum PE band line till March 2002, we find that the stock would have sold at ₹600/- if the PE during that period had been 20.2x.
• Also, we note that the stock has touched the lowest PE band of 5.0x many times in the last five years. Therefore, it denotes an ideal opportunity to buy the stock.

Why is PE Band Chart Useful?

• The advantage of the PE band is its consideration of both the fundamental factor (i.e., profitability) and the historical trading pattern of a stock.
• The use of the PE band is especially meaningful for listed companies with good track records.
• Its price tends to move within the PE band for a stock with stable earnings. In other words, the stock price in one extreme tends to move to the other extreme within the band.
• Also, note that the PE band chart is different from the PE ratio graph as we note that the Y-axis represents the price of the stock rather than the PE multiple.
• This PE band chart is effective because this graph can denote both the PE bands (valuation) and the corresponding prices. Along with the PE ratio graphs, this makes a case for taking a valuation call on stocks.

PE Band Chart Data set

The PE band chart data set differs from the one we used earlier. It is the same! We require the following:  -

• Historical Stock Prices
• Dates
• Forward EPS

Building a PE Band Chart

Step 1 – Calculate the Forward PE for the Historical Dataset

Step 2 – Calculate the average, maximum, and minimum of the PE ratios

Step 3 – Find the Implied Prices using the following formula

Calculate the implied prices using the procedure below: -

• Price (corresponding to Average) = Average PE x (Historical EPS)
• Price (corresponding to Maximum) = Maximum PE x (Historical EPS)
• Price (corresponding to Minimum) = Minimum PE x (Historical EPS)

Step 4 – Plot the graph using the following: -

• Stock Price
• Implied Average Price
• Implied Maximum Price
• Implied Minimum Price

Step 5 - Format the Graph:-

You can make Band Charts for another set of valuation multiples like EV/EBITDA (Enterprise Value to EBITDA), P/CF, etc

What is a Football Field Chart?

Sometimes it is easier to represent the data in floating columns or bars in which columns (or bars) float, spanning a region from minimum to the maximum values. For example, below is a football field column chart.

Interpretation of the Football Field Chart (above)

• The data represents the company's fair valuation (price/share) under different assumptions, and valuation methods.
• Using DCF, the firm's valuation comes out to be $30/share (pessimistic case) and $45 under (most optimistic case).
• The highest fair valuation of the company is $50/share when using the replacement cost method of valuation.
• However, the lowest fair valuation comes out to be $20/share when using the M&A Transaction Comp valuation.

Data for Football Field Chart

Let us assume that you have been provided with the following data set. Naturally, you want to represent the below data in the best possible graphical format.

There can be different ways of making graphs on such data. However, they may not provide great insights when making a regular line or bar graph. Below is the representation (poor) of these regular graphs: -

Line Graph

The problem with this representation is that it is very difficult to interpret this data.

Column Graph

Again, the same problem is that it is very difficult to interpret such data.

It is now easy to understand that the solution lies in making the floating column or the bar chart in excel.

Building a Football Field Chart

Step 1 – Create the Two series with Minimum and Range

The first series represents the minimum, and the second represents the range (maximum-minimum). Please see below the two series on which we create our graph.

Step 2 – Choose Stacked Column Chart

The secret to making the floating chart is to use the column chart in excel in an effective way by selecting the "stacked column chart in excel" using two series.

You will get the below chart.

Step 3 – Make the “minimum” columns Invisible!

Select the minimum column bars (blue color) and from the top menu, change the color to "No Fill."

With this, you will get the graph below.

Step 4 – Format the graph and make it awesome!

• Change the X-axis to reflect the valuation methodology.
• Remove the legends on the right-hand side (range and minimum)
• Change the color of the bars to suit your color taste (please do not make the columns pink; it is investment banking, you know!)

What are Scenario Graphs?

Sometimes, we need to accept that valuation is not scientific. It depends on assumptions and scenarios. While we value a stock, you may make different beliefs while preparing a financial model – projecting an income statement, balance sheet, and cash flows. While we take the most typical case while we do the valuation, it is equally important to show the impact of different topics, such as if the tax rates go down or if the production moves up more than expected. These scenarios can be easily built using financial models.

You can use the following financial models for your reference: -

• Alibaba IPO Financial Model 
• Box IPO Financial Model

Please see below a sample scenario graph: -

Interpretation of Scenario Graph

• We note above that the base case valuation for the stock XYZ is $300.
• The scenario graphs provide us with additional inputs on downsides concerning the following: -
  • If the product price decreases, the stock's fair price would decrease by $17.
  • If corporate taxes are higher, then the stock's fair price would move further down by another $28.
  • If raw material prices move up, then fair prices of the stock would move down even more by another $25.
  • If we consider all the pessimistic cases (an event when all three negative events happen together), then the fair valuation of the stock would drop to $230 per share.
• Likewise, you can find the additional inputs on the upsides. Considering all the optimistic cases (higher prices, higher taxes, and lower raw material costs), the stock's fair price will increase to $410 per share.

Dataset for the Scenario Graphs

The dataset required for this graph is shown below. The table below arrives after inputting new assumptions in your financial model and recalculating the fair share price.

Building a Scenario Graph

We will assume that you already have the data like the one above. With this, let us look at the steps involved in building the scenario graphs: -

Step 1 – Add two columns, X and Y, to the data set (tricky and most important)

• This graph builds on the Football Field Graph (#3) we made earlier.
• Again, we use the column stacked graph, where Y data gets stacked over the X data.
• In addition, we make the X data set invisible to get the floating Y dataset.
• For example, if you closely observe the scenario graph – downside scenario – Price decrease shows a floating visible data of $17 (Y data). Immediately below this dataset is the invisible $283 (X data).

Step 2 - The completed X and Y dataset should look something like below

Step 3 – Prepare the Column Stacked Graph on the X and Y datasets

Please note that we are not preparing the chart on the original data set. Instead, we prepare the converted dataset (X and Y) chart.

 Step 4 - Make the X dataset hidden.

Hide the X dataset by selecting the columns and choosing “No Fill” from the menu's formatting options.

Step 5 - Format the Graph, and be awesome!

As we noted above, there can be a different graphical representation of stock valuation. We use such graphs to save time for the clients and make the research report or pitch book a time-saving and effective document. You will find the four types of valuation graphs in a majority of the tier-1 brokerage firm research reports. I had worked earlier at JPMorgan as an equity research analyst and found the football field and scenario graphs to be the most useful representation for clients. Of course, you can use these in ratio analysis graphs too.

• Investment Banking in Atlanta
• Investment Banking Interview Questions with Answers
• Top Investment Banking in Malaysia

If you learned something new or enjoyed the post, please comment below. Let me know what you think. Many thanks, and take care.