VIEWS: 310 PAGES: 5 CATEGORY: Spreadsheets POSTED ON: 4/13/2010 Public Domain
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