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:
http://groups.yahoo.com/group/stock_analysis_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.
Inputs:
-Historical EPS
-Historical DPS
-EPS estimates (current yr and next yr)
-Projected 5yr EPS growth rate
-Stock’s Beta
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.
Interesting! Though it's definitely important to arrive at a well-estimated prospective EPS growth rate. I read somewhere that companies are able to manipulate these EPS figures by forming conglomerates through share exchange...
ReplyDeleteEPS is a nebulous figure. It can be distorted a number a ways, a analysts usually come up with their own figures after excluding/including some items to try to arrive at a cleaner number. Firm, knowing this, will take big "one-time" charges or "Big Baths" to write off expenses in one fail swoop since analyst will ignore those expenses in their EPS calculations. Additionally, those expenses are written off in one accounting period, instead of being spread over multiple periods, so the result is lower EPS in the current period (which analysts back out) and higher EPS in future periods.
ReplyDeleteEPS can also distorted from share buy-backs or share issuance. Plus, dilution from employee stock options, warrants, and convertible debentures. EPS has its shortcomings.
Prospective growth rates have major problems too. Research has illustrated that 5yr projections are very inaccurate, and most often err on the high side. Actual growth rates tend to be lower than forecasts. It seems that "when in doubt" analyst choose the customary growth rate of 15%. Many times this doesn't make sense regarding instances where projected EPS growth for year 1 and 2 may be expected to decline yet the Projected 5yr annual growth rate is 15% or so. So this implies that growth in years 3-5 will have to be extreme to offset the first two years of decline. Or it could be the other way around, growth years 1-2 may be estimated at 50% a yr, thus growth for 3-5 will have to be real low or negative.
I don't think analysts connect their near term EPS projections with their 5yr annual growth rate forecast.
Research has shown that estimates for EPS (year 1 & 2: current year and next year) are quite accurate.
That suggests the best approach possibly is to use estimated EPS for year 1&2 and then apply a growth rate for years 3-5. That growth rate should be reasonable, long-run historic average, or industry average, etc. Careful attention must be paid to analysts' 5yr projected growth rate to determine if it's reasonable and probable.
Turley, in Cell B12 of the EPS-PE Model, there is a very complex formula to calculate 'Normal PE'. I also noticed that the cell has no dependencies, so I assume it is there for informational purposes. Still, I can't quite decipher it. Could you explain what's going on there, either in algabraic terms or just verbally (preferably both)?
ReplyDeleteEssential that calculation is a two stage dividend discount model based on the formula: PE= payout ratio/(Ke-g). Essentially, the calculation assumes EPS is $1 and then values the DPS for 5 years @ payout ratio and growth rate assumptions, then assumes payout of 65% for the residual value and growth of 3% and (Ke-g) equals the LT discount rate. Hence, the calculation values $1 of EPS to arrive at a price which is similarly the P/E Ratio. Its just a guide or "reality indicator".
ReplyDeleteDoes that help?
How would import the earnings date of a stock into google excel from yahoo finance?
ReplyDeleteHow do you get the yahoo earnings date into a cell for a stock list?
ReplyDelete