Using a Simple Worksheet to Analyze a Stock

Step 2: What's the Best Way to Organize the Data I Collect?

To work through the equations at the bottom of the worksheet, you need to fill out the top section. This section—the Financial Statement and Ratio Analysis—collects the information needed in the valuation models, and also provides figures that will serve as a financial checklist. This financial checklist helps analyze the assumptions upon which the model is based, since if these assumptions are incorrect, your valuations are invalid.

The figures and ratios you fill in here can be gathered using a company's financial statements, which means you will have to calculate many of the ratios yourself. A better bet, particularly for a beginner, is to use the various stock information sources that do much of the legwork for you.

The first section indicates per share information concerning the stock: the high and low share prices for the last five years, as well as earnings per share and dividends per share for each of the last five years (numbers are entered left to right, with the Year 1 column containing the oldest figures and Year 5 the most recent). For the earnings per share and dividends per share figure, it is also useful to determine the five-year growth rate. This growth rate can then be used to develop your own estimate of next year's earnings and dividends.

Equation to Determine Growth Rate of Earnings Per Share (EPS) & Dividends Per Share (DPS)

 

The next section lists financial ratios; here, the two primary ratios we are focusing on are the price-earnings model and dividend yield. For this model, these two figures should be calculated from the per share data: for price-earnings ratios, divide the high and low share price by the earnings per share; for dividend yield, divide the annual cash dividends by the low and high price. Averages are obtained by adding the yearly figures and dividing by the number of years with valid figures. Note that if earnings are negative or dividends nonexistant, you will be unable to calculate a figure for that year.

Also listed in this form is the payout ratio (dividends per share divided by earnings per share); return on equity (earnings per share divided by book value per share), and financial leverage [such as long-term debt to capitalization (long-term debt plus equity) or long-term debt to equity], which are used as part of your financial checklist. Most of these ratios can be calculated from the per share financial data in this worksheet, or they can be taken from the stock information sources. Financial leverage cannot be calculated by the per share data in this worksheet and the various sources use different measures. For these reasons, it is important to stick to one information source when making comparisons.

Financial ratios for the industry in which the firm operates (or a close competitor) as well as for the market as a whole, are listed as part of the checklist.

The Financial Checklist

It's easy to compare the valuations you come up with to the current market price. But those valuations are only as good as the inputs and assumptions used in formulating the models.

For instance, the models assume that the firm's growth prospects have not fundamentally changed. But will growth continue at its current pace? The models also assume that historical relationships will continue. But were past relationships affected by a one-time occurrence that is unlikely to continue? Will dividends continue to be paid at the same rate?

Examining the historical patterns of the per share figures and ratios, and comparing them to competitors and industry and market benchmarks, is particularly useful in evaluating your inputs and assumptions.

• Have earnings grown at a stable rate?
• Have the earnings per share been steady and positive each year, or have they been volatile, making predictions more difficult?
• For dividend-paying firms, has the payout ratio been steady? Increases in the payout ratio, and payout ratios above 100%, are an indication that future dividends may go down; high payout ratios mean slower or no dividend growth, and perhaps even a decline.
• Is the current price-earnings ratio low relative to the market and industry or competitor, and does this vary from previous years?
• Is the current dividend yield high relative to the market and industry or a competitor, and does this vary from previous years?
• Has the return on equity, a measure of financial return that provides an indication of how well the firm has used reinvested earnings to generate additional earnings, been high and stable?
• Is the use of financial leverage, a measure of financial risk that indicates how much of the assets of the firm have been financed by debt, low relative to industry norms?

Continue to Step 3 »