Investment Banking Charts – I think the greatest gift of Dan Bricklin (“father” of electronic spreadsheet) and Bill Gates to Investment Banking Mankind is the Excel spreadsheet software that allows analyst to not only create rock star financial and valuation models but also help present their analysis in some awesome pictorial format (graphs).
With this, I thought why not a tutorial on most popular Investment Banking Graphs and charts. In this article I will discuss the following set of graphs –
 Investment Banking Tutorials  99 Course Bundle, 500+ hours Videos
 Financial Modeling Tutorials  10 Course Bundle, 50+ hours Videos
 Equity Research Tutorials  11 Course Bundle, 40+ hours Videos
 Private Equity Tutorials  9 Course Bundle, 35+ hours Videos
Download the Spreadsheet templates for all the graphs here
# 1 – PE Chart
This PE ratio is in essence a payback calculation: it states how many years’ earnings it will take for he investor to recover the price paid for the shares. PE (price to earnings) charts help us to understand the valuation multiple 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 the 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 valuation multiple of Stock or Index over a period of time. For example, the below PE graph of a company named Foodland Farsi is depicted over a period of March’02 until March’07.
Interpretation of PE Charts
 Historically Foodland Farsi has traded a 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 PE mutiple for period after June’06 has crossed the upper standard deviation line signifying higher valuation multiple.
Why it is useful?
 This chart is fairly useful because this provides the historical valuation details in a quick and easy format.
 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, Factiva (all are paid versions) to get access to such data
Building a PE Chart
Step 1 – Calculate the PE Ratio
Since we already know the Price of the Stock 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 fairly easy in Excel. You can use the formula STDDEV to calculate the standard deviation of the stock. Do not forget to use absolute references so as 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 also 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 –
 Forward PE
 Average PE
 UPPER
 LOWER
Step 6 – Format the Graph
This is very important as formatting can really win if you can highlight the important areas and make it more intuitive to comprehend.
Like the PE charts, you can also make Price to Book Value Chart (P/BV), PEG Graph, Price to Sales or Price to Cash flow Charts.
#2 – PE Band Chart
What is PE Band Chart?
Like the PE Ratio Graph, PE Band is also computed from the historical PE ratios for each individual stock/Index.The line plotted from the average highest PE will form the upper PE Band, whereas 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

 Currently, the Price Line (colored in GREEN) is touching the Maximum PE Band Line of 20.2x. This implies that the stock is trading at its maximum PE and may be overvalued!
 The upper band reflects the Historical Maximum Price of the stock if the stock would have traded at its Maximum PE. For example, If we trace backwards the Maximum PE Band Line till March’02, we find that the stock would have traded at Rs600/ if the PE during that period would was 20.2x
 Also, we note that the stock has touched the lowest PE Band of 5.0x many a times in the last 5 years period. It denotes an ideal opportunity to buy the stock.
Why PE Band Chart is Useful?

 The advantage of the PE Band is its consideration for both the fundamental factor (i.e. profitability) and the historical trading pattern of a stock.
 The use of PE Band is especially meaningful for listed companies, which have profitable track records.
 For a stock with stable earnings, its price tends to move within the PE Band. In other words, the stock price in one extreme tends to move to the other extreme within the Band
 Also, note that PE Band Chart is different from 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 is able to 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 is no different from the one that we used earlier. Infact, is the same! We require the following –

 Historical Stock Prices
 Dates
 Forward EPS
Building a PE Band Chart
Step 1 – Calculate the Forward PE Ratio for the Historical Dataset
Step 2 – Calculate the average, maximum and the minimum of the PE ratios
Step 3 – Find the Implied Prices using the following formula
Calculate the implied prices using the formula 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 other set of valuation multiples like EV/EBITDA (Enterprise Value to EBITDA), P/CF etc
#3 – Football Field Graph
What is Football Field Chart?
Sometimes it is easier for us to represent the data in floating columns or bars in which columns (or bars) float spanning a region from minimum to the maximum values. Below is a sample Football Field column chart.
Interpretation of the Football Field Chart (above)

 The data represents fair valuation (Price/Share) of the company under different assumptions and valuation methods.
 Using DCF the valuation of the firm 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 Replacement Cost method of valuation
 However, the lowest fair valuation comes out to be $20/share when using M&A Transaction Comp valuation.
