Step 4. Find the ‘Firstcall’ tab in the Valuation template
This presentation is the property of its rightful owner.
Sponsored Links
1 / 26

Step 4. Find the ‘Firstcall’ tab in the Valuation template Input CVS’ consensus EPS estimates PowerPoint PPT Presentation


  • 117 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

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

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Slide 1 wall street prep

  • 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.


Slide 1 wall street prep

  • 5. Input WAG’ consensus EPS figures

  • Use calendar year – not fiscal year - EPS estimates

  • Understand how and why 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.


Slide 1 wall street prep

  • 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


Slide 1 wall street prep

  • 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 Outstanding101.7

  • * In-the-money refers to when the strike (exercise) price of an option is below the current market share price. In such a case, the underlying option has an intrinsic value.

  • ** Treasury Stock Method: (Option average exercise price x in-the-money stock options) / Current stock price = ($14.00 x 4.0) / $24.00


Slide 1 wall street prep

  • 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 (whichever is latest)

  • Bloomberg provides data on stock splits.

  • In the absence of Bloomberg, check news between last share count date and date of share price.

  • Company websites generally compile company-related news 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 would 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 news 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.


Slide 1 wall street prep

  • 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 way to locate options data is to utilize the ‘Find’ command. Hit Ctrl + “F” and type 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, we recommend you intuitively understand why 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 options into cells D35 and D36, respectively.

  • 2). In cell D37, options are adjusted to reflect any stock splits that may have occurred between the latest share count date and the current stock price date.

    • For example, say a 2-for-1 split occurred between 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 naturally, 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” whether 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, otherwise 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 work: 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, warrants, convertible debt, etc… is usually disclosed only annually in the 10K

CVS AR-34, Footnote 7

Calculations..


Slide 1 wall street prep

  • 28. Review the sensitivity analysis worksheets

  • Input a range of discount rates into the input column

  • A data table will be used to analyze the impact of a range of discount rate assumptions on fair value

Terminal growth rate sensitivity

output column

Input column


Slide 1 wall street prep

  • 29. Review the sensitivity analysis worksheets

  • Input a range of discount rates into the input column

  • A data table will 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 knows 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.


Slide 1 wall street prep

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 table is called a vertical table. Had the input range been entered into a row, the data table would be called a horizontal table.

  • The output cell (PV of FCF) for this data table has already been selected.

  • 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.

  • Select “Data Table from Excel’s dropdown menu (Shortcut Alt+D+T)

  • A Dialog Box will pop up with 2 prompts:

  • This prompt is asking for the input (in this exercise, that’s the discount rate). Insert the input cell reference (the discount 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 Input’ cell for a horizontal data table.

  • Hit “OK”

  • 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 tables” and set Iterations to 100 maximum iterations


Slide 1 wall street prep

31. Review the sensitivity analysis worksheets

Terminal growth rate sensitivity

Data Table

  • Now that you’re done, sanity-check the results.

  • 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, which results in a lower fair value per share.

    • Increasing the assumed perpetual growth rate causes the present value of the terminal value to increase. Enterprise value increases as a result, which results in a higher fair value per share.

Fair value per share based on a range of discount rate and perpetual growth 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


Slide 1 wall street prep

  • 48. Reference CVS 2002A balance sheet results into the pro forma balance sheet. (See accretion/(dilution) chapter for detailed 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 within 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 = Liabilities + Shareholder’s Equity!

  • If they don’t, double-check that you included ALL of the line items from the original balance sheet.

Input


Slide 1 wall street prep

49. Complete and review pro forma balance sheet

Adjust pro forma results to exclude cash that was used to fund the transaction.

Adjust pro forma results to exclude old target goodwill (from BS worksheet) and reference new 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.


Slide 1 wall street prep

  • 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.


Slide 1 wall street prep

  • 2. Input target and acquirer into the assumption worksheet

  • Titles should be dynamic

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

Input target and acquirer name


Slide 1 wall street prep

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


Slide 1 wall street prep

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


Slide 1 wall street prep

5. Input offer price per share, as well as the target current market 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


Slide 1 wall street prep

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


Slide 1 wall street prep

  • 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 with the source document details!


Slide 1 wall street prep

20. Complete Sources & Uses Section

  • Complete 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: EqualsOffer Value

  • Target Debt Assumed: Reference from Target Balance Sheet


Slide 1 wall street prep

  • 22. Adjust pro forma balance sheet to reflect new goodwill

  • Eliminate old target goodwill balance

  • Replace with new calculated goodwill referenced from the transaction summary worksheet

  • Eliminate old target goodwill

  • Reference new transaction goodwill (which accounts for target’s previous existing goodwill) from the goodwill calculation on the transaction summary worksheet


Slide 1 wall street prep

  • 23. Complete 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 worksheet

  • Adjust to eliminate old target book value

  • Reference new equity issued (net of option proceeds)


Slide 1 wall street prep

  • 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 with 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


Slide 1 wall street prep

  • 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


Slide 1 wall street prep

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)


Slide 1 wall street prep

  • 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™


  • Login