CFIN6
Spreadsheet Problem Solution
Chapter 12
a. Based on EPS, debt financing is preferred.
INPUT DATA:
KEY OUTPUT:
Financing alternatives:
Using Debt financing:
Stock – Price per share
$54
Expected EPS
$5.78
Existing L-T debt interest rate
10.00%
Standard Deviation
$0.85
New L-T debt interest rate
12.00%
Expected TIE
Debt ratio
75.46%
Annual Sales
$2,250
Using Equity financing:
$2,700
Expected EPS
$5.40
$3,150
Standard Deviation
$0.68
Expected TIE
58.80%
Amount financed
$270
Tax rate
40%
Initial balance sheet data:
Current assets:
900.00
Net fixed assets:
450.00
Accounts payable:
172.50
Notes payable:
255.00
Other current liabilities:
225.00
300.00
Common stock:
Retained earnings:
337.50
Common stock par value:
Total Assets
1,350.00
Total Claims
1,350.00
Initial income statement data:
Sales
2,475.00
EBIT
247.50
Interest on S-T debt
Interest on L-T debt
Taxes
Net income
CFIN6
MODEL-GENERATED DATA:
Analysis if debt financing is used:
Probability
0.2
0.6
Sales
2,250
2,700
EBIT
Interest on S-T debt
Interest on L-T debt
Taxes
EPS
4.43
5.78
Expected EPS using debt
5.78
Std. Deviation of EPS:
0.85
TIE
2.91
3.49
Expected TIE:
3.49
Analysis if stock financing is used:
Sales
2,250
2,700
3,150
EBIT
Interest on S-T debt
Interest on L-T debt
Taxes
Net income
EPS
4.32
5.40
Expected EPS using stock
5.40
Std. Deviation of EPS:
0.68
TIE
5.00
6.00
7.00
Expected TIE:
6.00
CFIN6
b. Based on EPS, debt financing is preferred.
INPUT DATA:
KEY OUTPUT:
Financing alternatives:
Using Debt financing:
Stock – Price per share
$54
Expected EPS
$5.60
Existing L-T debt interest rate
12.00%
Standard Deviation
$0.85
New L-T debt interest rate
12.00%
Expected TIE
Debt ratio
Annual Sales
$2,250
Using Equity financing:
$2,700
Expected EPS
$5.40
$3,150
Standard Deviation
$0.68
Expected TIE
Debt ratio
Amount financed
Tax rate
MODEL-GENERATED DATA:
Analysis if debt financing is used:
Probability
0.2
0.6
0.2
Sales
2,250
2,700
3,150
EBIT
225
270
315
Interest on S-T debt
(15)
Interest on L-T debt
(68)
142
187
232
Taxes
(57)
Net income
112
139
EPS
6.95
Expected EPS using debt
Std. Deviation of EPS:
TIE
2.70
3.24
3.78
Expected TIE:
3.24
Analysis if stock financing is used:
Sales
2,250
2,700
3,150
EBIT
225
270
315
Interest on S-T debt
(15)
Interest on L-T debt
CFIN6
Taxes
70
90
108
Net income
108
135
162
EPS
4.32
5.40
6.48
Expected EPS using stock
5.40
Std. Deviation of EPS:
0.68
TIE
5.00
6.00
7.00
Expected TIE:
6.00
c(1). Rate on long-term debt = 5%; based on EPS, debt financing is preferred.
INPUT DATA:
KEY OUTPUT:
Financing alternatives:
Using Debt financing:
Stock – Price per share
$54
Expected EPS
$6.80
Existing L-T debt interest rate
5.00%
Standard Deviation
$0.85
New L-T debt interest rate
5.00%
Expected TIE
6.21
Debt ratio
75.46%
Annual Sales
Prob.
Using Equity financing:
Expected EPS
$5.54
Standard Deviation
Expected TIE
Debt ratio
58.80%
Amount financed
$270
Tax rate
40%
MODEL-GENERATED DATA:
Analysis if debt financing is used:
Probability
0.2
0.6
0.2
Sales
2,250
2,700
3,150
EBIT
Interest on S-T debt
Interest on L-T debt
Taxes
Net income
EPS
6.80
Expected EPS using debt
6.80
Std. Deviation of EPS:
0.85
225
270
315
CFIN6
TIE
5.17
6.21
7.24
Expected TIE:
6.21
Analysis if stock financing is used:
Sales
3,150
EBIT
Interest on S-T debt
Interest on L-T debt
Taxes
Net income
108
135
162
EPS
4.32
5.40
6.48
Expected EPS using stock
5.40
Std. Deviation of EPS:
0.68
TIE
5.00
6.00
7.00
Expected TIE
6.00
c(2). Rate on long-term debt = 20%; based on EPS, equity financing is preferred.
INPUT DATA:
KEY OUTPUT:
Financing alternatives:
Using Debt financing:
Stock – Price per share
$54
Expected EPS
$4.23
Existing L-T debt interest rate
Standard Deviation
$0.85
New L-T debt interest rate
Expected TIE
Debt ratio
75.46%
Annual Sales
0.20
Using Equity financing:
0.60
Expected EPS
$5.40
0.20
Standard Deviation
$0.68
1.00
Expected TIE
Debt ratio
58.80%
Amount financed
$270
Tax rate
40%
CFIN6
MODEL-GENERATED DATA:
Analysis if debt financing is used:
Probability
0.2
0.6
0.2
Sales
2,250
2,700
3,150
EBIT
225
270
315
Interest on S-T debt
Interest on L-T debt
141
186
Taxes
EPS
2.88
4.23
5.58
Expected EPS using debt
4.23
Std. Deviation of EPS:
0.85
TIE
1.74
2.09
2.44
Expected TIE:
2.09
Analysis if stock financing is used:
Sales
2,250
2,700
3,150
EBIT
225
270
315
Interest on S-T debt
Interest on L-T debt
180
Taxes
Net income
EPS
4.32
5.40
6.48
Expected EPS using stock
5.40
Std. Deviation of EPS:
0.68
TIE
5.00
6.00
7.00
Expected TIE
6.00
d(1). Stock price = $105; based on EPS, equity financing is preferred.
INPUT DATA:
KEY OUTPUT:
Financing alternatives:
Using Debt financing:
Stock – Price per share
Expected EPS
$5.60
Existing L-T debt interest rate
Standard Deviation
New L-T debt interest rate
Expected TIE
3.24
Debt ratio
CFIN6
Annual Sales
Prob.
$2,250
0.20
Using Equity financing:
$2,700
0.60
Expected EPS
$3,150
0.20
Standard Deviation
1.00
Expected TIE
Debt ratio
Amount financed
Tax rate
MODEL-GENERATED DATA:
Analysis if debt financing is used:
Probability
0.2
0.6
0.2
Sales
2,250
2,700
3,150
EBIT
Interest on S-T debt
Interest on L-T debt
Taxes
Net income
225
270
315
EPS
4.25
5.60
6.95
Expected EPS using debt
5.60
Std. Deviation of EPS:
0.85
TIE
2.70
3.24
3.78
Expected TIE:
3.24
Analysis if stock financing is used:
Sales
2,250
2,700
3,150
EBIT
225
270
315
Interest on S-T debt
Interest on L-T debt
180
225
270
Taxes
108
Net income
108
135
162
EPS
4.78
5.98
7.18
Expected EPS using stock
5.98
Std. Deviation of EPS:
0.76
TIE
5.00
6.00
7.00
Expected TIE
6.00
d(2). Stock price = $30; based on EPS, debt financing is preferred.
INPUT DATA:
KEY OUTPUT:
Financing alternatives:
Using Debt financing:
Stock – Price per share
$30
Expected EPS
$5.60
Existing L-T debt interest rate
Standard Deviation
$0.85
New L-T debt interest rate
Expected TIE
3.24
Debt ratio
75.46%
Annual Sales
0.20
Using Equity financing:
0.60
Expected EPS
$4.66
0.20
Standard Deviation
$0.59
1.00
Expected TIE
6.00
Debt ratio
58.80%
Amount financed
$270
Tax rate
40%
MODEL-GENERATED DATA:
Analysis if debt financing is used:
Probability
0.2
0.6
0.2
Sales
EBIT
Interest on S-T debt
Interest on L-T debt
Taxes
Net income
EPS
4.25
5.60
6.95
Expected EPS using debt
5.60
Std. Deviation of EPS:
0.85
TIE
2.70
3.24
3.78
Expected TIE:
3.24
CFIN6
Analysis if stock financing is used:
Sales
EBIT
Interest on S-T debt
Interest on L-T debt
Taxes
Net income
EPS
3.72
4.66
5.59
Expected EPS using stock
4.66
Std. Dev. of EPS:
0.59
TIE
5.00
6.00
7.00
Expected TIE
6.00
e(1). The general results for Alternative 1 indicate that debt financing would be preferred.
INPUT DATA:
KEY OUTPUT:
Financing alternatives:
Using Debt financing:
Stock – Price per share
Expected EPS
$5.60
Existing L-T debt interest rate
Standard Deviation
New L-T debt interest rate
Expected TIE
3.24
Debt ratio
75.46%
Annual Sales
$2,250
0.00
Using Equity financing:
$2,700
1.00
Expected EPS
$5.51
$3,150
0.00
Standard Deviation
$0.00
1.00
Expected TIE
6.00
Debt ratio
58.80%
CFIN6
e(2). The general results for Alternative 2 indicate that debt financing would be preferred.
INPUT DATA:
KEY OUTPUT:
Financing alternatives:
Using Debt financing:
Stock – Price per share
Expected EPS
$7.49
Existing L-T debt interest rate
Standard Deviation
$8.85
New L-T debt interest rate
Expected TIE
Debt ratio
Annual Sales
Prob.
$0
0.30
Using Equity financing:
$7,500
0.30
Standard Deviation
$7.22
1.00
Expected TIE
Debt ratio
$2,700
0.40
Expected EPS
$7.05