You are the financial manager of Pfizer Inc. (symbol: PFE). You are considering the purchase of a biopharmaceutical company on the west coast. PFE will benefit from not only receiving the net cash flows generated by this company, but also from synergies produced by having the companies combine forces. You want to figure out the maximum you are willing to pay for this pharmaceutical company. PFE has determined that the hypothetical cash flows generated by this purchase would be of similar riskiness to its own total asset cash flows. Therefore, PFE will use its asset expected return to discount the future net cash flows it expects to receive from this target company. Your challenge is to calculate PFEs asset expected return, which will then be used as the target pharmaceutical companys discount rate. a) First, calculate PFEs 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: = ( 1)1, where is the return in month t, and 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 ( ) 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 excess returns (your Y variable) on S&P500 excess returns (your X variable). You can find Regression in Microsoft Excel in Data Analysis under the Data tab.1 If the X-variable coefficient is positive and statistically significant (t-statistic greater than 1.95), then we conclude (with 95% confidence) that if S&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 PFEs market values of debt and equity. Go back to the PFE page in Yahoo! Finance. The market value of equity is also known as market capitalization and can be found on the Summary page. 1 If Data Analysis does not appear, do the following: click File, then Options, then Add-Ins, then click the Go button. Check the Analysis ToolPak box and click OK. Data Analysis should then appear in the Data tab. To find the market value of debt, click on Financials and then Balance Sheet to obtain PFEs Total Liabilities from the 12/31/2018 column of the balance sheet. Keep in mind that all financial statement items are reports in thousands of dollars, so you will need to add three zeros to the reported number. Because PFEs debt is fairly stable, it is safe to assume that the book value of debt equals the market value of debt. Because PFEs debt is considered very low risk, we will assume PFEs debt beta equals 0.05. Report 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 your workings. d) Using the CAPM, calculate and report the expected return for PFEs assets. Assume a market risk premium of 5.0% and a risk-free rate of 2.23% (the approximate yield on 30-year U.S. treasury securities). Show your workings. You expect this acquisition to generate its first cash flow next year, and that the cash flow will equal 0.50 percent of your firms cash flows provided by operating 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 acquisition will grow by 0.40 percent per year thereafter, and that you will receive the cash flows in perpetuity. e) What is the maximum you would be willing to pay for this pharmaceutical company? That is, what is the fair value of this company, according to your calculations?