
I was searching around the net for a morning read and a ran into The Financial Whiz. Like a regular blog user I first went to the WHO section. I like to feel like I can connect with the writer. Well to my surprise the blog, spreadsheets and articles were produced by a senior Finance Major at Indiana University of Pennsylvania, Bryan. This young guy already has seven years of trading under his belt, with knowledge in stocks, fixed income instruments, options and forex. Below is a post he made on June 9th and I thought that this may be of interested to MarketClub members and guests. Visit his blog by clicking on the logo above this paragraph.

[Courtesy of The Financial Whiz . com...]


One of the approaches that the Indiana University of Pennsylvania Student Managed Investment Portfolio utilizes to value companies is known as the Comparable Ratio Valuation. This method uses a spreadsheet that I developed last year in allowing a potential investment to be compared against its closet competitors to find a company’s “intrinsic value.” While this approach may ignore fundamental flaws in a company’s business model, it gives an investor a good base to start analyzing potential stocks to invest in.
To download a copy of the valuation spreadsheet model, please visit: http://www.iupsmip.com/component/
option,com_remository/Itemid,36/func,startdown/id,100/
In the above spreadsheet example, an analysis of Amgen (AMGN) was done and the spreadsheet ultimately found the company to be trading at a discount of about 19% to its peers.
This spreadsheet is not the only determining factor in an investment decision; the sheet is preliminary a tool to analyze and identify stocks that will require more detailed research before a final decision can be made. The twenty minutes that it takes to input the numbers into the spreadsheet will save you roughly two hours or more compiling due diligence. In a future post, I will present the SMIP’s fundamental analysis worksheet that must be completed prior to the company being presented to the team.
At first, the spreadsheet is somewhat difficult to maneuver, but I will guide you through the process.
The areas highlighted in green are areas that will require you to input information to be used in compiling the intrinsic value of the company.
Company Inputs Section:
The data to be used here can be found at the links provided, but when analyzing a different company, please replace the ticker symbol with that of your company.
|
Item
|
Can Be Found at
|
|
Price Per Share:
$56.94
|
http://finance.yahoo.com/q?s=
AMGN
|
|
EPS (Earnings Per Share): $4.41
|
http://finance.yahoo.com/q/ae?s=
AMGN under the Earnings Est section, I tend to use the Next Year Average Earnings Estimate
|
|
Growth Rate:
11.2%
|
the bottom of the page of the following link http://finance.yahoo.com/q/ae?s=
AMGN under the Growth Est, in this figure I tend to use the next 5 years prediction.
|
|
Market Cap:
$66,030.0
|
http://finance.yahoo.com/q?s=
AMGN, where the figure is $66.03 Billion, which the figure that should be used in the spreadsheet should be in millions, therefore, billions would appear in thousands. If a company had a market cap of $500 million, it should be inputted as 500.00 into the spreadsheet.
|
|
Sales:
$14,740.00
|
the Key Statistics in the Income Statement section at http://finance.yahoo.com/q/
ks?s=AMGN under Revenue (ttm). This number should be listed in the spreadsheet in millions.
|
|
Book Value
per Share: $17.01
|
the bottom of the page at http://finance.yahoo.com/q/ks?s=
AMGN under the Balance Sheet section.
|
|
Cashflow per
Share: $3.49
|
http://finapps.forbes.com/finapps/
jsp/finance/compinfo/Ratios.jsp?
tkr=AMGN under the Latest 12 Month
Data in the middle of the page, this
is a dollar amount per share.
|
|
Shares Outstanding (mil): 1,160.00
|
http://finance.yahoo.com/q/ks?s=
AMGN under the Share Statistics
section, this number should also be
listed as in millions.
|
|
EBITDA (mil):
$6,480.00
|
http://finance.yahoo.com/q/ks?s=
AMGN under the Income Statement section, this number should be listed in millions.
|
|
Enterprise Value
(mil): $68,610.00
|
http://finance.yahoo.com/q/ks?s=
AMGN under Valuation Measures section, this should be listed in millions.
|
|
PEG Ratio: 1.23
|
http://finance.yahoo.com/q/ks?s=
AMGN under Valuation Measures section, this should be listed as is.
|
AMGN Competitors Section:
To get a good start on which companies to use as your comparable analysis, you should visit http://finance.yahoo.com/q/co?s=AMGN and http://finance.google.com/finance?q=AMGN and look under the Related Companies section. You want to find a sample of 5-6 related companies to input their valuation ratios to value your company. When selecting companies to use as comparables, you should look for companies that have a market cap above $250 million.
I will give an example of how to collect the information for the first competitor on the list BAX, Baxter International. You will need to collect the following information:
After this step, repeat the above processes for the other 4-5 competitors. If the ratio is not available for a company, just leave that section blank so it will not be used in the valuation calculation. If one company’s ratio is an outlier as compared to the other companies in the spreadsheet, consider leaving that section blank so it will not give an artificially high intrinsic value.
The Intrinsic Price Calculation:
The spreadsheet then takes the average of the 5-6 competitors’ ratios and then applies those ratios to the information provided about the main company under analysis. It then gives a price of the company for each of the valuation ratios: Price/Earnings, PEG, Price/Sales, EV/EBITDA, Price/Cashflow, and Price/Book. All of the prices that are given from those ratios are then averaged together to give the “Average Intrinsic Price”, which is then compared to the current stock price. Finally, a percentage drawn from these calculations shows if the company is overvalued or undervalued—and by how much it is. If the percentage is positive, then the company is undervalued as compared to its peers, and if the percentage is negative, then the company is overvalued.
I hope you enjoy as much as I did!


Email me with any ideas, questions, or comments.
Lindsay Bittinger
li*****@in*.com