Asset Rebalancing Spreadsheet by ypj88685

VIEWS: 121 PAGES: 16

Asset Rebalancing Spreadsheet document sample

More Info
									Teaching Security Analysis: Equity Valuation Using Value Line and MSN Stock

                                 Screener




                                     by


                              Walter P. Neely

                            Professor of Finance
                         Else School of Management
                               Millsaps College
                             Jackson, MS 39210

                               601-974-1263
                            601-974-1260 (fax)
                           Neelywp@millsaps.edu



                             For presentation to


                       Financial Education Association
                                   Orlando




                                 April 2005
 Teaching Security Analysis: Equity Valuation Using Value Line and MSN Stock

                                         Screener

Teaching equity valuation is a component of many investments, security analysis, or

portfolio management courses including student-managed funds courses. Today there

is more and more evidence that markets are not as efficient as we once believed, either

overall and certainly for individual stocks (Jensen, 2004). Valuation in a less efficient

market should become a more important topic in future investments courses. Often the

subject of valuation is abbreviated in otherwise excellent and established leading texts

like Bodie, Kane and Marcus (BKM), 2002 and Reilly and Brown (RB), 2004. These and

other texts may rely on a simplified dependence on concepts and formulas described as

discounted cash flow (DCF) or the analogous term Dividend Discount Models (DDM).

Both texts include chapters on financial statement analysis that adds little to what is

covered in the basic junior-level finance course. Some books (Stowe, Robinson, Pinto

and McLeavey (2002), and Damodaran 1996) do excellent jobs on valuation, especially

the use of multiples in determining relative value. The widely used books cover topics

such as market efficiency and portfolio theory and management. These textbooks do

little to explain the ways investment bankers and portfolio managers exercise the

security analysis aspect of their professions. More attention to security analysis is often

required by courses involving student-managed funds as shown in survey results by

Neely and Cooley (2004).

       Projects involving company analysis using current information are not new to

finance courses or professors.    Stohs (1999) describes a company valuation project

used in a corporate finance course. He includes pro forma financial statements in the

process of seeking the intrinsic value of the firm. Kalra and Webster (2004) present a

project that facilitates learning by students in the first investments course. Students


                                             1
gain a better understanding of stock returns, beta calculation, growth rates, and required

return calculations. Many of these recommended exercises utilize Value Line data, and

similar exercises combined with Value Line are employed in this paper.

       A recent article by Hoover and Sterbenz (2003) represents another building block

for the current paper.   Hoover and Sterbenz look at companies in the same industry,

valuing the firms five years into the future based on their projected growth. Their DDM

model utilizes the projected dividends plus the terminal value based on a projected P/E

ratio times the projected year five EPS. The projected P/E ratio is set equal to the

average industry P/E ratio.    In the current paper we recommend basing projected P/E

ratios on Value Line estimates with adjustments based on analyst projections of growth

and risk. We use a DDM approach with a terminal value that utilizes the product of

market multiples times the relevant projected variable.

       This paper deals with a similar approach to equity valuation that relies on

generally available information sources. We utilize stock internet-based screening to

meet stated strategies. As part of this screening process we obtain industry firm specific

comparable data. Concepts involved in the security valuation exercise include producing

pro forma income statements and abbreviated balance sheets, and using the projected

values to project a valuation of the stock of the company in question.



       Equity Analysis by the Book

       The coverage of equity security analysis by RB and BKM includes coverage of

financial statement analysis (FSA) including analysis of growth, risk, asset management,

profitability, and valuation ratios. These books‟ FSA chapters build on the subject

coverage from the junior-level corporate finance courses. Another topic covered in the

widely used texts is industry analysis. RB include two excellent, although extended,

chapters on economic and industry analysis, while BKM combine, in good fashion,


                                            2
economic and industry analysis in a single chapter. Application of FSA and industry

analysis is recommended and facilitated with the approach recommended in this paper.

