Chapter 13 Spreadsheet Problem Solutions (C13)
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: $14,250,000 Break point with $3 div: $35,625,000
Current stock price: $56.25 Break point with $0 div: $35,625,000
Total capital budget $15,000,000
Project Cost IRR Cum. Cost
1 $15,000,000 18.00% $15,000,000
2 $6,000,000 10.40% $21,000,000
Total $21,000,000
MODEL-GENERATED DATA:
With proposed dividend:
Earnings 14,250,000
Dividend Payouts
1. There are a number of instructions with which you should be familiar to use these
computerized models. These instructions appear in a separate worksheet labeled
INSTRUCTIONS. If you have not already done so, you should read these instructions
now. To read these instructions, click on the worksheet labeled INSTRUCTIONS.
Marginal cost of capital up to break point:
After-Tax Weighted
Component Percent Cost Cost
0.1072
Marginal cost of capital above break point:
After-Tax Weighted
Component Percent Cost Cost
0.1072
Capital cost:
Range of financing Capital cost
1 10.72%
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
ones you should not change. The items that you can change are
highlighted in color (blue) whereas the other items are printed in black.
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