Data for Football Field Chart
Let us assume that you have been provided with the following set of data. You want to represent the below data in the best possible graphical format.
There can different ways of making graphs on such a data, however, they may not provide great insights when we make a regular line graph or the bar graphs. Below are 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 that it is very difficult to interpret such data.
With this it is now easy to understand that the solution lies in making the floating column or the bar chart.
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 an effective way by selecting the “stacked column chart” 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 don’t make the columns as pink, its Investment Banking you know!)
# 4 – Scenario Graphs
What are Scenario Graphs?
Sometimes It is important for us to accept the fact that understand that valuation is not a very scientific approach. It depends on assumptions and scenarios. While we value a stock, you may make a different set of assumptions while preparing a financial model – projecting income statement, balance sheet and cash flows. While it is important for us to take the most expected case while we do the valuation, it is equally important to show the impact of different cases like what if the tax rates go down or what if the production moves up more than expected etc. These scenarios can be easily built using Financial Models.
You can use the following financial models for your reference –
Please see below a sample Scenario Graph –
Interpretation of Scenario Graph
 We note from above that the base case valuation for the stock XYZ is $300
 The scenario graphs provide us with additional inputs on downsides with respect to the following
 If price of the product decreases then the fair price of the stock would go down by $17.
 If corporate taxes are higher, then the fair price of the stock 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 – If we consider all the optimistic cases (Higher prices, higher taxes and lower raw material costs), the fair price of the stock would move up to $410 per share
Dataset for the Scenario Graphs
The dataset required for this graph is shown as below. The table below is arrived at 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 to build the Scenario Graphs –
Step 1 – Add two columns X and Y in the dataset (tricky and most important)
 This graph builds on the Football Field Graph (#3) that we earlier made.
 In this we again use the Column Stacked Graph where Y data gets stacked over the X data.
 In addition to this we make the X dataset as invisible so that we 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) and 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 two dataset X and Y