While far from a complete coverage the recommended approach forces students to

apply the rather abstract coverage by the widely used investments texts.

        Two chapters in RB cover equity valuation and company analysis. BKM

combine FSA and company valuation. Both texts extensively cover the DDM theory with

extensive discussions of the effects of growth and the discount rate on the P/E ratio.

RB extends its DDM coverage with cursory coverage of free cash flow to equity,

although not sufficient for most students to understand the methodology. RB does

cover relative valuation better than BKM. Hence, use of market multiples is covered,

but not sufficiently in either text, but is covered well in Stowe, et al, and Damodaren. In

this paper the method recommended is a combination of the DDM method and relative

value method.

        Security analysis in practice (Rakers, 2005) requires a detailed knowledge of the

industry and the ways the industry‟s firms are affected by the economy. Students of

security analysis should understand the relationships between economic variables and

industry variables. The identification of competitors is the next step, and thorough

knowledge of public, foreign, and even private firms is important. It is also important to

identify similar firms, i.e. firms facing similar market conditions, and to identify suppliers

and customers.

        Equity Analysis with Current Data

        Screening to meet stated strategies may be accomplished in various ways

utilizing the Internet, using Value Line software and by various “manual” methods. In the

approach recommended in this paper, MSN Stock Screener (2005) is used with

screening criteria based on ratios such as ROE, ROA, growth, profit margins, etc.

These criteria are based on stated strategies, such as are shown in Exhibit 1. While the


                                               3
selected company, Merck, is a firm with many attributes matching the criteria, its growth

prospects and drug pipeline are causes for concern. The analysis presented in this

paper was initiated prior to the Vioxx controversy and adjustments will be discussed

below.

         Industry analysis is facilitated using MSN Stock Screener data taken directly from

the Internet.   While industry averages may be found in many Internet or other sources,

the recommended approach is to select comparable companies based in part on input

into MSN Stock Screener. Comparative analysis is then performed to identify strengths

and weaknesses of the subject company versus the comparative group.

         The task of selecting comparable companies forces the student to select

comparable companies using Value Line or Internet sources. The comparable group of

companies should include analysis of product mix and other factors characterizing each

company. This review may force the student to better understand the products and

markets of the subject company.      An understanding of factors driving the valuation of

the companies in the comparable group facilitates valuation of the subject company.

         MSN Stock Screener produces both a (long) list of comparable companies and a

good representative set of ratios and growth rates, including valuation ratios, for each

company. Exhibit 2 shows data on selected companies exported from MSN Stock

Screener to a spreadsheet format, then copied-pasted-transposed to the present format.

The selected ratios should be examined to show the companies with particular strengths

with a focus on how these strengths affect each company‟s valuation. Analyzing these

ratios helps the analyst to determine appropriate inputs to the valuation models for the

subject firm, and the strengths and weaknesses may be highlighted for later analysis as

shown in the exhibit.

         Before a valuation model can be employed the historical and forecast financial

statements of the subject firm must be obtained/forecast. Value Line covers 1600 of the


                                             4
largest companies with extensive historical and forecast data. Even if your subject

company is not included in Value Line‟s 1600 companies, forecasts may be based on

similar companies from the 1600. Exhibit 3 shows the Value Line page for Merck. Pro

forma 2004 through 2007-2009 (we assume 2008) forecasts are shown for items like

sales, margins, and earnings for each of 1600 firms.     The company pro forma

spreadsheet for Merck, shown in Exhibit 4, and it is based on historical data financial

statement data from Value Line and from company data taken from MSN, Yahoo,

Bloomberg, the SEC‟s Edgar database, and other similar sources. Historical data

obtained from Value Line are sales, operating (EBITDA) margins, depreciation, taxes,

number of shares, equity, long-term debt, and average price and dividends per share.

Data obtained from other sources include other income, short-term debt, and total

assets. The projections for 2004-2008 are initially based on Value Line‟s projections and

