C12
Chapter 12 Spreadsheet Problem Solutions (C12)
1. There are a number of instructions with which you should be familiar
to use these computerized models. These instructions appear in a
2. A graph that shows the expected EPS and standard deviations using
debt or equity financing is displayed when you click on the worksheet
labeled GRAPH at the bottom of this spreadsheet. To return to this
worksheet, click on the worksheet labeled C12 at the bottom of the
GRAPH worksheet.
INPUT DATA: KEY OUTPUT:
Financing alternatives: Using Debt financing:
Stock – Price per share $100 Expected EPS $18.12
Debt ratio 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
Effects of Financial Leverage
C12
Sales 2,475.00
EBIT (10% of sales) 247.50
MODEL-GENERATED DATA:
Analysis if debt financing is used:
Probability 0.4 0.4 0.2
Sales 5,000 7,500 8,500
EBIT 500 750 850
Interest on S-T debt (15) (15) (15)
TIE 7.58 11.36 12.88
Expected TIE: 10.15
Analysis if stock financing is used:
Sales 5,000 7,500 8,500
EBIT 500 750 850
Interest on S-T debt (15) (15) (15)
Interest on L-T debt (30) (30) (30)
Page 2
GRAPH
$14
$16
$18
$20
Page 3
We have already entered the base case data for each model in this
file, and the models have performed the analysis for preceding parts
of the problem. You will need to enter the data for each of the
remaining parts of the problem–we indicate in each problem the parts
that should be done using the spreadsheet. However, there are several
points worth noting before you go into a model:
1. The input data are entered in specified cells in the INPUT DATA
section. When you change an input item, the model automatically
2. The key output data are displayed to the right of the INPUT DATA
3. Input data items that you can change are distinguished from the
4. All percentages must be entered as decimals. Dollars and other
numbers must be entered without dollar signs or commas.
5. Instructions and comments concerning specific models accompany
GENERAL INSTRUCTIONS FOR COMPUTERIZED PROBLEM SOLUTIONS