please include excel formulas and answer all questions in yellow EXCEPT #2 as it is already answered

Numbers File Edit Insert Table Organize Format Arrange Arrange View Window Help week 4 hw fin310 - Edited 125% Ev DAV My Apps T Chart Taxt Shape Media Comment Zoom Add Category Week 4 Final Assignment 12-14 12-17 Chapter 13 Case Study B D E F 0 H TJO 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 failed 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 annual pretax eamings 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). Input area: Shares outstanding Share price $ 8,500,000 44.50 50,000,000 Purchase price of land $ Pernetual earnings increase S 11 000 000 1 2 3 5 6 7 8 10 11 12 View + Text A ? 29 View Share Insert Table tv Qe Collaborate Table A 09 Cell Data Format Automatic Fill Border 44% Fri 10:23 PM QE Format Organize Text Arrange Border Styles No Border Conditional Highlighting... TI FO |! Numbers File Edit Insert Table Organize Format Arrange Arrange View View Share Window Help week 4 hw fin310 - Edited 125% al T DAV My Apps Ev Add Category Zoom Insert Table Chart Taxt Shape Media Comment Week 4 Final Assignment 12-17 Chapter 13 Case Study D E F G H Input area: Shares outstanding Share price $ Purchase price of land $ 8,500,000 44.50 50,000,000 11,000,000 12.50% Perpetual earnings increase $ Current cost of capital Cost of new debt 8% Optimal equity weight 70% Optimal debt weight 30% Tax rate 21% Output area: 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) $ Assets Total assets $ $ 378,250,000 378,250,000 Equity Debt & Equity 378,250,000 378,250,000 $ 3) a) Perpetual aftertax eamings $ 88,000,000 NPV of purchase $ 38,000,000 Balance Sheet Old assets NPV of project ? Total assets ? HII A 09 View + O 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 34 35 36 38 Text ? b) 12-14 ? ? ? 29 Equity Deht & Fauity tv I J Qe Collaborate . II. Table Data Format Automatic Fill Border Cell 44%D Fri 10:23 PM QE Format Organize Text Arrange reen Shot Border Styles No Border Conditional Highlighting... TI FO |! -0...23.23 PM b) Old assets NPV of project Total assets New share price Shares to issue Cash Old assets NPV of project Total assets Total shares outstanding Share price d) PV of earnings increase Old assets PV of project Total assets 4) a) Value of levered company b) Value unlevered Tax shield value Total assets c) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Balance Sheet Balance Sheet Balance Sheet Balance Sheet Equity Debt & Equity Equity Debt & Equity Equity Debt & Equity