Stock Valuation Spreadsheet - PDF

Document Sample
Stock Valuation Spreadsheet - PDF Powered By Docstoc
					    A        B       C      D       E       F     G       H       I      J       K      L       M       N       O      P       Q        R
3            Spreadsheet                           A Simple Stock Valuation Spreadsheet
5            Corner                                                                                                   By AAII Staff

         M       any individual investors view
                 individual stock analysis and
        selection as a daunting task. How-
                                                  are actually quite similar. They equate
                                                  a stock’s price to a stream of future
                                                  earnings or dividends by asking the
                                                                                             for forming those expectations. In ad-
                                                                                             dition, there are a number of sources
                                                                                             where you can obtain analysts’
        ever, the process can be made easier      question: How much are investors           estimates of future earnings, includ-
        by organizing the decision-making         willing to pay now for these future        ing,,
        process to ensure that pertinent data     expected streams?                          Yahoo! Finance and
        and information are evaluated in a          Both models make the key as-
        logical sequence so that the inves-       sumption that the growth prospects         Dividend Model
        tor can arrive at a reasonable and        of the firm will not change funda-            The second valuation model in this
        thoughtful decision.                      mentally over time. Relying on this        worksheet is primarily for mature,
          The ultimate goal of fundamen-          assumption, we can use the histori-        dividend-paying stocks, which tend to
        tal analysis is to determine, using a     cal relationships between the stock’s      be low-growth stocks. As such, it is
        variety of variables, what you think a    price and its earnings or dividends        a dividend yield approach. Dividend
        stock is really worth and comparing       to estimate future values. If current      yield—annual dividends per share
        that estimated value to the stock’s       market prices differ significantly from     divided by share price—is the annual
        current stock price. This way you can     the estimated value based on these         dividend as a percentage of the cur-
        gain an insight into whether the stock    historical relationships, it means that    rent stock price. In other words, it
        is undervalued, overvalued or fairly      the market—for whatever reason—is          relates share price to dividends: the
        valued.                                   evaluating future income potential         lower the dividend yield, the greater
          This installment of the Spreadsheet     differently and may be mispricing the      the company’s emphasis on earnings
        Corner introduces a simple valuation      stock.                                     growth and the greater the disregard
        spreadsheet from AAII’s book “Stock                                                  for dividend income. The higher the
        Investing Strategies,” by Maria           Earnings Model                             dividend yield, the lower the expec-
        Crawford Scott and John Bajkowski.          The first model at the bottom of the      tation among investors of earnings
        This spreadsheet provides an easy-to-     valuation spreadsheet is geared to-        growth; instead there is a greater
        follow, systematic format that walks      ward stocks with low or non-existent       emphasis on dividend income. At the
        you through the complete process of       dividends—the traditional “growth”         extreme, a high dividend yield may
        arriving at a value for a stock without   stock—and is a price-earnings ratio        indicate the expectation of a dividend
        getting bogged down in complicated        (P/E) approach.                            decrease.
        financial formulas and analysis.             The price-earnings ratio (share            This approach requires an estimate
        AAII members can download a free          price divided by earnings per share        of the next expected annual cash
        electronic copy of the book from          over the last 12 months) indicates         dividend. Again, the recent dividend
        the Investment E-books segment of         how much investors are willing to          history in the worksheet should pro-
        the Getting Started area of AAII.         pay for each dollar of a firm’s earn-       vide you with a feel for changes over
        com (       ings. The higher the price-earnings        time. Also, some services provide the
        investment-ebooks). You can down-         ratio—the more investors are paying        indicated dividend, which is the total
        load the Simple Valuation Spread-         for earnings—the more confident in-         projected dividend per share payment
        sheet from the online version of this     vestors are about the expected future      over the next 12 months.
        article at the Computerized Investing     earnings. Conversely, lower ratios           Dividing the expected annual
        website, as well as from the Spread-      indicate low earnings expectations, or     dividend by the average low dividend
        sheets section of the AAII Download       a low confidence in earnings predict-       yield will give a high-price estimate;
        Library (           ability.                                   dividing the expected annual divi-
        library/results?Category=SS).               For the earnings valuation, the          dend by the average high dividend
                                                  average annual high and low price-         yield results in the low-price estimate.
        The Valuation Spreadsheet                 earnings ratios are calculated for
                                                  prior years. Multiplying these his-         Financial Checklist
          The Simple Valuation Spreadsheet,       torical ranges by an estimate of next
        shown in Figure 1, contains two           year’s earnings per share provides an        It’s easy to compare the valuations
        valuation models at the bottom. One       estimate of future value.                  you come up with to the current
        is based on a firm’s earnings and the        While it may seem difficult to make       market price. But those valuations
        other on its dividends.                   an earnings estimate, the recent           are only as good as the inputs and
          The underlying formulas for the         earnings history that is part of the       assumptions used in formulating the
        two models look different, but they       worksheet will give you some basis         models.

        12                                                                                  Computerized Investing

 Figure 1. Simple Valuation Spreadsheet

              A                    B                C           D           E            F           G          H            I            J             K             L
   1                                                                        Valuation Worksheet

   2       Company:        Kellogg Company                                Current Price $:         49.23                                      Date: 11/30/2010
   4           Ticker:              K                Exchange:            NYSE          Current P/E:           15.2           Current Yield:          3.3%

   6    Financial Statement & Ratio Analysis
   7                                                                                  Company
                                                                                                                                      Indus. or Competitor          Market
   8                                                Use Year 1 for the most recent data, Year 5 for the oldest data.

   9                                              Year 1      Year 2      Year 3       Year 4      Year 5      5-Yr         5-yr                      5-Year
                                                                                                                                       Year 1                       Year 1
                                                   2009       2008        2007         2006        2005        Avg         Growth                      Avg
   11   Per Share Information

   12   Price: High                                 54.10       58.51       56.89        50.95       46.99      --           --

   13         Low                                   35.64       40.32       48.68        42.41       42.35      --           --

   14   Earnings per Share (EPS)                     3.16        2.98        2.76         2.51        2.36                   7.6%                             7.4
   15   Dividends per Share                          1.43        1.30        1.20         1.14        1.06                   7.8%                             7.3
   16   Book Value per Share (BV)                    5.95        3.79        6.38         5.21        5.54                   --
   17   Financial Ratios

   18   Price-Earnings Ratio (P/E): Avg*            14.20       16.58       19.13        18.60       18.93     17.49         --                17.7           9.0         15.1

   19         High (High Price ÷ EPS)               17.12       19.63       20.61        20.30       19.91     19.52         --                              13.6

   20         Low (Low Price ÷ EPS)                 11.28       13.53       17.64        16.90       17.94     15.46         --                               4.4

   21   Dividend Yield % (DY): Avg*                 3.3%        2.7%        2.3%         2.5%        2.4%      2.6%          --                 3.2           1.5          1.9

   22         High (DPS ÷ Low Price)                4.0%        3.2%        2.5%         2.7%        2.5%      3.0%          --

   23         Low (DPS ÷ High Price)                2.6%        2.2%        2.1%         2.2%        2.3%      2.3%          --

   24   Payout Ratio % (DPS ÷ EPS)                 45.3%       43.6%       43.5%        45.4%       44.9%      44.5%         --                 5.9           9.2

   25   Return on Equity % (EPS ÷ BV)              53.1%       78.6%       43.3%        48.2%       42.6%      53.2%         --                 7.2          22.4         22.4

   26   Financial Leverage                              2.1         2.8         1.3          1.5         1.6         1.9     --              0.2          0.2              1.1
   27   * Avg (High + Low) ÷ 2                                                                                                      Shaded areas do not need to be filled in.
   28   Valuation Estimates
   29   Model based on earnings:

   30   Average high P/E x estimated Year 6 EPS:                          19.52          x          3.40        =          $66.34    (high valuation estimate)
   32   Average low P/E x estimated Year 6 EPS:                           15.46          x          3.40        =          $52.54    (low valuation estimate)
   34   Model based on dividends:

   35   Estimated Year 6 annual DPS ÷ average low DY                       1.54          ÷         2.3%         =          $67.19    (high valuation estimate)
   37   Estimated Year 6 annual DPS ÷ average high DY                      1.54          ÷         3.0%         =          $51.74    (low valuation estimate)

  For instance, the models assume                         one-time occurrence that is unlikely                               particularly useful in evaluating your
