Stock Valuation & Data collection
Figure 1. Simple Valuation Spreadsheet
Read Comments (9)
Computerized Investing > January 2011
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 decisionmaking 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 easytofollow, 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 Ebooks segment of the Getting Started area of AAII.com (www.aaii.com/investingbasics/investmentebooks). 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/downloadlibrary/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
To download the Excel Spreadsheet, click here.
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
nonexistent dividends—the traditional “growth” stock—and is a priceearnings ratio (P/E) approach.
The priceearnings 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 priceearnings 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 priceearnings 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, dividendpaying stocks, which tend to be lowgrowth 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 lowprice 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 onetime occurrence that is unlikely to continue? Will dividends continue to be paid at the same rate?
SPECIAL OFFER: Get AAII membership FREE for 30 days!
Get full access to AAII.com, including our marketbeating Model Stock Portfolio, currently outperforming the S&P 500 by 2to1. Plus 60 stock screens based on the winning strategies of legendary investors like Warren Start your trial now and get immediate access to our marketbeating Model Stock Portfolio (beating the S&P 500 2to1) plus 60 stock screens based on the strategies of legendary investors like Warren Buffett and Benjamin Graham. PLUS get unbiased investor education with our awardwinning AAII Journal, our comprehensive ETF Guide and more – FREE for 30 days
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 dividendpaying 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
Figure 2. Kellogg Data on Morningstar.com
down; high payout ratios mean slower or no dividend growth, and perhaps even a decline. Is the current priceearnings 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 yellowhighlighted 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
Figure 3. Kellogg Data on SmartMoney.com
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 timesaving 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 fiveyear 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:
[(Y1 ÷ Y5) 1/n – 1.00] = g
Where:
Y1 = latest EPS or DPS value
Y5 = 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 priceearnings 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 fiveyear 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 longterm debt to equity or longterm debt to capitalization (longterm 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 debttoequity 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 Michiganbased 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 priceearnings 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 priceearnings model and the yieldbased 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 priceearnings 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 priceearnings 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 debttoequity ratio, which is what we used to measure financial leverage). The company’s debttoequity 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 easyto 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.
Click here to download spreadsheet.
Discussion
Edward from NY posted over 6 years ago:
Is there a spreadsheet that compares different companies Revenue/Employee? Net earnings/employee?
Richard from CA posted over 6 years ago:
Could we get this spreadsheet incorporated into SIPro???
Richard from CA posted over 5 years ago:
Or is the Valuations Tab serving the same function?
Carroll from CA posted over 5 years ago:
Are these evaluations essentially what is used by the various advisors, ie) Standard and Poors, etc? If so, is this exercise merely a duplication of what is already available?
E from GA posted over 5 years ago:
It would be most helpfull if you published the answers to the questions.
Joesph from PA posted over 5 years ago:
I, like others, are interested in the responses to the above questions. Where can one easily and collectively get the information to propagate/load into the spreadsheet to facilitate analysis?
Fuad Nuwaysir from FL posted over 4 years ago:
After updating Adobe Reader, I tried downloading the book “Stock Investing Strategies,” by Maria Crawford Scott and John Bajkowski. I get the following message: “This file is damaged and could not be repaired.” “Local/EWH#6btt[g” I hope you are able to repair the damaged file and post it again. The other two books downloaded without a hitch. F. Nuwaysir
Joe Lan from IL posted over 4 years ago:
Hi Fuad,
The file is working properly and is not damaged.
Thanks.
Stu from RI posted over 3 years ago:
Can you suggest a site from which you could obtain the historical data listed on this sheet. Can find lots of sites for the current info, but having a real problem finding the past 5 yrs
Sorry, you cannot add comments while on a mobile device or while printing.
© 2017 The American Association of Individual Investors
This content originally appeared in the Computerized Investing
Full Version Mobile Version iPhone/Touch Version
Back to top