excel finance project
Saud_ar
Project3Template1.xlsxQ1&Q2
| Wm. Wrigley Jr. Company, Inc. | ||||
| (values are in thousands) | ||||
| Before | Step One: | Alternatives for Step Two: | ||
| Recapitalization | Changes | Releveraging | Dividend | Share Repurchase |
| Market Value Balance Sheets | ||||
| Long-term debt | ||||
| Deferred taxes, other noncurrent liabilities | ||||
| Common equity | ||||
| Total capital | ||||
| Number of shares | ||||
| Price per share | ||||
| Market value of equity divided by total capital | ||||
Q3&Q4
| Prerecap | |||||
| Prerecap stock price | $56.37 | Total equity (mkt.) | $13,103 | ||
| Prerecap shares outstanding | Equity/ total cap. | ||||
| Beta | Debt/ total cap. | ||||
| Long-term debt (LTD) | Tax rate | ||||
| Corporate AAA | Ke (CAPM) | ||||
| 20-year Treasury | |||||
| Risk premium | WACC: | ||||
| (pre-recap) | |||||
| Post-Recap | |||||
| Wrigley | Equity | LTD | Equity/ Cap. | Debt/ Cap. | Debt/ Equity |
| Book value | |||||
| Market value (MV) | |||||
| MV (repurch. & tax shield) | |||||
| Target Capital Structure | Wrigley | ||||
| Unlevered beta | |||||
| Relevered beta | MV (repurch & tax shield) | ||||
| Levered Beta | Equity | LTD | Equity/ Cap | Debt/ Cap | |
| Prerecap (MV equity) | |||||
| Post-recap (MV repurch.& tax shield) | |||||
| Pretax Kd | After-Tax Kd | Wrigley Ke | Wrigley WACC | ||
| Prerecap (MV equity) | |||||
| Post-recap (MV repurch. & tax shield) | |||||
Q5-Q6
| Status Quo |
| Operating income (EBIT) |
| Interest expense |
| Taxable income |
| Taxes (@ 40%) |
| Net income |
| Shares outstanding |
| EPS (status quo) |
| Recapitalization with Dividend |
| Operating income (EBIT) |
| Interest expense |
| Taxable income |
| Taxes (@ 40%) |
| Net income |
| Shares outstanding |
| EPS (recapitalization with dividend) |
| Recapitalization with Share Repurchase |
| Operating income (EBIT) |
| Interest expense |
| Taxable income |
| Taxes (@ 40%) |
| Net income |
| Shares outstanding |
| EPS (recap. with share repurchase) |
