This is a bigger project but I was mostly looking for help on the parts with the short term and long term debt/interest rates. I have the sheet set up I just need help getting those numbers to fill gaps.
B D E F G H K L 1 2 Chapter 16 - Master it! 3 The TL Corporation currently has no debt outstanding. Josh Culberson, the CFO, is considering restructuring the company by issuing debt and using the proceeds to repurchase outstanding equity. The company's assets are worth $40 million, the stock price is $25 per share, and there are 1,600,000 shares outstanding. In the expected state of the economy, EBIT is expected to be $3 million. If there is a recession, EBIT would fall to $1.8 million and in an expansion EBIT would increase to $4.3 million. If the company issues debt, it will issue a combination of short-term debt and long-term debt. The ratio of short-term debt to long-term debt will be .20. The short-term debt will have an interest rate of 3 percent and the long-term debt will have an interest rate of 8 percent. 4 5 a. On the next worksheet, fill in the values in each table. For the debt-equity ratio, create a spinner that changes the debt-equity ratio. The resulting debt- equity ratio should range from 0 to 10 at increments of .1. 6 7 8 b. Graph the EBIT and EPS for the TL Corporation on the same graph using a line graph. 9 10 c. What is the breakeven EBIT between the current capital structure and the new capital structure? 11 d. To illustrate the new capital structure, you would like to create a pie chart. Another pie chart that is available is the pie of pie chart. Using the pie of pie chart, graph the equity and total debt in the main pie chart and the short-term debt and long-term debt in the secondary pie chart. Note, if you right-click on a data series in the chart and select Format Data Series, the Series Options will permit you to display the series by a customized choice. In the customization, you can select which data series you want displayed in the primary pie chart and which you want displayed in the secondary pie chart. 12 13 14 15 16 17 Section 16.3 Master it! Solution (+) 4 A A 3 4 5 g. 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 B Section 16.3 Assets Short-term debt Long-term debt Debt Equity Short-term debt/Long-term debt Debt-equity ratio Share price Shares outstanding Short-term debt interest rate Long-term debt interest rate EBIT Interest Net income ROE EPS EBIT Interest Net income ROE EPS D F Current Proposed $ 40,000,000 $ 40,000,000 $ $ $ $ $ $ $ 40,000,000 $ 40,000,000 0.20 0 0.0 $ 25 $ 25 1,600,000 1,600,000 3% 8% Current Capital Structure: Debt = $0 million Recession Expansion Expected $ 1,800,000 $3,000,000 $ 4,300,000 $ 1,800,000 $3,000,000 $4,300,000 4.50% 7.50% 1.88 $ 10.75% 2.69 $ 1.13 $ Proposed Capital Structure: Debt = $0 million Recession Expected Expansion $ 1,800,000 $3,000,000 $ 4,300,000 $ 1,800,000 $3,000,000 $ 4,300,000 4.50% 7.50% 1.88 $ 10.75% 2.69 $ 1.13 $ Master it! Solution (+) E G H I Counter: K B D E F G H K L 1 2 Chapter 16 - Master it! 3 The TL Corporation currently has no debt outstanding. Josh Culberson, the CFO, is considering restructuring the company by issuing debt and using the proceeds to repurchase outstanding equity. The company's assets are worth $40 million, the stock price is $25 per share, and there are 1,600,000 shares outstanding. In the expected state of the economy, EBIT is expected to be $3 million. If there is a recession, EBIT would fall to $1.8 million and in an expansion EBIT would increase to $4.3 million. If the company issues debt, it will issue a combination of short-term debt and long-term debt. The ratio of short-term debt to long-term debt will be .20. The short-term debt will have an interest rate of 3 percent and the long-term debt will have an interest rate of 8 percent. 4 5 a. On the next worksheet, fill in the values in each table. For the debt-equity ratio, create a spinner that changes the debt-equity ratio. The resulting debt- equity ratio should range from 0 to 10 at increments of .1. 6 7 8 b. Graph the EBIT and EPS for the TL Corporation on the same graph using a line graph. 9 10 c. What is the breakeven EBIT between the current capital structure and the new capital structure? 11 d. To illustrate the new capital structure, you would like to create a pie chart. Another pie chart that is available is the pie of pie chart. Using the pie of pie chart, graph the equity and total debt in the main pie chart and the short-term debt and long-term debt in the secondary pie chart. Note, if you right-click on a data series in the chart and select Format Data Series, the Series Options will permit you to display the series by a customized choice. In the customization, you can select which data series you want displayed in the primary pie chart and which you want displayed in the secondary pie chart. 12 13 14 15 16 17 Section 16.3 Master it! Solution (+) 4 A A 3 4 5 g. 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 B Section 16.3 Assets Short-term debt Long-term debt Debt Equity Short-term debt/Long-term debt Debt-equity ratio Share price Shares outstanding Short-term debt interest rate Long-term debt interest rate EBIT Interest Net income ROE EPS EBIT Interest Net income ROE EPS D F Current Proposed $ 40,000,000 $ 40,000,000 $ $ $ $ $ $ $ 40,000,000 $ 40,000,000 0.20 0 0.0 $ 25 $ 25 1,600,000 1,600,000 3% 8% Current Capital Structure: Debt = $0 million Recession Expansion Expected $ 1,800,000 $3,000,000 $ 4,300,000 $ 1,800,000 $3,000,000 $4,300,000 4.50% 7.50% 1.88 $ 10.75% 2.69 $ 1.13 $ Proposed Capital Structure: Debt = $0 million Recession Expected Expansion $ 1,800,000 $3,000,000 $ 4,300,000 $ 1,800,000 $3,000,000 $ 4,300,000 4.50% 7.50% 1.88 $ 10.75% 2.69 $ 1.13 $ Master it! Solution (+) E G H I Counter: K
