i need answer in excel sheet please ? if possibel

the bottam one is direction

Chapter 14 Stephenson Real Estate Recapitalization Input area: Directions: Work the Stephenson Real Estate Recapitaliation Case on page 450 . 2) Assets Total assets Equity Debt \& Equity 3) a) Perpetual aftertax earnings NPV of purchase b) Balance Sheet Old assets NPV of project Total assets Equity Debt \& Equity New share price Shares to issue c) Balance Sheet Cash Old assets NPV of project Total assets Equity Debt \& Equity Total shares outstanding Share price d) PV of earnings increase Balance Sheet Old assets PV of project Equity Total assets Debt \& Equity 4) a) Value of levered company b) Balance Sheet Value unlevered Debt Tax shield value Equity Total assets Debt \& Equity Stock price 5) Assuming that there are 100 shares outstanding, each share is worth $5=$500/100. Next, imagine the company announces that, in the near future, it will issue $200 of debt to buy back $200 of stock. We know from our previous discussion that the value of the firm will rise to reflect the tax shield of debt. If we assume that capital markets efficiently price securities, the increase occurs immediately. That is, the rise occurs on the day of the announcement, not on the date of the debt-for-equity exchange. The market value balance sheet now becomes: Note that the debt has not yet been issued. Therefore, only equity appears on the right-hand side of the balance sheet. Each share is now worth $570/100=$5.70, implying that the stockholders have benefited by $70. The equityholders gain because they are the owners of a firm that has improved its financial policy. The introduction of the tax shield to the balance sheet is perplexing to many students. Although physical assets are tangible, the ethereal nature of the tax shield bothers these students. However, remember that an asset is any item with value. The tax shield has value because it reduces the stream of future taxes. The fact that one cannot touch the shield in the way that one can touch a physical asset is a philosophical, not financial, consideration. At some point, the exchange of debt for equity occurs. Debt of $200 is issued, and the proceeds are used to buy back shares. How many shares of stock are repurchased? Because shares are now selling at $5.70 each, the number of shares that the firm acquires is $200/$5.70=35.09. This leaves 64.91(=10035.09) shares of stock outstanding. The market value balance sheet is now: Each share of stock is worth $370/64.91=$5.70 after the exchange. Notice that the stock price does not change on the exchange date. As we mentioned above, the stock price moves on the date of the announcement only. Because the shareholders participating in the exchange receive a price equal to the market price per share after the exchange, they do not care whether they exchange their stock or not. This example was provided for two reasons. First, it shows that an increase in the value of the firm from debt financing leads to an increase in the price of the stock. In fact, the Capital Structure and Dividend Policy stockholders capture the entire $70 tax shield. Second, we wanted to provide more work with market value balance sheets. A summary of the main results of Modigliani-Miller with corporate taxes is presented in the following boxed section. Chapter 14 Stephenson Real Estate Recapitalization Input area: Directions: Work the Stephenson Real Estate Recapitaliation Case on page 450 . 2) Assets Total assets Equity Debt \& Equity 3) a) Perpetual aftertax earnings NPV of purchase b) Balance Sheet Old assets NPV of project Total assets Equity Debt \& Equity New share price Shares to issue c) Balance Sheet Cash Old assets NPV of project Total assets Equity Debt \& Equity Total shares outstanding Share price d) PV of earnings increase Balance Sheet Old assets PV of project Equity Total assets Debt \& Equity 4) a) Value of levered company b) Balance Sheet Value unlevered Debt Tax shield value Equity Total assets Debt \& Equity Stock price 5) Assuming that there are 100 shares outstanding, each share is worth $5=$500/100. Next, imagine the company announces that, in the near future, it will issue $200 of debt to buy back $200 of stock. We know from our previous discussion that the value of the firm will rise to reflect the tax shield of debt. If we assume that capital markets efficiently price securities, the increase occurs immediately. That is, the rise occurs on the day of the announcement, not on the date of the debt-for-equity exchange. The market value balance sheet now becomes: Note that the debt has not yet been issued. Therefore, only equity appears on the right-hand side of the balance sheet. Each share is now worth $570/100=$5.70, implying that the stockholders have benefited by $70. The equityholders gain because they are the owners of a firm that has improved its financial policy. The introduction of the tax shield to the balance sheet is perplexing to many students. Although physical assets are tangible, the ethereal nature of the tax shield bothers these students. However, remember that an asset is any item with value. The tax shield has value because it reduces the stream of future taxes. The fact that one cannot touch the shield in the way that one can touch a physical asset is a philosophical, not financial, consideration. At some point, the exchange of debt for equity occurs. Debt of $200 is issued, and the proceeds are used to buy back shares. How many shares of stock are repurchased? Because shares are now selling at $5.70 each, the number of shares that the firm acquires is $200/$5.70=35.09. This leaves 64.91(=10035.09) shares of stock outstanding. The market value balance sheet is now: Each share of stock is worth $370/64.91=$5.70 after the exchange. Notice that the stock price does not change on the exchange date. As we mentioned above, the stock price moves on the date of the announcement only. Because the shareholders participating in the exchange receive a price equal to the market price per share after the exchange, they do not care whether they exchange their stock or not. This example was provided for two reasons. First, it shows that an increase in the value of the firm from debt financing leads to an increase in the price of the stock. In fact, the Capital Structure and Dividend Policy stockholders capture the entire $70 tax shield. Second, we wanted to provide more work with market value balance sheets. A summary of the main results of Modigliani-Miller with corporate taxes is presented in the following boxed