spreadsheet calculations.

       Since the data are input into a spreadsheet, the impact the Vioxx withdrawal from

the market news was based on the complete reduction of all Vioxx sales as given in the

Merck annual report. Value Line’s sales estimate for Merck is reduced from the given

estimates for 2004-2008. Analysis of the Merck annual reports suggests that Vioxx

sales are $2.4 billion, and we have deducted that full amount from the Value Line

estimate. In addition the impact of potential lawsuits is reflected in other

income/expense, which was reduced by $1 billion per year for the next four years—

maybe too little?

       The spreadsheet valuation model shown in Exhibit 5 is a DCF model utilizing

Value Line’s estimates (as modified) of dividends, earnings, book value, etc.   The pro

forma dividends are compounded to a future value and added to a terminal-2008 value

estimate. The 2008 terminal value estimates may be based on the spreadsheet tabs

using the following models: P/E, P/BV, P/CF, P/S, P/EBITDA, and P/Enterprise Value.


                                              5
The P/E terminal value model is shown below with the future P/E ratio based on Value

Line estimates, high and low values from the company‟s history taken from Value Line,

MSN, or Bloomberg. The mid P/E, sales, EBITDA margin, etc. for 2008 are based on

the Value Line estimate, as shown on the annotated Value Line page for Merck in

Exhibit 3. The future values of dividends and the P/E based selling price are summed

and present valued to determine the “indicated present value” or “intrinsic value.”   Then

a Graham and Dodd-like “margin of safety” is applied to determine a targeted buying

price.    Comparison of expected and CAPM-required returns is enabled.

         An example analysis of Merck is shown in the spreadsheets that follow. The first

is the industry analysis spreadsheet. Second is the pro forma spreadsheet with data

and certain projections from Value Line. Other inputs are calculations or values from

the company‟s financial statements or from the analyst. Exhibit 6 is the P/E tab of the

spreadsheet with the DCF/Relative value calculation, the one shown having a terminal

value based on the estimated future P/E ratio. There are high and low P/E multiples for

the terminal value estimates. The high and low estimates are based on past highs and

lows that are found on Exhibit 2 from MSN and may also be found from Bloomberg and

other sources. Care must be taken in selecting reasonable estimates of future high and

low P/E (or any valuation) multiples. In practice very high (e.g. 35 times or higher) and

very low P/E multiples are considered unreasonable, and the need for other valuation

models is called for.

         The valuation targets are summarized on Exhibit 6 that is based on the format of

the student-managed fund, the Louis Wilson Fund. The key factors driving the

company strategy are summarized in “bullet point” format. This narrative should be well-

written to clearly indicate the analyst‟s understanding of the company and the insights

gained from thorough analysis. Some of these insights would come from the industry

spreadsheet and other from the company pro forma spreadsheet. Other insights come


                                             6
from reading the company‟s annual report, brokerage firm reports, articles about the

company and its industry, etc. The second area of focus on the company summary

sheet focuses on risk, with insights from similar sources.    The third focus area is

valuation, based on the DCF analysis shown in Exhibit 5. Other information on the

summary sheet may include current news and recent earnings announcements

considered important to the analyst.

       Conclusion

       The valuation models recommended combine aspects of DCF and relative

valuation models.    The relative valuation models employ an analysis of ratios and

multiples, and these data are taken from Value Line and sources like MSN Stock

Screener from the Internet.    A spreadsheet template allows the user to implement

equity valuation by initially replicating Value Line’s valuation. Further sensitivity analysis

becomes possible with easily implemented changes to the spreadsheet. With these

sensitivity analyses, ranges of possible values are enabled. Students can then better

understand how professional security analysts do equity valuation. More important they

understand valuation beyond the abstract formulas found in most texts.

       The approach recommended in this paper utilizes generally available sources,

principally Value Line and MSN Stock Screener in conjunction with spreadsheet

