Formula to Calculate Adjusted Eps - PowerPoint by whn12745

VIEWS: 360 PAGES: 26

More Info
									Step 4. Find the ‘Firstcall’ tab in the Valuation template
• Input CVS‟ consensus EPS estimates




 Input EPS forecasts using the attached EPS projection sheet provided by Firstcall.
5. Input WAG’ consensus EPS figures
• Use calendar year – not fiscal year - EPS estimates
• Understand how and w hy to calendarize (see explanation below )




                                                                    Input Calendar year EPS forecasts
                                                                    using the attached EPS projection by
                                                                    Firstcall.




   Why calendarize?: Since the fiscal year end for Walgreen's is August (versus December for most companies), EPS projections for WAG
   should be adjusted for the calendar year (December) year end in order for the multiples for each company to be on an apples to apples basis.

   How to calendarize: Refer to and input the calendar year estimates as provided by firstcall in the attached sheet. Note that for Walgreen's
   calendar year estimates are calculated simply by adding the current year‟s fiscal Q2 + Q3 + Q4 and next year‟s fiscal Q1.
Before we proceed…
• We need to calculate diluted shares outstanding in order to calculate market value
  and enterprise value
      – Recall the calculations of market value and enterprise value:
            – Market (Equity) Value = Current share price x diluted shares outstanding
            – Enterprise Value = Market (Equity) Value + Net Debt (see page 6 for a full definition of net debt)

• Calculation of Diluted shares outstanding:
      – Latest count of basic shares outstanding (found on the front page of a company‟s latest 10Q or 10K
        filed with the SEC)
      – Plus: potentially dilutive shares from the exercise of stock options, warrants, convertible debt,
        and/or convertible preferred stock
            – Dilutive securities are defined as securities that are not common stock in form but that enable their holders to obtain common stock
              upon exercise or conversion. The most notable examples include stock options, warrants, convertible bonds, and convertible
              preferred stocks.
            – Stock options are issued in stock compensation plans and used to pay and motivate employees. This type of security gives selected
              employees the option to purchase common stock at a given price over an extended period of time.
            – Warrants are similar to options. They are certificates entitling the holder to acquire shares of stock at a certain price within a stated
              period. When warrants are exercised, the holder must pay a certain amount of money to obtain the shares. Also, when stock warrants
              are attached to debt, the debt remains after the warrants are exercised.
            – In the case of convertible bonds, company issues bonds which can be converted into common shares. The conversion feature allows
              the corporation an opportunity to obtain equity capital without giving up more ownership control than necessary. (Also, the conversion
              feature entices the investor to accept a lower interest rate than he or she would normally accept on a straight debt issue).
            – Convertible preferred stock is similar to convertible debt, except that preferred stock, instead of debt, is originally issued.

      – Less: stock repurchased using the treasury stock (TS) method*
            – Treasury stock method assumes that all proceeds from exercised options, warrants, convertible debt and stock are used to repurchase
                outstanding shares




* Treasury Stock Method: (Option average exercise price x in-the-money stock options) / Current stock price = ($14.00 x 4.0) / $24.00
Before we proceed…
• Dilutive securities: Example:
         – Company A stock trades at $24.00 on the stock market.
         – Basic shares outstanding (as found on the front page of company A‟s latest 10Q): 100.0
         – 4.0 million in-the-money shares from in-the-money stock options (at an average exercise price of
           $14)
         – Calculate diluted shares outstanding for company A using the treasury stock method

                        Basic shares Outstanding (from 10Q/K cover)                                                                              100.0
                        Plus: Stock options (in-the-money)*                                                                                        4.0
                        Less: Repurchased Shares using option proceeds**                                                                          (2.3)
                        Diluted Shares Outstanding                                                                                               101.7




