There truly is no better program when it comes to crunching financial data than MS Excel. It is very simple to build valuation and analysis models by creating spreadsheet formulas to perform calculations on a firm’s financial data. Getting the data into Excel can be a problem especially if it has to be manually keyed-in. That can become very time consuming and transcribing figures manually is very error prone. With the use of VBA macros, Excel can import specified data automatically from the web. This is an extremely powerful tool for researching investments. Randy Harmelink developed an Excel Add-in that imports a whole myriad of stock data. The best part is that anyone can download and use.
Professionals on Wall Street have had this type of automated analysis for years through such services as Bloomberg. Yet, for the individual investor not willing to shell out $2k a month, he/she resorted to a legal pad and financial calculator. Every investor needs to do their own math so that they get a grasp to the larger story that the numbers tell.
Mr. Harmelink’s Excel Stock Market Function Add-in is located in the files section on this website: http://finance.groups.yahoo.com/group/smf_addin/
It’s a yahoo group where members discuss ideas and share spreadsheets.
Additionally, Models I have created that use this add-in are posted in the files section at this yahoo group:
I recommend visiting these sites and downloading/installing the add-in and then look at the spreadsheets already created by group members.
When I conduct my analysis of a given stock, I usually apply three valuation methodologies
1) 5yr Forecasted EPS / Exit Multiple
2) 3-Stage Discounted Cash Flow Valuation
3) Relative Price-Multiple Valuation
For this post, I will discuss the first model and will discuss the others in subsequent posts.
The EPS / PE model doesn’t require much data to calculate.
-EPS estimates (current yr and next yr)
-Projected 5yr EPS growth rate
First, I analyze historical EPS growth to see if it’s accelerating, decelerating, or constant. I compare this to the analyst’s forecasts and make any adjustments to arrive at what I think is a reasonable growth rate.
Then I take last year’s EPS and project it out 5 years- (EPS * (1+g)). If Consensus estimates are available for this year and next year I will use those figures for year 1 & 2 EPS, and the apply my forecasted growth rate for years 3-5. At the end of year 5, I multiple EPS by a P/E ratio that is reasonable- Industry average etc. That will give the projected stock price in year 5. To calculate today’s value we must discount Year 5’s price back to the present using the required rate of return on equity. If the stock pay’s dividends, those too must be discounted back to the present and added to the PV of the stock price.
Using the Excel Stock Market Add-in all the required inputs can be downloaded automatically in excel. I have created a spreadsheet called Cash Flow Model that includes the above calculation that can be found at the locations mentioned above.
The primary drivers are the projected growth rate and exit multiple so it is crucial that these assumptions represent the most accurate predictions as possible. A useful strategy is to “invert” the model, meaning finding the input assumptions that equate model value to market value. For example, using the consensus growth rate and then finding the exit multiple value that returns the current price. Alternatively, the user could input a desired multiple then find the growth rate implied by the Market price.
The usefulness of valuation techniques is not to arrive at a single valuation as calculated by the model, yet to formulate a range of values for various assumptions. For instance, the assumptions that are implied by the price and valuations for pessimistic / optimistic scenarios. This provides the practitioner with an understanding of what must occur to support a given valuation.
I will discuss methods for estimating growth rates and exit multiple in upcoming posts.