Need help with the problem being done in Excel

4. What remains to be seen however, is whether shareholders are better or worse off with more leverage. Problem 2 does not tell us, because there we computed total value of equity, and shareholders care about value per share. Ordinarily, total value will be a good proxy for what is happening to the price per share, but in the case of a relevering firm, that may not be true. Implicitly we assumed that, as our firm in problems 1-3 levered up, it was repurchasing stock on the open market (you will note that EBIT did not change, so management was clearly not investing the proceeds from the loans in cash-generating assets). We held EBIT constant so that we could see clearly the effect of financial changes without getting them mixed up in the effects of investments. The point is that, as the firm borrows and repurchases shares, the total value of equity may decline, but the price per share may rise. Now, solving for the price per share may seem impossible, because we are dealing with two unknowns-share price and change in the number of shares: Share Price = Total Market Value of Equity (Original - Repurchased Shares Shares) But by rewriting the equation, we can put it in a form that can be solved: Share Total Market Cash Price = Value of Equity + Paid Out # Original Shares Referring to the results of problem 2, let's assume that all the new debt is equal to the cash paid to repurchase shares. Please complete the following table: 0% Debt/ 100% Equity 25% Debt/ 75% Equity 50% Debt/ 50% Equity 1000 1000 1000 Total Market Value of Equity (from Prob. 2) (this is like E1) Cash Paid Out (to buy back shares) (this is like DI) # Original Shares Total Value Per Share Number of shares repurchased Number of shares outstanding 4. What remains to be seen however, is whether shareholders are better or worse off with more leverage. Problem 2 does not tell us, because there we computed total value of equity, and shareholders care about value per share. Ordinarily, total value will be a good proxy for what is happening to the price per share, but in the case of a relevering firm, that may not be true. Implicitly we assumed that, as our firm in problems 1-3 levered up, it was repurchasing stock on the open market (you will note that EBIT did not change, so management was clearly not investing the proceeds from the loans in cash-generating assets). We held EBIT constant so that we could see clearly the effect of financial changes without getting them mixed up in the effects of investments. The point is that, as the firm borrows and repurchases shares, the total value of equity may decline, but the price per share may rise. Now, solving for the price per share may seem impossible, because we are dealing with two unknowns-share price and change in the number of shares: Share Price = Total Market Value of Equity (Original - Repurchased Shares Shares) But by rewriting the equation, we can put it in a form that can be solved: Share Total Market Cash Price = Value of Equity + Paid Out # Original Shares Referring to the results of problem 2, let's assume that all the new debt is equal to the cash paid to repurchase shares. Please complete the following table: 0% Debt/ 100% Equity 25% Debt/ 75% Equity 50% Debt/ 50% Equity 1000 1000 1000 Total Market Value of Equity (from Prob. 2) (this is like E1) Cash Paid Out (to buy back shares) (this is like DI) # Original Shares Total Value Per Share Number of shares repurchased Number of shares outstanding