A Simple Valuation Spreadsheet

Many individual investors view individual stock analysis and selection as a daunting task. However, the process can be made easier by organizing the decision-making process to ensure that pertinent data and information are evaluated in a logical sequence so that the investor can arrive at a reasonable and thoughtful decision.

The ultimate goal of fundamental analysis is to determine, using a variety of variables, what you think a stock is really worth and comparing that estimated value to the stock’s current stock price. This way you can gain an insight into whether the stock is undervalued, overvalued or fairly valued.

This installment of the Spreadsheet Corner introduces a simple valuation spreadsheet from AAII’s book “Stock Investing Strategies,” by Maria Crawford Scott and John Bajkowski. This spreadsheet provides an easy-to-follow, systematic format that walks you through the complete process of arriving at a value for a stock without getting bogged down in complicated financial formulas and analysis. AAII members can download a free electronic copy of the book from the Investment E-books segment of the Getting Started area of AAII.com (www.aaii.com/investing-basics/investment-ebooks). You can download the Simple Valuation Spreadsheet from the online version of this article at the Computerized Investing website, as well as from the Spreadsheets section of the AAII Download Library (www.aaii.com/download-library/results?Category=SS).

The Valuation Spreadsheet

The Simple Valuation Spreadsheet, shown in Figure 1, contains two valuation models at the bottom. One is based on a firm’s earnings and the other on its dividends.

The underlying formulas for the two models look different, but they are actually quite similar. They equate a stock’s price to a stream of future earnings or dividends by asking the question: How much are investors willing to pay now for these future expected streams?

Both models make the key assumption that the growth prospects of the firm will not change fundamentally over time. Relying on this assumption, we can use the historical relationships between the stock’s price and its earnings or dividends to estimate future values. If current market prices differ significantly from the estimated value based on these historical relationships, it means that the market—for whatever reason—is evaluating future income potential differently and may be mispricing the stock.

Earnings Model

The first model at the bottom of the valuation spreadsheet is geared toward stocks with low or non-existent dividends—the traditional “growth” stock—and is a price-earnings ratio (P/E) approach.

The price-earnings ratio (share price divided by earnings per share over the last 12 months) indicates how much investors are willing to pay for each dollar of a firm’s earnings. The higher the price-earnings ratio—the more investors are paying for earnings—the more confident investors are about the expected future earnings. Conversely, lower ratios indicate low earnings expectations, or a low confidence in earnings predictability.

For the earnings valuation, the average annual high and low price-earnings ratios are calculated for prior years. Multiplying these historical ranges by an estimate of next year’s earnings per share provides an estimate of future value.

While it may seem difficult to make an earnings estimate, the recent earnings history that is part of the worksheet will give you some basis for forming those expectations. In addition, there are a number of sources where you can obtain analysts’ estimates of future earnings, including Morningstar.com, Reuters.com, Yahoo! Finance and Zacks.com.

Dividend Model

The second valuation model in this worksheet is primarily for mature, dividend-paying stocks, which tend to be low-growth stocks. As such, it is a dividend yield approach. Dividend yield—annual dividends per share divided by share price—is the annual dividend as a percentage of the current stock price. In other words, it relates share price to dividends: the lower the dividend yield, the greater the company’s emphasis on earnings growth and the greater the disregard for dividend income. The higher the dividend yield, the lower the expectation among investors of earnings growth; instead there is a greater emphasis on dividend income. At the extreme, a high dividend yield may indicate the expectation of a dividend decrease.

This approach requires an estimate of the next expected annual cash dividend. Again, the recent dividend history in the worksheet should provide you with a feel for changes over time. Also, some services provide the indicated dividend, which is the total projected dividend per share payment over the next 12 months.

Dividing the expected annual dividend by the average low dividend yield will give a high-price estimate; dividing the expected annual dividend by the average high dividend yield results in the low-price estimate.

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, the industry or a competitor, and does this vary from previous years?
• Is the current dividend yield high relative to the market, the industry or a competitor, and does this vary from previous years?
• Has the return on equity, 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 firm’s assets have been financed by debt, low relative to industry norms?

Sources of Data

Thus far our discussion has focused on the bottom portion of the spreadsheet—the valuation models that help you find stocks that are potentially undervalued or overvalued based on their earnings and dividend histories.

However, in order to arrive at these valuations, you need to fill out the top portion of the spreadsheet. This section—the Financial Statement and Ratio Analysis—organizes the information needed for the valuation models and provides figures that serve as a financial checklist for your analysis. Here you can analyze the assumptions underlying the valuation models; this is important, because if these assumptions are wrong, your valuations will be flawed.

You have several options for collecting the underlying data to populate this spreadsheet. Luckily, filling in the yellow-highlighted cells is the only legwork you need to do, as this spreadsheet calculates the ratios and valuations for you.

The first section indicates per share information regarding the stock—the high and low prices for each of the last five years, as well as earnings per share and dividends per share figures for each of the last five years (moving from left to right, the Year 1 column contains the most recent figures and the Year 5 column contains the oldest figures).

Price Data

Using a free site such as Morningstar.com, you can find the high and low prices of a company on an annual basis. Figure 2 shows the annual price history for Kellogg Company (K) from the start of 2005 through the end of 2009. Interestingly enough, however, we found a data error at the Morningstar site. While the site claimed that the high price for K shares in 2006 was $57, we could not find another supporting source. Instead, we found multiple stock charts that pegged the high price for that year at $50.95, which is what we used for our analysis.