that the firm’s growth prospects have                      to continue? Will dividends continue                               inputs and assumptions.
not fundamentally changed. But will                       to be paid at the same rate?                                         • Have earnings grown at a stable
growth continue at its current pace?                        Examining the historical patterns of                                  rate?
The models also assume that histori-                      the per share figures and ratios, and                                 • Have the earnings per share been
cal relationships will continue. But                      comparing them to competitors and                                       steady and positive each year, or
were past relationships affected by a                     industry and market benchmarks, is                                      have they been volatile, making

First Quarter 2011                                                                                                                                                           13

     predictions more difficult?           only legwork you need to do, as this               [(Y1 ÷ Y5)1/n – 1.00] = g
 •   For dividend-paying firms, has        spreadsheet calculates the ratios and
     the payout ratio been steady?        valuations for you.                         Where:
     Increases in the payout ratio, and     The first section indicates per share       Y1 = latest EPS or DPS value
     payout ratios above 100%, are        information regarding the stock—the          Y5 = earliest EPS or DPS value
     an indication that future divi-      high and low prices for each of the          n = number of annual compound-
     dends may go down; high payout       last five years, as well as earnings per          ing periods
     ratios mean slower or no divi-       share and dividends per share figures         g = growth rate (%)
     dend growth, and perhaps even        for each of the last five years (moving
     a decline.                           from left to right, the Year 1 column        Note that with five years of data,
 •   Is the current price-earnings        contains the most recent figures and        you will have only four annual com-
     ratio low relative to the market,    the Year 5 column contains the oldest      pounding periods—Year 5 to Year
     the industry or a competitor,        figures).                                   4, Year 4 to Year 3, Year 3 to Year 2
     and does this vary from previous                                                and Year 2 to Year 1. Also, this for-
     years?                               Price Data                                 mula only works when the beginning
 •   Is the current dividend yield high     Using a free site such as Morning-       and ending values are positive.
     relative to the market, the indus-, you can find the high and
     try or a competitor, and does this   low prices of a company on an an-          Financial Ratios
     vary from previous years?            nual basis. Figure 1 shows the annual        The next section of the valuation
 •   Has the return on equity, an         price history for Kellogg Company          worksheet lists financial ratios, spe-
     indication of how well the firm       (K) from the start of 2005 through         cifically two primary multiples—price
     has used reinvested earnings to      the end of 2009. Interestingly             earnings ratio and dividend yield.
     generate additional earnings,        enough, however, we found a data er-       For the valuation models, these two
     been high and stable?                ror at the Morningstar site. While the     figures are calculated from the per
 •   Is the use of financial leverage,     site claimed that the high price for       share data. For the price-earnings ra-
     a measure of financial risk that      K shares in 2006 was $57, we could         tios, the spreadsheet divides the high
     indicates how much of the firm’s      not find another supporting source.         and low prices by earnings per share
     assets have been financed by          Instead, we found multiple stock           for the same year. For dividend yield,
     debt, low relative to industry       charts that pegged the high price for      the annual cash dividends per share
     norms?                               that year at $50.95, which is what we      payments are divided by the low and
                                          used for our analysis.                     high price for the same year. The
 Sources of Data                                                                     spreadsheet arrives at the five-year
                                          Earnings and Dividends                     averages by adding the yearly fig-
  Thus far our discussion has focused        After entering in the high and low      ures and dividing the sum by five. If