* In-the-money ref ers to when the strike (exercise) price of an option is below the current market share price. In such a case, the underly ing option has an intrinsic v alue.
** Treasury Stock Method: (Option av erage exercise price x in-the-money stock options) / Current stock price = ($14.00 x 4.0) / $24.00
8. Calculate CVS’ diluted shares outstanding
• Input diluted shares outstanding and make any adjustments for stock splits.




                                                                                                                       Latest share count can be found
                                                                                                                       on first page of the latest 10Q or 10K (w hichever is latest)

                                                                                                                       Bloomberg provides data on stock splits.
                                                                                                                       • In the absence of Bloomberg, check new s betw een last
                                                                                                                          share count date and date of share price.
                                                                                                                       • Company w ebsites generally compile company-related
                                                                                                                          new s in the “Investor Relations” section.


                                                                                                                       Calculation (Actual share count* Stock split).



    • Share data needs to be adjusted if any stock splits occurred after the date of the latest share count and before the current date.
    • Understand the intuition: since stock prices are as of the current date, they w ould already reflect any stock splits that may have occurred since the latest share count date.
      In case of any such stock split, the share count needs to be adjusted in order to bring the share price and shares outstanding to a comparable “apples to apples” basis.
    • Firms generally use Bloomberg as the authoritative source for tock split information.. In the absence of a Bloomberg terminal, check new s releases between the share count
      date and the current date.
    • Note that the line item “Stock split” has been custom formatted to look like x:1 to reflect an “x to 1” stock split.
     9. Input dilutive securities detail
     •    Input options details - Option detail is usually found in a company's 10K footnotes.
     •    Tip: When looking at a 10K online, a quick w ay to locate options data is to utilize the „Find‟ command. Hit Ctrl + “F” and ty pe in “Options” or Options Outstanding”
     •    For CVS the options footnote can be found in CVS‟ 10K (Annual Report), page 34, Footnote 7

Note: Although the formulas for the calculations in the option detail have already been made for you, w e recommend you intuitively understand w hy they are being made. Below is an
explanation. For each batch (tranche) of options:

1). Input the number of outstanding options and weighted average exercise price of outstanding optionsinto cells D35 and D36, respectively.

2). In cell D37, options are adjusted to reflect any stock splits that m ay have occurred between the latest share count date and the current stock price date.
   • For example, say a 2-for-1 split occurred betw een the latest share count date and the current stock price date. Recall that you should have inputted “2” in the stock split line item (cell D29).
      The formula in cell D37 calculates the total number of options in Batch 1 (cell D35), by the stock split parameter.

3). Next, the formula in cell D38 divides the exercise price by the split parameter to determine the split adjusted exercise price.

4). The formula in cell D39 determines whether to include this batch in the dilutive share count. This depends, quite natura lly, on the exercise price of the options.
   • If the options are “in-the-money” (the exercise price is less than the current share price)  include the shares in the dilutive share count. The formula in cell D39 “checks” w hether the
     option batch is in-the-money =IF(D38<D$9,D37,0)
   • If the split adjusted exercise price is less than the current share price, the number options in this batch are included, otherw ise the options in this batch are ignored

5). Next, cell D40 calculates the total proceeds to CVS’ from the exercise of options.
   • The formula =D38*D39. Remember how options w ork: an employee exercises her options by paying the exercise price to her company.
   • CVS receives proceeds equaling the exercise price * options exercised) and has to deliver shares in return, thus diluting the share base.

