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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

19. 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!

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

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

22. 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)

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

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

25. 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)

26. 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™