Use excel

Vertex Biotech is contemplating a purchase of BioB, a European biotech firm. BioB has the following balance sheet and market information available: ($ thousands) Current Assets 40,000 Accts Payable 10,000 Long term Debt 50,000 Fixed Assets 85,000 Common Equity 65,000 Common stock ($1 par) 1,500 Paid in Capital 4,500 Retained earnings 59,000 Total Assets 125,000 Total Liab & Equity 125,000 Current Market Rates: Current Market Prices & Other Info: 1 yr Tbill rate 1.00% BioB Debt YTM 9.00% 10 yr Tbond Rate 3.00% Vertex Debt YTM 7.00% Market Risk Premium 5.50% Vertex Yahoo Beta 1.30 Tax rate 27% BioB Yahoo Beta 1.85 BioB stock price $25.00 Vertex stock price $55.00 The current liabilities consist entirely of accounts payable and accruals. BioB bonds are selling at 100% of par and the firm has 1.5 million shares of equity stock outstanding. The company believes a D/E ratio of 1.25 is more appropriate for its target debt level. Show all work on your Excel worksheet for the following questions: Specify the following estimates needed to develop the WACC for the acquisition: 1. The after tax cost of debt. 2. Specify the beta that should be used in the discount rate estimate. 3. Estimate the cost of equity. 4. Determine the weights for the WACC. 5. Provide a summary table that shows the inputs into the WACC and resulting estimate of the WACC