Please note that we are not preparing the chart on the original set of data. We are preparing the chart on the converted dataset (X and Y)
Step 4 – Make the X dataset hidden
Hide the X dataset by selecting the columns and choosing “No Fill” from the Formatting options in the menu
Step 5 – Format the Graph and be awesome!
Conclusions
As we note above that there can be a different graphical representation of stock valuation. The primary reason we use such graphs is to comply 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 tier1 brokerage firm research reports. I had worked earlier at JPMorgan as an equity research analyst and found the Football field graph and Scenario graph to be most useful representation for clients. You can use these in Ratio analysis Graphs too.
What’s Next?
If you learned something new or enjoyed the post, please leave a comment below. Let me know what you think. Many thanks and take care.
Ravi Kumar Gupta says
I am a regular visitor. Thank you for explaining the concepts so well.
Dheeraj Vaidya says
thanks Ravi!
Mohammad Salman Ghani says
Thanks for the all graphs. Really helpful.
Dheeraj Vaidya says
thanks Mohammad!
Jadranka says
Thanks for sharing this. I am happy that your blog reached me.
You have the gift to share your knowledge on very simple ,concise and understandable way.
Dheeraj Vaidya says
Thanks Jadranka 🙂 Glad you know that you find these resources useful.
Best,
Dheeraj
EDMUND STRAMISS says
I like what you are offering serious interested in Financial Analyst work will have to pursue this career as well.
Dheeraj Vaidya says
thanks Edmund. Glad you found these useful.
Abhinav Kaul says
Hi Dheeraj,
Can you mail me the video of this tutorial
Dheeraj Vaidya says
Hi Abhinav, I don’t have the video tutorials of these charts as of now.
Aditya says
i stumbled upon this blog when i look for PE Band formula, thanks for the awesome tutorial sir! it helps me a lot in building a similar template for myself.
but i have a question. why do you “limit” the max PER to 50 and the min PER to 0.001?
=(MIN(50;MAX($G$6:$G$267)))
=MAX(0.001;MIN($G$6:$G$267))
your explanation would be much appreciated
Dheeraj says
Hi Aditya,
not much logic here. You can change the limits so to accommodate your stock PER fluctuations.
Thanks,
Dheeraj
Oketak says
I know that you get the estimated eps from bloomberg or similiar services, but can you explain how is it that the eps data is not quarterly in your example but weekly? Thanks
Dheeraj says
Hi Oketak,
Each week, Bloomberg and many other service providers release the annual/quarterly EPS estimated for companies. This EPS which is released each week is an average of the estimates provided by different equity research analyst (jpmorgan, nomura, merrill etc). If even one of the analyst changes their EPS estimate, then then the average estimate would change.
Thanks,
Dheeraj
Alex Lee says
Hi,
I see that in the excel file you provide you have fwd EPS filled in for every weekly entry. But is that practical when applying this? I see companies only have the Est EPS per quarter, and this will greatly affect the standard deviation afterwards.
Thanks.
Dheeraj says
HI Alex, you can get the data of fwd EPS every week from databases like bloomberg etc. it is not readily available publicly.
Thanks,
Dheeraj
Chern yi says
THIS IS A GEM! So glad I found this 🙂 Thank you so much for sharing!
Dheeraj says
Thank you Chern!
adeel says
Well explained! Btw how are you coming up with weekly epsforecasts?
Piyush Arora says
Hi Dheeraj,
Informative and insightful piece.
Quick question, where are we supposed to download Indianbased historical P/E and per share prices?
You wrote, “Please download the PE Chart dataset here. ”
But the hyperlink (if it was intended to be there) doesn’t seem to work.
Really want this data, paid or not.
Looking forward to hearing from you soon.
Regards,
Piyush
Fellow U.S based analyst !
mahendra edunoori says
what a great things that i am learning from your articles….
Rohitesh says
Extremely well explained sir. I have been a fan of your teaching…since my IICM days, when you had taught us Financial Modelling..way back in 200809.
Refreshing to read your articles Sir.
Regards,
Wall Street Mojo says
Thank you Rohitesh for your kind words.
Madhu says
thank you. it is really a useful stuff. thanks once again.
Wall Street Mojo says
Hi Madhu,
I am glad you found this article on Investment Banking Charts to be useful.
Best,
Dheeraj
Joseph says
Great stuff especially twisting Excel around for optimal results. Keep the good work and I impatiently await your next issue.
Amrit V says
Amazing and all free. Thanks very much for sharing
MUHAMMAD NAWAZ says
sir i am glad to see your publication sir i want your help in making my project proposal for COL (MBA) in H.R.M.
will you please help me in this matter i shall be very greateful to you in this matter if you help me
Pravin says
Its really really great Sir..
Kindly keep posting to me for all such post.
Thank you.
henning says
Nicely done – thank you
Mona says
Awesome.. Thanks for sharing this..
Kinshook Chaturvedi says
One of the best practical article on stock valuation. But where is the Excel sheet model which is downloadable ?
Wall Street Mojo says
Hi Kinshook,
Please look at the first set of paragraphs for “Download the Spreadsheet templates for all the graphs here”. You should be able to download the associated template.
Thanks, Dheeraj
Vishey says
Hello Sir,
I have one question? Do we need to know VBA for building financial models?
Wall Street Mojo says
Hi Vishwantha, You will not require VBA for building financial models for Investment Banks. However, if you are doing lot of scenario analysis and data download and anlaysis, one can make efficient use of VBA for automating mundane tasks
James says
Can you give all this stuff in pdf form so that we can download .
Mahesh says
What an exciting and intelligent way of explaining. Language is very simple and easy to understand. Please continue to do so.
Thanks,
Mahesh
Wall Street Mojo says
Thank you Mahesh for your kind words!
Arinjay says
Great Knowledge sharing process Sir !
Neeraj says
Awesome financial stuff. Amazing Work. Waiting for new IPO valuations.
Raushan says
Thanks …..
Rajan says
Thank you sir for sharing this knowledge. When are you going to write on DCF?
Wall Street Mojo says
I am glad you liked the tutorials. A lot of subscribers want me to write on DCF. Will write about it soon.
Thanks, Dheeraj