Case study on capital structure with solution

Healthcare Finance.

CASE17
CASE 19 Instructor Version Copyright 2014 Health Administration Press
11/26/14
RN TEMPS, INC.
Capital Structure Analysis
This case illustrates the capital structure decision for a firm that begins with zero debt
financing.
The spreadsheet consists of two separate models:
1) Debt amount and ROE (Rows 35-43) uses income statements to examine the
effects of financial leverage on ROE. This model uses a single (constant)
interest rate input for all capital structure scenarios.
2) Debt amount and Stock Price / CCC (Rows 45-56) examines the effects of debt financing
on firm value, cost of capital, and stock price, assuming zero growth (perpetual cash flows.)
The model consists of a complete base case analysis--no changes need to be made
to the existing MODEL-GENERATED DATA section. However, all values in the INPUT DATA
section of the student version have been replaced with zeros. Thus, students must determine
the appropriate input values and enter them into the model. These cells are colored red.
When this is done, any error cells will be corrected and the base case solution will appear.
Note that the model does not contain any risk analyses, so students will have to create
their own if required by the case. Furthermore, students must create their own graphics
(charts) as needed to present their results.
The instructor version of the model contains a sheet (Figure 1) that plots both stock price
and cost of capital versus the dollar amount of debt financing. It also contains sheets that
contain the inputs and outputs for the increased and decreased business risk scenarios.
INPUT DATA: KEY OUTPUT:
Tax rate 40.0%
Debt Amount and ROE Input: Debt Amount and ROE Analysis:
Market value $12,000,000 Expected
Cost of debt 10.0% ROE SD of ROE
All Equity 15.0% 1.8%
Probability EBIT 25% Debt 18.0% 2.4%
0.25 $2,500,000 50% Debt 24.0% 3.5%
0.5 $3,000,000 75% Debt 42.0% 7.1%
0.25 $3,500,000
Debt Amount and Stock Price / CCC Input: Debt Amount and Stock Price / CCC Analysis:
EBIT $3,000,000
No. of shares 10,000,000
Debt Amount Cost of Debt Cost of Equity Debt Amount Stock Price CCC
$0 0.0% 15.0% $0 $1.200 15.0%
2,500,000 10.0% 15.5% 2,500,000 $1.315 13.7%
5,000,000 11.0% 16.5% 5,000,000 $1.391 12.9%
7,500,000 13.0% 18.0% 7,500,000 $1.425 12.6%
10,000,000 16.0% 20.0% 10,000,000 $1.420 12.7%
12,500,000 20.0% 25.0% 12,500,000 $1.370 13.1%
MODEL-GENERATED DATA:
Debt and ROE Analysis:
All Equity 25% Debt
Probability 0.25 0.50 0.25 0.25 0.50 0.25
EBIT $2,500,000 $3,000,000 $3,500,000 $2,500,000 $3,000,000 $3,500,000
Interest 0 0 0 300,000 300,000 300,000
EBT $2,500,000 $3,000,000 $3,500,000 $2,200,000 $2,700,000 $3,200,000
Taxes 1,000,000 1,200,000 1,400,000 880,000 1,080,000 1,280,000
Net Income $1,500,000 $1,800,000 $2,100,000 $1,320,000 $1,620,000 $1,920,000
ROE 12.5% 15.0% 17.5% 14.7% 18.0% 21.3%
TIE Ratio n.a. n.a. n.a. 8.33 10.00 11.67
E(ROE) 15.0% 18.0%
Std dev of ROE 1.8% 2.4%
Coefficient of variation 0.12 0.13
50% Debt 75% Debt
Probability 0.25 0.50 0.25 0.25 0.50 0.25
EBIT $2,500,000 $3,000,000 $3,500,000 $2,500,000 $3,000,000 $3,500,000
Interest 600,000 600,000 600,000 900,000 900,000 900,000
EBT $1,900,000 $2,400,000 $2,900,000 $1,600,000 $2,100,000 $2,600,000
Taxes 760,000 960,000 1,160,000 640,000 840,000 1,040,000
Net Income $1,140,000 $1,440,000 $1,740,000 $960,000 $1,260,000 $1,560,000
ROE 19.0% 24.0% 29.0% 32.0% 42.0% 52.0%
TIE Ratio 4.17 5.00 5.83 2.78 3.33 3.89
E(ROE) 24.0% 42.0%
Std dev of ROE 3.5% 7.1%
Coefficient of variation 0.15 0.17
Debt Amount and Stock Price / CCC Analysis:
Market Value Market Value Total Stock Number of Earnings per
of Debt of Equity Value Debt Ratio Price CCC Shares Share (EPS)
$0 $12,000,000 $12,000,000 0.0% $1.200 15.0% 10,000,000 $0.18
2,500,000 10,645,161 13,145,161 19.0% 1.315 13.7% 8,098,160 $0.20
5,000,000 8,909,091 13,909,091 35.9% 1.391 12.9% 6,405,229 $0.23
7,500,000 6,750,000 14,250,000 52.6% 1.425 12.6% 4,736,842 $0.26
10,000,000 4,200,000 14,200,000 70.4% 1.420 12.7% 2,957,746 $0.28
12,500,000 1,200,000 13,700,000 91.2% 1.370 13.1% 875,912 $0.34
Optimal Debt Level = $7,500,000
END
Figure1
STOCK PRICE AND COST OF CAPITAL

