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

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 that this post has been helpful to those of you who are looking for a model of valuing stocks. This model should not be used by itself since it ignores other factors that go into the value of a stock, but it can help identify stocks for further research.

If you require any assistance in using the spreadsheet, or have any other questions or comments, please feel free to contact me at bryan@thefinancialwhiz.com.