on the bottom portion of the spread-      prices for the last five years, the next    earnings are negative in a given year,
sheet—the valuation models that help      step is to locate the annual earnings      or if dividends are nonexistent, the
you find stocks that are potentially       per share and dividends per share          spreadsheet will return an “na” value
undervalued or overvalued based on        figures, also for each of the last five      for that year. In this case, you will
their earnings and dividend histories.    years. The “Financials” section of         need to adjust the divisor in column
  However, in order to arrive at these provides five years         H to reflect the number of years with
valuations, you need to fill out the       of financial statement data for free.       a valid earnings or dividend value.
top portion of the spreadsheet. This      While, from a time-saving standpoint,        Also included in this spreadsheet
section—the Financial Statement           it is convenient to find the necessary      is the payout ratio (dividends per
and Ratio Analysis—organizes the          data from a single source, another         share divided by earnings per share),
information needed for the valua-         excellent site for financial statement      return on equity (earnings per share
tion models and provides figures that      data is Here you           divided by book value per share), and
serve as a financial checklist for your    will find up to 10 years of annual data     financial leverage [such as long-term
analysis. Here you can analyze the        and 15 quarters of quarterly data.         debt to equity or long-term debt to
assumptions underlying the valuation         Using the historical earnings and       capitalization (long-term debt plus
models; this is important, because if     dividends data, we can calculate           equity)], which are used as part of
these assumptions are wrong, your         five-year growth rates for both. The        your financial checklist. Most of
valuations will be flawed.                 equation built into this valuation         these ratios can be calculated from
  You have several options for col-       spreadsheet to calculate the growth        the per share financial data in this
