Part VIII & Part IX

Excel File Edit mi 8%O Sun 9:24 PM Q AutoSave OFF View Insert Format Tools Data Window Help BESU- Page Layout Formulas Data Review View Tell me WACC_HW Q Home Insert Draw Share Comments Calibri (Body) 1 v 11 . Al = = = ab Wrap Text Percentage Ev 28- v O D V O Paste GO a. Ar $ % ) .00 2 Merge & Center Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data C23 fx C D E F G J K L M N P Q Q R S 3 Excel, which will calculate things with precision. This means that none of your answers should be rounded in any way, shape, or form at any point. Rounded answers may be marked as incorrect, so be sure to compute your answers within Excel and then link your answer appropriately. All calcula be performed within this Excel workbook - no external links are allowed. Students are allowed to work together, but they must complete their own unique assignment. When graded, correct answers will appear as green and incorrect answers will appear as red. 5 6 *** Any and all questions regarding the test that remain after consulting your notes should be posted to the Ask the Instructor discussion board on PLATO. *** 7 8 9 10 Please use the following information to answer the questions that follow, as appropriate. 11 You are the financial manager at XYZ Corp and you are trying to determine the appropriate cost of capital to use for evaluating upcoming projects. 12 To help with this, you have gathered the following information regrading relevant market values for your firm: 13 14 Debt $199,167,485.24 YTM on Similar Securities 5.50% Risk Free Rate 5.00% 15 Preferred Stock $89,625,368.36 Tax Rate 21.00% Market Return 7.00% 16 Common Stock $707,044,572.59 Equity Beta 1.2 17 Preferred Stock Price $33.18 Common Stock Price $68.67 18 Next Preferred Dividend $6.67 Next Common Dividend $2.32 19 Long Run Growth Rate 4.00% 20 21 Part I: Expressed as a percentage, what is the weight of debt for this firm? 22 23 24 25 26 Part II: Expressed as a percentage, what is the weight of preferred stock for this firm? 27 28 Answer = 29 30 31 Part III: Expressed as a percentage, what is the weight of common stock for this firm? 32 33 Answers 34 35 36 Part IV: Expressed as a percentage, what is the effective cost of debt capital for this firm? 37 38 Answer = 39 40 Answers A Homesheet Questions Workspace + Ready 5 100% MAY 23,539 1 5 2 O 84 Adli S tv A P X W Excel File Edit Tools Data Window Help mi * 53% (4) Sun 10:30 PM Q View Insert Format BESU- AutoSave OFF WACC_HW Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) 1 v - AP 11 ab Wrap Text = = = Percentage v I ( 28" O U Paste GO .00 Delete av A. $ %) = Insert V Format Merge & Center Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select Analyze Data C58 fx B D E F G H K L M N 0 P Q R 38 Answer = 4.3% 39 40 41 Part V: Expressed as a percentage, what is the cost of preferred stock capital for this firm? 42 43 Answers 20.1% 44 45 46 Part VI: Expressed as a percentage, what is the cost of common stock capital for this firm according to the CAPM model? 47 48 Answer = 7.4% 49 50 51 Part VII: Expressed as a percentage, what is the cost of common stock capital for this firm according to the DDM model? 52 53 Answer = 7.4% 54 55 56 Part VIII: Expressed as a percentage, what is the average cost of common stock capital for this firm across the two models? 57 58 Answer = 59 60 61 Part IX: Expressed as a percentage, what is the Weighted Average Cost of Capital (WACC) for this firm? 62 63 Answer = 64 65 66 67 68 69 70 71 72 73 74 75 A Homesheet Questions Workspace + Ready 5 100% MAY 23,539 1 6 5 2 O tv P W