Question

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 PFE’s asset 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: ???? = (???? − ????−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 PFE’s 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 PFE’s “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 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. 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 PFE’s 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 firm’s 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?

Answer 1

PFE's Equity Beta value is :0.61

Equity Beta	0.6114
Risk Free Rate	0.19%
P Value	0.0003
T stat Value	3.8969
X Variable	0.6112

The Benchmark S&P 500 is the Significant predictor of PFE as the P value is less than 0.05 and T stat Value is greater than 1.95. please find the below tables for PFE and S&P 500 monthly returns and other regression values.

Date	PFE	Returns PFE	S&P 500	Returns S&P 500	Excess Retruns
01-08-2014	23.62	0.00%	2003.37	0.00%	0.00%
01-09-2014	23.77	0.61%	1972.29	-1.55%	0.43%
01-10-2014	24.07	1.29%	2018.05	2.32%	1.10%
01-11-2014	25.03	4.01%	2067.56	2.45%	3.82%
01-12-2014	25.25	0.87%	2058.90	-0.42%	0.68%
01-01-2015	25.33	0.32%	1994.99	-3.10%	0.14%
01-02-2015	27.82	9.82%	2104.50	5.49%	9.64%
01-03-2015	28.45	2.26%	2067.89	-1.74%	2.08%
01-04-2015	27.75	-2.47%	2085.51	0.85%	-2.66%
01-05-2015	28.42	2.42%	2107.39	1.05%	2.23%
01-06-2015	27.65	-2.71%	2063.11	-2.10%	-2.90%
01-07-2015	29.73	7.55%	2103.84	1.97%	7.36%
01-08-2015	27.43	-7.76%	1972.18	-6.26%	-7.95%
01-09-2015	26.10	-4.82%	1920.03	-2.64%	-5.01%
01-10-2015	28.10	7.67%	2079.36	8.30%	7.49%
01-11-2015	27.23	-3.10%	2080.41	0.05%	-3.29%
01-12-2015	27.04	-0.70%	2043.94	-1.75%	-0.89%
01-01-2016	25.54	-5.55%	1940.24	-5.07%	-5.73%
01-02-2016	24.86	-2.69%	1932.23	-0.41%	-2.88%
01-03-2016	25.08	0.90%	2059.74	6.60%	0.72%
01-04-2016	27.68	10.36%	2065.30	0.27%	10.17%
01-05-2016	29.36	6.08%	2096.95	1.53%	5.90%
01-06-2016	30.06	2.38%	2098.86	0.09%	2.19%
01-07-2016	31.49	4.77%	2173.60	3.56%	4.59%
01-08-2016	29.71	-5.67%	2170.95	-0.12%	-5.85%
01-09-2016	29.16	-1.86%	2168.27	-0.12%	-2.05%
01-10-2016	27.30	-6.38%	2126.15	-1.94%	-6.56%
01-11-2016	27.67	1.36%	2198.81	3.42%	1.17%
01-12-2016	27.56	-0.39%	2238.83	1.82%	-0.57%
01-01-2017	27.59	0.09%	2278.87	1.79%	-0.09%
01-02-2017	29.66	7.53%	2363.64	3.72%	7.35%
01-03-2017	30.05	1.29%	2362.72	-0.04%	1.10%
01-04-2017	29.79	-0.85%	2384.20	0.91%	-1.03%
01-05-2017	28.68	-3.74%	2411.80	1.16%	-3.93%
01-06-2017	29.79	3.87%	2423.41	0.48%	3.69%
01-07-2017	29.40	-1.28%	2470.30	1.93%	-1.47%
01-08-2017	30.08	2.29%	2471.65	0.05%	2.11%
01-09-2017	31.97	6.28%	2519.36	1.93%	6.09%
01-10-2017	31.39	-1.79%	2575.26	2.22%	-1.98%
01-11-2017	32.47	3.42%	2584.84	0.37%	3.24%
01-12-2017	32.73	0.80%	2673.61	3.43%	0.62%
01-01-2018	33.47	2.26%	2823.81	5.62%	2.08%
01-02-2018	32.81	-1.97%	2713.83	-3.89%	-2.16%
01-03-2018	32.37	-1.35%	2640.87	-2.69%	-1.54%
01-04-2018	33.39	3.16%	2648.05	0.27%	2.97%
01-05-2018	32.77	-1.86%	2705.27	2.16%	-2.04%
01-06-2018	33.41	1.96%	2718.37	0.48%	1.77%
01-07-2018	36.77	10.06%	2816.29	3.60%	9.87%
01-08-2018	38.23	3.98%	2901.52	3.03%	3.80%
01-09-2018	40.93	7.05%	2913.98	0.43%	6.86%
01-10-2018	39.99	-2.29%	2711.74	-6.94%	-2.48%
01-11-2018	42.93	7.36%	2760.17	1.79%	7.18%
01-12-2018	40.85	-4.85%	2506.85	-9.18%	-5.04%
01-01-2019	39.73	-2.75%	2704.10	7.87%	-2.93%
01-02-2019	40.92	3.01%	2784.49	2.97%	2.83%
01-03-2019	40.09	-2.03%	2834.40	1.79%	-2.22%
01-04-2019	38.34	-4.38%	2945.83	3.93%	-4.57%
01-05-2019	39.20	2.24%	2752.06	-6.58%	2.06%
01-06-2019	41.26	5.26%	2941.76	6.89%	5.07%
01-07-2019	36.99	-10.34%	2980.38	1.31%	-10.53%
01-08-2019	33.86	-8.47%	2926.46	-1.81%	-8.66%

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.45
R Square	0.20
Adjusted R Square	0.19
Standard Error	0.04
Observations	61
ANOVA
	df	SS	MS	F	Significance F
Regression	1	0.0264	0.0264	15.1858	0.0003
Residual	59	0.1025	0.0017
Total	60	0.1288
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	0.000979	0.005442	0.179855	0.857883	-0.009910	0.011868	-0.009910	0.011868
X Variable 1	0.611249	0.156855	3.896898	0.000251	0.297382	0.925116	0.297382	0.925116