Hershey spreadsheets Ouma by It1M1gjf

VIEWS: 10 PAGES: 12

									                                                                       GENERAL LOUIS WILSON FUND
                                                                         PRO FORMA WORKSHEET
                                                                       The Hershey Company
COMPANY BASICS
Analyst: Robin Perry
Ticker:                         HSY        Dividend Yield:                 1.57%                   Exp Growth (VL)          7.5%                       Ouma Model                          P/E
Date:                        2/20/2006     52 Week High/Low:               67-51                   Lynch Ratio (>1)            0.4                     Base Exp. Price             $      123
Price:                         $53.65      Debt Ratio:                        0.99                 Sector                 Food                         Base Exp. Multiple                 32.7
Last Dividend:                $ 0.84       Shares Oustanding               246.6                   Industry            Confectioners                   Implied Multiple                     28
P/E (next year's EPS)             19.0     Beta:     MSN                    -0.1                   VL Financial Strength           A++                 Target Price                $      105

Risk and other ANALYSIS
                                                 1999         2000           2001         2002           2003         2004       2005E       2006E         2007E         2008E          2009E
Times Interest Earned        calc               10.62          6.35           9.88       18.59          35.95        132.99       17.76       19.68         21.33         23.04          24.84
Debt/Equity                  calc                 0.80         0.75           0.76         0.62           0.76         0.63        0.89        0.74          0.60          0.49           0.42

GROWTH - PROFITABILITY - LEVERAGE
NET RETURN ON EQUITY (In Millions)
                                                 1999          2000           2001         2002           2003         2004       2005E       2006E         2007E         2008E         2009E
Net Profit Margin            calc                7.4%          7.8%           8.3%       10.6%          11.4%        12.0%        12.1%       12.7%         13.2%         14.0%         14.8%
Total Asset Turnover         calc                 0.58          0.58           0.53         0.68           0.76         0.92        0.89        0.89          0.91          0.93          0.95
Total Assets/Equity          calc                 2.08          2.07           2.11         2.05           2.47         3.75        4.05        3.57          3.06          2.67          2.41
Net Return on Equity         calc              26.9%         28.1%          32.9%        31.8%          37.1%        48.6%        54.7%       51.0%         44.6%         40.3%         37.7%
EBITDA Oper. Profit Margin   VL                18.2%         18.7%          18.9%        22.5%          23.8%        24.7%        25.0%       26.0%         27.0%         28.0%         29.0%
The Hershey Company                              1999          2000           2001         2002           2003         2004       2005E       2006E         2007E         2008E         2009E
Revenues                            6.0%   $   3,971     $   4,221     $    4,557     $ 4,120      $    4,173     $ 4,429      $ 4,850     $ 5,141     $    5,347    $    5,561    $    5,783 Per 2004 Annual Report - long-term goals included increasing net sales 3%-4% per year, increasing gross margin 70-90 basis points per year, increasing before interest & income taxes 7%-9% per year and to increase earnings per share 9%-11% per year.
EBITDA                       calc          $      723    $     789     $      861     $ 927        $      993     $ 1,094      $ 1,213     $ 1,337     $    1,444    $    1,557    $    1,677
Depreciation                 VL            $      163    $     176     $      191     $ 178        $      181     $    190     $    200    $    215    $      228    $      244    $      261
EBIT                         calc          $      559    $     613     $      671     $ 749        $      812     $    904     $ 1,013     $ 1,122     $    1,216    $    1,313    $    1,416
Other Income (expense)       MSN           $      -      $      -      $        19    $       4    $         2    $     -      $    -      $    -      $      -      $      -      $      -
Interest                            6.0%   $       53    $       97    $        68    $      40    $        23    $       7    $     57    $     57    $       57    $       57    $       57
EBT                          calc          $      507    $     517     $      622     $ 713        $      792     $    898     $    956    $ 1,065     $    1,159    $    1,256    $    1,359
Taxes (per VL)                    36.5%    $      185    $     189     $      227     $ 260        $      289     $    328     $    349    $    389    $      423    $      458    $      496
Net Income                   calc          $      322    $     328     $      395     $ 452        $      503     $    570     $    607    $    676    $      736    $      798    $      863
Net Income (reference)       VL            $      295    $     330     $      378     $ 436        $      475     $    530     $    585    $    655    $      707    $      778    $      856
No. Shares                   VL                    277           273            271          268            259          247         243         240           237           234           230
EPS                          VL            $     1.05    $     1.20    $      1.37    $ 1.59       $      1.80    $ 2.06       $ 2.35      $ 2.65      $     2.97    $     3.26    $     3.59
EPS                          calc          $     1.16    $     1.20    $      1.46    $ 1.69       $      1.94    $ 2.31       $ 2.50      $ 2.82      $     3.10    $     3.41    $     3.75
Total Assets                 BB            $   2,285     $   2,431     $    2,424     $ 2,816      $    3,159     $ 4,084      $ 4,329     $ 4,589     $    4,864    $    5,156    $    5,465
Total Equity                 VL/calc       $   1,099     $   1,175     $    1,147     $ 1,372      $    1,280     $ 1,089      $ 1,070     $ 1,285     $    1,587    $    1,931    $    2,269
ST Debt                      BB            $      212    $     258     $         8    $      28    $        13    $    622     $    819    $    868    $      920    $      976    $    1,034
LT Debt                      VL            $      878    $     878     $      877     $ 852        $      969     $    691     $    950    $    950    $      950    $      950    $      950
BOOK Value per share         calc                 3.97          4.31           4.23         5.11           4.94         4.42        4.40        5.35          6.70          8.25          9.87
Sales per share              calc          $       14    $       15    $        17    $      15    $        16    $      18    $     20    $     21    $       23    $       24    $       25
MV Equity                    calc          $   7,657     $   7,100     $    8,505     $ 9,114      $    9,015     $ 11,602     $ 14,653    $ 15,340    $   16,057    $   16,805    $   17,509
Enterprise Value             calc          $   8,535     $   7,978     $    9,382     $ 9,965      $    9,984     $ 12,293     $ 15,603    $ 16,290    $   17,007    $   17,755    $   18,459
Average Price                VL/calc       $       28    $       26    $        31    $      34    $        35    $      47    $     60    $     64    $       68    $       72    $       76
Dividends per share          VL            $     0.50    $     0.54    $      0.58    $ 0.63       $      0.73    $ 0.84       $ 0.93      $ 1.05      $     1.15    $     1.25    $     1.35
CHG NWC                      VL                 $248          ($39)           $33         $156          ($171)       ($649)         $33        $180            $0            $0            $0
CAPX                         BB                ($115)        ($138)         ($160)       ($133)         ($219)       ($182)         ($0)        ($0)          ($0)          ($0)          ($0) Per 2004 Annual Report - the Co anticipates capital expenditures and capitalized software additions will be in the range of $175mm to $200mm per annum during the next several years primarily for cont'd efficiency improvements in existing facilities and capacity expansion to support sales growth and new products, along with continued improvement and enhancements of computer software.
Free Cash Flow to the Firm                      $725          $498           $599         $800           $438          $78       $1,082      $1,338        $1,252        $1,349        $1,452
Free Cash Flow to Equity                        $987          $766           $921       $1,208           $898         $604       $1,631      $1,957        $1,923        $2,091        $2,272
                                                 1999          2000           2001         2002           2003         2004       2005E       2006E         2007E         2008E         2009E

Free cash flow (BB)                            212.24        273.88         546.3       492.61          374.29       615.72
Working Capital (BB)                                          561.1        716.77       545.76         -102.94      -109.28


                                                                                                                   244752345 shares
                                                                                                                  1995600000 debt
                                                                                                                  4295240000
                                                                                                                        46%
                                                                                                                         6%
OUMA IPV Model                 P/E

HSY                                                   1            2              3
year              Growth              2006        2007         2008           2009
Dividends            23.00% $         1.27       $1.56        $1.92          $2.36
FVIF @            Bond rate          8.00%         1.08         1.17           1.26

                  FV                 $1.60       $1.82        $2.08          $2.36
                  FV sum div                                                 $7.87

EPS rate calc        10.03% $         2.82   $    3.10    $    3.41      $    3.75


IPV Calculation                                           VL est         BB High      BB Low        My Est
P/E Range                                                         22             33           19            26                            per various analyst estimates
Term Val                2009                                    $83           $123          $72           $98 Target Selling prices
sum div                                                       $7.87          $7.87        $7.87         $7.87 during the next 3-4 years
FV yr 3                                                         $90           $131          $80          $105

PV @               Indicated Present Value Range                   $72       $104          $63           $84
            8.00%
Margin of Safety (25-40%)            30%                  $      50 $          73     $        44   $      59 Target Buying prices
Current Price                                             $      54                                           Today
Expected Return                                                 19%            35%          14%           25%
Required Return                                                7.6%           7.6%         7.6%          7.6%
OUMA IPV Model

HSY                                                1        2            3
year                Growth          2006       2007     2008         2009
Dividends              23.00% $     1.27      $1.56    $1.92        $2.36
FVIF @              Bond rate      8.00%        1.08     1.17         1.26

                    FV             $1.60      $1.82    $2.08        $2.36
                    FV sum div                                      $7.87

BV rate calc           23.30%      $4.40      $5.35    $6.70        $8.25


IPV Calculation                                                 BB High      BB Low       My Est
P/BV Range                                                              15            6            9                            per various analyst estimates
Term Val                 2009                                        $126          $49          $74 Target Selling prices
sum div                                                             $7.87        $7.87        $7.87 during the next 3-4 years
FV yr 3                                                              $134          $57          $82

PV @                Indicated Present Value Range                   $106          $45          $65
            8.00%
Margin of Safety                     30%                        $     74     $      32 $         46 Target Buying prices
Current Price                                                                      $54              Today
Expected Return                                                       36%           2%          15%
Required Return                                                      7.6%         7.6%         7.6%
OUMA IPV Model

HSY                                                 1         2             3
year                Growth          2006        2007      2008          2009
Dividends              23.00% $     1.27       $1.56     $1.92         $2.36
FVIF @              Bond rate      8.00%         1.08      1.17          1.26

                    FV             $1.60       $1.82     $2.08         $2.36
                    FV sum div                                         $7.87

Sales g rate            5.99%     $19.96      $21.42    $22.56        $23.76


IPV Calculation                                                   BB High       BB Low        My Est
P/S Range                                                                   4           2              3
Term Val                 2009                                            $92         $43            $71 Target Selling prices
sum div                                                                $7.87       $7.87          $7.87 during the next 3-4 years
FV yr 3                                                                 $100         $50            $79

PV @                Indicated Present Value Range                       $79          $40           $63
            8.00%
Margin of Safety                     30%                          $       55 $           28   $      44 Target Buying prices
Current Price                                                            $54                            Today
Expected Return                                                          14%         -9%             5%
Required Return                                                         7.6%        7.6%           7.6%
OUMA IPV Model

HSY                                                 1            2              3
year                Growth          2006        2007         2008           2009
Dividends              23.00% $     1.27       $1.56        $1.92          $2.36
FVIF @              Bond rate      8.00%         1.08         1.17           1.26

                    FV             $1.60       $1.82        $2.08          $2.36
                    FV sum div                                             $7.87

CF rate calc           10.26%      $3.32       $3.71        $4.07          $4.45


IPV Calculation                                         VL Est         BB High      BB Low        My Est
P/CF Range                                                      16             25            11            17                            BB 2005 #
Term Val                 2009                                 $71           $112           $48           $76 Target Selling prices
sum div                                                     $7.87          $7.87         $7.87         $7.87 during the next 3-4 years
FV yr 3                                                       $79           $120           $56           $84

PV @                Indicated Present Value Range                $63        $95         $44.72        $66.31
            8.00%
Margin of Safety                     30%                $      44 $          67     $        31   $       46 Target Buying prices
Current Price                                                 $54                                            Today
Expected Return                                               14%            31%            2%           16%
Required Return                                              7.6%           7.6%          7.6%          7.6%
OUMA IPV Model

HSY                                                  1           2             3
year                Growth            2006       2007        2008          2009
Dividends              23.00% $       1.27      $1.56       $1.92         $2.36
FVIF @              Bond rate        8.00%        1.08        1.17          1.26

                    FV               $1.60      $1.82       $2.08         $2.36
                    FV sum div                                            $7.87

EBITDA/sh rate clac    10.07%        $4.99      $5.57       $6.09         $6.65


IPV Calculation                                                      BB High       BB Low        My Est
P/EBITDA Range                                                                16           9              13                            BB 2005 #
Term Val                 2009                                              $104         $61             $86 Target Selling prices
sum div                                                                   $7.87       $7.87           $7.87 during the next 3-4 years
FV yr 3                                                                    $112         $69             $94

PV @                             Indicated Present Value Range           $89.01      $54.68          $74.91
            8.00%
Margin of Safety                       30%                           $       62 $           38   $       52 Target Buying prices
Current Price                                                               $54                             Today
Expected Return                                                             18%          1%             12%
Required Return                                                            7.6%        7.6%            7.6%
OUMA IPV Model

HSY                                               1           2             3
year                Growth         2006       2007        2008          2009
Dividends              23.00% $    1.27      $1.56       $1.92         $2.36
FVIF @              Bond rate     8.00%        1.08        1.17          1.26

                    FV            $1.60      $1.82       $2.08         $2.36
                    FV sum div                                         $7.87
                    rate calc
EBITDA/sh               10.07%    $4.99      $5.57       $6.09         $6.65


IPV Calculation                                                   BB High       BB Low
EV / T 12 EBITDA Range                                                     14          11
Term Val EV less Debt=TV Equity                2009                      $84         $63 Target Selling prices
sum div                                                                $7.87       $7.87 during the next 3-4 years
FV yr 3                                                                  $91         $71

PV @                              Indicated Present Value Range       $72.59      $56.53
            8.00%
Margin of Safety                    30%                           $       51 $        40 Target Buying prices
Current Price                                                            $54             Today
Expected Return                                                          11%          2%
Required Return                                                         7.6%        7.6%
Dividend Discount Model
The Hershey Company
Enter 0 for noncon. growth or 1 for constant                0
Cost of Equity                                         8.00%
nonconstant growth period (n)                               7
nonconstant growth rate                                8.00%                        BB default (implied growth rate)
infinite growth rate                                   4.20%                        BB default (growth rate at maturity)
Do                                                   $  0.84
Dividend at end of year                            1    0.91
                                                   2    0.98
                                                   3    1.06
                                                   4    1.14
                                                   5    1.23

           Period        Dividend (or price)          Discounted PV (Po)
                     1       D1                0.91        0.84
                     2       D2                0.98        0.84
                     3       D3                1.06        0.84
                     4       D4                1.14        0.84
                     5       D5                1.23        0.84
Price at end
 of year             7         Pn =            0.00        0.00      Po =   $4.20
                                                    --------------
                                               Po =       $4.20
FCFF
HSY                                               1         2              3              4
year             Growth                       2006      2007           2008           2009
FCFF                  2.07%                 $1,338    $1,252         $1,349         $1,452
                 Equity %       8.00%
                 WACC           7.50%
                 PV                         $1,244    $1,083         $1,086         $1,087
                 PV FCFF years 1-4                                                  $4,501
Terminal Value

Constant growth model        WACC=           7.50%              $    19,761    Terminal value
                             Growth=         2.07%              $       950    less debt        2009
                                                                    $18,811    TV Equity        2009
                                                                         230   no. shares       2009
                                                                $     81.79    Selling price    2009

Plus PV FCFF years 1-4                                               $4,501                     2006
PV of TV                                                             14,796
Less PV Debt                                                    $     1,769
PV Equity                                                           $17,528
Number of shares                                                     243.00
Intrinsic Value per share based on FCFF                              $72.13


PV @             Indicated Present Value Range                         $72 INTRINSIC VALUE

Margin of Safety (25-40%)          30%                          $       50 Target Buying prices
Current Price                                                          $54 Today
Target selling price
Expected Return at M of S price                                         13%
Expected Return at current price                                        11%
Required Return CAPM                                                   7.6%
FCFEquity
HSY                                               1         2              3               4
year             Growth                       2006      2007           2008            2009
FCFEquity             3.81%                 $1,957    $1,923         $2,091          $2,272
                 Equity %          8.00%

                 PV                         $1,812    $1,649         $1,660          $1,670
                 PV FCFF years 1-4                                                   $6,791
Terminal Value

Constant growth model         Equity%        8.00%              $    29,482    Terminal value
                              Growth=        3.81%                       $0                     2009
                                                                    $29,482    TV Equity        2009
                                                                         230   no. shares       2009
                                                                $    128.18    Selling price    2009

Plus PV FCFF years 1-4                                               $6,791                     2006
PV of TV                                                              21670
                                                                         $0
PV Equity                                                           $28,461
Number of shares                                                     243.00
Intrinsic Value per share based on FCFF                             $117.12


PV @             Indicated Present Value Range                        $117 INTRINSIC VALUE

Margin of Safety (25-40%)           30%                         $       82 Target Buying prices
Current Price                                                          $54 Today
Target selling price
Expected Return at M of S price                                         12%
Expected Return at current price                                        24%
Required Return CAPM                                                   7.6%
WACC

HSY
                    BV   MV           Weight      Product
Gov Bond    4.50%

Debt=Bond   5.50%        $    1,818         11%        0.35%
Equity %    8.00%        $15,340.32         89%        7.15%
                         $17,158.58                    7.50% WACC

                                      HSY historical capital structure
The Hershey Company                                SUMMARY OF OUMA MODEL FINDINGS
   2/20/2006 Price                  $      53.65
                                                              Multiple      Indicated Present Values
             Key Variables                         EPS v      PE >                19.15         22         32.7

10% & 33%    Sales growth           High           $   4.39                  $     73    $     83      $   120
7.5% & 29%   Base Case              VL             $   3.75                  $     72    $     63      $   104
5% & 25%     Oper. Margin           Low            $   3.11                  $     54    $     61      $    87

                                                                            Target Prices 2009
             Key Variables                         EPS v      PE >               19.15         22          32.7

10% & 33%    Sales growth           High           $   4.39                  $     84    $     97      $   144
7.5% & 29%   Base Case              VL             $   3.75                  $     83    $     72      $   123
5% & 25%     Oper. Margin           Low            $   3.11                  $     60    $     69      $   102

             Target Selling Price                                                                      $    105
             Base EPS                                                                                  $   3.75
             Implied PE Multiple                                                                              28

             Analyst Name:          Robin Perry


Current Price $             53.65 During Horizon Period Now                 Now
                                  Sell        VL        IPV                 buy w mofs
             PE                    $    123 $        83 $                72 $      44
             PCF                   $    112 $        71 $                63 $      44
             PBV                   $    126                                  $     32
             PS                    $     92                                  $     28
             PEBITDA               $    104                                  $     38
             EV                    $     84                                  $     40
                                   $    107                                  $     38

								
To top