Questions are attached. Please use excel formula's to solve problems.

Problem 11-49 Consider a portfolio consisting of the following three stocks: HEC Corp Green Midget AliveAndWell Portfolio Weight 0.25 0.35 0.40 Volatility 12% 25% 13% The volatility of the market portfolio is 10% and it has an expected return of 8%. The ris a. Compute the beta and expected return of each stock. b. Using your answer from part a, calculate the expected return of the portfolio. c. What is the beta of the portfolio? d. Using your answer from part c, calculate the expected return of the portfolio and v to part b. Volatility of market portfolio Market expected return Risk-free rate 10% 8% 3% a. Compute the beta and expected return of each stock. Stock HEC Corp Green Midget AliveAndWell Beta E[R] b. Using your answer from part a, calculate the expected return of the portfolio. Expected return c. What is the beta of the portfolio? Beta d. Using your answer from part c, calculate the expected return of the portfolio and v to part b. Expected return Match? Requirements 1. In cell D24, by using relative and absolute cell references, calculate the beta for H 2. To calculate the betas for Green Midget and AliveAndWell, copy cell D24 and pas 3. In cell E24, by using relative and absolute cell references, calculate the expected r 4. To calculate the expected returns for Green Midget and AliveAndWell, copy cell E E25:E26 (1 pt.). 5. Calculate the expected return of the portfolio by using the function SUMPRODU function SUMPRODUCT and cell references, calculate the expected return of the 6. Calculate the beta of the portfolio by using the function SUMPRODUCT. In cell SUMPRODUCT and cell references, calculate the beta of the portfolio (1 pt.). 7. In cell D38, by using cell references, calculate the expected return of the portfolio 8. To verify that your answer for part (d) matches your answer for part (b), you need of the portfolio in part (d) with the expected return of the portfolio in part (b). In c pt.). e stocks: Correlation with Market Portfolio 0.4 0.6 0.5 as an expected return of 8%. The risk-free rate is 3%. stock. xpected return of the portfolio. xpected return of the portfolio and verify that it matches your answer stock. xpected return of the portfolio. xpected return of the portfolio and verify that it matches your answer l references, calculate the beta for HEC Corp (1 pt.). liveAndWell, copy cell D24 and paste it onto cells D25:D26 (1 pt.). references, calculate the expected return for HEC Corp (1 pt.). idget and AliveAndWell, copy cell E24 and paste it onto cells by using the function SUMPRODUCT. In cell D30, by using the , calculate the expected return of the portfolio (1 pt.). function SUMPRODUCT. In cell D34, by using the function e the beta of the portfolio (1 pt.). the expected return of the portfolio (1 pt.). s your answer for part (b), you need to compare the expected return eturn of the portfolio in part (b). In cell D39, type either Yes or No (1 Problem 12-4 Suppose all possible investment opportunities in the world are limited to the five stocks l below. What does the market portfolio consist of? Stock A B C D E Price/Share ($) 10 20 8 50 45 Shares Outstanding (millions) 10 12 3 1 20 Market value (million) Share of Market portfolio Stock A Stock B Stock C Stock D Stock E Total Requirements 1. In cell D17, by using cell references, calculate the market value of stock A (1 pt.). To calculate the market values of stocks B thru E copy cell D17 2. You will calculate the total market value of all the stocks in the market portfolio by pt.). 3. SUM. In cell D22, by using the function SUM and cell references, calculate the to In cell E17, by using absolute and relative cell references, calculate the share (perc 4. all the stocks in the market portfolio (1 pt.). in marketthe portfolio (1 pt.). Tothe calculate share (percentage) of stocks B thru E in the market portfolio, copy 5. it onto cells E18:E21 (1 pt.). are limited to the five stocks listed in the table market value of stock A (1 pt.). py cell D17 and paste it onto cells D18:D21 (1 ocks in the market portfolio by using the function cell references, calculate the total market value of ences, calculate the share (percentage) of stock A E in the market portfolio, copy cell E17 and paste Problem 13-20 Consider the following stocks, all of which will pay a liquidating dividend in a year and Stock A Stock B Stock C Stock D Market Capitalization ($ million) 800 750 950 900 Expected Liquidating Dividend ($ million) 1,000 1,000 1,000 1,000 Beta 0.77 1.46 1.25 1.07 a. Calculate the expected return of each stock. b. What is the sign of the correlation between the expected return and market capitali a. Calculate the expected return of each stock. Expected return Stock A Expected return Stock B Expected return Stock C Expected return Stock D b. What is the sign of the correlation between the expected return and market capitali Correlation coefficient The sign of the correlation is Requirements 1. In cell E20, by using cell references, calculate the expected return of stock A (1 pt 2. To calculate the expected returns of stocks B thru D, copy cell E20 and paste it on 3. You will calculate the correlation between the expected return and the market capi using the function CORREL. In cell E27, by using the function CORREL and ce the correlation between the expected return and the market capitalization of the sto 4. To discern the sign of the correlation between the expected return and the market c stocks, you need to compare whether the correlation coefficient is greater or less th either Positive or Negative (1 pt.). g dividend in a year and nothing in the interim: eturn and market capitalization of the stocks? eturn and market capitalization of the stocks? ed return of stock A (1 pt.). y cell E20 and paste it onto cells E21:E23 (1 pt.). eturn and the market capitalization of the stocks by unction CORREL and cell references, calculate et capitalization of the stocks (1 pt.). d return and the market capitalization of the icient is greater or less than zero. In cell E28, type Problem 14-21 Yerba Industries is an all-equity firm whose stock has a beta of 1.2 and an expected retur 12.5%. Suppose it issues new risk-free debt with a 5% yield and repurchases 40% of its s Assume perfect capital markets. a. What is the beta of Yerba stock after this transaction? b. What is the expected return of Yerba stock after this transaction? Suppose that prior to this transaction, Yerba expected earnings per share this coming yea with a forward P/E ratio (that is, the share price divided by the expected earnings for the year) of 14. c. What is Yerba's expected earnings per share after this transaction? Does this chang shareholders? Explain. d. What is Yerba's forward P/E ratio after this transaction? Does the P/E ratio go up o Unlevered beta Expected return Risk-free rate New debt level 1.20 12.50% 5.00% 40.00% New Debt/Equity Market risk premium a. What is the beta of Yerba stock after this transaction? New beta b. What is the expected return of Yerba stock after this transaction? New expected return on equity Suppose that prior to this transaction, Yerba expected earnings per share this coming yea with a forward P/E ratio (that is, the share price divided by the expected earnings for the year) of 14. c. What is Yerba's expected earnings per share after this transaction? Does this chang shareholders? Explain. Old EPS Forward P/E $1.50 14 Assumed shares 100 Price per share Old equity New debt New earnings New equity New shares New EPS d. What is Yerba's forward P/E ratio after this transaction? Does the P/E ratio go up o New P/E Price/Earnings ratio Requirements 1. In cell D16, by using cell references, calculate the new debt/equity ratio (1 pt.). 2. In cell D17, by using cell references, calculate the market risk premium (1 pt.). 3. In cell D21, by using cell references, calculate the new beta (1 pt.). 4. In cell D25, by using cell references, calculate the new expected return on equity ( 5. In cell D34, by using cell references, calculate the price per share (1 pt.). 6. In cell D35, by using cell references, calculate the old equity (1 pt.). 7. In cell D36, by using cell references, calculate the new debt (1 pt.). 8. In cell D37, by using cell references, calculate the new earnings (1 pt.). 9. In cell D38, by using cell references, calculate the new equity (1 pt.). 10. In cell D39, by using cell references, calculate the new number of shares (1 pt.). 11. In cell D40, by using cell references, calculate the new earnings per share (1 pt.). 12. In cell D44, by using cell references, calculate the new price/earnings ratio (1 pt.). 13. To answer whether the price/earnings ratio rises or falls, you need to compare the price/earnings ratio to the forward price/earnings ratio. In cell D45, type either Fa the same, or Rises (1 pt.). has a beta of 1.2 and an expected return of 5% yield and repurchases 40% of its stock. nsaction? fter this transaction? ed earnings per share this coming year of $1.50, vided by the expected earnings for the coming after this transaction? Does this change benefit transaction? Does the P/E ratio go up or down? nsaction? fter this transaction? ed earnings per share this coming year of $1.50, vided by the expected earnings for the coming after this transaction? Does this change benefit transaction? Does the P/E ratio go up or down? ate the new debt/equity ratio (1 pt.). ate the market risk premium (1 pt.). ate the new beta (1 pt.). ate the new expected return on equity (1 pt.). ate the price per share (1 pt.). ate the old equity (1 pt.). ate the new debt (1 pt.). ate the new earnings (1 pt.). ate the new equity (1 pt.). ate the new number of shares (1 pt.). ate the new earnings per share (1 pt.). ate the new price/earnings ratio (1 pt.). ises or falls, you need to compare the new nings ratio. In cell D45, type either Falls, Stays Problem 15-7 Ten years have passed since Arnell issued $10 million in perpetual interest-only de 6% annual coupon. Tax rates have remained the same at 35% but interest rates hav Arnell's current cost of debt capital is 4%. a. so What is Arnell's annual interest tax shield? b. What is the present value of the interest tax shield today? Interest rate Tax rate Amount of debt Discount rate 6.00% 35% $10,000,000 4.00% a. What is Arnell's annual interest tax shield? Interest payment Annual interest tax shield b. What is the present value of the interest tax shield today? PV(interest tax shield) Requirements 1. In cell D15, by using cell references, calculate the annual interest payment (1 pt.). 2. In cell D16, by using cell references, calculate the annual interest tax shield (1 pt.) In cell D20, by using cell references, calculate the present value of the annual inte 3. shield (1 pt.). erpetual interest-only debt with a % but interest rates have dropped nterest payment (1 pt.). nterest tax shield (1 pt.). value of the annual interest tax Problem 16-19 Sarvon Systems has a debt-equity ratio of 1.2, an equity beta of 2.0, and a debt beta of 0. currently evaluating the following projects, none of which would change the firm's volat Project Investment (million) NPV (million) A B $100 $20 C $50 $6 $85 $10 a. Which project will equity holders agree to fund? b. What is the cost to the firm of the debt overhang? D/E Ratio b Equity b Debt 1.20 2.00 0.30 a. Which project will equity holders agree to fund? Cutoff Project Profitability index Equity funded? A B C b. What is the cost to the firm of the debt overhang? Opportunity cost (million) Requirements 1. In cell D19, by using cell references, find the ratio between the debt beta and equi equity ratio (1 pt.). 2. In cell D22, by using cell references, calculate the profitability index of project A ( cells E22:H22 (1 pt.). 3. To decide which project would be eligible for equity funding, you need to compar the cutoff measure by using the function IF. In cell D23, by using the function IF compare the profitability index of project A to the cutoff measure; if true, type "Ye D23 and paste it onto cells E23:H23 (1 pt.). 4. In cell E27, by using cell references, calculate the cost to the firm of the debt over .0, and a debt beta of 0.30. It is change the firm's volatility: D E $30 $15 D $75 $18 E n the debt beta and equity beta and multiply it by the debt-to- ility index of project A (1 pt.). Copy cell D22 and paste it onto ng, you need to compare the profitability index of each project to y using the function IF and absolute and relative cell references, easure; if true, type "Yes"; if false, type "No" (1 pt.). Copy cell he firm of the debt overhang (1 pt.). Problem 22-2 You are trying to decide whether to make an investment of $500 million in a new technol Gobstoppers. There is a 60% chance that the market for these candies will produce profit 20% chance the market will produce profits of $50 million, and a 20% chance that there market will become clear one year from now. Currently, the cost of capital of the project chance that the cost of capital will drop to 9% in a year and stay at that level forever, and 11% forever. Movements in the cost of capital are unrelated to the size of the candy mark that shows the choices you have to make to see if you make the investment either today o Investment (million) $500.00 Expected Value Decision tree ($ million): Profits (million) $100.00 $50.00 $0.00 Requirements To calculate the expected return on the investment opportunity, we need to constru probabilities. 1. For the first possible outcome, in cell H15, by using cell references, input the first G15, by using cell references, input the probability that this outcome will occur (1 2. For the second possible outcome, in cell H21, by using cell references, input the se cell G21, by using cell references, input the probability that this outcome will occu 3. For the third possible outcome, in cell H27, by using cell references, input the thir G27, by using cell references, input the probability that this outcome will occur (1 4. Independent of the profits outcome, there are two possible costs of capital with the occurrence. For the first possible profit outcome, in cell J14, by using cell referenc first cost of capital will occur (1 pt.). 5. In cell J16, by using cell references, input the probability that the second cost of c 6. For the second possible profit outcome, in cell J20, by using cell references, input of capital will occur (1 pt.). 7. In cell J22, by using cell references, input the probability that the second cost of c 8. For the third possible profit outcome, in cell J26, by using cell references, input th of capital will occur (1 pt.). 9. In cell J28, by using cell references, input the probability that the second cost of c 10. For the first possible profit outcome, calculate the NPV of the investment opportun capital. In cell K13, by using cell references, calculate the NPV (1 pt.). 11. In cell K17, by using cell references, calculate the NPV (1 pt.). 12. For the second possible profit outcome, calculate the NPV of the investment oppor capital. In cell K19, by using cell references, calculate the NPV (1 pt.). 13. In cell K23, by using cell references, calculate the NPV (1 pt.). 14. For the third possible profit outcome, calculate the NPV of the investment opportu capital. In cell K25, by using cell references, calculate the NPV (1 pt.). 15. In cell K29, by using cell references, calculate the NPV (1 pt.). 16. Finally, in cell E21, by using cell references, calculate the expected value of the in 00 million in a new technology to produce Everlasting candies will produce profits of $100 million annually, a d a 20% chance that there will be no profits. The size of the ost of capital of the project is 11% per year. There is a 20% ay at that level forever, and a 80% chance that it will stay at the size of the candy market. Construct the decision tree e investment either today or one year from now. Probability 60% 20% 20% Cost of Capital Probability 11.00% 80% 9.00% 20% Firm Value rtunity, we need to construct the decision tree of outcome l references, input the first possible profit (1 pt.). In cell this outcome will occur (1 pt.). cell references, input the second possible profit (1 pt.). In that this outcome will occur (1 pt.). ll references, input the third possible profit (1 pt.). In cell this outcome will occur (1 pt.). ble costs of capital with their respective probabilities of J14, by using cell references, input the probability that the ty that the second cost of capital will occur (1 pt.). using cell references, input the probability that the first cost ty that the second cost of capital will occur (1 pt.). ng cell references, input the probability that the first cost ty that the second cost of capital will occur (1 pt.). of the investment opportunity for both possible costs of he NPV (1 pt.). (1 pt.). PV of the investment opportunity for both possible costs of he NPV (1 pt.). (1 pt.). V of the investment opportunity for both possible costs of he NPV (1 pt.). (1 pt.). he expected value of the investment opportunity (1 pt.). Problem 18-16 You are evaluating a project that requires an investment of $90 million today and provide of $115 million for sure one year from now. You decide to use 100% debt financing, that $90 million. The risk-free rate is 5% and the tax rate is 40%. Assume that the investment the end of the year, so without leverage you would owe taxes on the difference between t and the investment. a. Calculate the NPV of this investment opportunity using the APV method. b. Using your answer to part (a), calculate the WACC of the project. c. Verify that you get the same answer using the WACC method to calculate NPV. d. Finally, show that flow-to-equity also correctly gives the NPV of this investment o Investment today (million) Amount of debt (million) Cash flow in 1 year (million) Tax rate Risk-free rate $90.00 $90.00 $115.00 40% 5.00% a. Calculate the NPV of this investment opportunity using the APV method. Tax basis at year end (million) Taxes owed (million) FCF at year end (million) VU (million) Interest expense (million) Interest tax shield (million) PV of ITS (million) VL (million) NPV (million) b. Using your answer to part (a), calculate the WACC of the project. Debt-to-value ratio WACC c. Verify that you get the same answer using the WACC method to calculate NPV. VL (million) NPV (million) d. Finally, show that flow-to-equity also correctly gives the NPV of this investment o After-tax interest expense (million) FCFE (million) NPV (million) Requirements To calculate the project's NPV using the APV method, you need to calculate the un values of the project. 1. In cell D18, by using cell references, calculate the tax basis at year end (1 pt.). 2. In cell D19, by using cell references, calculate the taxes owed (1 pt.). 3. In cell D20, by using cell references, calculate the free cash flow at year end (1 pt 4. To calculate the project's unlevered value, calculate the present value of the free ca function PV. In cell D21, by using the function PV and cell references, calculate t the free cash flow (1 pt.). 5. In cell D22, by using cell references, calculate the interest expense (1 pt.). 6. In cell D23, by using cell references, calculate the interest tax shield (1 pt.). 7. To calculate the present value of the interest tax shield use the function PV. In cell function PV and cell references, calculate the present value of the interest tax shiel 8. In cell D25, by using cell references, calculate the project's levered value (1 pt.). 9. In cell D26, by using cell references, calculate the net present value using the APV 10. To calculate the WACC of the project using the answer from part (a), you will use WACC formula: r_wacc = r_Unlevered - (Debt/Value) * Marginal tax rate * r_ In cell D30, by using cell references, calculate the debt-to-value ratio (1 pt.). 11. In cell D31, by using cell references, calculate the WACC (1 pt.). 12. To verify that you get the same answer using the WACC method to calculate the N the project's levered value by using the function PV. In cell D35, by using the fun references, calculate the NPV (1 pt.). 13. In cell D36, by using cell references, calculate the NPV (1 pt.). 14. To show that the flow-to-equity approach also correctly gives the NPV of this inve you need to calculate the free cash flow-to-equity after calculating the after-tax int D40, by using cell references, calculate the after-tax interest expense (1 pt.). 15. In cell D41, by using cell references, calculate the free cash flow-to-equity (1 pt.). 16. To calculate the NPV of this investment opportunity, you need to calculate the pre cash flow-to-equity using the function PV. In cell D42, by using the function PV a calculate the NPV (1 pt.). ion today and provides a single cash flow % debt financing, that is, you will borrow me that the investment is fully depreciated at e difference between the project cash flow APV method. oject. d to calculate NPV. V of this investment opportunity. APV method. oject. d to calculate NPV. V of this investment opportunity. eed to calculate the unlevered and levered at year end (1 pt.). d (1 pt.). flow at year end (1 pt.). ent value of the free cash flow using the references, calculate the present value of xpense (1 pt.). x shield (1 pt.). e function PV. In cell D24, by using the of the interest tax shield (1 pt.). evered value (1 pt.). t value using the APV method (1 pt.). part (a), you will use the project-based Marginal tax rate * r_Debt. alue ratio (1 pt.). pt.). hod to calculate the NPV, first, calculate D35, by using the function PV and cell t.). s the NPV of this investment opportunity, lating the after-tax interest expense. In cell expense (1 pt.). flow-to-equity (1 pt.). ed to calculate the present value of the free sing the function PV and cell references, Problem 19-3 Assuming that Ideko's market share will increase by 0.5% per year, the required producti are shown below: Sales Data Market size (000 units) Market share Production volume Growth/Year 5% 0.5% 2005 10,000 10.00% 1,000 2006 10,500 10.50% 1,103 Ideko's production plant will require an expansion in 2010 (when production volume wil cost of this expansion will be $15.0 million. Assuming the financing of the expansion wil projected interest payments and the amount of the projected interest tax shields (assumin 6.8% and the corporate tax rate is 35%) through 2010. Ideko's balance sheet for 2005 is s Balance Sheet ($000) Assets Cash and Cash Equivalents Accounts Receivable Inventories Total Current Assets Property, Plant & Equipment Goodwill Total Assets Liabilities Accounts Payable Debt Total Liabilities Stockholders' Equity Total Liabilities & Stockholders' Equity Interest rate on loan Corporate tax rate Cost of the expansion ($000) Debt and Interest ($000) Outstanding debt Interest on the term loan, 6.8% Interest tax shield 6,164 18,493 6,165 30,822 49,500 72,332 152,654 4,654 100,000 104,654 48,000 152,654 6.80% 35% 15,000 2005 2006 Requirements 1. In cell E34, by using relative and absolute cell references, calculate the outstandin pt.). Copy cell E34 and paste it onto cells F34:I34 (1 pt.). 2. In cell J34, by using cell references, calculate the outstanding debt for the year 20 3. In cell E35, by using relative and absolute cell references, calculate the interest on 2005 (1 pt.). Copy cell E35 and paste it onto cells F35:J35 (1 pt.). Note that the in not be applied until the following year. 4. In cell E36, by using relative and absolute cell references, calculate the interest tax pt.). Copy cell E36 and paste it onto cells F36:J36 (1 pt.). he required production capacities for the following five years 2007 11,025 11.00% 1,213 2008 11,576 11.50% 1,331 2009 12,155 12.00% 1,459 2010 12,763 12.50% 1,595 duction volume will exceed the current level by 50%), and the of the expansion will be delayed accordingly, calculate the ax shields (assuming that the interest rate on the term loans is e sheet for 2005 is shown below: 2007 2008 2009 2010 ulate the outstanding debt for the year 2005 (1 debt for the year 2010 (1 pt.). ulate the interest on the term loan for the year pt.). Note that the interest on the new loan will ulate the interest tax shield for the year 2005 (1 Problem 11-49 Consider a portfolio consisting of the following three stocks: HEC Corp Green Midget AliveAndWell Portfolio Weight 0.25 0.35 0.40 Volatility 12% 25% 13% The volatility of the market portfolio is 10% and it has an expected return of 8%. The ris a. Compute the beta and expected return of each stock. b. Using your answer from part a, calculate the expected return of the portfolio. c. What is the beta of the portfolio? d. Using your answer from part c, calculate the expected return of the portfolio and v answer to part b. Volatility of market portfolio Market expected return Risk-free rate 10% 8% 3% a. Compute the beta and expected return of each stock. Stock HEC Corp Green Midget AliveAndWell Beta 0.4800 1.5000 0.6500 E[R] 5.40% 10.50% 6.25% b. Using your answer from part a, calculate the expected return of the portfolio. Expected return 7.53% c. What is the beta of the portfolio? Beta 0.9050 d. Using your answer from part c, calculate the expected return of the portfolio and v answer to part b. Expected return Match? 7.53% 100.00% Requirements 1. In cell D24, by using relative and absolute cell references, calculate the beta for H 2. To calculate the betas for Green Midget and AliveAndWell, copy cell D24 and pas 3. In cell E24, by using relative and absolute cell references, calculate the expected r 4. To calculate the expected returns for Green Midget and AliveAndWell, copy cell E E25:E26 (1 pt.). 5. Calculate the expected return of the portfolio by using the function SUMPRODU function SUMPRODUCT and cell references, calculate the expected return of the 6. Calculate the beta of the portfolio by using the function SUMPRODUCT. In cell SUMPRODUCT and cell references, calculate the beta of the portfolio (1 pt.). 7. In cell D38, by using cell references, calculate the expected return of the portfolio 8. To verify that your answer for part (d) matches your answer for part (b), you need of the portfolio in part (d) with the expected return of the portfolio in part (b). In c pt.). e stocks: Correlation with Market Portfolio 0.4 0.6 0.5 as an expected return of 8%. The risk-free rate is 3%. stock. xpected return of the portfolio. xpected return of the portfolio and verify that it matches your stock. xpected return of the portfolio. xpected return of the portfolio and verify that it matches your l references, calculate the beta for HEC Corp (1 pt.). liveAndWell, copy cell D24 and paste it onto cells D25:D26 (1 pt.). l references, calculate the expected return for HEC Corp (1 pt.). idget and AliveAndWell, copy cell E24 and paste it onto cells by using the function SUMPRODUCT. In cell D30, by using the , calculate the expected return of the portfolio (1 pt.). e function SUMPRODUCT. In cell D34, by using the function e the beta of the portfolio (1 pt.). the expected return of the portfolio (1 pt.). s your answer for part (b), you need to compare the expected return eturn of the portfolio in part (b). In cell D39, type either Yes or No (1 Problem 12-4 Suppose all possible investment opportunities in the world are limited to the five stocks l below. What does the market portfolio consist of? Stock A B C D E Price/Share ($) 10 20 8 50 45 Shares Outstanding (millions) 10 12 3 1 20 Market value (million) Share of Market portfolio Stock A Stock B Stock C Stock D Stock E Total Requirements 1. In cell D17, by using cell references, calculate the market value of stock A (1 pt.). To calculate the market values of stocks B thru E copy cell D17 2. You will calculate the total market value of all the stocks in the market portfolio by pt.). 3. SUM. In cell D22, by using the function SUM and cell references, calculate the to In cell E17, by using absolute and relative cell references, calculate the share (perc 4. all the stocks in the market portfolio (1 pt.). in the marketthe portfolio (1 pt.). To calculate share (percentage) of stocks B thru E in the market portfolio, copy 5. it onto cells E18:E21 (1 pt.). are limited to the five stocks listed in the table market value of stock A (1 pt.). py cell D17 and paste it onto cells D18:D21 (1 ocks in the market portfolio by using the function cell references, calculate the total market value of ences, calculate the share (percentage) of stock A E in the market portfolio, copy cell E17 and paste Problem 13-20 Consider the following stocks, all of which will pay a liquidating dividend in a year and Stock A Stock B Stock C Stock D Market Capitalization ($ million) 800 750 950 900 Expected Liquidating Dividend ($ million) 1,000 1,000 1,000 1,000 Beta 0.77 1.46 1.25 1.07 a. Calculate the expected return of each stock. b. What is the sign of the correlation between the expected return and market capitali a. Calculate the expected return of each stock. Expected return Stock A Expected return Stock B Expected return Stock C Expected return Stock D b. What is the sign of the correlation between the expected return and market capitali Correlation coefficient The sign of the correlation is Requirements 1. In cell E20, by using cell references, calculate the expected return of stock A (1 pt 2. To calculate the expected returns of stocks B thru D, copy cell E20 and paste it on 3. You will calculate the correlation between the expected return and the market capi using the function CORREL. In cell E27, by using the function CORREL and ce the correlation between the expected return and the market capitalization of the sto 4. To discern the sign of the correlation between the expected return and the market c stocks, you need to compare whether the correlation coefficient is greater or less th either Positive or Negative (1 pt.). g dividend in a year and nothing in the interim: eturn and market capitalization of the stocks? eturn and market capitalization of the stocks? ed return of stock A (1 pt.). y cell E20 and paste it onto cells E21:E23 (1 pt.). eturn and the market capitalization of the stocks by unction CORREL and cell references, calculate et capitalization of the stocks (1 pt.). d return and the market capitalization of the ficient is greater or less than zero. In cell E28, type Problem 14-21 Yerba Industries is an all-equity firm whose stock has a beta of 1.2 and an expected retur 12.5%. Suppose it issues new risk-free debt with a 5% yield and repurchases 40% of its s Assume perfect capital markets. a. What is the beta of Yerba stock after this transaction? b. What is the expected return of Yerba stock after this transaction? Suppose that prior to this transaction, Yerba expected earnings per share this coming year with a forward P/E ratio (that is, the share price divided by the expected earnings for the year) of 14. c. What is Yerba's expected earnings per share after this transaction? Does this chang shareholders? Explain. d. What is Yerba's forward P/E ratio after this transaction? Does the P/E ratio go up o Unlevered beta Expected return Risk-free rate New debt level 1.20 12.50% 5.00% 40.00% New Debt/Equity Market risk premium a. What is the beta of Yerba stock after this transaction? New beta b. What is the expected return of Yerba stock after this transaction? New expected return on equity Suppose that prior to this transaction, Yerba expected earnings per share this coming year with a forward P/E ratio (that is, the share price divided by the expected earnings for the year) of 14. c. What is Yerba's expected earnings per share after this transaction? Does this chang shareholders? Explain. Old EPS Forward P/E $1.50 14 Assumed shares 100 Price per share Old equity New debt New earnings New equity New shares New EPS d. What is Yerba's forward P/E ratio after this transaction? Does the P/E ratio go up o New P/E Price/Earnings ratio Requirements 1. In cell D16, by using cell references, calculate the new debt/equity ratio (1 pt.). 2. In cell D17, by using cell references, calculate the market risk premium (1 pt.). 3. In cell D21, by using cell references, calculate the new beta (1 pt.). 4. In cell D25, by using cell references, calculate the new expected return on equity ( 5. In cell D34, by using cell references, calculate the price per share (1 pt.). 6. In cell D35, by using cell references, calculate the old equity (1 pt.). 7. In cell D36, by using cell references, calculate the new debt (1 pt.). 8. In cell D37, by using cell references, calculate the new earnings (1 pt.). 9. In cell D38, by using cell references, calculate the new equity (1 pt.). 10. In cell D39, by using cell references, calculate the new number of shares (1 pt.). 11. In cell D40, by using cell references, calculate the new earnings per share (1 pt.). 12. In cell D44, by using cell references, calculate the new price/earnings ratio (1 pt.). 13. To answer whether the price/earnings ratio rises or falls, you need to compare the n price/earnings ratio to the forward price/earnings ratio. In cell D45, type either Fa the same, or Rises (1 pt.). has a beta of 1.2 and an expected return of 5% yield and repurchases 40% of its stock. nsaction? after this transaction? ted earnings per share this coming year of $1.50, vided by the expected earnings for the coming after this transaction? Does this change benefit transaction? Does the P/E ratio go up or down? nsaction? after this transaction? ted earnings per share this coming year of $1.50, vided by the expected earnings for the coming after this transaction? Does this change benefit transaction? Does the P/E ratio go up or down? ate the new debt/equity ratio (1 pt.). ate the market risk premium (1 pt.). ate the new beta (1 pt.). ate the new expected return on equity (1 pt.). ate the price per share (1 pt.). ate the old equity (1 pt.). ate the new debt (1 pt.). ate the new earnings (1 pt.). ate the new equity (1 pt.). ate the new number of shares (1 pt.). ate the new earnings per share (1 pt.). ate the new price/earnings ratio (1 pt.). ises or falls, you need to compare the new nings ratio. In cell D45, type either Falls, Stays Problem 15-7 Ten years have passed since Arnell issued $10 million in perpetual interest-only de 6% annual coupon. Tax rates have remained the same at 35% but interest rates hav Arnell's current cost of debt capital is 4%. a. so What is Arnell's annual interest tax shield? b. What is the present value of the interest tax shield today? Interest rate Tax rate Amount of debt Discount rate 6.00% 35% $10,000,000 4.00% a. What is Arnell's annual interest tax shield? Interest payment Annual interest tax shield b. What is the present value of the interest tax shield today? PV(interest tax shield) Requirements 1. In cell D15, by using cell references, calculate the annual interest payment (1 pt.). 2. In cell D16, by using cell references, calculate the annual interest tax shield (1 pt.) In cell D20, by using cell references, calculate the present value of the annual inter 3. shield (1 pt.). erpetual interest-only debt with a % but interest rates have dropped nterest payment (1 pt.). nterest tax shield (1 pt.). value of the annual interest tax Problem 16-19 Sarvon Systems has a debt-equity ratio of 1.2, an equity beta of 2.0, and a debt beta of 0. currently evaluating the following projects, none of which would change the firm's volat Project Investment (million) NPV (million) A B $100 $20 C $50 $6 $85 $10 a. Which project will equity holders agree to fund? b. What is the cost to the firm of the debt overhang? D/E Ratio b Equity b Debt 1.20 2.00 0.30 a. Which project will equity holders agree to fund? Cutoff Project Profitability index Equity funded? A B C b. What is the cost to the firm of the debt overhang? Opportunity cost (million) Requirements 1. In cell D19, by using cell references, find the ratio between the debt beta and equit equity ratio (1 pt.). 2. In cell D22, by using cell references, calculate the profitability index of project A ( cells E22:H22 (1 pt.). 3. To decide which project would be eligible for equity funding, you need to compare to the cutoff measure by using the function IF. In cell D23, by using the function I references, compare the profitability index of project A to the cutoff measure; if tru pt.). Copy cell D23 and paste it onto cells E23:H23 (1 pt.). 4. In cell E27, by using cell references, calculate the cost to the firm of the debt over .0, and a debt beta of 0.30. It is change the firm's volatility: D E $30 $15 D $75 $18 E n the debt beta and equity beta and multiply it by the debt-to- ility index of project A (1 pt.). Copy cell D22 and paste it onto ng, you need to compare the profitability index of each project , by using the function IF and absolute and relative cell he cutoff measure; if true, type "Yes"; if false, type "No" (1 . he firm of the debt overhang (1 pt.). Problem 22-2 You are trying to decide whether to make an investment of $500 million in a new technol Gobstoppers. There is a 60% chance that the market for these candies will produce profit 20% chance the market will produce profits of $50 million, and a 20% chance that there the market will become clear one year from now. Currently, the cost of capital of the proj 20% chance that the cost of capital will drop to 9% in a year and stay at that level foreve stay at 11% forever. Movements in the cost of capital are unrelated to the size of the cand decision tree that shows the choices you have to make to see if you make the investment now. Investment (million) $500.00 Expected Value Decision tree ($ million): Requirements Profits (million) $100.00 $50.00 $0.00 To calculate the expected return on the investment opportunity, we need to constru probabilities. 1. For the first possible outcome, in cell H15, by using cell references, input the first G15, by using cell references, input the probability that this outcome will occur (1 2. For the second possible outcome, in cell H21, by using cell references, input the se cell G21, by using cell references, input the probability that this outcome will occu 3. For the third possible outcome, in cell H27, by using cell references, input the thir G27, by using cell references, input the probability that this outcome will occur (1 4. Independent of the profits outcome, there are two possible costs of capital with the occurrence. For the first possible profit outcome, in cell J14, by using cell referenc the first cost of capital will occur (1 pt.). 5. In cell J16, by using cell references, input the probability that the second cost of c 6. For the second possible profit outcome, in cell J20, by using cell references, input of capital will occur (1 pt.). 7. In cell J22, by using cell references, input the probability that the second cost of c 8. For the third possible profit outcome, in cell J26, by using cell references, input th of capital will occur (1 pt.). 9. In cell J28, by using cell references, input the probability that the second cost of c 10. For the first possible profit outcome, calculate the NPV of the investment opportun capital. In cell K13, by using cell references, calculate the NPV (1 pt.). 11. In cell K17, by using cell references, calculate the NPV (1 pt.). 12. For the second possible profit outcome, calculate the NPV of the investment oppor capital. In cell K19, by using cell references, calculate the NPV (1 pt.). 13. In cell K23, by using cell references, calculate the NPV (1 pt.). 14. For the third possible profit outcome, calculate the NPV of the investment opportu capital. In cell K25, by using cell references, calculate the NPV (1 pt.). 15. In cell K29, by using cell references, calculate the NPV (1 pt.). 16. Finally, in cell E21, by using cell references, calculate the expected value of the in 00 million in a new technology to produce Everlasting candies will produce profits of $100 million annually, a d a 20% chance that there will be no profits. The size of e cost of capital of the project is 11% per year. There is a nd stay at that level forever, and a 80% chance that it will lated to the size of the candy market. Construct the f you make the investment either today or one year from Probability 60% 20% 20% Cost of Capital Probability 11.00% 80% 9.00% 20% Firm Value ortunity, we need to construct the decision tree of outcome ll references, input the first possible profit (1 pt.). In cell this outcome will occur (1 pt.). cell references, input the second possible profit (1 pt.). In that this outcome will occur (1 pt.). ell references, input the third possible profit (1 pt.). In cell this outcome will occur (1 pt.). ble costs of capital with their respective probabilities of J14, by using cell references, input the probability that ty that the second cost of capital will occur (1 pt.). using cell references, input the probability that the first cost ty that the second cost of capital will occur (1 pt.). ing cell references, input the probability that the first cost ty that the second cost of capital will occur (1 pt.). of the investment opportunity for both possible costs of the NPV (1 pt.). V (1 pt.). PV of the investment opportunity for both possible costs of the NPV (1 pt.). V (1 pt.). V of the investment opportunity for both possible costs of the NPV (1 pt.). V (1 pt.). he expected value of the investment opportunity (1 pt.). Problem 18-16 You are evaluating a project that requires an investment of $90 million today and provide of $115 million for sure one year from now. You decide to use 100% debt financing, that $90 million. The risk-free rate is 5% and the tax rate is 40%. Assume that the investment at the end of the year, so without leverage you would owe taxes on the difference betwee flow and the investment. a. Calculate the NPV of this investment opportunity using the APV method. b. Using your answer to part (a), calculate the WACC of the project. c. Verify that you get the same answer using the WACC method to calculate NPV. d. Finally, show that flow-to-equity also correctly gives the NPV of this investment o Investment today (million) Amount of debt (million) Cash flow in 1 year (million) Tax rate Risk-free rate $90.00 $90.00 $115.00 40% 5.00% a. Calculate the NPV of this investment opportunity using the APV method. Tax basis at year end (million) Taxes owed (million) FCF at year end (million) VU (million) Interest expense (million) Interest tax shield (million) PV of ITS (million) VL (million) NPV (million) b. Using your answer to part (a), calculate the WACC of the project. Debt-to-value ratio WACC c. Verify that you get the same answer using the WACC method to calculate NPV. VL (million) NPV (million) d. Finally, show that flow-to-equity also correctly gives the NPV of this investment o After-tax interest expense (million) FCFE (million) NPV (million) Requirements To calculate the project's NPV using the APV method, you need to calculate the un values of the project. 1. In cell D18, by using cell references, calculate the tax basis at year end (1 pt.). 2. In cell D19, by using cell references, calculate the taxes owed (1 pt.). 3. In cell D20, by using cell references, calculate the free cash flow at year end (1 pt 4. To calculate the project's unlevered value, calculate the present value of the free ca function PV. In cell D21, by using the function PV and cell references, calculate t the free cash flow (1 pt.). 5. In cell D22, by using cell references, calculate the interest expense (1 pt.). 6. In cell D23, by using cell references, calculate the interest tax shield (1 pt.). 7. To calculate the present value of the interest tax shield use the function PV. In cell function PV and cell references, calculate the present value of the interest tax shiel 8. In cell D25, by using cell references, calculate the project's levered value (1 pt.). 9. In cell D26, by using cell references, calculate the net present value using the APV 10. To calculate the WACC of the project using the answer from part (a), you will use WACC formula: r_wacc = r_Unlevered - (Debt/Value) * Marginal tax rate * r_ In cell D30, by using cell references, calculate the debt-to-value ratio (1 pt.). 11. In cell D31, by using cell references, calculate the WACC (1 pt.). 12. To verify that you get the same answer using the WACC method to calculate the N the project's levered value by using the function PV. In cell D35, by using the fun references, calculate the NPV (1 pt.). 13. In cell D36, by using cell references, calculate the NPV (1 pt.). 14. To show that the flow-to-equity approach also correctly gives the NPV of this inve you need to calculate the free cash flow-to-equity after calculating the after-tax int D40, by using cell references, calculate the after-tax interest expense (1 pt.). 15. In cell D41, by using cell references, calculate the free cash flow-to-equity (1 pt.). 16. To calculate the NPV of this investment opportunity, you need to calculate the pres cash flow-to-equity using the function PV. In cell D42, by using the function PV a calculate the NPV (1 pt.). lion today and provides a single cash flow % debt financing, that is, you will borrow me that the investment is fully depreciated the difference between the project cash APV method. oject. d to calculate NPV. V of this investment opportunity. APV method. oject. d to calculate NPV. V of this investment opportunity. eed to calculate the unlevered and levered at year end (1 pt.). d (1 pt.). flow at year end (1 pt.). ent value of the free cash flow using the references, calculate the present value of xpense (1 pt.). x shield (1 pt.). he function PV. In cell D24, by using the of the interest tax shield (1 pt.). evered value (1 pt.). nt value using the APV method (1 pt.). part (a), you will use the project-based Marginal tax rate * r_Debt. alue ratio (1 pt.). pt.). hod to calculate the NPV, first, calculate D35, by using the function PV and cell t.). s the NPV of this investment opportunity, lating the after-tax interest expense. In cell expense (1 pt.). flow-to-equity (1 pt.). ed to calculate the present value of the free sing the function PV and cell references, Problem 19-3 Assuming that Ideko's market share will increase by 0.5% per year, the required producti are shown below: Sales Data Market size (000 units) Market share Production volume Growth/Year 5% 0.5% 2005 10,000 10.00% 1,000 2006 10,500 10.50% 1,103 Ideko's production plant will require an expansion in 2010 (when production volume wil cost of this expansion will be $15.0 million. Assuming the financing of the expansion wil projected interest payments and the amount of the projected interest tax shields (assumin 6.8% and the corporate tax rate is 35%) through 2010. Ideko's balance sheet for 2005 is s Balance Sheet ($000) Assets Cash and Cash Equivalents Accounts Receivable Inventories Total Current Assets Property, Plant & Equipment Goodwill Total Assets Liabilities Accounts Payable Debt Total Liabilities Stockholders' Equity Total Liabilities & Stockholders' Equity Interest rate on loan Corporate tax rate Cost of the expansion ($000) Debt and Interest ($000) Outstanding debt Interest on the term loan, 6.8% Interest tax shield 6,164 18,493 6,165 30,822 49,500 72,332 152,654 4,654 100,000 104,654 48,000 152,654 6.80% 35% 15,000 2005 2006 Requirements 1. In cell E34, by using relative and absolute cell references, calculate the outstandin pt.). Copy cell E34 and paste it onto cells F34:I34 (1 pt.). 2. In cell J34, by using cell references, calculate the outstanding debt for the year 20 3. In cell E35, by using relative and absolute cell references, calculate the interest on 2005 (1 pt.). Copy cell E35 and paste it onto cells F35:J35 (1 pt.). Note that the in not be applied until the following year. 4. In cell E36, by using relative and absolute cell references, calculate the interest tax pt.). Copy cell E36 and paste it onto cells F36:J36 (1 pt.). he required production capacities for the following five years 2007 11,025 11.00% 1,213 2008 11,576 11.50% 1,331 2009 12,155 12.00% 1,459 2010 12,763 12.50% 1,595 duction volume will exceed the current level by 50%), and the of the expansion will be delayed accordingly, calculate the ax shields (assuming that the interest rate on the term loans is e sheet for 2005 is shown below: 2007 2008 2009 2010 ulate the outstanding debt for the year 2005 (1 debt for the year 2010 (1 pt.). ulate the interest on the term loan for the year pt.). Note that the interest on the new loan will ulate the interest tax shield for the year 2005 (1