# Financial Analysis Spreadsheet Tutorial by SupremeLord

VIEWS: 310 PAGES: 5

• pg 1
```									           Financial Analysis Spreadsheet Templates

Problem 16-1                      Problem 16-2                         Problem 16-10

Problem 16-18

Corporate Finance by Ross, Westerfield, and Jaffe -- Seventh Edition

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

```
To top