1.2 1.3145161290322582 1.3909090909090909 1.425 1.42 1.37 0.15 0.13693251533742332 0.12941176470588234 0.12631578947368421 0.12676056338028169 0.13138686131386862
Amount of Debt ($)

Increased Business Risk
CASE 17 Instructor Version (Increased Business Risk) Copyright 2014
By FACHE
RN TEMP SERVICES, INC.
Capital Structure Decisions
This sheet contains the increased business risk scenario valuation.
INPUT DATA: KEY OUTPUT:
Debt Amount and ROE Input: Debt Amount and ROE Analysis:
Total value $12,000,000 Debt Amount Stock Price CCC
Cost of debt 12.0% $0 $1.13 16.0%
EBIT $3,000,000 2,500,000 1.21 14.9%
Tax rate 40.0% 5,000,000 1.24 14.5%
No. of shares 10,000,000 7,500,000 1.24 14.5%
10,000,000 1.23 14.6%
12,500,000 1.23 14.7%
Debt Amount and Stock Price / CCC Input:
Debt Amount Cost of Debt
$0 0.0%
2,500,000 11.0%
5,000,000 13.0%
7,500,000 16.0%
10,000,000 20.0%
12,500,000 25.0%
Debt Amount Cost of Equity
$0 16.0%
2,500,000 17.0%
5,000,000 19.0%
7,500,000 22.0%
10,000,000 26.0%
12,500,000 31.0%
MODEL-GENERATED DATA:
Debt Amount and Stock Price / CCC Analysis:
Market Value Market Value Total Stock Number of Earnings per
of Debt of Equity Value Debt Ratio Price CCC Shares Share (EPS)
$0 $11,250,000 $11,250,000 0.0% $1.125 16.0% 10,000,000 $0.18
2,500,000 9,617,647 12,117,647 20.6% 1.212 14.9% 7,936,893 0.21
5,000,000 7,421,053 12,421,053 40.3% 1.242 14.5% 5,974,576 0.24
7,500,000 4,909,091 12,409,091 60.4% 1.241 14.5% 3,956,044 0.27
10,000,000 2,307,692 12,307,692 81.3% 1.231 14.6% 1,875,000 0.32
12,500,000 (241,935) 12,258,065 102.0% 1.226 14.7% (197,368) 0.38
Optimal Debt Level: $5,000,000
END
Decreased Business Risk
CASE 17 Instructor Version (Decreased Business Risk) Copyright 2014
By FACHE
RN TEMPS SERVICES, INC.
Capital Structure Decisions
This sheet contains the decreased business risk scenario valuation.
INPUT DATA: KEY OUTPUT:
Debt Amount and ROE Input: Debt Amount and ROE Analysis:
Total value $12,000,000 Debt Amount Stock Price CCC
Cost of debt 12.0% $0 $1.29 14.0%
EBIT $3,000,000 2,500,000 1.41 12.7%
Tax rate 40.0% 5,000,000 1.51 11.9%
No. of shares 10,000,000 7,500,000 1.58 11.4%
10,000,000 1.60 11.3%
12,500,000 1.57 11.5%
Debt Amount and Stock Price / CCC Analysis:
Debt Amount Cost of Debt
$0 0.0%
2,500,000 9.0%
5,000,000 9.5%
7,500,000 10.5%
10,000,000 12.5%
12,500,000 15.5%
Debt Amount Cost of Equity
$0 14.0%
2,500,000 14.3%
5,000,000 15.0%
7,500,000 16.0%
10,000,000 17.5%
12,500,000 20.0%
MODEL-GENERATED DATA:
Debt Amount and Stock Price / CCC Analysis:
Market Value Market Value Total Stock Number of Earnings per
of Debt of Equity Value Debt Ratio Price CCC Shares Share (EPS)
$0 $12,857,143 $12,857,143 0.0% $1.286 14.0% 10,000,000 $0.18
2,500,000 11,643,357 14,143,357 17.7% 1.414 12.7% 8,232,386 0.20
5,000,000 10,100,000 15,100,000 33.1% 1.510 11.9% 6,688,742 0.23
7,500,000 8,296,875 15,796,875 47.5% 1.580 11.4% 5,252,226 0.25
10,000,000 6,000,000 16,000,000 62.5% 1.600 11.3% 3,750,000 0.28
12,500,000 3,187,500 15,687,500 79.7% 1.569 11.5% 2,031,873 0.31
Optimal Debt Level: $10,000,000