Problem 14-5 Suppose there are no taxes. Firm ABC has no debt, and firm XYZ has debt of $5,000 on which it pays interest of 10% each year. Both companies have identical projects that generate free cash flows of $800 or $1,000 each year. After paying any interest on debt, both companies use all remaining free cash flows to pay dividends each year. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. a. Fill in the table below showing the payments debt and equity holders of each firm will receive given each of the two possible levels of free cash flows. b. Suppose you hold 10% of the equity of ABC. What is another portfolio you could hold that would provide the same cash flows? c. Suppose you hold 10% of the equity of XYZ. If you can borrow at 10%, what is an alternative strategy that would provide the same cash flows? Debt ABC Debt XYZ Interest rate $5,000 10% Fill in the table below showing the payments debt and equity holders of each firm will receive given each of the two possible levels of free cash flows. FCF $800 $1,000 ABC Debt Payments $0 $0 Equity Dividends $800 $1,000 XYZ Debt Payments $500 $500 Equity Dividends $300 $500 b. Suppose you hold 10% of the equity of ABC. What is another portfolio you could hold that would provide the same cash flows? Ownership 10% FCF=$800 FCF=$1,000 or of debt and 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 or per year of equity of XYZ per year per year per year, as you would get from buying ABC equity or or c. Suppose you hold 10% of the equity of XYZ. If you can borrow at 10%, what is an alternative strategy that would provide the same cash flows? Ownership 10% FCF-$800 FCF $1,000 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 or and buying or in one case per year of the equity of ABC per year in another case per year, as you would get from buying XYZ equity or Requirements 1. Start Excel - completed. 2. In cell D18, by using cell references, calculate firm ABC's debt payments given the first level of free cash flows (1 pt.). 3. To calculate firm ABC's debt payments given the second level of free cash flows, copy cell D18 and paste it onto cell D19 (1 pt.). 4. In cell E18, by using absolute cell references, calculate firm ABC's equity dividends given the first level of free cash flows and the debt payments (1 pt.). 5. To calculate firm ABC's equity dividends given the second level of free cash flows, copy cell E18 and paste it onto cell E19 (1 pt.). 6. In cell F18, by using cell references, calculate firm XYZ's debt payments given the first level of free cash flows (1 pt.). 7. To calculate firm XYZ's debt payments given the second level of free cash flows, copy cell F18 and paste it onto cell F19 (1 pt.). 8. * In cell G18, by using cell references, calculate firm XYZ's equity dividends given the first level of free cash flows and the debt payments (1 pt.). 9. To calculate firm XYZ's equity dividends given the second level of free cash flows, copy cell G18 and paste it onto cell G19 (1 pt.). 10. In cell F25, by using cell references, calculate the cash flows provided by owning 10% equity in company ABC given the first level of free cash flows (1 pt.). 11. In cell H25, by using cell references, calculate the cash flows provided by owning 10% equity in company ABC given the second level of free cash flows (1 pt.) 12. In cell F26, by using a cell reference, calculate the percent of debt ownership that could help to replicate the cash flows (1 pt.). 13. In cell H26, by using a cell reference, calculate the percent of equity ownership that could help to replicate the cash flows (1 pt.). 14. * In cell F27, by using cell references, calculate the debt cash flows given the first level of free cash flows and corresponding debt payments (1 pt.). 15. * In cell H27, by using cell references, calculate the debt cash flows given the second level of free cash flows and corresponding debt payments (1 pt.). 16. In cell F28, by using cell references, calculate the equity cash flows given the first level of free cash flows and corresponding equity dividends (1 pt.). Note: Use a cell reference to the percent of equity ownership from Step 13 in your calculations. 17. In cell H28, by using cell references, calculate the equity cash flows given the second level of free cash flows and corresponding equity dividends (1 pt.). Note: Use a cell reference to the percent of equity ownership from Step 13 in your calculations. 18. In cell F29, by using cell references, calculate the total cash flows given the first level of free cash flows (1 pt.). 19. To calculate the total cash flows given the second level of free cash flows, copy cell F29 and paste it onto cell H29 (1 pt.). 20. In cell F35, by using cell references, calculate the cash flows provided by owning 10% equity in company XYZ given the first level of free cash flows (1 pt.). 21. In cell H35, by using cell references, calculate the cash flows provided by owning 10% equity in company XYZ given the second level of free cash flows (1 pt.). 22. In cell F36, by using cell references, calculate the amount to borrow to help replicate the cash flows by multiplying the percent of XYZ equity ownership by the amount of debt that XYZ firm currently has (1 pt.). 23. In cell H36, by using cell references, calculate the percent of firm ABC's ownership to help replicate the cash flows by making a cell reference to cell D33 (1 pt.). 24. In cell F37, by using cell references, calculate the dividends that you would receive given the first level of free cash flows (1 pt.). 25. In cell H37. by using cell references, calculate the dividends that you would receive given the second level of free cash flows (1 pt.) 14. 15. 16. 17. In cell F27, by using cell references, calculate the debt cash flows given the first level of free cash flows and corresponding debt payments (1 pt.). In cell H27, by using cell references, calculate the debt cash flows given the second level of free cash flows and corresponding debt payments (1 pt.). In cell F28, by using cell references, calculate the equity cash flows given the first level of free cash flows and corresponding equity dividends (1 pt.). Note: Use a cell reference to the percent of equity ownership from Step 13 in your calculations. In cell H28, by using cell references, calculate the equity cash flows given the second level of free cash flows and corresponding equity dividends (1 pt.). Note: Use a cell reference to the percent of equity ownership from Step 13 in your calculations. 18. In cell F29, by using cell references, calculate the total cash flows given the first level of free cash flows (1 pt.). 19. To calculate the total cash flows given the second level of free cash flows, copy cell F29 and paste it onto cell H29 (1 pt.). 20. In cell F35, by using cell references, calculate the cash flows provided by owning 10% equity in company XYZ given the first level of free cash flows (1 pt.). 21. In cell H35, by using cell references, calculate the cash flows provided by owning 10% equity in company XYZ given the second level of free cash flows (1 pt.). 22. In cell F36, by using cell references, calculate the amount to borrow to help replicate the cash flows by multiplying the percent of XYZ equity ownership by the amount of debt that XYZ firm currently has (1 pt.). 23. In cell H36, by using cell references, calculate the percent of firm ABC's ownership to help replicate the cash flows by making a cell reference to cell D33 (1 pt.) 24. In cell F37, by using cell references, calculate the dividends that you would receive given the first level of free cash flows (1 pt.). 25. In cell H37, by using cell references, calculate the dividends that you would receive given the second level of free cash flows (1 pt.). 26. In cell F38, by using cell references, calculate the amount that you would pay in interest given the first level of free cash flows (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 27. In cell H38, by using a cell reference, calculate the amount that you would pay in interest given the second level of free cash flows, since the interest payments are independent of the level of free cash flows (1 pt.). 28. In cell F39, by using cell references, calculate the total cash flows given the first level of free cash flows (1 pt.). 29. In cell H39, by using cell references, calculate the total cash flows given the second level of free cash flows (1 pt.). 30. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Problem 14-5 Suppose there are no taxes. Firm ABC has no debt, and firm XYZ has debt of $5,000 on which it pays interest of 10% each year. Both companies have identical projects that generate free cash flows of $800 or $1,000 each year. After paying any interest on debt, both companies use all remaining free cash flows to pay dividends each year. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. a. Fill in the table below showing the payments debt and equity holders of each firm will receive given each of the two possible levels of free cash flows. b. Suppose you hold 10% of the equity of ABC. What is another portfolio you could hold that would provide the same cash flows? c. Suppose you hold 10% of the equity of XYZ. If you can borrow at 10%, what is an alternative strategy that would provide the same cash flows? Debt ABC Debt XYZ Interest rate $5,000 10% Fill in the table below showing the payments debt and equity holders of each firm will receive given each of the two possible levels of free cash flows. FCF $800 $1,000 ABC Debt Payments $0 $0 Equity Dividends $800 $1,000 XYZ Debt Payments $500 $500 Equity Dividends $300 $500 b. Suppose you hold 10% of the equity of ABC. What is another portfolio you could hold that would provide the same cash flows? Ownership 10% FCF=$800 FCF=$1,000 or of debt and 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 or per year of equity of XYZ per year per year per year, as you would get from buying ABC equity or or c. Suppose you hold 10% of the equity of XYZ. If you can borrow at 10%, what is an alternative strategy that would provide the same cash flows? Ownership 10% FCF-$800 FCF $1,000 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 or and buying or in one case per year of the equity of ABC per year in another case per year, as you would get from buying XYZ equity or Requirements 1. Start Excel - completed. 2. In cell D18, by using cell references, calculate firm ABC's debt payments given the first level of free cash flows (1 pt.). 3. To calculate firm ABC's debt payments given the second level of free cash flows, copy cell D18 and paste it onto cell D19 (1 pt.). 4. In cell E18, by using absolute cell references, calculate firm ABC's equity dividends given the first level of free cash flows and the debt payments (1 pt.). 5. To calculate firm ABC's equity dividends given the second level of free cash flows, copy cell E18 and paste it onto cell E19 (1 pt.). 6. In cell F18, by using cell references, calculate firm XYZ's debt payments given the first level of free cash flows (1 pt.). 7. To calculate firm XYZ's debt payments given the second level of free cash flows, copy cell F18 and paste it onto cell F19 (1 pt.). 8. * In cell G18, by using cell references, calculate firm XYZ's equity dividends given the first level of free cash flows and the debt payments (1 pt.). 9. To calculate firm XYZ's equity dividends given the second level of free cash flows, copy cell G18 and paste it onto cell G19 (1 pt.). 10. In cell F25, by using cell references, calculate the cash flows provided by owning 10% equity in company ABC given the first level of free cash flows (1 pt.). 11. In cell H25, by using cell references, calculate the cash flows provided by owning 10% equity in company ABC given the second level of free cash flows (1 pt.) 12. In cell F26, by using a cell reference, calculate the percent of debt ownership that could help to replicate the cash flows (1 pt.). 13. In cell H26, by using a cell reference, calculate the percent of equity ownership that could help to replicate the cash flows (1 pt.). 14. * In cell F27, by using cell references, calculate the debt cash flows given the first level of free cash flows and corresponding debt payments (1 pt.). 15. * In cell H27, by using cell references, calculate the debt cash flows given the second level of free cash flows and corresponding debt payments (1 pt.). 16. In cell F28, by using cell references, calculate the equity cash flows given the first level of free cash flows and corresponding equity dividends (1 pt.). Note: Use a cell reference to the percent of equity ownership from Step 13 in your calculations. 17. In cell H28, by using cell references, calculate the equity cash flows given the second level of free cash flows and corresponding equity dividends (1 pt.). Note: Use a cell reference to the percent of equity ownership from Step 13 in your calculations. 18. In cell F29, by using cell references, calculate the total cash flows given the first level of free cash flows (1 pt.). 19. To calculate the total cash flows given the second level of free cash flows, copy cell F29 and paste it onto cell H29 (1 pt.). 20. In cell F35, by using cell references, calculate the cash flows provided by owning 10% equity in company XYZ given the first level of free cash flows (1 pt.). 21. In cell H35, by using cell references, calculate the cash flows provided by owning 10% equity in company XYZ given the second level of free cash flows (1 pt.). 22. In cell F36, by using cell references, calculate the amount to borrow to help replicate the cash flows by multiplying the percent of XYZ equity ownership by the amount of debt that XYZ firm currently has (1 pt.). 23. In cell H36, by using cell references, calculate the percent of firm ABC's ownership to help replicate the cash flows by making a cell reference to cell D33 (1 pt.). 24. In cell F37, by using cell references, calculate the dividends that you would receive given the first level of free cash flows (1 pt.). 25. In cell H37. by using cell references, calculate the dividends that you would receive given the second level of free cash flows (1 pt.) 14. 15. 16. 17. In cell F27, by using cell references, calculate the debt cash flows given the first level of free cash flows and corresponding debt payments (1 pt.). In cell H27, by using cell references, calculate the debt cash flows given the second level of free cash flows and corresponding debt payments (1 pt.). In cell F28, by using cell references, calculate the equity cash flows given the first level of free cash flows and corresponding equity dividends (1 pt.). Note: Use a cell reference to the percent of equity ownership from Step 13 in your calculations. In cell H28, by using cell references, calculate the equity cash flows given the second level of free cash flows and corresponding equity dividends (1 pt.). Note: Use a cell reference to the percent of equity ownership from Step 13 in your calculations. 18. In cell F29, by using cell references, calculate the total cash flows given the first level of free cash flows (1 pt.). 19. To calculate the total cash flows given the second level of free cash flows, copy cell F29 and paste it onto cell H29 (1 pt.). 20. In cell F35, by using cell references, calculate the cash flows provided by owning 10% equity in company XYZ given the first level of free cash flows (1 pt.). 21. In cell H35, by using cell references, calculate the cash flows provided by owning 10% equity in company XYZ given the second level of free cash flows (1 pt.). 22. In cell F36, by using cell references, calculate the amount to borrow to help replicate the cash flows by multiplying the percent of XYZ equity ownership by the amount of debt that XYZ firm currently has (1 pt.). 23. In cell H36, by using cell references, calculate the percent of firm ABC's ownership to help replicate the cash flows by making a cell reference to cell D33 (1 pt.) 24. In cell F37, by using cell references, calculate the dividends that you would receive given the first level of free cash flows (1 pt.). 25. In cell H37, by using cell references, calculate the dividends that you would receive given the second level of free cash flows (1 pt.). 26. In cell F38, by using cell references, calculate the amount that you would pay in interest given the first level of free cash flows (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 27. In cell H38, by using a cell reference, calculate the amount that you would pay in interest given the second level of free cash flows, since the interest payments are independent of the level of free cash flows (1 pt.). 28. In cell F39, by using cell references, calculate the total cash flows given the first level of free cash flows (1 pt.). 29. In cell H39, by using cell references, calculate the total cash flows given the second level of free cash flows (1 pt.). 30. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed