Get documents about "Financial Analysis Spreadsheet Tutorial
Document Sample
Financial Analysis Spreadsheet Templates
MAIN MENU -- CHAPTER 16
Problem 16-1 Problem 16-2 Problem 16-10
Problem 16-18
Corporate Finance by Ross, Westerfield, and Jaffe -- Seventh Edition
Copyright © 2005 Irwin/McGraw-Hill and KMT Software, Inc. (www.kmt.com)
Copyright © 2005 Irwin/McGraw-Hill File: 44ea9442-a327-47ed-b9dd-dcdeab876d05.xls Printed: 4/12/2010
Corporate Finance
Ross, Westerfield, and Jaffe -- Seventh Edition
Problem 16-1 Objective
Calculate value of the firm
Student Name:
Course Name:
Student ID:
Course Number:
Good Time Company is a regional chain department store. It will remain in business for one more year. The
probability of a boom year is 60 percent and a recession is 40 percent. It is projected that Good Time will
generate a total cash flow of $250 million in a boom year and $100 million in a recession. The firm's required
debt payment at the end of the year is $150 million. The market value of Good Time's outstanding debt
is $108.93 million. Assume a one-period model, risk neutrality, and an annual discount rate of 12 percent
for both the firm's debt and equity. Good Time pays no taxes.
a. What is the value of the firm's equity?
b. What is the promised return on Good Time's debt?
c. What is the value of the firm?
d. How much would Good Time's debt be worth if there were no bankruptcy costs?
e. What payoff, after bankruptcy costs, do bondholders expect to receive in the event of a recession?
f. What costs do bondholders expect Good Time to incur should bankruptcy arise at the end of the year?
Solution
Problem 16-1
Instructions
Enter formulas and comments to meet the requirements of this problem.
Assumptions
Boom Recession
Probabilities 60% 40%
Cash flow $250,000,000 $100,000,000
Required debt payment $150,000,000
Market value of debt $108,930,000
Discount rate 12%
a. What is the value of the firm's equity?
Value of equity FORMULA
b. What is the promised return on Good Time's debt?
Promised return FORMULA
c. What is the value of the firm?
Value of firm FORMULA
d. How much would Good Time's debt be worth if there were no bankruptcy costs?
Value of debt FORMULA
e. What payoff, after bankruptcy costs, do bondholders expect to receive in the event of a recession?
Payoff $80,004,000
f. What costs do bondholders expect Good Time to incur should bankruptcy arise at the end of the year?
Costs $19,996,000
Copyright © 2005 Irwin/McGraw-Hill FAST Workbooks by Ross, Westerfield, and Jaffe Problem: 16-1
Corporate Finance
Ross, Westerfield, and Jaffe -- Seventh Edition
Problem 16-2 Objective
Calculate the value of the firm
Student Name:
Course Name:
Student ID:
Course Number:
Steinberg Corporation and Dietrich Corporation are identical firms except that Dietrich is more levered. Both
companies will remain in business for one more year. The companies' economists agree that the
probability of a recession next year is 20 percent and the probability of a continuation of the current
expansion is 80 percent. If the expansion continues, each firm will generate earnings before interest and
taxes (EBIT) of $2 million. If a recession occurs, each firm will generate earnings before interest and
taxes (EBIT) of $0.8 million. Steinberg's debt obligation requires the firm to pay $750,000 at the end of the
year. Dietrich's debt obligation requires the firm to pay $1 million at the end of the year. Neither firm pays
taxes. Assume a one-period model, risk neutrality, and an annual discount rate of 15 percent.
a. Assuming there are no costs of bankruptcy, what is the market value of each firm's debt and equity?
b. What is the value of each firm?
c. Steinberg's CEO recently stated that Steinberg's value should be higher than Dietrich's since the firm
has less debt, and, therefore, less bankruptcy risk. Do you agree or disagree with this statement?
Solution
Problem 16-2
Instructions
Use Excel formulas to calculate the requirements of this problem.
Assumptions
Expansion Recession
Probability 80% 20%
EBIT $2,000,000 $800,000
Steinberg's debt obligation $750,000
Diethrich's debt obligation $1,000,000
Annual discount rate 15%
a. Assuming there are no costs of bankruptcy, what is the market value of each firm's debt and equity?
Steinberg Dietrich
Value of equity FORMULA FORMULA
Value of debt FORMULA FORMULA
b. What is the value of each firm?
Steinberg Dietrich
Value of each firm FORMULA FORMULA
c. Steinberg's CEO recently stated that Steinberg's value should be higher than Dietrich's since the firm
has less debt, and, therefore, less bankruptcy risk. Do you agree or disagree with this statement?
Copyright © 2005 Irwin/McGraw-Hill FAST Workbooks by Ross, Westerfield, and Jaffe Problem: 16-2
Corporate Finance
Ross, Westerfield, and Jaffe -- Seventh Edition
Problem 16-10 Objective
Evaluate cash flows in light of personal tax rates.
Student Name:
Course Name:
Student ID:
Course Number:
Fortune Enterprises is an all-equity firm that is considering issuing $13,500,000 of perpetual 10 percent debt.
The firm will use the proceeds of the bond sale to repurchase equity. Fortune distributes all earnings available
to stockholders immediately as dividends. The firm will generate $3 million of earnings before interest
and taxes (EBIT) every year into perpetuity. Fortune is subject to a corporate tax rate of 40 percent.
Financial information for the firm under each of its two possible financial structures is shown below:
Unlevered Levered
EBIT $3,000,000 $3,000,000
Interest 0 1,350,000
EBT 3,000,000 1,650,000
Taxes (Tax rate is 40%) 1,200,000 660,000
Net income $1,800,000 $990,000
a. Suppose the personal tax rate on interest income (T b) and equity distributions (T s) is 30 percent.
1. Which plan do equity holders prefer?
2. Which plan does the IRS prefer?
3. Suppose equity holders demand a 20 percent return after personal taxes on the firm's unlevered
equity. What is the value of the firm under each plan?
b. Suppose T b = 0.55 and T s = 0.20.
1. What is the annual after-tax cash flow to equity holders under each plan?
2. What is the annual after-tax cash flow to debt holders under each plan?
Solution
Problem 16-10
Instructions
Enter formulas and comments to meet the requirements of this problem.
a.1. If the personal tax rate is 30 percent, which plan offers the investors the higher cash flows? Why?
Personal tax rate 30%
Equity Plan Debt Plan
Stockholders:
Dividends
Taxes FORMULA FORMULA
Net cash flow $0 $0
Bondholders:
Interest $0
Taxes 0 FORMULA
Net cash flow $0 $0
Total cash flow to stakeholders:
Equity plan $0
Debt plan $0
Comment on why one plan offers more cash flow than the other.
a.2. Which plan does the IRS prefer?
a.3. Suppose stockholders demand a 20 percent return after personal taxes. What is the value of the firm under
each plan?
Required return 20%
Corporate tax rate 40%
Bond issue $13,500,000
Value under all equity plan FORMULA
Value under debt plan FORMULA
b. Suppose TS = .2 and TB = .55.
1. What are the investors' returns under each plan?
Tax rate for stockholders 20%
Tax rate for bondholders 55%
Equity Plan Debt Plan
Stockholders:
Dividends $1,800,000 $990,000
Taxes FORMULA FORMULA
Net cash flow $1,800,000 $990,000
Bondholders:
Interest $0 $1,350,000
Taxes 0 FORMULA
Net cash flow $0 $1,350,000
Total cash flow to stakeholders:
Equity plan $1,800,000
Debt plan $2,340,000
Copyright © 2005 Irwin/McGraw-Hill FAST Workbooks by Ross, Westerfield, and Jaffe Problem: 16-10
Corporate Finance
Ross, Westerfield, and Jaffe -- Seventh Edition
Problem 16-18 Objective
Calculate equilibrium returns
Student Name:
Course Name:
Student ID:
Course Number:
Assume that there are three groups of investors with the following tax rates and investable funds:
Investable Funds
Group ($ millions) Tax rate
A 375 50%
B 220 32.50%
C 105 10%
Each group requires a minimum after-tax return of 8.1 percent on any security. The only types of securities
available are common stock and perpetual corporate bonds. Income from corporate bonds is subject to a
personal tax, but it is deductible for corporate tax purposes. Capital gains from common stock are untaxed
at the personal level. In equilibrium, common stock yields an 8.1 percent pretax return; foreign real estate
also earns this rate. All funds not invested in stocks or bonds will be invested in foreign real estate. Assume
the common stock and the bonds are both risk-free.
Corporate earnings before interest and taxes total $85 million each year in perpetuity. The corporate tax
rate is 35 percent.
a. What is the equilibrium market rate of interest on corporate bonds?
b. In equilibrium, what is the composition of each of the groups' portfolios?
c. What is the total market value of all companies?
d. What is the total tax bill?
Solution
Problem 16-18
Instructions
Enter formulas to solve the requirements of this problem. Wherever possible, use cell references in your
formulas.
a. What is the equilibrium market rate of interest on corporate bonds?
Assumptions
Corporate tax rate 35%
Stock required return 8.10%
Equilibrium rate on corporate bonds FORMULA
b. In equilibrium, what is the composition of each of the groups' portfolios?
The indifferent bond rate calculated below, is the rate bondholders must earn to make the same return as
stockholders.
Indifferent After-Tax
Group Tax rate bond rate Return (Debt)
A 50% 16.2% FORMULA
B 32.50% 12.0% FORMULA
C 10% 9.0% FORMULA
Comment
c. What is the total market value of all companies?
Assumptions
EBIT 85 ($ millions)
Tax rate 35%
Rate of return on bonds 12.46%
Total amount invested in bonds 325 ($ millions)
Stock value FORMULA ($ millions)
d. What is the total tax bill?
(Hint: the total tax bill is the sum of the taxes paid by corporations and individuals)
($ millions)
Corporate taxes 15.577
Interest income:
Group B 8.909
Group C 1.308
Dividend income 0.000
Total taxes 25.794
Copyright © 2005 Irwin/McGraw-Hill FAST Workbooks by Ross, Westerfield, and Jaffe Problem: 16-18
Other docs by SupremeLord
Realism and Abstraction in Economics -- Aristotle and Mises versus Friedman
Views: 65 | Downloads: 0
