Please see attachment for question. Please use the attached spreadsheet and follow instructions on the attachment. If you can't use excel do not attempt the problem. Thanks.

Problem 14-5 Suppose there are no taxes. Firm ABC has no debt, and firm XYZ has debt of $5000 on whi have identical projects that generate free cash flows of $800 or $1000 each year. After payin remaining free cash flows to pay dividends each year. a. Fill in the table below showing the payments debt and equity holders of each firm wil cash flows. b. Suppose you hold 10% of the equity of ABC. What is another portfolio you could hol same cash flows? c. Suppose you hold 10% of the equity of XYZ. If you can borrow at 10%, what is an al flows? Debt ABC Debt XYZ Interest rate $0 $5,000 10% a. Fill in the table below showing the payments debt and equity holders of each firm wil cash flows. ABC FCF $800 $1,000 Debt Payments $0 $0 XYZ Equity Dividends $800 $1,000 Debt Payments $500 $500 b. Suppose you hold 10% of the equity of ABC. What is another portfolio you could hol same cash flows? Ownership 10% Equity in ABC would provide cash flows of Cash flows could be replicated by owning The debt cash flows would be The equity cash flows would be For total cash flows of c. Suppose you hold 10% of the equity of XYZ. If you can borrow at 10%, what is an al flows? Ownership 10% Equity in XYZ would provide cash flows of Cash flows could be replicated by borrowing You would receive dividends of You would pay interest of Your total cash flow would be Requirements 1. In cell D18, by using absolute cell references, calculate firm ABC's debt payments gi 2. Calculate firm ABC's debt payments given the second level of free cash flows by cop 3. In cell E18, by using cell references, calculate firm ABC's equity dividends given the 4. Calculate firm ABC's equity dividends given the second level of free cash flows by c 5. In cell F18, by using absolute cell references, calculate firm XYZ's debt payments gi 6. Calculate firm XYZ's debt payments given the second level of free cash flows by cop 7. In cell G18, by using cell references, calculate firm XYZ's equity dividends given the 8. Calculate firm XYZ's equity dividends given the second level of free cash flows by c 9. In cell F25, by using cell references, calculate the cash flows provided by owning 10% cash flows (1 pt.). 10. In cell H25, by using cell references, calculate the cash flows provided by owning 10 free cash flows (1 pt.). 11. In cell F26, calculate the percent of debt ownership that could help to replicate the ca 12. In cell H26, calculate the percent of equity ownership that could help to replicate the pt.). 13. In cell F27, by using cell references, calculate the debt cash flows given the first leve 14. In cell H27, by using cell references, calculate the debt cash flows given the second l 15. In cell F28, by using cell references, calculate the equity cash flows given the first lev 16. In cell H28, by using cell references, calculate the equity cash flows given the second 17. In cell F29, by using cell references, calculate the total cash flows given the first leve 18. Calculate the total cash flows given the second level of free cash flows by copying ce 19. In cell F35, by using cell references, calculate the cash flows provided by owning 10% cash flows (1 pt.). 20. In cell H35, by using cell references, calculate the cash flows provided by owning 10 free cash flows (1 pt.). 21. In cell F36, by using cell references, calculate the amount to borrow to help replicate 22. In cell H36, by using cell references, calculate the percent of firm ABC's ownership t 23. In cell F37, by using cell references, calculate the dividends that you would receive g 24. In cell H37, by using cell references, calculate the dividends that you would receive g 25. In cell F38, by using cell references, calculate the amount that you would pay in inter 26. In cell H38, calculate the amount that you would pay in interest given the second leve F38, since the interest payments are independent of the level of free cash flows (1 pt. 27. In cell F39, by using cell references, calculate the total cash flows given the first leve 28. In cell H39, by using cell references, calculate the total cash flows given the second l YZ has debt of $5000 on which it pays interest of 10% each year. Both companies $1000 each year. After paying any interest on debt, both companies use all quity holders of each firm will receive given each of the two possible levels of free other portfolio you could hold that would provide the borrow at 10%, what is an alternative strategy that would provide the same cash quity holders of each firm will receive given each of the two possible levels of free XYZ Equity Dividends $300 $500 other portfolio you could hold that would provide the or of debt and or or or per year of equity of XYZ per year per year per year, as you would get from buying ABC equity borrow at 10%, what is an alternative strategy that would provide the same cash or and buying or in one case or per year of the equity of ABC per year in another case per year, as you would get from buying XYZ equity irm ABC's debt payments given the first level of free cash flows (1 pt.). vel of free cash flows by copying cell D18 and pasting it onto cell D19 (1 pt.). 's equity dividends given the first level of free cash flows (1 pt.). level of free cash flows by copying cell E18 and pasting it onto cell E19. irm XYZ's debt payments given the first level of free cash flows (1 pt.). vel of free cash flows by copying cell F18 and pasting onto cell F19 (1 pt.). Z's equity dividends given the first level of free cash flows (1 pt.). level of free cash flows by copying cell G18 and pasting it onto cell G19 (1 pt.). ows provided by owning 10% equity in company ABC given the first level of free lows provided by owning 10% equity in company ABC given the second level of could help to replicate the cash flows by making a cell reference to cell D23 (1 pt.). at could help to replicate the cash flows by making a cell reference to cell D23 (1 ash flows given the first level of free cash flows (1 pt.). ash flows given the second level of free cash flows (1 pt.). cash flows given the first level of free cash flows (1 pt.). cash flows given the second level of free cash flows (1 pt.). ash flows given the first level of free cash flows (1 pt.). ree cash flows by copying cell F29 and pasting it onto cell H29 (1 pt.). ows provided by owning 10% equity in company XYZ given the first level of free lows provided by owning 10% equity in company XYZ given the second level of nt to borrow to help replicate the cash flows (1 pt.). nt of firm ABC's ownership to help replicate the cash flows (1 pt.). nds that you would receive given the first level of free cash flows (1 pt.). ends that you would receive given the second level of free cash flows (1 pt.). nt that you would pay in interest given the first level of free cash flows (1 pt.). interest given the second level of free cash flows by making a cell reference to cell evel of free cash flows (1 pt.). ash flows given the first level of free cash flows (1 pt.). ash flows given the second level of free cash flows (1 pt.). BC equity YZ equity Problem 17-29 AMC Corporation currently has an enterprise value of $400 million and $100 million in exc million shares outstanding and no debt. Suppose AMC uses its excess cash to repurchase sha repurchase, news will come out that will change AMC's enterprise value to either $600 mill a. What is AMC's share price prior to the share repurchase? b. What is AMC's share price after the repurchase if its enterprise value goes up? What i the repurchase if its enterprise value declines? c. Suppose AMC waits until after the news comes out to do the share repurchase. What i the repurchase if its enterprise value goes up? What is AMC's share price after the rep value declines? d. Suppose AMC management expects good news to come out. Based on your answers t management desires to maximize AMC's ultimate share price, will they undertake the the news comes out? When would management undertake the repurchase if they expe e. Given your answers to part (d), what would you expect an announcement of a share re news to have on the stock price? Why? Enterprise value before announcement (million) Excess cash (million) Shares outstanding (million) High enterprise value (million) Low enterprise value (million) $400 $100 10 $600 $200 a. What is AMC's share price prior to the share repurchase? Share price before repurchase b. What is AMC's share price after the repurchase if its enterprise value goes up? What i the repurchase if its enterprise value declines? Shares to repurchase (million) High share price post repurchase Low share price post repurchase c. Suppose AMC waits until after the news comes out to do the share repurchase. What i the repurchase if its enterprise value goes up? What is AMC's share price after the rep value declines? High share price post repurchase Low share price post repurchase d. Suppose AMC management expects good news to come out. Based on your answers t management desires to maximize AMC's ultimate share price, will they undertake the the news comes out? When would management undertake the repurchase if they expe If management expects the news to be good, it will benefit shareholders if they repurc If management expects the news to be bad it will benefit shareholders if they repurcha e. Given your answers to part (d), what would you expect an announcement of a share re news to have on the stock price? Why? Requirements 1. In cell D21, by using cell references, calculate the company's share price prior to the 2. In cell D25, by using cell references, calculate the number of shares that can be repurc cash (1 pt.). 3. In cell D26, by using cell references, calculate the company's share price after the sha enterprise value goes up (1 pt.). 4. In cell D27, by using cell references, calculate the company's share price after the sha enterprise value goes down (1 pt.). 5. In cell D31, by using cell references, calculate the company's share price after the ann the share repurchase) if its enterprise value goes up (1 pt.). 6. In cell D32, by using cell references, calculate the company's share price after the ann the share repurchase) if its enterprise value goes down (1 pt.). 7. In cell C37, type before the news is released. (lowercase) (1 pt.). 8. In cell C40, type after the news is released (lowercase). (1 pt.). 9. In cell C44, type You would expect the share price to increase, because the annou good news is about to be released. (sentences case) (1 pt.). d $100 million in excess cash. The firm has 10 ash to repurchase shares. After the share e to either $600 million or $200 million. alue goes up? What is AMC's share price after e repurchase. What is AMC's share price after are price after the repurchase if its enterprise ed on your answers to parts (b) and (c), if ll they undertake the repurchase before or after urchase if they expect bad news to come out? ncement of a share repurchase prior to the alue goes up? What is AMC's share price after e repurchase. What is AMC's share price after are price after the repurchase if its enterprise ed on your answers to parts (b) and (c), if ll they undertake the repurchase before or after urchase if they expect bad news to come out? holders if they repurchase ders if they repurchase ncement of a share repurchase prior to the re price prior to the share repurchase (1 pt.). res that can be repurchased with the excess re price after the share repurchase if its re price after the share repurchase if its re price after the announcement (but before re price after the announcement (but before ). because the announcement will mean that Problem 15-7 Ten years have passed since Arnell issued $10 million in perpetual interest-only debt 6% annual coupon. Tax rates have remained the same at 35% but interest rates have d so Arnell's current cost of debt capital is 4%. a. 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 $600,000 $210,000 b. What is the present value of the interest tax shield today? PV(interest tax shield) $201,923 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.). 3. In cell D20, by using cell references, calculate the present value of the annual interest 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.30. currently evaluating the following projects, none of which would change the firm's volatility 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? 0.18 A B 0.20 Yes C 0.12 No 0.12 No 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 equity b equity ratio (1 pt.). 2. In cell D22, by using cell references, calculate the profitability index of project A (1 p cells E22:H22 (1 pt.). 3. To decide which project would be eligible for equity funding, you need to compare th to the cutoff measure by using the function IF. In cell D23, by using the function IF a references, compare the profitability index of project A to the cutoff measure; if true, 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 overhan .0, and a debt beta of 0.30. It is change the firm's volatility: D E $30 $15 D $75 $18 E 0.50 Yes 0.24 Yes 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.)