There is one more attachment I could not attach. Let me know what i need to do.
Acct 5401 Pfizer Analysis Case Page 1 of 4 This assignment is worth 100 points. It must be submitted electronically in Canvas by 11:59 p.m. on Sun., Dec. 13. Your submission must include both your memo (in either MS Word or PDF format), and a completed copy of the spreadsheet. Update: The “Pfizer Analysis Case.xlsx” spreadsheet was updated at about 11:25 am on Dec. 3 to identify derivative-related amounts in several “other assets” and “other liabilities” line items as nonoperating amounts on the Balance Sheet. The specific changes were all on the “Bal Sht” worksheet, as follows: • row 14 “Other current assets” … identified 465 and 574 (2020 and 2019 respectively) as nonoperating; the rest of this line item is assumed to be operating • row 23 “Other noncurrent assets” … identified 1,102 and 1,082 (2020 and 2019 respectively) as nonoperating; the rest of this line item is assumed to be operating • row 32 “Other current liabilities” … added 114 and 82 (2020 and 2019 respectively) for derivative liabilities to the nonoperating amounts already listed for lease liabilities; the rest of this line item is assumed to be operating • row 41 “Other noncurrent liabilities” … added 718 and 1,024 (2020 and 2019 respectively) for derivative liabilities to the nonoperating amounts already listed for lease liabilities; the rest of this line item is assumed to be operating In this Analysis Case, you will analyze 2019 financial reporting for Pfizer, Inc. You will use this information, and some additional values provided to estimate Pfizer’s equity value per share. You will use three of the types of valuation models we are discussing in modules 13, 14 and 15: discounted cash flow (DCF), residual operating income (ROPI), and market-based valuation. Pretend you are an analyst assigned to investigate Pfizer’s recent financial performance. Your boss has asked you to prepare several estimates of Pfizer’s equity value per share, then analyze them and conclude with what you feel Pfizer’s current stock value should be. The “Pfizer Analysis Case.xlsx” spreadsheet provides the following worksheets with data for Pfizer. Note: there is more information provided than you will need to complete your analysis. • DCF – worksheet for you to complete your discounted cash flow-based valuation • ROPI – worksheet for you to complete your residual operating income-based valuation • Market – worksheet for you to complete your market-based valuation • Comparables – Key financial data for PFE and 12 other companies that other analysts have used as comparables for market comparison (note: there is additional detail available in the “Pharma Comparables.xlsx” spreadsheet, also published with the assignment) • Bal Sht – PFE’s 2019 Balance Sheet data • Inc St – PFE’s 2019 Income Statement data • Comp Inc – PFE’s 2019 Statement of Comprehensive Loss data • Equity – PFE’s 2019 Statement of Stockholders’ Equity data • Cash Flows – PFE’s 2019 Statement of Cash Flow data In the additional “Pharma Comparables.xlsx” spreadsheet, I have also provided some additional detail, including some historical ratios for Pfizer, some competitors, and the pharmaceutical industry. Required: a) Using Pfizer’s 2019 annual financial statement data from the provided Excel workbook, perform the following analyses for Pfizer. • On the [Inc St] worksheet, for 2019 only: In the yellow-colored column E, enter “N” on the rows of nonoperating line items to break out the Operating and Non-Operating columns, as we did in the module 3 activity for Netflix. Acct 5401 Pfizer Analysis Case Page 2 of 4 Pfizer doesn’t present an “Operating Income” line item and includes interest expense and interest income in other line items. On spreadsheet row 16, I have already identified the nonoperating part of “Other (income)/deductions—net” based on the information in disclosure note 4. In spreadsheet row 18 – “Provision/(benefit) for taxes on income”), cells K18 and L18, enter the amount of the “tax shield” on nonoperating results that will be used to calculate NOPAT. The formulas in cells H18 and I18 will add these to the income tax in cells B18 and C18 to complete the Operating columns. Use a marginal income tax rate of 22% for this calculation. Note: if you do not use an Excel formula, type in your calculation as text in a nearby cell for possible partial credit. Note: the NOPAT value you calculate here for 2019 will be used (via Excel formula) on the [DCF], [ROPI], and [Comparables] sheets. • On the [Bal Sht] worksheet, for both years 2019 and 2018: In the yellow-colored column E, enter “N” on the rows of nonoperating line items to break out the Operating and Non-Operating columns. I have already provided values for the nonoperating lease-related interest liabilities on spreadsheet rows 32 and 41. In cells G48 and H48, calculate Net Operating Assets and, in cells J48 and K48, calculate Net Non-Operating Obligations. Hint: remember to check the way we learned to make sure your NOA and NNO values are consistent with another value on the Balance Sheet. Note: if you do not use an Excel formula, type in your calculation as text in a nearby cell for possible partial credit. Note: the NOA value you calculate here for 2019 will be used (via Excel formula) on the [DCF], [ROPI], and [Comparables] sheets. b) Once you have completed the calculations in part 1) above, perform the following analyses for Pfizer: Note: In the DCF and ROPI worksheets I entered starter values for horizon and terminal growth rates on row 5, based on 2019 analyst projections. I also entered starter formulas for NOPM and NOAT in rows 9 & 10 for columns C-I; the formulas I entered simply copied the column B values to the other years, making this a “parsimonious model” to begin with. I encourage you to adjust these values and formulas to make them more realistic in your mind, but be sure to explain the thoughts behind your changes in your part c) memo. • Using the [DCF] worksheet, prepare a Discounted Cash Flow estimate of Pfizer’s equity value per share (stock value) … if you use Excel formulas to do the calculations, you will save a lot of effort doing the sensitivity analyses below In the tables provided at the bottom (rows 37-51), conduct sensitivity analyses for changes in WACC & Terminal Growth Rate, and for changes in NOPM & NOAT (Hint: see the information at the end of this document about using Excel’s Data Table function). • Using the [ROPI] worksheet, prepare a Residual Operating Income estimate of Pfizer’s equity value per share (stock value) In the tables provided at the bottom (rows 37-51), conduct sensitivity analyses for changes in WACC & Terminal Growth Rate, and for changes in NOPM & NOAT (Hint: see the information at the end of this document about using Excel’s Data Table function). Hint: the preliminary model settings I provided are in a “steady state” so the DCF and ROPI models should yield (within rounding) the same result—notice this is no longer true when you adjust the revenue growth rate, NOPM and NOAT assumptions. • Prepare Market-based estimates of Pfizer’s equity value per share, as follows: Using the data in the “Comparables” worksheet (considering measures of profitability, growth and risk), select 3 other companies to use as comparables for each of the analyses (note: additional detail is available in the “Pharma Comparables.xlsx” spreadsheet, also published with the assignment) Acct 5401 Pfizer Analysis Case Page 3 of 4 Using the [Market] worksheet, prepare the following market-based estimates of Pfizer’s equity value per share 1. Book Equity-Based … sometimes called Price-to-Book (PB) 2. Income-Based … sometimes called Price-to-Earnings (PE) 3. NOA-Based 4. NOPAT-Based For each approach, (rows 2, 12, 22, 34) enter the ticker symbol for the 3 comparable companies (column A of the [Comparables] sheet), and (3 rows below) adjust the weightings of these 3 comparable companies from the 33.33% starter values I used. As an example, I illustrated and commented on my choices for various MarineMax comparables in the published solution for activity 15. c) Write a business memo to your boss (make up a name), discussing the topics below. You should reference in your memo the completed spreadsheet described above as an attachment. 1) Summarize the results of your six valuation estimates, including a brief explanation of the key values that form the basis of each estimate. Explain why you did or did not adjust the preliminary revenue growth rates, NOPM and NOAT to give what you feel is a more realistic estimate. 2) For the DCF and ROPI estimates, briefly discuss the sensitivity analysis and what conclusions you draw from it in relation to your estimate. 3) For each of the market-based estimates, explain why you selected the three comparable companies and relative weightings, including the factors upon which you most relied, and the extent to which each was or was not strongly comparable (e.g., “Microsoft is also in the technology industry, but its overall size is considerably greater”). 4) Contrast your valuations with Pfizer’s current market price ($34.37 on Nov. 2, 2020), and give some reasons why the overall market (as reflected in the stock price) might value it differently. Identify the stock value you would estimate based on your analysis, and explain your rationale for your chosen value. Your memo must be in appropriate business-memo format, no more than 2 pages long, single-spaced, with 1-inch margins, using 11 or 12-point easily legible font. This is another opportunity to impress your boss with your understanding and appreciation of the issues involved, and also to impress her with your ability to write a concise, well-organized, clearly communicated analysis and recommendation. Using Excel’s Data Table function to automate Sensitivity Analyses Excel contains a function that is designed to automatically prepare a sensitivity analysis table. It is called the Data Table function. To find it in Excel go to the “Data” menu, find the “Forecast” section, choose the “What-If Analysis” drop-down and select “Data Table.” Our textbook (pg 13-13 & 13-14)