# Formula to Calculate Adjusted Eps - PowerPoint by whn12745

VIEWS: 360 PAGES: 26

• pg 1
```									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

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