ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
PRELIMINARY STUFF AND INPUTS
2
Objective
This spreadsheet allows you to compute the optimal capital structure for a non-financial
3
service firm
4
Before you start
Open preferences in excel, go into calculation options and put a check in the iteration box.
5
If it is already checked, leave it as is.
6
Inputs
The inputs are primarily in the input sheet. If your company has operating leases,
7
use the operating lease worksheet to enter your lease or rental commitments.
8
Units
Enter all numbers in the same units (000s, millions or even billions)
9
Income inputs
The key income input is the earnings before long term interest expenses and depreciation.
10
Enter the most updated numbers you have for each (even if they are 12-month trailing
11
numbers). If the most recent period for which you have data has an operating income that
12
is abnormal, either because of extraordinary losses/gains or some other occurrence, use
13
an average operating income over the last few years.
14
Balance Sheet
Enter the book value of total debt. If you have a market value enter that
15
number. Alternatively, input the average maturity of the debt and I will estimate the
16
market value of debt.
17
Market Data
Enter the current stock price, the current risk free rate, the equity risk
18
premium you would like to use to estimate your cost of equity and the current rating for
19
your firm. If you do not have a rating, there is an option for you at the very bottom of
20
the spreadsheet to compute a synthetic rating.
21
Tax Rate
Enter a marginal tax rate, if you can find it. Otherwise, use the marginal tax rate of country
22
Default Spreads
This spreadsheet has interest coverage ratios, ratings and default spreads built into it in
23
the worksheet. You can choose between two tables, one for large and stable
24
firms, and the other for small or risky firms. If you want you can change the interest
25
coverage ratios and ratings in these tables.
26
READING THE OUTPUT
27
Summary
The summary provides a picture of your firm's current cost of capital and debt ratio, and
28
compares it to your firm's optimal debt ratio and the cost of capital at that level. The
29
firm value is computed at each debt ratio, based upon how the expected operating income
30
and the cost of capital. The optimal debt ratio is that ratio at which firm value is
31
maximized. It might not be the same point at which cost of capital is minimized.
32
Details
The details of the calculation at each debt ratio are below the summary.
33
34
References
35
Corporate Finance: Theory and Practice, Chapter 18
36
Applied Corporate Finance: Chapter 8
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100