templates.   This approach can be an added component of the traditional text based

courses to improve the coverage of equity security analysis.      It can also be added to

any course in investments or security analysis to add a real time exercise in establishing

the valuation of a stock. The recommended approach reinforces important skills in

accounting including the forecasting of financial statements.    In addition important skills

in industry analysis are reinforced. Finally spreadsheet proficiency is encouraged.

These analytical skills when coupled with a well-written narrative are needed by today‟s

finance graduates.


                                              7
       Exhibit 1. Strategy Statement for Stock Screening

OBJECTIVE: LONG TERM CAPITAL APPRECIATION

The Wilson Fund maintains a policy of capital appreciation through investment
in securities judged to be undervalued and thus positioned for significant
comparative appreciation.

PORTFOLIO GUIDELINES:

COMPANIES WITH:

       Excellent Management and strong business franchises.

       Evidence of growing markets and/or market share.

       Low Market/Book Value and high growth/PE ratio (Lynch Ratio)

       High rate of return based on a discounted cash flow model (The Ouma
        Model)

       High ROE, ROA, and profit margins; demonstrated consistent earnings
        power

       Low risk measures and strong financial condition

OTHER FACTORS:

The focus of the selection process is the search for undervalued securities.
Growing companies in growing industries are favored.

Companies that are understandable and have low institutional following are
good candidates for original security analysis.

PORTFOLIO:

In order to understand each company, we target holding no more than 12
investment positions diversified by industry.

In order to minimize transaction costs, low portfolio turnover is preferred.

BONDS:

Total return of fixed income instruments is stressed.


DERIVATIVES:
       Calls and/or Puts may be used to reduce the risk of certain holdings.



                                              8
Exhibit 2. Industry Analysis Spreadsheet

                                           Merck's Competitors and Other Comparable Companies                                           Louis Wilson Fund Holdings

                                                              Bristol      Genzy Glaxo                                  Renal Amer
                                              Cardinal Biogen Myers Forest me SmithK   Abbot       Scher. Watson        Care Heal. Home
                                  Merck Amgen Health IDEC Sq. Labs Gen. ADR Lilly Eli Lab Novartis Plough Pharm. Pfizer Grp Way Depot
                          Tickers MRK AMGN CAH BIIB BMY FRX GENZ GSK LLY ABT NVS SGP WPI PFE RCI AMHC HD
