1 by subtracting the monthly risk-free rate, which we will assume as 0.0223/22. Vou will not have a retum observation for the first month of your time series. Then, run a linear regression of PFE excess returns (rour Y variable) on $ PSO0 eacess retums (your X variable). You can find "Regression" in Mcrosoft Encel in "Data Lnabpes" under the "Daka' tab. 1 If the X-variable coefficient is positive and statistically significant it-statistic greater than 1959 then we condude (with 95\% confidence) that if SEPS60 returns are 1 percent hicher (lowes! then PFE returns are (Coefficient multiplied by 1 percent] higher (1owe). Report the X-variable coefficient along with is s-statiotic. The X-variable coefficent is the eoaty beta for PFE. This was dethed by fitsing data to the CAPM. b) Next, we need PFE's market values of debt and equity. Go back to the PFE paper in Vhool Finance. The maket valuer of equity is abo known as "market capitalisation" and can be found on the "summary page. To find the market value of debt, dick on "Financiats" and then "Balance Sheer" to obtain FFE's 'Total Labilitier' from the 12/31/2018 column of the balance sheet. Keep in mind that all firanclal statement items are reports in thousands of dollan, so you will need to add three zeros to the reported number. Because PFI's debt is fairly stable, it is sofe to assume that the book value of dete equals the market value of debe. Because PI's deter is concidered very low riak we will assume PFE's deta beta equals 0.05 . Report the market value of equity and the market value of debt for Pf: c) Uwing the information from parts (a) and (b), calculate the anset beta for Fe, Show pour wonung: d) Using the CAPM, caiculate and report the expected retum for PFE's assets. Assume a marier na securtiest, Show your working. Yov eapect this acoulsition to generate its first canh flow neat veas, and that the cosh flow wall equal 0 so percent of your firm's canb flows provided by operating activities from the 12/31/2013 column of the cash flow statement (the cash flow statement can be found by clidine on Francias" and then "Cohh Fow" the numbers on this statement are also reported in thousands of dollani. Forecasts inform you that the cash flows generated by this acquisition will erow by 0.40 percest per year thereathes, and that you will receive the cash lows in perpetuity. e) What is the maximum you would be willene to pay for this pharmaceutical campany? Thut is, what is the fair value of this company, accoerdine to your calculations? estion 1 1 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. uant 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 defoult 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. 1 by subtracting the monthly risk-free rate, which we will assume as 0.0223/22. Vou will not have a retum observation for the first month of your time series. Then, run a linear regression of PFE excess returns (rour Y variable) on $ PSO0 eacess retums (your X variable). You can find "Regression" in Mcrosoft Encel in "Data Lnabpes" under the "Daka' tab. 1 If the X-variable coefficient is positive and statistically significant it-statistic greater than 1959 then we condude (with 95\% confidence) that if SEPS60 returns are 1 percent hicher (lowes! then PFE returns are (Coefficient multiplied by 1 percent] higher (1owe). Report the X-variable coefficient along with is s-statiotic. The X-variable coefficent is the eoaty beta for PFE. This was dethed by fitsing data to the CAPM. b) Next, we need PFE's market values of debt and equity. Go back to the PFE paper in Vhool Finance. The maket valuer of equity is abo known as "market capitalisation" and can be found on the "summary page. To find the market value of debt, dick on "Financiats" and then "Balance Sheer" to obtain FFE's 'Total Labilitier' from the 12/31/2018 column of the balance sheet. Keep in mind that all firanclal statement items are reports in thousands of dollan, so you will need to add three zeros to the reported number. Because PFI's debt is fairly stable, it is sofe to assume that the book value of dete equals the market value of debe. Because PI's deter is concidered very low riak we will assume PFE's deta beta equals 0.05 . Report the market value of equity and the market value of debt for Pf: c) Uwing the information from parts (a) and (b), calculate the anset beta for Fe, Show pour wonung: d) Using the CAPM, caiculate and report the expected retum for PFE's assets. Assume a marier na securtiest, Show your working. Yov eapect this acoulsition to generate its first canh flow neat veas, and that the cosh flow wall equal 0 so percent of your firm's canb flows provided by operating activities from the 12/31/2013 column of the cash flow statement (the cash flow statement can be found by clidine on Francias" and then "Cohh Fow" the numbers on this statement are also reported in thousands of dollani. Forecasts inform you that the cash flows generated by this acquisition will erow by 0.40 percest per year thereathes, and that you will receive the cash lows in perpetuity. e) What is the maximum you would be willene to pay for this pharmaceutical campany? Thut is, what is the fair value of this company, accoerdine to your calculations? estion 1 1 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. uant 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 defoult 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