Earnings and Dividends

After entering in the high and low prices for the last five years, the next step is to locate the annual earnings per share and dividends per share figures, also for each of the last five years. The “Financials” section of Morningstar.com provides five years of financial statement data for free. While, from a time-saving standpoint, it is convenient to find the necessary data from a single source, another excellent site for financial statement data is SmartMoney.com. Here you will find up to 10 years of annual data and 15 quarters of quarterly data. Figure 3 shows the annual financial data for Kellogg over the last five years.

Using the historical earnings and dividends data, we can calculate five-year growth rates for both. The equation built into this valuation spreadsheet to calculate the growth rate of earnings per share (EPS) and dividends per share (DPS) is as follows:

[(Y₁ ÷ Y₅)^1/n – 1.00] = g

Where:

Y₁ = latest EPS or DPS value
Y₅ = earliest EPS or DPS value
n = number of annual compounding periods
g = growth rate (%)

Note that with five years of data, you will have only four annual compounding periods—Year 5 to Year 4, Year 4 to Year 3, Year 3 to Year 2 and Year 2 to Year 1. Also, this formula only works when the beginning and ending values are positive.

Financial Ratios

The next section of the valuation worksheet lists financial ratios, specifically two primary multiples—price earnings ratio and dividend yield. For the valuation models, these two figures are calculated from the per share data. For the price-earnings ratios, the spreadsheet divides the high and low prices by earnings per share for the same year. For dividend yield, the annual cash dividends per share payments are divided by the low and high price for the same year. The spreadsheet arrives at the five-year averages by adding the yearly figures and dividing the sum by five. If earnings are negative in a given year, or if dividends are nonexistent, the spreadsheet will return an “na” value for that year. In this case, you will need to adjust the divisor in column H to reflect the number of years with a valid earnings or dividend value.

Also included in this spreadsheet 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 equity or long-term debt to capitalization (long-term debt plus 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 stock information sources. Financial leverage cannot be calculated by the per share data in this worksheet, and various sources use different measures. Again, Morningstar.com offers five years of financial leverage (assets to equity) and debt-to-equity data for free.

Financial ratios for the industry in which the firm operates (or for a close competitor), as well as for the market as a whole, are part of the checklist. Unfortunately, it is becoming more difficult to find multiple years of historical financial data and sector/industry/market data all at the same site, for free. Morningstar.com and Reuters.com are two sites that do offer this comparative data for a more complete company analysis.

Are Figures Reasonable?

For this article we used Kellogg Company, the Michigan-based cereal and snack maker, to illustrate the use of this simple worksheet. We used data available for free to supply the data required for this spreadsheet (those cells in Figure 1 highlighted in yellow).

Plugging these numbers into the valuation models, you see that the price-earnings ratio model determines a high price of $66.34 (cell I30) and a low price of $52.54 (cell I32) for an average price of $59.44, while the dividend model produces a high of $67.19 (cell I35) and a low of $51.74 (cell I37) for an average price of $59.47. [You may end up with slightly different numbers due to rounding.] The current price is around $50: It’s trading slightly below the predicted range of both the price-earnings model and 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, and all of the figures were positive. Morningstar’s earnings estimates for the current fiscal year of $3.58 per share and $4.10 per share for the next fiscal year indicate that this trend is expected to continue. This signals earnings growth over the next two years that is almost double that of the last five years. Further analysis would be useful to determine whether or not you agree with Morningstar’s assessment. Had we used Morningstar’s earnings estimates for Year 6 ($3.58), we would have arrived at a valuation range of $55.34 to $69.86 using the price-earnings model.
• Kellogg’s payout ratio been relatively stable over the last five years. We were unable to locate an online source for industry norms, so we used AAII’s Stock Investor Pro fundamental stock screening and research database program for the industry payout ratio data. From this data we see that Kellogg’s payout ratio is well above the industry median, or midpoint, value. Further investigation revealed that only about 30% of the companies in the food processing industry pay a dividend, lowering the median value for the overall industry. When comparing the median value of those companies paying a dividend, Kellogg’s payout ratio is in line with its industry counterparts. This indicates that the company should be able to support its dividend payout or even increase the payout if earnings continue to grow.
• Kellogg’s price-earnings ratio is low compared to its industry. Its dividend yield is roughly equal to the industry average.
• Kellogg’s return on equity (ROE) has risen over the last five years, although it dropped from Year 2 to Year 1. There was a significant increase from Year 3 to Year 2, as Kellogg appears to have significantly raised its debt load (as shown by the increase in its debt-to-equity ratio, which is what we used to measure financial leverage). The company’s debt-to-equity ratio is significantly higher 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 assumptions concerning dividend and earnings growth—should be examined in more detail. A higher Year 6 earnings per share estimate would, of course, produce higher valuation estimates.

Conclusion

While this valuation spreadsheet offers a basic framework for analysis, you would need to look at other fundamental aspects of the company before any investment decision is made.

For a simple beginning, the Simple Valuation Spreadsheet 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, and
• Determine what information you need in order to evaluate the assumption and other inputs used in the models.

Maria Crawford Scott, former editor of the AAII Journal, John Bajkowski, president of AAII, and Wayne A. Thorp, CFA, editor of Computerized Investing, contributed to this article.