Growth Rates (%)--MSN
Sales (qtr. v yr. ago)          -54.7% 32.6% 27.7% 336.0% 7.5% 29.2% 31.2%               5.7%   15.2%   -0.4% 12.4% -8.2% 12.2% 22.8%            38.0%   56.3%   11.0%
EPS (YTD v yr ago)               5.3% 30.7% 9.0% -100.0% -9.0% 27.9% NA                  1.5%   -3.8%   39.1% 19.5% -99.0% -17.2% 371.0%         24.6%   25.2%   19.8%
Sales (5-yr avg)                 4.6% 24.6% 19.5% 56.6% 1.3% 31.5% 22.8%                23.0%    5.7%    9.5% 17.7% -1.4% -19.0% 23.4%           17.6%   35.1%   13.1%
EPS (5-yr avg)                   4.8% NA 25.6% NA -7.5% 51.0% NA                        10.2%    0.0%    2.9% NA      NA 0.9% 1.8%               19.0%   73.8%   16.9%
Financial Condition-MSN
Debt to Equity                  0.29     0.01   0.32    0.12    0.80    0.00    0.27    0.40    0.34    0.37    0.11    0.34    0.27     0.10    0.96    0.28     0.05
Interest Coverage               30.9    229.0   24.0   -69.0    16.0    NA       6.1    24.8    79.6    23.9    26.9    -27.7   16.9     -81.6   27.6    14.0    129.0
Leverage Ratio                   2.5      1.3    2.6     1.3     2.8     1.2     1.5     4.0     2.2     2.2     1.6     2.1     1.5      1.8     2.6     1.6      1.7
Profit Margins (%)--MSN
Gross Margin                    85.0%   87.9%   8.2%   59.6%    66.6%   79.4%   78.6%   83.3%   82.3%   59.3%   82.7%   69.0%   56.4%   88.9%    34.4%   35.3%   34%
Pre-tax Margin                  40.0%   37.3%   3.7%   -69.8%   21.6%   35.3%   10.7%   28.9%   24.6%   20.9%   23.9%    1.0%   18.5%   19.4%    18.7%   16.7%   11%
Net Margin                      28.8%   26.9%   2.4%   -64.9%   13.6%   27.8%    5.5%   20.5%   18.6%   15.6%   20.6%   -7.3%   11.8%   15.6%    10.0%    9.9%    7%
5 yr Avg Gross Margin           48.3%   91.6%   9.3%   84.9%    72.7%   79.5%   76.4%   82.4%   84.3%   59.5%   78.1%   80.0%   59.3%   87.7%    35.1%   49.5%   32%
5 yr avg Pre-tax Margin         25.3%   30.0%   3.4%   -22.9%   21.8%   31.6%    3.4%   29.6%   31.0%   20.5%   25.1%   23.3%   26.7%   22.9%    18.0%   13.0%   10%
5 yr avg Net Margin             17.4%   17.4%   2.2%   -32.2%   19.8%   23.9%   -0.3%   21.0%   24.7%   15.7%   21.0%   17.7%   15.8%   18.1%     9.8%    7.6%    6%
Investment Returns (%)--MSN
ROE                             39.1%   13.4%   19.6% NA 28.4% 22.5% 3.4% 81.2%                 24.0%   24.7%   18.7%    NA      8.6%   12.1%    22.2%   15.2%   21.1%
ROA                             15.9%   10.0%    7.5% -10.7% 10.2% 19.4% 2.3% 20.5%             11.1%   11.4%   11.4%   -3.9%    5.8%    6.7%     8.7%    9.3%   12.4%
ROC                             30.4%   13.2%   14.8% NA 15.7% 22.5% 2.7% 42.8%                 18.0%   18.1%   16.9%    NA      6.8%   10.9%    11.3%   11.9%   20.1%
5 yr avg ROE                    43.6%    8.4%   16.7% NA 41.4% 21.7% NA 57.8%                   38.3%   25.3%   17.7%   24.7%   10.4%   21.9%    15.7%   11.2%   18.2%
5 yr avg ROA                    16.4%    6.3%    6.6% -3.8% 16.4% 17.9% -0.1% 18.8%             16.4%   11.9%   10.9%   13.6%    6.8%   11.0%    11.3%    8.7%   12.3%
5 yr avg ROC                    33.9%    7.4%   12.5% -4.3% 22.3% 21.7% -0.1% 42.2%             25.4%   19.5%   16.4%   23.0%    8.3%   18.0%    15.0%   11.2%   17.0%
Management Efficiency--MSN
Receivable Turnover              4.7     8.6    19.4    7.0      6.5    11.6     4.8     3.3     6.0     7.1     6.4     5.4     9.2     5.6      5.8     8.0    47.4
Inventory Turnover               1.1     1.7     6.5    4.5      4.3     1.1     1.8     1.7     1.3     3.3     1.4     1.6     1.8     0.8     37.8     NA      4.9
Asset Turnover                   0.5     0.4     3.2    0.3      0.8     0.8     0.4     1.0     0.6     0.8     0.6     0.6     0.5     0.4      1.1     1.2     1.9
Valuation Ratios --MSN
P/E                             14.8    29.2    13.5    NA      16.1    21.6     NA     15.4    27.4    21.2    21.2     NA     17.6     28.2    20.6     43.0   18.2
P/E High (L5Y)                  33.8    NA      50.9    NA      48.6    68.2     NA     47.3    39.7    57.7    NA       NA     62.0     83.5    33.1    362.0   69.8
P/E Low (L5Y)                   11.6    NA      15.0    NA      13.0    19.4     NA     11.6    17.4    16.5    NA       NA     10.9     16.4    13.5     10.0   10.7
Price/Sales                      4.2     7.5     0.3    13.3     2.2     5.9     6.6     3.1     5.3     3.3     4.3     3.4     2.1      4.4     1.9      4.1    1.2
Price/Book Value                 5.8     3.7     2.6     2.9     4.5     3.9     4.1    12.4     6.8     5.2     3.9     3.9     1.5      3.4     4.2      6.3    3.8
Price/Cash Flow                 12.5    22.0    11.0   -28.0    12.4    19.6     4.3    12.5    23.4    14.9    17.3    -18.6   11.3     16.8    13.3     23.3   15.4



