What are the Excel Formulas/Equations/Inputs to get the answers highlighted in grey?
A B C D - E F G H J K 1 2 Question la 3 Madoff Corporation raised money through a bond issue with a total principal value of $3,000,000. Each bond has a face (par) value of $1,000 and coupon rate of 6%. The company's applicable tax rate is 21%. a) What is the annual coupon payment bond holders expect to receive? b) What is the total after-tax annual interest expense to the company? 4 $60 5 Question 1b 6 7 $ 142,200 8 4 points 9 10 11 12 Question 2 13 Your good friend is looking to buy a bond that pays semi-annual interest. The par value is $1,000 and the coupon rate is 4%. Your friend plans to hold the bond to its maturity, which is 10 years from now. If her required rate of return is 3%, what is the most you recommend your friend pay for the bond? (round to nearest cent) 14 $1,085.84 4 points 15 16 17 18 19 Question 3 A preferred stock has a par value of $110 and pays an annual dividend of 5% of par. If similar investments have an annual rate of return of 3%, what is the current value of this preferred stock. (round to nearest cent) 20 21 $183.33 D E F G K 26 Question 4a 27 28 $137.33 29 Question 4b 30 31 1.93% B Gillette's most recent annual dividend was $8 per share. The company expects the growth of its dividends to be stable at 3% per year going forward. a) If investors require a 9% return, what is the current value of Gillette's stock? (round to nearest cent) b) If the stock currently trades at $116.57 per share, what is the dividend growth rate investors expect? (round to nearest percent) Hint: When the constant-growth formula is solved for the growth variable, it is g = -D/P+r 32 4 points 33 34 35 36 Question 5a 37 38 39 40 An analyst uses a two-stage variable growth model to estimate the value of Old Maid Company's common stock. The most recent annual dividend paid by the company was $3 per share. The analyst expects dividends to increase 8% per year for the next 3 years and then drop to 4% starting in year 4 and remain stable for the foreseable future. The required rate of return used for the analysis is 10% a) What are the expected dividends for the next 4 years? b) What is the value of the stock attributable to the first 3 years of dividends? (use NPV function) c) What is the value of the stock at the end of year 3? (use constant-growth model) d) What is the value of the stock attributable to years 4 and beyond? (use pv function, where answer to part Cis e fv) e) What is the total value of the stock? 41 42 43 $3.24 $3.50 $3.78 $3.93 4 points 44 45 46 Question 5b Question 5c Question 5d Question 5e 47 48 8 points $8.68 $65.50 $49.21 $57.89 B D F G H J 50 51 52 Question 6a A music company is considering creating the world's largest piano. Cost and cash flow details are listed below. 53 The company requires a 12% return on its capital. 54 $2,452,153 a) What is the present value of the yearly cash flows? (round to nearest dollar) Cost of Project ($2,500,000) 55 Question 6b b) What is the net present value of the project? (round to nearest dollar) Yr 1 cash flow (300,000) 56 Yr 2 cash flow (150,000) 57 ($47,847) Yr 3 cash flow 50,000 58 Question 6c c) What is the internal rate of return of the project? (round to two decimal places) Yr 4 cash flow 250,000 59 Yr 5 cash flow 800,000 60 11.69% Yr 6 cash flow 1,200,000 61 6 points Yr 7 cash flow 3,500,000 B D E F G H 63 64 65 Question 7a 66 67 4.00 68 Question 7b 69 Two projects, Alpha and Beta, are being considered using the payback method. Each has an initial cost of $100,000. The annual cash flows for each project are listed below. a) What is the pay back period in years for Alpha? (round to two decimal places) Year Project Alpha Project Beta b) What is the pay back period in years for Beta? (round to two decimal places) 1 25,000 15,000 2 25,000 25,000 3 25,000 45,000 25,000 30,000 25,000 20,000 25,000 15,000 70 3.50 71 4 points 4 72 5 73 6 74 75 76 77 Question 8 78 79 7.16% 4 points 80 A fitness equipment manufacturer is capitalized with long-term debt, preferred stock and common stock. The amount and cost of each capital source is in the table below. The cost of debt is the after-tax cost. What is the firm's weighted average cost of capital? (round to two decimal places) Amount ($) Cost (%) Long-term debt 800,000 4% Preferred stock 350,000 5% Common stock 3,900,000 8% Total 5,050,000 81 82 83 84 89 90 91 88 Question 9a A flour mill is considering buying a new jumbo sifter, which would have an installed cost of $80,000. The new one would replace the existing sifter that was purchased 3 years ago at an installed cost of $60,000. If the company $42,600 moves forward with the replacement, it could sell the old sifter for $19,000. Purchasing the new sifter would result Question 9b in the company's current assets increasing by $10,000 and current liabilities increasing by $8,000. 92 The company uses the 5-year MACRS table for depreciation and is taxed at 21%. 93 $17,400 a) What is the accumulated depreciation of the old sifter? 94 Question 9c b) What is the current book value of the old sifter? c) What is the amount of depreciation recapture/recovery? $1,600 Question 9d d) What is the tax on the sale of the old sifter? 95 96 97 98 99 102 $336 100 Question 9e e) What are the after-tax proceeds from the sale of the old sifter? 101 $18,664 103 Question 9f f) What is the change in Net Working Capital? 104 105 $2,000 106 Question 9g g) What is the initial investment for the project? 107 108 $63,336
