CFIN6
Spreadsheet Problem Solution
Chapter 11
a. There are three breaks in the MCC schedule. These breaks occur as follows:
Break #1 (New debt—9%): $500,000/0.45 = $1,111,111
Break #1 is caused by exhausting the 9 percent debt, Break #2 is caused by using up retained earnings in
financing needs, and Break #3 is caused by exhausting the 11 percent debt.
b. & c. Projects 1, 2, and 3 should be purchased.
INPUT DATA:
KEY OUTPUT:
Debt ratio:
45.00%
Ret. earnings break
1,818,182
Earnings:
$2,500,000
1st debt break
1,111,111
Dividend payout ratio:
60.00%
2nd debt break
2,000,000
Tax rate:
40.00%
Current Stock Price (P0):
WACC before break 1
11.0%
Last dividend paid (D0):
WACC before break 2
11.5%
Growth rate:
5.00%
WACC before break 3
12.1%
Equity flotation cost:
10.00%
WACC after break 3
12.7%
Accepted
Beginning of
Projects
Project
New debt cost:
Range
rd
(non-zero)
IRR
Cost
1
9.00%
1
16.0%
$675,000
500,001
11.00%
2
15.0%
$900,000
900,001
13.00%
3
14.0%
$375,000
Capital budget =
$1,950,000
Project
Number/rank
Cost
IRR
IRR > WACC?
Total cost
WACC
1
675,000
16.00%
YES
675,000
11.0%
2
900,000
15.00%
YES
11.5%
3
375,000
14.00%
YES
12.1%
4
562,500
12.7%
5
750,000
11.00%
12.7%
CFIN6
MODEL-GENERATED DATA:
Breaks in the MCC schedule:
Use of retained earnings
Use of debt at:
9%
1,111,111
Use of debt at:
2,000,000
1,818,182
Cost of financing below first break:
After-tax
Weighted
Component
Weight
Cost
Cost
Debt
0.45
5.40%
2.43%
Equity
0.55
15.50%
8.53%
WACC 1 =
10.96%
Cost of financing between first and second breaks:
After-tax
Weighted
Component
Weight
Cost
Cost
Debt
0.45
6.60%
2.97%
Equity
0.55
15.50%
8.53%
WACC 2 =
11.50%
Cost of financing between second and third breaks:
After-tax
Weighted
Component
Weight
Cost
Cost
Debt
0.45
6.60%
2.97%
Equity
0.55
16.67%
9.17%
WACC 3 =
12.14%
After-tax
Weighted
Component
Weight
Cost
Cost
Debt
0.45
7.80%
3.51%
Equity
0.55
16.67%
9.17%
WACC 4 =
12.68%
e(1). If the dividend payout ratio is 0%, Projects 1, 2, 3, and 4 are acceptable investments.
INPUT DATA:
KEY OUTPUT:
Debt ratio:
45.00%
Ret. earnings break
4,545,455
Earnings:
$2,500,000
1st debt break
1,111,111
Dividend payout ratio:
0.00%
2nd debt break
2,000,000
Tax rate:
Current Stock Price (P0):
WACC before break 1
11.0%
Last dividend paid (D0):
$2.20
WACC before break 2
11.5%
Growth rate:
5.00%
WACC before break 3
12.0%
Equity flotation cost:
10.00%
WACC after break 3
12.7%
40.00%
Accepted
Beginning of
Projects
Project
New debt cost:
Range
rd
(non-zero)
IRR
Cost
1
9.00%
1
16.0%
$675,000
500,001
11.00%
2
15.0%
$900,000
900,001
13.00%
3
14.0%
$375,000
4
12.5%
$562,500
Capital budget =
$2,512,500
Project
Number/rank
Cost
IRR
IRR > WACC?
Total cost
WACC
1
675,000
16.00%
YES
675,000
11.0%
2
900,000
15.00%
YES
11.5%
3
375,000
14.00%
YES
11.5%
4
562,500
12.50%
YES
12.0%
5
750,000
11.00%
12.0%
e(2). If the dividend payout ratio is 100 percent, Projects 1, 2, and 3 are acceptable investments.
INPUT DATA:
KEY OUTPUT:
Debt ratio:
45.00%
Ret. earnings break
0
Earnings:
$2,500,000
1st debt break
1,111,111
Dividend payout ratio:
2nd debt break
2,000,000
Tax rate:
40.00%
Current Stock Price (P0):
WACC before break 1
11.0%
Last dividend paid (D0):
$2.20
WACC before break 2
11.6%
CFIN6
500,001
12.00%
2
15.0%
$900,000
900,001
14.00%
3
14.0%
$375,000
4
12.5%
$562,500
Capital budget =
$2,512,500
Equity flotation cost:
10.00%
WACC after break 3
12.7%
Accepted
Beginning of
Projects
Project
New debt cost:
Range
rd
(non-zero)
IRR
Cost
500,001
11.00%
2
15.0%
$900,000
900,001
13.00%
3
14.0%
$375,000
1
9.00%
1
16.0%
$675,000
Capital budget =
$1,950,000
Project
Number/rank
Cost
IRR
IRR > WACC?
Total cost
WACC
1
675,000
16.00%
YES
675,000
11.6%
2
900,000
15.00%
YES
12.1%
3
375,000
14.00%
YES
12.1%
4
562,500
12.50%
12.7%
5
750,000
11.00%
12.7%
f. Under the new conditions, Projects 1, 2, 3, and 4 are acceptable investments.
INPUT DATA:
KEY OUTPUT:
Debt ratio:
65.00%
Ret. earnings break
2,857,143
Earnings:
$2,500,000
1st debt break
Dividend payout ratio:
60.00%
2nd debt break
1,384,615
Tax rate:
40.00%
Current Stock Price (P0):
WACC before break 1
Last dividend paid (D0):
$2.20
WACC before break 2
10.5%
Growth rate:
6.00%
WACC before break 3
11.3%
Equity flotation cost:
10.00%
WACC after break 3
11.7%
Accepted
Beginning of
Projects
Project
New debt cost:
Range
rd
(non-zero)
IRR
Cost
1
10.00%
1
16.0%
$675,000
CFIN6
Project
Number/rank
Cost
IRR
IRR > WACC?
Total cost
WACC
1
675,000
16.00%
YES
675,000
9.7%
2
900,000
15.00%
YES
1,575,000
11.3%
3
375,000
14.00%
YES
1,950,000
11.3%
4
562,500
12.50%
YES
2,512,500
11.3%
5
750,000
11.00%
3,262,500
11.7%
g(1). If the tax rate drops to 20 percent, Projects 1, 2, and 3 are acceptable.
INPUT DATA:
KEY OUTPUT:
Debt ratio:
65.00%
Ret. earnings break
2,857,143
Earnings:
$2,500,000
1st debt break
769,231
Dividend payout ratio:
60.00%
2nd debt break
1,384,615
Tax rate:
20.00%
Current Stock Price (P0):
WACC before break 1
11.0%
Last dividend paid (D0):
$2.20
WACC before break 2
12.1%
Growth rate:
WACC before break 3
13.1%
Equity flotation cost:
10.00%
WACC after break 3
13.5%
Accepted
Beginning of
Projects
Project
New debt cost:
Range
rd
(non-zero)
IRR
Cost
500,001
12.00%
2
15.0%
$900,000
900,001
14.00%
3
14.0%
$375,000
1
10.00%
1
16.0%
$675,000
Capital budget =
$1,950,000
Project
Number/rank
Cost
IRR
IRR > WACC?
Total cost
WACC
1
675,000
16.00%
YES
675,000
11.0%
2
900,000
15.00%
YES
1,575,000
13.1%
3
375,000
14.00%
YES
1,950,000
13.1%
4
562,500
12.50%
2,512,500
13.1%
5
750,000
11.00%
3,262,500
13.5%
CFIN6
g(2). If the tax rate drops to 0 percent, Projects 1 and 2 are acceptable.
INPUT DATA:
KEY OUTPUT:
Debt ratio:
65.00%
Ret. earnings break
2,857,143
Earnings:
$2,500,000
1st debt break
769,231
Dividend payout ratio:
60.00%
2nd debt break
1,384,615
Tax rate:
0.00%
Current Stock Price (P0):
WACC before break 1
12.3%
Last dividend paid (D0):
$2.20
WACC before break 2
13.6%
Growth rate:
6.00%
WACC before break 3
14.9%
Equity flotation cost:
10.00%
WACC after break 3
15.3%
Accepted
Beginning of
Projects
Project
New debt cost:
Range
rd
(non-zero)
IRR
Cost
1
10.00%
1
16.0%
$675,000
500,001
12.00%
2
15.0%
$900,000
900,001
14.00%
Capital budget =
$1,575,000
Project
Number/rank
Cost
IRR
IRR > WACC?
Total cost
WACC
1
675,000
16.00%
YES
675,000
12.3%
2
900,000
15.00%
YES
14.9%
3
375,000
14.00%
14.9%
4
562,500
12.50%
14.9%
5
750,000
11.00%
15.3%
CFIN6
h(1). If sales are $3.25 million, Projects 1, 2, and 3 are acceptable.
INPUT DATA:
KEY OUTPUT:
Debt ratio:
45.00%
Ret. earnings break
2,363,636
Earnings:
$3,250,000
1st debt break
1,111,111
Dividend payout ratio:
60.00%
2nd debt break
2,000,000
Tax rate:
40.00%
Current Stock Price (P0):
$22.00
WACC before break 1
11.0%
Last dividend paid (D0):
$2.20
WACC before break 2
11.5%
Growth rate:
5.00%
WACC before break 3
12.0%
Equity flotation cost:
10.00%
WACC after break 3
12.7%
Accepted
Beginning of
Projects
Project
New debt cost:
Range
rd
(non-zero)
IRR
Cost
1
9.00%
1
16.0%
$675,000
500,001
11.00%
2
15.0%
$900,000
900,001
13.00%
3
14.0%
$375,000
Capital budget =
$1,950,000
Project
Number/rank
Cost
IRR
IRR > WACC?
Total cost
WACC
1
675,000
16.00%
YES
675,000
11.0%
2
900,000
15.00%
YES
11.5%
3
375,000
14.00%
YES
11.5%
4
562,500
12.50%
12.7%
5
750,000
11.00%
12.7%
h(2). If sales are $1 million, Projects 1, 2, and 3 are acceptable.
INPUT DATA:
KEY OUTPUT:
Debt ratio:
45.00%
Ret. earnings break
727,273
Earnings:
Dividend payout ratio:
60.00%
2nd debt break
2,000,000
Tax rate:
40.00%
Current Stock Price (P0):
WACC before break 1
11.0%
Last dividend paid (D0):
$2.20
WACC before break 2
11.6%
Growth rate:
5.00%
WACC before break 3
12.1%
Equity flotation cost:
10.00%
WACC after break 3
12.7%
$1,000,000
1st debt break
1,111,111
Accepted
Beginning of
Projects
Project
New debt cost:
Range
rd
(non-zero)
IRR
Cost
1
9.00%
1
16.0%
$675,000
500,001
11.00%
2
15.0%
$900,000
900,001
13.00%
3
14.0%
$375,000
Capital budget =
$1,950,000
Project
Number/rank
Cost
IRR
IRR > WACC?
Total cost
WACC
1
675,000
16.00%
YES
675,000
11.0%
2
900,000
15.00%
YES
12.1%
3
375,000
14.00%
YES
12.1%
4
562,500
12.50%
12.7%
5
750,000
11.00%
12.7%