Source: http://moneycentral.msn.com/investor/finder/customstocks.asp




                                                                  9
Exhibit 3. Value Line sheet for Merck, Annotated.




                                       10
Exhibit 4. Company Pro Forma Spreadsheet
                                                           PRO FORMA WORKSHEET
     Merck
COMPANY BASICS

Ticker:                    MRK        Dividend Yield:           4.5%                  Exp Growth (VL)          3.0%                  Ouma Model                   P/E
Date:                   10/25/2004    52 Week High/Low:         33-63                 Lynch Ratio (>1)           0.2                 Base Exp. Price        $     50
Price:                    $32.00      Debt Ratio:                23%                  Sector              Healthcare                 Base Exp. Multiple            16
Last Dividend:          $     1.45    Shares Oustanding        2,222.0                Industry              Pharm.                   Implied Multiple              24
P/E (next year's EPS)          13.8   Beta:                       0.9                 VL Financial Strength
                                                                                                          A++                        Target Price           $     75

Risk and other ANALYSIS
                                          1998         1999        2000        2001       2002       2003      2004E       2005E        2006E      2007E        2008E
Times Interest Earned calc                   32          25          19          22         25         23          26          31           37         44           51
Debt/Equity           calc                 0.25        0.24        0.24        0.30       0.27       0.33        0.27        0.23         0.22       0.16         0.15

GROWTH - PROFITABILITY - LEVERAGE
NET RETURN ON EQUITY (In Millions)
                                           1998         1999        2000        2001      2002        2003      2004E       2005E       2006E       2007E     2008E
Net Profit Margin      calc              19.9%        18.0%       16.9%       15.3%     13.8%       29.3%       33.3%       33.8%       32.9%       31.7%     31.0%
Total Asset Turnover calc                   0.84        0.92        1.01        1.08      1.09        0.55        0.50        0.50        0.50        0.50      0.50
Total Assets/Equity    calc                 2.49        2.69        2.69        2.74      2.61        2.59        2.39        2.38        2.57        2.32      2.36
Net Return on Equity calc                41.8%        44.5%       46.0%       45.4%     39.3%       42.1%       39.7%       40.1%       42.2%       36.7%     36.4%
                       VL
EBITDA Oper. Profit Margin               28.5%        27.7%       26.5%       24.1%     21.9%       42.0%       42.0%       43.0%       44.0%       44.0%     44.5%
Merck                                      1998         1999        2000        2001      2002        2003      2004E       2005E       2006E       2007E     2008E
Revenues                       3.6%    $26,898      $32,714     $40,363     $47,716 $51,790       $22,500     $20,975     $21,730     $22,512     $23,323   $24,162
EBITDA                 VL               $7,666       $9,062     $10,696     $11,500 $11,342        $9,450      $8,810      $9,344      $9,905     $10,262   $10,752
Depreciation           VL               $1,015       $1,145      $1,277          1464  $1,488      $1,314      $1,370      $1,375      $1,425      $1,476    $1,529
EBIT                   calc             $6,651       $7,917      $9,419     $10,036    $9,854      $8,136      $7,440      $7,969      $8,481      $8,786    $9,223
Other Income (exp)     IS               $1,816         $509      $1,494      $1,006    $1,159        $891          $0          $0          $0          $0        $0
Interest                       6.0%       $206         $317        $484        $465      $391        $351        $288        $255        $228        $198      $180
EBT                    calc             $8,261       $8,109     $10,429     $10,577 $10,622        $8,676      $7,152      $7,714      $8,253      $8,588    $9,043
Taxes                           29%     $2,396       $2,352      $3,024      $3,067    $3,080      $2,516      $2,074      $2,237      $2,393      $2,491    $2,623
Net Income             calc             $5,865       $5,757      $7,405      $7,509    $7,542      $6,160      $5,078      $5,477      $5,860      $6,098    $6,421
Net Income (reference) VL               $5,348       $5,890      $6,822      $7,282    $7,150      $6,590      $6,995      $7,335      $7,400      $7,400    $7,490
No. Shares             VL                 2,361        2,329       2,308       2,273     2,245       2,222       2,190       2,160       2,100       2,100     2,050
EPS                    VL             $ 2.15       $ 2.45      $ 2.90      $ 3.14 $ 3.14         $ 2.92      $ 3.15         $3.35       $3.45       $3.55     $3.60
EPS                    calc           $ 2.48       $ 2.47      $ 3.21      $ 3.30 $ 3.36         $ 2.77      $ 2.32      $ 2.54      $ 2.79      $ 2.90 $      3.13
Total Assets           BB              $31,853      $35,634     $39,910     $44,007 $47,561       $40,587     $42,048     $43,562     $45,130     $46,755   $48,438
Total Equity           VL/calc         $12,802      $13,242     $14,832     $16,050 $18,201       $15,650     $17,610     $18,283     $17,544     $20,162   $20,559
ST Debt                BB/VL              $624       $2,859      $3,319      $4,067    $3,670      $1,700      $1,700      $1,700      $1,700      $1,700    $1,700
LT Debt                VL               $3,221       $3,144      $3,601      $4,799    $4,897      $5,096      $4,800      $4,250      $3,800      $3,300    $3,000
BOOK Value per share calc                   5.42        5.69        6.43        7.06      8.11        7.04        8.04        8.46        8.35        9.60     10.03
Sales per share        calc              $11.40       $14.05      $17.49      $20.99    $23.07      $10.13       $9.58      $10.06      $10.72      $11.11    $11.79
MV Equity              calc           $155,793     $172,353    $170,762    $172,748 $115,618     $116,655    $100,740    $105,840    $113,400    $117,482   $102,733
Enterprise Value       calc           $159,014     $175,497    $174,363    $177,547 $120,515     $121,751    $105,540    $110,090    $117,200    $120,782   $105,733
Average Price          VL/calc        $      66    $     74    $     74    $      76 $     52    $     53    $     46    $     49    $     54    $     56 $      50
Dividends per share    VL             $ 0.95       $ 1.10      $ 1.21      $ 1.37 $ 1.41         $ 1.45      $ 1.49      $ 1.54      $ 1.60      $ 1.66 $      1.72




Data Source: Value Line Investment Survey.




                                                                           11
Exhibit 5. Valuation Using DCF and P/E Ratio Terminal Value

       OUMA IPV Model                 P/E

       MRK                                                     1            2         3
       year              Growth              2005          2006          2007     2008
       Dividends            3.00%           $1.54         $1.58        $1.63     $1.68
       FVIF @            Bond rate          5.00%           1.05       1.1025 1.157625

                         FV                 $1.78         $1.75         $1.71        $1.68
                         FV sum div                                                  $6.92

       EPS rate clac        7.30% $          2.54   $      2.79    $     2.90    $    3.13


       IPV Calculation                              VL est.        Bloomberg
       P/E Range                                              16            33           12
       Term Val              2008                          $50           $103         $38 Target Selling prices
       sum div                                            $6.92         $6.92        $6.92 during the next 3-4 years
       FV yr 3                                             $57           $110         $45

       PV @                                         $49
                        Indicated Present Value Range                    $95          $38
                 5.00%
       Margin of Safety (25-40%)         30% $        34 $                 67 $         27 Target Buying prices
       Current Price                                                     $32               Today
       Expected Return                              21%                  51%           12%
       Required Return                              8.6%                 8.6%         8.6%




                                                     12
Exhibit 6. Summary Sheet for Merck

Merck-MRK                         Analysts: Corbett Gibson and Matt
Russell
PRICE: $28                               52 Week Range: $45 – $23
Company Rating: Buy, Moderate to High Risk
         Purchase:         400@$27                             Holding Period 3-4 years
   Target sell price:      $ 75                                Basis for Target Price: Ouma P/E of 24x

          Key Factors

            Product Strategy.
            Management
            Fit with Economic projections
            Industry – Porter analysis conclusions
            Competition – how does the firm fit
            Historical Earnings model comments
            Factors driving projections
            Growth Potential– comments from industry sheet
            Margins– comments from industry sheet
            Profitability– comments from industry sheet
            Investment Returns– comments from industry sheet
            Management Efficiency– comments from industry sheet
            Valuation – comments from industry sheet
            Does it fit the LWF strategy? Why?
          Risks – Why they may not succeed
            Key risks in product strategy
            Key risks of competitive analysis
            Financial Condition – comments from industry sheet
            Variability in sales, margins, etc. from pro forma
            Debt / TIE from Proforma
            Why might growth be slower than projected?
            Why may margins be lower than projected?
          Valuation
            Price relative to last 5 years and last 52 weeks
            Price multiples from Industry sheet compared to competitors and LWF firms
            PE HI LO from industry sheet compared to competitors and LWF firms
            Ouma assumptions
            Ouma model used PE, PCF, PBV, EV/EBITDA, etc.
            Ouma conclusions
            Ouma with MofS




Business Trends and Current Quarterly Results:

   Recent news:
          Industry news
          Same store sales recent results
          EPS recent quarterly results compared to expectations
          What is the trend, are they beating expectations or failing?



                                                   13
14
                                    REFERENCES

Bodie, Zvi, Alex Kane, and Alan J. Marcus, Investments, McGraw Hill-Irwin, 2002.

Damodaran, Aswath, Investment Valuation, Wiley, 1996.

Hoover, Scott A. and Frederic P. Sterbenz, “A Reality-Based Method for Valuing
Stocks,” Journal of Financial Education, Spring 2003, pp. 49-65.

Jensen, Michael, speech to the FMA Annual Meeting, October 2004.

Kalra, Rajiv and Marsha Weber, “A Comprehensive Stock Analysis Project For The First
Course in Investments,” Journal of Financial Education, Summer 2004, pp. 44-55.

MSN Money Stock Screener,
http://moneycentral.msn.com/investor/finder/customstocks.asp?Query=&tools=standard
&target=%2Finvestor%2Ffinder%2Fcustomstocks%2Easp%3FQuery%3D, April 15,
2005.

Rakers, Brent, Morgan Keegan equity analyst, unpublished presentation, February 28,
2005.

Reilly, Frank K. and Keith C. Brown, Investment Analysis and Portfolio Management,
Thomson-Southwestern, 2004.

Stohs, M., „Teaching Corporate Finance by Valuing a Corporation,” Journal of Financial
Education, Fall 1999, pp. 66-74.

Stowe, John D., Thomas R. Robinson, Jerald E. Pinto, and Dennis W. McLeavey,
Analysis of Equity Investments: Valuation, AIMR, 2002.

Value Line Investment Survey, July 23, 2004, Merck page.




                                          15

								
To top