Using a Simple Worksheet to Analyze a Stock

Step 3: How Can I Tell if My Figures Are Reasonable?

An example using Communications Inc., with information reported by Value Line, helps illustrate the use of this simple worksheet. The per share information is presented in Table 1, along with some selected ratios.

If you plug these numbers into the worksheet, you would see that the price-earnings ratio model determines a high price of $49.83, a low price of $38.94 and an average price of $44.38, while the dividend model produces a high of $43.91, a low of $34.83 and an average price of $39.37. [You may end up with slightly different numbers due to rounding.] The current price is around $44: It's trading within the predicted range of the price-earnings model, but just above the range for the yield-based model.

Are the assumptions and figures used in the model reasonable? A run through the checklist evaluates this:

Yearly earnings per share appear to be increasing in a fairly stable pattern (except for Year 2), and all of the figures were positive. Value Line's estimate for next year (Year 6), however, shows a smaller percentage increase than in the previous year. Further analysis—and understanding Value Line's reasons for this change—would be useful to determine whether or not you agree with Value Line's assessment.
Communications Inc.'s payout ratio has generally decreased over time and is now below that of the industry. This should enable Communications Inc. to support its dividend payout or even increase the payout if earnings continue to grow.
Communications Inc.'s price-earnings ratio is low compared to its industry. Its dividend yield is roughly equal to the industry average. Who are those competitors? In the Value Line industry comparisons, many are high-flying cellular stocks, which tend to be higher growth and higher price-earnings ratios.
Communications Inc.'s return on equity has been stable and increasing and currently exceeds its industry norm. However, Communications Inc.'s long-term debt ratio (Value Line's measure of financial leverage) has increased recently. Fortunately it is slightly lower than the industry average. Companies can boost return on equity by taking on more debt, but they increase their risk to shareholders in the process.

The financial checklist indicates that some of the assumptions in the model are reasonable, but some—such as the Value Line assumptions concerning dividend and earnings growth—should be examined in more detail. A lower Year 6 earnings per share estimate would, of course, produce lower valuation estimates.

Table 1. Communications Inc.: An Example

	Year 1	Year 2	Year 3	Year 4	Year 5
Price: High ($)	34.9	34.9	37	45.6	43.1
Price: Low ($)	26.3	27.9	28.1	35.1	36.3
EPS ($)	2.37	2.32	2.51	2.67	3.07
DPS ($)	1.61	1.72	1.78	1.86	1.94
BV ($)	14.63	15.18	12.94	14.35	10.98
Estimated Year 6 EPS: $3.30
Estimated Year 6 DPS: $2.00

Financial Ratios	Communications Inc.		Industry
Financial Ratios	Year 5	5-Yr Avg	Year 5	5-Yr Avg
Price-Earnings Ratio (X)	13.2	13.4*	13.9	14.8
Dividend Yield (%)	4.8	5.2*	4.9	4.7
Payout Ratio (%)	63	69	66	69
Return on Equity (%)	28	19.5	20.4	16.5
Long-Term Debt to Capitalization (%)	42.3	38.7	44.7	44.2

* An average of the 5-year high and 5-year low. Source: Value Line.

Conclusion

For this particular company, your search may stop here. For stocks that appear more promising, however, you would need to look at other fundamental aspects of the company before any investment decision is made.

For a simple beginning, the worksheet will provide you with an easy-to-follow systematic approach to determining value. The basic format is to:

Determine which valuation model best suits your needs.
Determine what information you need to gather for those valuations.
Determine what information you need to evaluate the assumption and other inputs used in the models.

Click here for downloadable version of the Valuation Worksheet with formulas imbedded for automatic calculation of averages, ratios, and valuation estimates once you enter the basic data.

TEST YOUR KNOWLEDGE ⟫