Financial Analysis Spreadsheet Tutorial by SupremeLord

VIEWS: 310 PAGES: 5

									           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

								
To top