This spreadsheet allows you to compute the optimal capital structure for a non-financial
| PRELIMINARY STUFF AND INPUTS | ||||
| Objective | This spreadsheet allows you to compute the optimal capital structure for a non-financial | |||
| service firm | ||||
| Before you start | Open preferences in excel, go into calculation options and put a check in the iteration box. | |||
| If it is already checked, leave it as is. | ||||
| Inputs | The inputs are primarily in the input sheet. If your company has operating leases, | |||
| use the operating lease worksheet to enter your lease or rental commitments. | ||||
| Units | Enter all numbers in the same units (000s, millions or even billions) | |||
| Income inputs | The key income input is the earnings before long term interest expenses and depreciation. | |||
| Enter the most updated numbers you have for each (even if they are 12-month trailing | ||||
| numbers). If the most recent period for which you have data has an operating income that | ||||
| is abnormal, either because of extraordinary losses/gains or some other occurrence, use | ||||
| an average operating income over the last few years. | ||||
| Balance Sheet | Enter the book value of total debt. If you have a market value enter that | |||
| number. Alternatively, input the average maturity of the debt and I will estimate the | ||||
| market value of debt. | ||||
| Market Data | Enter the current stock price, the current risk free rate, the equity risk | |||
| premium you would like to use to estimate your cost of equity and the current rating for | ||||
| your firm. If you do not have a rating, there is an option for you at the very bottom of | ||||
| the spreadsheet to compute a synthetic rating. | ||||
| Tax Rate | Enter a marginal tax rate, if you can find it. Otherwise, use the marginal tax rate of country | |||
| Default Spreads | This spreadsheet has interest coverage ratios, ratings and default spreads built into it in | |||
| the worksheet. You can choose between two tables, one for large and stable | ||||
| firms, and the other for small or risky firms. If you want you can change the interest | ||||
| coverage ratios and ratings in these tables. | ||||
| READING THE OUTPUT | ||||
| Summary | The summary provides a picture of your firm’s current cost of capital and debt ratio, and | |||
| compares it to your firm’s optimal debt ratio and the cost of capital at that level. The | ||||
| firm value is computed at each debt ratio, based upon how the expected operating income | ||||
| and the cost of capital. The optimal debt ratio is that ratio at which firm value is | ||||
| maximized. It might not be the same point at which cost of capital is minimized. | ||||
| Details | The details of the calculation at each debt ratio are below the summary. | |||
| References | ||||
| Corporate Finance: Theory and Practice, Chapter 18 | ||||
| Applied Corporate Finance: Chapter 8 | ||||
Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"
