A B C D E F G H K M N 10 CHAPTER 12 - EXCEL EXERCISE - WACC Calculation 2 3 Use the information below to calculate the Calendar Year WACC for Bod Corp. Please fill in all highlighted blue cells with formulas. BOD CORPORATION Common Equity, Preferred Equity and Debt Balances 2010 2011 2012 2013 2014 2015 10 Common Equity 500,000 600,000 650,000 525,000 11 12 Preferred Equity 50,000 55,000 60,000 60,000 70,000 70,000 13 14 Debt 600,000 650,000 700,000 750,000 15 16 Total Firm Value 1, 150,000 1,230,000 1,310,000 1,410,000 1,355,000 1,345,000 17 18 Corp. Tax Rate 35% 30% 30% 40% 35% 35% The Equity portion of the 19 Debt-to-Equity ratio 20 Debt to Equity Ratio 1.03 1.10 includes both preferred 21 22 BOD CORPORATION and common equity 23 Common Equity, Preferred Equity and Debt Expected Returns 24 25 Common Equity 2010 2011 2012 2013 2014 2015 26 Expected Return 27 28 Beta 1.1 0.9 1.2 1.3 1.0 0.8 29 Risk Free Rate 1.5% 2.0% 1.8% 1.6% 2.2% 2.5% 30 Exp. Market Return 10.0% 9.5% 11.0% 12. 0% 11.5% 13.0% 31 32 Preferred Equity 2010 2011 2012 2013 2014 2015 33 Expected Return 34 35 Dividend Payment 5.00 4.25 4.50 5.25 4.00 6.00 36 End of Year Price 75.00 70.00 82.00 80.00 72.00 88.00 37 38 Debt 2010 2011 2012 2013 2014 2015 All Coupon payments are 39 YTM annual. 40 41 Par/Face 1,000 1,000 1,000 1,000 1,000 1.000 42 Coupon Payment 55 55 50 55 45 40 43 Years to Maturity 10 15 13 20 21 18 44 Current Price 1,100 980 1,000 1,050 900 950 45 46 BOD CORPORATION 47 WACC Calculation 48 49 2010 2011 2012 2013 2014 2015 50 WACC