please show excel formulas thankyou
Chapter Case Stephenson Real Estate Recapitalization Stephenson Real Estate Company was founded 25 years ago by the current CEO, Robert Stephenson. The company purchases real estate, including land and buildings, and rents the property to tenants. The company has shown a profit every year for the past 18 years, and the shareholders are satisfied with the company's management. Prior to founding Stephenson Real Estate, Robert was the founder and CEO of a falled alpaca farming operation. The resulting bankruptcy made him extremely averse to debt financing. As a result, the company is entirely equity financed, with 8.5 million shares of common stock outstanding. The stock currently trades at $44,50 per share. Stephenson is evaluating a plan to purchase a huge tract of land in the southeastern United States for $50 million. The land will subsequently be leased to tenant farmers. This purchase is expected to increase Stephenson's onnual pretax earnings by $11 million in perpetuity, Kim Weyand, the company's new CFO, has been put in charge of the project. Kim has determined that the company's current cost of capital is 12.5 percent. She feels that the company would be more valuable if it included debt in its capital structure, so she is evaluating whether the company should issue debt to entirely finance the project. Based on some conversations with investment banks, she thinks that the company can issue bonds at par value with a coupon rate of 8 percent. From her analysis, she also believes that a capital structure in the range of 70 percent equity/30 percent debt would be optimal. If the company goes beyond 30 percent debt, its bonds would carry a lower rating and a much higher coupon because the possibility of financial distress and the associated costs would rise sharply, Stephenson has a 21 percent corporate tax rate (state and federal). QUESTIONS 1. If Stephenson wishes to maximize its total market value, would you recommend that it issue debt or equity to finance the land purchase? Explain. 2. Construct Stephenson's market value balance sheet before it announces the purchase. 3. Suppose Stephenson decides to issue equity to finance the purchase. a. What is the net present value of the project? b. Construct Stephenson's market value balance sheet after it announces that the firm will finance the purchase using equity. What would be the new price per share of the firm's stock? How many shares will Stephenson need to issue to finance the purchase? c Construct Stephenson's market value balance sheet after the equity issue but before the purchase has been made, How many shares of common stock does Stephenson have outstanding? What is the price per share of the firm's stock? d. Construct Stephenson's market value balance sheet after the purchase has been made. 4. Suppose Stephenson decides to issue debt to finance the purchase a. What will the market value of the Stephenson Company be if the purchase is financed with debt? b. Construct Stephenson's market value balance sheet after both the debt issue and the land purchase. What is the price per share of the firm's stock? 5. Which method of financing maximizes the per-share stock price of Stephemon's equity
