I need help with the full excel spreadsheet for this case study. The questions are attached as "Case 18 Questions 5th edition-edited"; please disregard the questions at the bottom of the case study. Thanks

Cases in Healthcare Finance, 5th Edition Copyright 2014 Health Administration Press CASE 18 QUESTIONS SOUTHEASTERN HOMECARE Cost of Capital 1. What corporate cost of capital (CCC) do you estimate for Southeastern Homecare? 2. The company's financial plan calls for the issue of 30-year bonds to meet long-term debt needs. How valid is an estimate of the cost of debt based on 15-year bonds? If the estimate is not valid, how might it be adjusted to remove any bias? 3. The Board Chair is concerned about factors that affect the corporate cost of capital for any business: the level of interest rates, tax rates, capital structure policy, and capital investment policy. Does the tax rate, cost of debt, or cost of equity have the most influence on the CCC of Southeastern Homecare? Why? (Hint: In one graph, show the CCC at +/- 25% and 50% values of the tax rate, cost of debt, and cost of equity.) 4. Southeastern Homecare has two operating divisions: the Healthcare Services Division and the Information Systems Division. a. Estimate the divisional cost of capital for each of Southeastern's divisions assuming that both divisions have the same optimal (target) capital structure. (Hint: Use the CAPM to produce a cost of equity for each division and assume the same corporate tax rate and debt cost for each division.) b. Southeastern's divisions are each considering two investment opportunities for next year. In which of the projects should Southeastern invest? c. The divisional presidents have expressed concern that a single cost of capital will be applied across the company, regardless of any divisional risk differences. What would be the short-term and longterm consequences of Southeastern using a single cost of capital for both divisions? d. Is the divisional cost of capital applicable for all projects within that division? Explain. e. Suppose that one division has a greater capacity for debt financing than the other (perhaps due to higher asset value and profitability.) How could different target capital structures be incorporated into the divisional costs of capital? 5. The founders of Southeastern are concerned about the threat posed by home health care businesses started by not-for-profit hospitals. Using your answer to Question 4a and the data in Table 18.3, estimate the cost of capital for the homecare division of an average not-for-profit hospital. How much confidence do you have in the cost of capital estimate? Why? 6. One of Southeastern's directors has expressed concern over the difference between the company's target capital structure and the current structure as reported on the balance sheet. a. What are the book values of Southeastern's long-term debt and equity? What are the current market values of Southeastern's long-term debt and equity? b. What are Southeastern's target weights, book value weights, and current market value weights of long-term debt and equity (that is, long-term debt / total capital and common stock / total capital)? c. Which set of weights should be used in the corporate cost of capital estimate? Why? 7. In your opinion, what are three key learning points from this case? 12/6/2013 Southeastern Homecare Questions Assume all assumptions as stated in the Case. Additional assumptions are stated in questions below, if required. Q1: What are Target, Book & Market Value weights of Southeastern Homecare? (1 page ) Q2: Calculate the following: a. After tax cost of debt. b. Cost of Equity using Dividend Growth Model. Assume future growth in dividends 10% rate/year. c. Cost of Equity using CAPM model. . Q3: Calculate WACC of Southeastern assuming Cost of Equity using CAPM model and Target Capital Structure Weights Page 1 Questions Q4: Southeastern has 2 operating divisions: Healthcare & Information Services. Calculate cost of capital for its 2 divisions based on the business risk stated in the Case. Hint: Allocate it based on the % assets & risk inherent in the business. Q5: Which projects should Southeastern accept or reject? Briefly state why? 1. A new office in Naples 2. A new office in Sarasota 3. A new medical record system 4. Expanded Billing System Healthcare Services Healthcare Services Information Systems Information Systems Page 2 Questions Q6: Calculate the cost of capital of a not-for-profit competitor, based on data provided in Exhibit 18.3. > Compare this with WACC of Southeastern Homecare as calculated in Q3 above. Q7: Founders of Southeastern are concerned about potential threat posed by not-for-profit companies which has inherent tax advantages. Should the Founders be concerned about this threat? Why or why not? ( 1 Paragraph) Page 3