are the financial manager of Pfizer Inc. (symbol: PFE). You are considering the purchase of a pharmaceutical company on the west coast. PFE will benefit from not only receiving the net cash flows erated by this company, but also from synergies produced by having the companies combine forces. I want to figure out the maximum you are willing to pay for this pharmaceutical company. has determined that the hypothetical cash flows generated by this purchase would be of similar iness to its own total asset cash flows. Therefore, PFE will use its asset expected return to discount the ure net cash flows it expects to receive from this target company. Your challenge is to calculate PFE's et expected return, which will then be used as the target pharmaceutical company's discount rate. a) First, calculate PFE's equity beta. Go to Yahoo! Finance and click on "Historical Data" for PFE. Click on "Historical Prices" and download monthly PFE price data from August. 1, 2014 to August 31, 2019. Do the same for the S\&P500 (symbol: ^GSPC), our underlying proxy for the market portfolio. Next, calculate monthly returns for PFE and the S\&P500 using the equation: rt= (PtPt1)/Pt1, where rt is the return in month t, and Pt is the adjusted closing price in month t. When calculating returns, make sure your data are sorted so that time is in ascending order, not the default descending order. Convert all of your returns into excess returns (rtrf) by subtracting the monthly risk-free rate, which we will assume as 0.0223/12. You will not have a return observation for the first month of your time series. , where rt is the return in month t, and Pt is the adjusted closing price in month t. When calculating returns, make sure your data are sorted so that time is in ascending order, not the default descending order. Convert all of your returns into excess returns (rtrf) by subtracting the monthly risk-free rate, which we will assume as 0.0223/12. You will not have a return observation for the first month of your time series. is the return in month t, and Pt is the adjusted closing price in month t. When calculating returns, make sure your data are sorted so that time is in ascending order, not the default descending order. Convert all of your returns into excess returns (rtrf) by subtracting the monthly risk-free rate, which we will assume as 0.0223/12. You will not have a return observation for the first month of your time series. is the adjusted closing price in month t. When calculating returns, make sure your data are sorted so that time is in ascending order, not the default descending order. Convert all of your returns into excess returns (rtrf) by subtracting the monthly risk-free rate, which we will assume as 0.0223/12. You will not have a return observation for the first month of your time series. j by subtracting the monthly risk free rate, which we will assume as 0.0223/12. You will not have a return observation for the first month of your time series. Then, run a linear regression of PFE acess returns fyour Y variable) on S\&PS00 excess returns (your X variable), You can find "Regression" in Microsoft Ercel in "Data Analysis" under the "Data" tab. If the X-variable coefficient is positive and statisticaly significant (t-statistic greater than 1.95). then we conclude (with 95% confidence) that if SS.P500 returns are 1 percent higher (lower), then PFE returns are (Coefficient multiplied by 1 percent) higher (lower). Report the X-variable coefficient along with its t-statistic. The X-variable coefficient is the equity beta for PFE. This was derived by fitting data to the CAPM. b) Next, we need PFE's market values of debt and equity. Go back to the PFE page in Yahool Finance. The maket value of equity is also known as "market capitalization" and can be found on the "Summary" page. To find the market value of debt, click on "Financials" and then "Balance Sheet" to obtain PFE's "Total Liablities" from the 12/31/2018 column of the balance sheet. Keep in mind that all financial statement items are reports in thousands of dolars, so you will need to add three zeros to the reported number. Because PFE's debt is fairly stable, it is safe to assume that the book value of debt equals the market value of debt. Because PFE's debt is considered very low risk, we will assume PFE's debt beta equals 0.05 . Peport the market value of equity and the market value of debt for PFE. c) Using the information from parts (a) and (b), calculate the asset beta for PFE. Show vour workings. d) Using the CAPM, colculate and report the expected return for PFE's assets. Assume a market risk premium of 5.0% and a risk-free rate of 2.23% (the approaimute yeid on 30 -year U.S. treasury securities). Show your workines. You expect this acquisition to generate its first cash flow next year, and that the cash flow will equal 0.50 percent of your firm's cash flows provided by operatine activities from the 12/31/2018 column of the cash flow statement (the cash flow statement can be found by clicking on "Financials" and then "Cash Flow"; the numbers on this statement are also reported in thousands of dollars). Forecasts inform you that the cash flows generated by this acouisition will grow by 0.40 percent per year thereafter, and that you will receive the cash flows in perpetuity. I If "Oata Analvis" does not apoear, do the followint click "File", then "Options", then "Add-ins", then click the "Go" buttoh. Check the "Analyis Foolfax" bor and click OK. "Duta Analvis" should then appear in the "Data" tab. e) What is the maximum you would be wiling to pay for this pharmaceutical company? That is, what is the fair value of this company, according to your calculations