CFIN6
Spreadsheet Problem Solution
Chapter 13
a. – c.
INPUT DATA:
KEY OUTPUTS:
Previous dividend:
$3.00
Expected payout:
63.16%
Proposed dividend:
$3.00
WACC before break:
10.67%
Shares outstanding:
3,000,000
WACC after break:
10.96%
Earnings:
$14,250,000
Break point with $3 div:
$9,545,455
Current stock price:
Break point with $0 div:
$25,909,091
Debt ratio:
45.00%
Cost of new debt, rd:
11.00%
Optimal capital budget:
Net price new stock
Project
Cost
Cost of equity, rs
14.00%
$15,000,000
Tax rate:
40.00%
Total capital budget
$15,000,000
Project
Cost
IRR
1
$15,000,000
18.00%
2
$6,000,000
10.40%
Total
$21,000,000
MODEL-GENERATED DATA:
With proposed dividend:
Dividend
(9,000,000)
Retained earnings
Borrow
Financing before break
Earnings
Marginal cost of capital up to break point:
Weighted
Component
Percent
Cost
55%
0.1400
0.0770
Debt
45%
0.0297
0.1067
CFIN6
Marginal cost of capital above break point:
Weighted
Debt
Component
Percent
Cost
Ybor’s capital budget should be $15 million, because only the growth project’s return exceeds the marginal
cost of capital.
maintenance of the current dividend, this would be better than cutting the dividend.
An announcement of the firm’s new growth prospects should accompany any announcement of the gradual
reduction in dividend payout ratio. The $3.00 current dividend should be maintained this year, but it should
not be increased as fast as earnings so as to lower the payout ratio.
d. Generally, new areas of business involve greater-than-average risk as well as greater-than-average
expected returns. The new growth aspects of Ybor City Tobacco might cause investors to require a higher
return on their invested capital as compensation for the greater risk. Lenders might balk at lending large
amounts to a highly leveraged firm, such as Ybor, as it enters into new markets and product lines. Together,
these factors could increase Ybor’s cost of capital and might reduce the firm’s optimum debt/assets ratio.
CFIN6
INPUT DATA:
KEY OUTPUTS:
Previous dividend:
$3.00
Proposed dividend:
$3.00
WACC before break:
10.12%
Shares outstanding:
3,000,000
WACC after break:
10.33%
Earnings:
Break point with $3 div:
$13,125,000
Current stock price:
$56.25
Break point with $0 div:
$35,625,000
Debt ratio:
60.00%
Expected payout:
63.16%
Cost of new debt, rd:
12.00%
Optimal capital budget:
Net price new stock
$51.25
IRR
Cost
Cost of equity, rs
14.50%
18.00%
$15,000,000
Tax rate:
40.00%
10.40%
$6,000,000
Total capital budget
$21,000,000
Project
Cost
IRR
1
$15,000,000
18.00%
2
10.40%
$21,000,000
Total
$21,000,000
MODEL-GENERATED DATA:
With proposed dividend:
Earnings
Dividend
(9,000,000)
Retained earnings
Borrow
Financing before break
13,125,000
Marginal cost of capital up to break point:
Weighted
Component
Percent
Cost
Debt
60%
0.0432
RE
40%
0.0580
0.1012
Marginal cost of capital above break point:
After-Tax
Weighted
Component
Percent
Cost
Cost
Debt
60%
0.0720
0.0432
RE
40%
0.1502
0.0601
0.1033
CFIN6
f. Now, only Project 1 is acceptable.
INPUT DATA:
KEY OUTPUTS:
Previous dividend:
$3.00
Expected payout:
63.16%
Proposed dividend:
$3.00
WACC before break:
10.72%
Shares outstanding:
3,000,000
Earnings:
Break point with $3 div:
$13,125,000
Current stock price:
Break point with $0 div:
$35,625,000
Debt ratio:
60.00%
Cost of new debt, rd:
12.00%
Optimal capital budget:
Net price new stock
Project
Cost
Cost of equity, rs
16.00%
$15,000,000
Tax rate:
40.00%
WACC after break:
10.93%
Total capital budget
$15,000,000
Project
Cost
IRR
1
$15,000,000
18.00%
2
$6,000,000
10.40%
Total
$21,000,000
MODEL-GENERATED DATA:
With proposed dividend:
Dividend
(9,000,000)
Retained earnings
Borrow
Financing before break
13,125,000
Earnings
Marginal cost of capital up to break point:
Weighted
Component
Percent
Cost
Debt
0.0432
0.0640
0.1072
CFIN6
Marginal cost of capital above break point:
Weighted
Component
Percent
Cost
Debt
60%
0.0432
RE
40%
0.1652
0.0661
0.1093
g. Project 1 still is the only acceptable project.
INPUT DATA:
KEY OUTPUTS:
Previous dividend:
$3.00
Expected payout:
0.00%
Proposed dividend:
$0.00
WACC before break:
10.72%
Shares outstanding:
3,000,000
WACC after break:
10.72%
Earnings:
Break point with $3 div:
$35,625,000
Current stock price:
Break point with $0 div:
$35,625,000
Debt ratio:
60.00%
Cost of new debt, rd:
12.00%
Optimal capital budget:
Net price new stock
Project
Cost of equity, rs
16.00%
$15,000,000
Tax rate:
40.00%
Total capital budget
$15,000,000
Project
Cost
IRR
1
$15,000,000
18.00%
2
10.40%
$21,000,000
Total
$21,000,000
MODEL-GENERATED DATA:
With proposed dividend:
Earnings
Dividend
Retained earnings
14,250,000
Borrow
21,375,000
Financing before break
35,625,000
Marginal cost of capital up to break point:
Weighted
Component
Percent
Cost
Debt
60%
0.0432
RE
40%
0.0640
0.1072
CFIN6
Marginal cost of capital above break point:
Weighted
Component
Percent
Cost
Debt
60%
0.0432
40%
0.1600
0.0640
0.1072