lecting the underlying data to popu-      rate of earnings per share (EPS) and       worksheet, or they can be taken from
late this spreadsheet. Luckily, filling    dividends per share (DPS) is as fol-       stock information sources. Financial
in the yellow-highlighted cells is the    lows:                                      leverage cannot be calculated by the

14                                                                                  Computerized Investing

per share data in this worksheet, and     • Yearly earnings per share ap-              low compared to its industry. Its
various sources use different mea-          pear to be increasing in a fairly          dividend yield is roughly equal to
sures. Again, offers        stable pattern, and all of the             the industry average.
five years of financial leverage (assets      figures were positive. Morning-          • Kellogg’s return on equity (ROE)
to equity) and debt-to-equity data for      star’s earnings estimates for the          has risen over the last five years,
free.                                       current fiscal year of $3.58 per            although it dropped from Year 2
  Financial ratios for the industry         share and $4.10 per share for the          to Year 1. There was a significant
in which the firm operates (or for a         next fiscal year indicate that this         increase from Year 3 to Year 2,
close competitor), as well as for the       trend is expected to continue.             as Kellogg appears to have sig-
market as a whole, are part of the          This signals earnings growth               nificantly raised its debt load (as
checklist. Unfortunately, it is becom-      over the next two years that is            shown by the increase in its debt-
ing more difficult to find multiple           almost double that of the last             to-equity ratio, which is what
years of historical financial data and       five years. Further analysis would          we used to measure financial
sector/industry/market data all at the      be useful to determine whether             leverage). The company’s debt-
same site, for free.        or not you agree with Morning-             to-equity ratio is significantly
and are two sites that          star’s assessment. Had we used             higher than the industry average.
do offer this comparative data for a        Morningstar’s earnings estimates           Companies can boost return on
more complete company analysis.             for Year 6 ($3.58), we would               equity by taking on more debt,
                                            have arrived at a valuation range          but they increase their risk to
 Are Figures Reasonable?                    of $55.34 to $69.86 using the              shareholders in the process.
                                            price-earnings model.                   The financial checklist indicates
  For this article we used Kellogg        • Kellogg’s payout ratio been           that some of the assumptions in the
Company, the Michigan-based cereal          relatively stable over the last five   model are reasonable, but some—
and snack maker, to illustrate the use      years. We were unable to locate       such as the assumptions concerning
of this simple worksheet. We used           an online source for industry         dividend and earnings growth—
data available for free to supply the       norms, so we used AAII’s Stock        should be examined in more detail.
data required for this spreadsheet          Investor Pro fundamental stock        A higher Year 6 earnings per share
(those cells in Figure 1 highlighted in     screening and research database       estimate would, of course, produce
yellow).                                    program for the industry payout       higher valuation estimates.
  Plugging these numbers into the           ratio data. From this data we
valuation models, you see that the          see that Kellogg’s payout ratio is     Conclusion
price-earnings ratio model deter-           well above the industry median,
mines a high price of $66.34 (cell          or midpoint, value. Further             While this valuation spreadsheet
I30) and a low price of $52.54 (cell        investigation revealed that only      offers a basic framework for analy-
I32) for an average price of $59.44,        about 30% of the companies in         sis, you would need to look at other
while the dividend model produces a         the food processing industry pay      fundamental aspects of the company
high of $67.19 (cell I35) and a low         a dividend, lowering the median       before any investment decision is
of $51.74 (cell I37) for an aver-           value for the overall industry.       made.
age price of $59.47. [You may end           When comparing the median               For a simple beginning, the Simple
up with slightly different numbers          value of those companies pay-         Valuation Spreadsheet will provide
due to rounding.] The current price         ing a dividend, Kellogg’s payout      you with an easy-to-follow systematic
is around $50: It’s trading slightly        ratio is in line with its industry    approach to determining value. The
below the predicted range of both the       counterparts. This indicates that     basic format is to:
price-earnings model and the yield-         the company should be able to           • Determine which valuation
based model.                                support its dividend payout or             model best suits your needs,
  Are the assumptions and figures            even increase the payout if earn-       • Determine what information you
used in the model reasonable? A run         ings continue to grow.                     need to gather for those valua-
through the checklist evaluates this:     • Kellogg’s price-earnings ratio is          tions, and
                                                                                    • Determine what information you
                                                                                       need in order to evaluate the as-
      Maria Crawford Scott, former editor of the AAII Journal, John                    sumption and other inputs used
  Bajkowski, president of AAII, and Wayne A. Thorp, CFA, editor of                     in the models.
  Computerized Investing, contributed to this article.

First Quarter 2011                                                                                                   15

Shared By:
Description: Stock Valuation Spreadsheet document sample