6). Repeat for each batch of options




                                                                                                                       Detailed information on dilutive securities (options,
                                                                                                                       w arrants, convertible debt, etc… is usually disclosed only
                                                                                                                       annually in the 10K


                                                                                                                        CVS AR-34, Footnote 7


                                                                                                                       Calculations..
28. Review the sensitivity analysis worksheets
• Input a range of discount rates into the input column
• A data table w ill be used to analyze the impact of a range of discount rate assumptions on fair value




                                                                                                           Terminal grow th rate sensitivity
                                                                    output column




                             Input column
       29. Review the sensitivity analysis worksheets
       • Input a range of discount rates into the input column
       • A data table w ill be used to analyze the impact of a range of discount rate assumptions on fair value




Link the WACC (from cell D58) into the
upper leftmost cell of the input column in
the data table. Then, input a range of
discount rates below . DO NOT link any
formulas into this column. Simply input
(blue cell) the desired values




                                                             Link the PV of FCF (cell P34) to upper rightmost range of the
                                                             data table. The data table know s to look to the upper rightmost
                                                             part of data tables to calculate a range of present values of FCF
                                                             based on the inputted discount rate range in the column to the
                                                             left.
30. Insert data tables




                                                                Output Cell




    Highlight the cell range D65:E70
    and follow instructions below




Notes on data tables
•   Before we proceed note that we entered our discount rate range vertically into a column. Such a data tab le is called a vertic al tab le. Had the input range b een
    entered into a row, the data tab le would b e called a horizontal tab le.
•   The output cell (PV of FCF) for this data tab le has already b een selected.
1. Highlight cell range D65:E70. The upper rightmost cell of a vertical data table always represents the „Output cell‟ in a vertical data table. The output here is
    the PV of FCF: Remember, we want to analyze how the PV of FCF changes when different discount rates are used.
2. Select “Data Table from Excel‟s dropdown menu (Shortcut Alt+D+T)
3. A Dialog Box will pop up with 2 prompts:
4. This prompt is asking for the input (in this exercise, that‟s the discount rate). Insert the input cell reference (the disco unt rate in cell D58) into the dialog box.
    In a vertical data table always use the „Column Input‟ cell and leave the „Row Input‟ cell blank. Conversely, use the „Row I nput‟ cell for a horizontal data table.
5. Hit “OK”
6. Hit F9 if you have Excel set to “Manual calculation” or “Automatic except tables.” To determine what Excel is set to, select Tools  Options  Calculation. We
    recommend you set Excel to “Automatic except tab les” and set Iterations to 100 maximum iterations
31. Review the sensitivity analysis worksheets




                                                                                                                       Terminal grow th rate sensitivity




                               Data Table




Now that you’re done, sanity-check the results.
                                                                                                                                            Fair value per share based on a
                                                                                                                                            range of discount rate and
                                                                                                                                            perpetual grow th rate assumptions
           When WACC               fair value per share must
           When WACC               fair value per share must
           When perpetual          growth rate fair value per share must
           When perpetual          growth rate fair value per share must

• Understand the intuition
    • Increasing the WACC causes the present value of FCF and the present value of the terminal value to decrease. Enterprise
      value decreases as a result, w hich results in a low er fair value per share.
    • Increasing the assumed perpetual grow th rate causes the present value of the terminal value to increase. Enterprise value
      increases as a result, w hich results in a higher fair value per share.
48. Reference CVS 2002A balance sheet results into the pro forma balance sheet. (See accretion/(dilution) chapter for detaile d discussion on pro forma analysis).
• Note that balance sheet line items have been consolidated. Nonetheless, ALL balance sheet line items from the actual reported balance sheet must be accounted for w ithin
  this pro forma balance sheet.
• Check to insure that Assets = Liabilities + Shareholder‟s Equity!




                                                                          Reference from CVS’ 2002A balance sheet
                                                                          Note: reference all of CVS‟ debt items into the
                                                                          Senior Bank Debt line item
                                         Input



                                                                                                                 Check to insure that Assets =
                                         Input                                                                     Liabilities + Shareholder’s Equity!
                                                                                                                 • If they don‟t, double-check that you
                                                                                                                   included ALL of the line items from the
                                                                                                                   original balance sheet.
49. Com plete and review pro forma balance sheet




         Adjust pro forma results to exclude cash                       Adjust pro forma results to exclude old target goodwill (from BS w orksheet) and reference new
         that w as used to fund the transaction.                        calculated goodwill from ‘Goodwill Calculation’ section above.




                                                                                                      Reference capitalized (amortizable)
                                                                                                      transaction fees from ‘Sources & Uses’
                                                                                                      section (cell D111).


                                                                                                            Adjust pro forma results to exclude debt that was
                                                                                                            refinanced (paid down) and add newly borrowed senior
                                                                                                            bank debt (reference from ‘Sources & Uses’ section).


                                                                                                                 Adjust to include newly borrowed notes
                                                                                                                 (reference from ‘Sources & Uses’ section).


                        Adjust pro forma results to exclude old target book value (reference from ‘Goodwill Calculation’
                        section above) and reference new common equity from the ‘Sources & Uses’ section.
1. Find and open the M&A Transaction Summary worksheet within the valuation template
• The sample transaction in this seminar is the hypothetical acquisition by CVS of Duane Reade.
2. Input target and acquirer into the assumption w orksheet
• Titles should be dynamic




                                                              Dynamic titles: formula ="Acquisition of "&C6&" by "&C7




                                                                   Input target and acquirer name
3. Input acquisition date and acquirer’s fiscal year end prior to the acquisition




                                                                              Input the acquition date

                                                                               Input CVS‟ fiscal year end prior to the acquistion date
4. Calculate stub year fraction




                                  Calculate the „stub‟ year fraction: the period that has elapsed from the acquirer‟s
                                  fiscal year end to the acquistion date

                                  Formula in excel: =(C8-C9)/365
5. Input offer price per share, as well as the target current m arket price per share (if public)




                                                                              Input the Offer price per share
                                                                              Input the Target Stock price and date as it trades in the public market
6. Calculate acquisition premium (offer price per share / target stock price prior to acquisition date).




                                                                            Calculate the offer premium = Offer price/Target stock price
                                                                            Input the Acquirer Stock price as it trades in the public market
7. Begin offer value calculation
• Reference offer price per share from above
• Input target basic shares outstanding




                         • Reference offer price from „Deal Information‟ section above
                         • Input Target basic shares outstanding from the front page of the Target‟s most
                           recent 10K/10Q leading up to the acquistion date
                         • In this case, the most recent filing is DRD‟s 10Q for Q1, 2003 ended 3/29/03.
                         • Remember to footnote or insert a comment w ith the source document details!
20. Com plete Sources & Uses Section




• Com plete the Sources & Uses Schedule. Understand the intuition: Sources
  MUST Equal Uses of Funds

Sources
• Excess Cash: Excess cash is used solely for transaction expenses
• Stock Issed: (Acquirer Shares In Transaction) x (Acquirer Stock Price)
• Acquistion Debt Financing: Equals Offer Value - Stock Issued
• Target Debt Assumed: Reference from Target Balance Sheet

Uses
• Transaction Expenses: Reference from Transaction Assumptions above
• Purchase of Equity: Equals Offer Value
• Target Debt Assumed: Reference from Target Balance Sheet
22. Adjust pro forma balance sheet to reflect new goodwill
• Eliminate old target goodw ill balance
• Replace w ith new calculated goodw ill referenced from the transaction summary w orksheet




                                                                                              • Eliminate old target goodw ill
                                                                                              • Reference new transaction goodwill (which accounts for target‟s
                                                                                                previous existing goodw ill) from the goodw ill calculation on the
                                                                                                transaction summary w orksheet
23. Com plete pro forma adjustments
• Balances from the latest financial statements prior to the acquisition date




                                                                                • Reference acquistion debt from the Sources & Uses section of
                                                                                  the transaction summary w orksheet




                                                                                • Adjust to eliminate old target book value
                                                                                • Reference new equity issued (net of option proceeds)
30. Input balance sheet items
• Balances from the latest financial statements prior to the acquisition date




                                                                         Adjust income statement to reflect pro forma results
                                                                         • Eliminate old target interest expense and replace w ith total new interest expense:

                                                                         Calculation for total new interest expense:
                                                                           (New Acquistion Debt) x (New Debt Interest Rate)
                                                                           Plus: (Target Debt Assumed) x (Target Debt Interest Rate)
                                                                         Equals: Total New Interest Expesne




                                         Calclate incremental tax from pro forma adjustments:
                                         • Multiply pretax income adjustmets from elimination of
                                           target interest expense by target‟s marginal tax rate



                                                                                                                                                      Reference from transaction summary



                                                                                                     Calculate incremental tax from pro forma adjustments:
                                                                                                     • Multiply pretax income adjustments from new interest
                                                                                                       expense by Acquirer marginal tax rate
35. Input year 2 EPS, Diluted Shares Outstanding, and Net Income Estimates
• Reference from comparable company analysis




                                                                             Reference acquirer and Target EPS Projections from
                                                                             comparble company analysis
42. Calculate accretion/(dilution)




                                     Accretion/(Dilution)- $: PF Diluted EPS - Acquirer EPS
                                     Accretion/(Dilution)- %: PF Diluted EPS / Acquirer EPS-1
                                     Pretax synergies to breakeven: -1 x $ accretion/(dilution) x diluted share outstanding / (1- acquirer marginal tax rate)
Wall StreetPrep’s Complete Self Study Program includes:

•       Reference Manual I: Fundamental of Financial Modeling Step-by-Step Guide (180 pages).

•       Reference Manual II: Valuation Modeling Step-by-Step Guide (330 pages)

•       Wall StreetPrep's Proprietary Financial Model and Valuation Templates in Excel
          Models Included (on Compact Disc):
          •  Financial Statement Forecasting (Projection) Model
          •  DCF Model
          •  LBO Model
          •  Accretion/Dilution Model
          •  Comparable Company Analysis Model
          •  Comparable Transaction Model
          •  All are yours to keep and reference throughout your career

•       Free Online Support




          Go beyond learning financial modeling. Learn the technical,
          conceptual, and intuitive framework behind what you are modeling.


    Wall StreetPrep™

								
To top