attached is a finance mini case with all the answers and data by the instructor. I just need help making the formulas and data of each question answered in an excel spreadsheet.

MINI CASE Assume you have just been hired as a business manager of PizzaPalace, a regional pizza restaurant chain. The company's EBIT was $50 million last year and is not expected to grow. The firm is currently financed with all equity and it has 10 million shares outstanding. When you took your corporate finance course, *your instructor stated that most firms' owners would be financially better off if the firms used some debt. When you suggested this to your new boss, he encouraged you to pursue the idea. As a first step, assume that you obtained from the firm's investment banker the following estimated costs of debt for the firm at different capital structures: % Financed With Debt 0% 20 30 40 50 * with the Legendary Dr. Torres. rd --8.0% 8.5 10.0 12.0 If the company were to recapitalize, debt would be issued, and the funds received would be used to repurchase stock. PizzaPalace is in the 40 percent state-plus-federal corporate tax bracket, its beta is 1.0, the risk-free rate is 6 percent, and the market risk premium is 6 percent. a. Using the free cash flow valuation model, show the only avenues by which capital structure can affect value. Answer: The basic definitions are: (1) V = Value of Firm (2) FCF = Free Cash Flow (3) WACC = Weighted Average Cost Of Capital (4) rs And rd are costs of stock and debt (5) ws and wd are percentages of the firm that are financed with stock and debt. VOP T FCFT 1 WACC T The impact of capital structure on value depends upon the effect of debt on: WACC and/or FCF. b. (1) What is business risk? What factors influence a firm's business risk? Answer: Business risk is uncertainty about EBIT. Factors that influence business risk include: uncertainty about demand (unit sales); uncertainty about output prices; uncertainty about input costs; product and other types of liability; degree of operating leverage (DOL). b. (2) What is operating leverage, and how does it affect a firm's business risk? Show the operating break even point if a company has fixed costs of $200, a sales price of $15, and variables costs of $10. Answer: Operating leverage is the change in EBIT caused by a change in quantity sold. The higher the proportion of fixed costs within a firm's overall cost structure, the greater the operating leverage. Higher operating leverage leads to more business risk, because a small sales decline causes a larger EBIT decline. Q is quantity sold, F is fixed cost, V is variable cost, TC is total cost, and P is price per unit. Operating Breakeven = QBE QBE = F / (P - V) Example: F=$200, P=$15, AND V=$10: QBE = $200 / ($15 - $10) = 40. c. Now, to develop an example which can be presented to PizzaPalace's management to illustrate the effects of financial leverage, consider two hypothetical firms: Firm U, which uses no debt financing, and Firm L, which uses $10,000 of 12 percent debt. Both firms have $20,000 in assets, a 40 percent tax rate, and an expected EBIT of $3,000. leverage = palanca 1. Construct partial income statements, which start with EBIT, for the two firms. Answer: Here are the fully completed statements: c. Assets Equity Firm U $20,000 $20,000 Firm L $20,000 $10,000 EBIT INT (12%) EBT Taxes (40%) NI $ 3,000 0 $ 3,000 1,200 $ 1,800 $ 3,000 1,200 $ 1,800 720 $ 1,080 Firm U 15.0% 9.0% 9.0% Firm L 15.0% 11.4% 10.8% 2.5 2. Now calculate ROE for both firms. Answer: BEP ROI ROE TIE c. 3. What does this example illustrate about the impact of financial leverage on ROE? Answer: Conclusions from the analysis: The firm's basic earning power, BEP = EBIT/total assets, is unaffected by financial leverage. Firm L has the higher expected ROI because of the tax savings effect: o ROIU = 9.0%. o ROIL = 11.4%. Firm L has the higher expected ROE: o ROEU = 9.0%. o ROEL = 10.8%. Therefore, the use of financial leverage has increased the expected profitability to shareholders. The higher roe results in part from the tax savings and also because the stock is riskier if the firm uses debt. At the expected level of EBIT, ROEL > ROEU. The use of debt will increase roe only if ROA exceeds the after-tax cost of debt. Here ROA = unleveraged roe = 9.0% > rd(1 - t) = 12%(0.6) = 7.2%, so the use of debt raises roe. Finally, note that the TIE ratio is huge (undefined, or infinitely large) if no debt is used, but it is relatively low if 50 percent debt is used. The expected tie would be larger than 2.5 if less debt were used, but smaller if leverage were increased. d. Explain the difference between financial risk and business risk. Answer: Business risk increases the uncertainty in future EBIT. It depends on business factors such as competition, operating leverage, etc. Financial risk is the additional business risk concentrated on common stockholders when financial leverage is used. It depends on the amount of debt and preferred stock financing. e. What happens to ROE for Firm U and Firm L if EBIT falls to $2,000? What does this imply about the impact of leverage on risk and return? Answer: EBIT Interest EBT Taxes NI ROIC ROE Firm U $2,000 $0 $2,000 $800 $1,200 Firm L $2,000 $1,200 $800 $320 $480 6.0% 6.0% 6.0% 4.8% f. What does capital structure theory attempt to do? What lessons can be learned from capital structure theory? Be sure to address the MM models. Answer: MM theory begins with the assumption of zero taxes. MM prove, under a very restrictive set of assumptions, that a firm's value is unaffected by its financing mix: VL = VU. Therefore, capital structure is irrelevant. Any increase in roe resulting from financial leverage is exactly offset by the increase in risk (i.e., rs), so WACC is constant. MM theory later includes corporate taxes. Corporate tax laws favor debt financing over equity financing. With corporate taxes, the benefits of financial leverage exceed the risks because more EBIT goes to investors and less to taxes when leverage is used. MM show that: VL = VU + TD. If T=40%, then every dollar of debt adds 40 cents of extra value to firm. Miller later included personal taxes. Personal taxes lessen the advantage of corporate debt. Corporate taxes favor debt financing since corporations can deduct interest expenses, but personal taxes favor equity financing, since no gain is reported until stock is sold, and long-term gains are taxed at a lower rate. Miller's conclusions with personal taxes are that the use of debt financing remains advantageous, but benefits are less than under only corporate taxes. Firms should still use 100% debt. Note: however, miller argued that in equilibrium, the tax rates of marginal investors would adjust until there was no advantage to debt. MM theory ignores bankruptcy (financial distress) costs, which increase as more leverage is used. At low leverage levels, tax benefits outweigh bankruptcy costs. At high levels, bankruptcy costs outweigh tax benefits. An optimal capital structure exists that balances these costs and benefits. This is the trade-off theory. MM assumed that investors and managers have the same information. But managers often have better information. Thus, they would sell stock if stock is overvalued, and sell bonds if stock is undervalued. Investors understand this, so view new stock sales as a negative signal. This is signaling theory. The pecking order theory states that Firms use internally generated funds first, because there are no flotation costs or negative signals. If more funds are needed, firms then issue debt because it has lower flotation costs than equity and not negative signals. If more funds are needed, firms then issue equity. One agency problem is that managers can use corporate funds for non-value maximizing purposes. The use of financial leverage bonds \"free cash flow,\" and forces discipline on managers to avoid perks and non-value adding acquisitions. A second agency problem is the potential for \"underinvestment\". Debt increases risk of financial distress. Therefore, managers may avoid risky projects even if they have positive NPVs. Firms with many investment opportunities should maintain reserve borrowing capacity, especially if they have problems with asymmetric information (which would cause equity issues to be costly). The market timing theory states that managers try to \"time the market\" when issuing securities. They issue equity when the market is \"high\" and after big stock price run ups. They issue debt when the stock market is \"low\" and when interest rates are \"low.\" They issue short-term debt when the term structure is upward sloping and long-term debt when it is relatively flat. g. What does the empirical evidence say about capital structure theory? What are the implications for managers? Answer: Tax benefits are important. At the optimal capital structure, $1 debt adds about $0.10 to $0.20 to value on average. For the average firm financed with 25% to 30% debt, this adds about 3% to 6% to the total value. Bankruptcies are costly- costs can be up to 10% to 20% of firm value. Firms have targets, but don't make quick corrections when stock price changes cause their debt ratios to change. The average speed of adjustment from the current capital structure to the target capital structure is about 30% per year. The speed is about 50% per year for firms with high cash flow. The speed is about 70% for firms with high cash flow that are above target. The lost value from being above target is bigger than lost value from being below target. When a company is above the target, distress costs rise very rapidly. Sometimes companies will deliberately increase debt to above target to take advantage of unexpected investment opportunity. After big stock price run ups, the debt ratio falls, but firms tend to issue equity instead of debt. This is inconsistent with the trade-off model, inconsistent with the pecking order theory, but is consistent with the windows of opportunity hypothesis. Many firms, especially those with growth options and asymmetric information problems, tend to maintain excess borrowing capacity. Managers should take advantage of tax benefits by issuing debt, especially if the firm has a high tax rate, stable sales, and less operating leverage than the typical firm in its industry. Managers should avoid financial distress costs by maintaining excess borrowing capacity, especially if the firm has volatile sales, high operating leverage, many potential investment opportunities, or special purpose assets (instead of general purpose assets that make good collateral). If a manager has asymmetric information regarding the firm's future prospects, then the manager should avoid issuing equity if actual prospects are better than the market perceives. Managers should always consider the impact of capital structure choices on lenders' and rating agencies' attitudes. h. With the above points in mind, now consider the optimal capital structure for PizzaPalace. h. (1) For each capital structure under consideration, calculate the levered beta, the cost of equity, and the WACC. Answer: MM theory implies that beta changes with leverage. bu is the beta of a firm when it has no debt (the unlevered beta.) Hamada's equation provides the beta of a levered firm: bL = bU [1 + (1 - T)(D/S)]. For example, to find the cost of equity for wd = 20%, we first use Hamada's equation to find beta: b = bU [1 + (1 - T)(D/S)] = 1.0 [1 + (1-0.4) (20% / 80%)] = 1.15 Then use CAPM to find the cost of equity: rs = rRF + b (RPM) = 6% + 1.15 (6%) = 12.9% We can repeat this for the capital structures under consideration. D/S b rs wd 0% 0.00 1.000 12.00% 20% 0.25 1.150 12.90% 30% 0.43 1.257 13.54% 40% 0.67 1.400 14.40% 50% 1.00 1.600 15.60% Next, find the WACC. For example, the WACC for wd = 20% is: WACC = wd (1-T) rd + ws rs WACC = 0.2 (1 - 0.4) (8%) + 0.8 (12.9%) WACC = 11.28% Then repeat this for all capital structures under consideration. wd 0% 20% 30% 40% 50% h. (2) rd 0.0% 8.0% 8.5% 10.0% 12.0% rs 12.00% 12.90% 13.54% 14.40% 15.60% WACC 12.00% 11.28% 11.01% 11.04% 11.40% Now calculate the corporate value. Answer: For example the corporate value for wd = 20% is: V = FCF(1+g) / (WACC-g) Using these values, V = $30(1+0) / (0.1128 0) = $2,65.96 million. Repeating this for all capital structures gives the following table: wd WACC 0% 12.00% 20% 11.28% 30% 11.01% 40% 11.04% 50% 11.40% Corp. Value $250.0000 $265.9574 $272.4796 $271.7391 $263.1579 As this shows, value is maximized at a capital structure with 30% debt. Debt = wd Vop and S = ws Vop: wd Vop D S 0% $250.00 $0.00 $250.00 20% $265.96 $53.19 $212.77 30% $272.48 $81.74 $190.74 40% 50% $271.74 $263.16 $108.70 $131.58 $163.04 $131.58 i. Describe the recapitalization process and apply it to PizzaPalace. Calculate the resulting the value of the debt that will be issued, the resulting market value of equity, the price per share, the number of shares repurchased, and the remaining shares. Considering only the capital structures under analysis, what is PizzaPalace's optimal capital structure? Answer: First, find the dollar value of debt. For example, for wd = 20%, the dollar value of debt is: d = wd V = 0.2 ($2,659,574) = $53.19. We can then find the dollar value of equity: S=V-D S = $265.9574 - $53.1915 = $212.7659. We repeat this process for all the capital structures. wd Debt, D 0% $ 0 20% $ 53.1915 30% $ 81.7439 40% $108.6957 50% $131.5789 Note: these are rounded; Excel full calculations. The situation before the recap is: Vop + ST Inv. VTotal Debt S n P Before Debt $250 0 $250 0 $250 10 $25.00 S Cash distr. Wealth $250 0 $250 The stock price is $25 and the total wealth of shareholders is $2,500,000. Now consider the situation if the firm moves to a capital structure with wd = 20% by issuing $53.1915 in debt but has not yet repurchased equity. The firm's value of operations increases because its WACC decreases. The firm also temporarily has $531,915 in short-term investments. Vop + ST Inv. VTotal Debt S n P Before Debt $250 0 $250 0 $250 10 $25.00 After Debt, Before Rep. $265.9574 53.1915 $319.1489 53.1915 $265.9574 10 $26.60 S Cash distr. Wealth $250 0 $250 $265.9574 0 $265.9574 Notice that the stock price increases and the wealth of shareholders increases. The repurchase itself will not change the stock price. If investors thought that the repurchase would increase the stock price, they would all purchase stock the day before, which would drive up its price. If investors thought that the repurchase would decrease the stock price, they would all sell short the stock the day before, which would drive down the stock price. The number of shares repurchased is: # repurchased = (D - D0) / P # rep. = ($53.1915 - 0) / $26.596 = 2. The number of remaining shares after the repurchase is: # remaining = n0 - # rep. n = 10 - 2 = 8. Vop + ST Inv. VTotal Debt S n P Before Debt $250 0 $250 0 $250 10 $25.00 After Debt, Before Rep. $265.9574 53.1915 $319.1489 53.1915 $265.9574 10 $26.60 After Rep. $265.9574 0 $265.9574 53.1915 $212.7660 8 $26.60 S Cash distr. Wealth $250 0 $250 $265.9574 0 $265.9574 $212.7660 53.1915 $265.9574 Notice that the value of the equity declines as more debt is issued, because debt is used to repurchase stock. But the total wealth of shareholders is the value of stock after the recap plus the cash received in repurchase, and this total is not changed by the repurchase. There are some shortcuts we can take to find the values of S, P, and n after the repurchase: S = (1 - wd) Vop nPost VopNew D New = nPrior VopNew D Old PPost = VopNew D Old n Pr ior We apply these relationships for each possible capital structure: wd rd ws b rs WACC Vop D S n P 0% 0.0% 100% 1.000 12.00% 12.00% $250.00 $0.00 $250.00 10 $25.00 20% 8.0% 80% 1.150 12.90% 11.28% $265.96 $53.19 $212.77 8 $26.60 30% 8.5% 70% 1.257 13.54% 11.01% $272.48 $81.74 $190.74 7 $27.25 40% 50% 10.0% 12.0% 60% 50% 1.400 1.600 14.40% 15.60% 11.04% 11.40% $271.74 $263.16 $108.70 $131.58 $163.04 $131.58 6 5 $27.17 $26.32 The optimal capital structure is for wd = 30%. This gives the highest corporate value, the lowest WACC, and the highest stock price per share. But notice that wd = 40% is very similar to the optimal solution; in other words, the optimal range is pretty flat