Please provide answer by also showing work in excel

Hyatt WACC Beta | CAPM | WACC Team Date Team Members Present 1: Determine Financial Structure (2 pts) Amount $1,200.0M $4,800.0M % 20.40% 79.60% Risk Free Rate of Return (5 Year T-Bond) Credit Rating Bond Rating Spread Total Cost of Debt (Before Tax) http://www.bloomberg.com/markets/rates-bonds/gove http://quicktake.morningstar.com/StockNet/bonds.asp http://w4.stern.nyu.edu/~adamodar/New_Home_Page A % 0.770% BBB2.250% 3.020% Current Bond Yeild to Maturity B 2.290% http://quicktake.morningstar.com/StockNet/bonds.asp (A+B)/2 2.655% 35.000% http://en.wikipedia.org/wiki/Corporate_tax_in_the_Uni Debt Equity http://quicktake.morningstar.com/StockNet/bonds.asp http://quicktake.morningstar.com/StockNet/bonds.asp 2: Determine Cost of Debt (5 pts) Average cost of debt before taxes (kdbt) Top Tier Corporate Tax Rate Cost of Debt (kd) 1.726% 3: Calculate Hyatt's Equity Beta (3 pts) Date 1st day after date if date is unavailable 11/1/2009 12/1/2009 1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010 6/1/2010 7/1/2010 8/1/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/1/2011 2/1/2011 3/1/2011 4/1/2011 5/1/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/1/2011 11/1/2011 12/1/2011 1/1/2012 2/1/2012 S&P 500 Hyatt S&P 500 Hyatt 1095.63 1115.10 1073.87 1104.49 1169.43 1186.69 1089.41 1030.71 1101.60 1049.33 1141.20 1183.26 1180.55 1257.64 1286.12 1327.22 1325.83 1363.61 1345.20 1320.64 1292.28 1286.94 1204.42 1099.23 1218.28 1244.58 1277.06 1324.09 28.75 29.81 29.62 33.43 38.96 41.17 40.46 37.09 39.11 37.66 37.39 40.30 41.86 45.76 48.59 45.75 43.04 44.31 44.58 40.82 38.79 38.76 33.63 29.79 35.40 35.30 38.42 42.95 2% -4% 3% 6% 1% -8% -5% 7% -5% 9% 4% 0% 7% 2% 3% 0% 3% -1% -2% -2% 0% -6% -9% 11% 2% 3% 4% 4% -1% 13% 17% 6% -2% -8% 5% -4% -1% 8% 4% 9% 6% -6% -6% 3% 1% -8% -5% 0% -13% -11% 19% 0% 9% 12% 3/1/2012 4/1/2012 5/1/2012 6/1/2012 7/1/2012 8/1/2012 9/1/2012 10/1/2012 11/1/2012 12/1/2012 1/1/2013 2/1/2013 3/1/2013 1374.09 41.57 1419.04 42.80 1405.82 43.74 1278.04 35.39 1365.51 37.05 1375.14 36.79 1404.94 38.49 1444.49 39.65 1427.59 36.17 1409.46 36.46 1462.42 39.42 1513.17 40.23 1518.20 41.00 Standard Deviation Correlation Coefficient to S&P (=correl) Beta (xmx/m) Beta (Yahoo Finance) 4% 3% -1% -9% 7% 1% 2% 3% -1% -1% 4% 3% 0% 5% -3% 3% 2% -19% 5% -1% 5% 3% -9% 1% 8% 2% 2% 8% 0.75 1.29 1.25 http://finance.yahoo.com/ 4: Determine Hyatt's Cost of Equity (3 pts) Risk Free Rate of Return (30 Year T-Bond) Market Risk Premium for 2011 Hyatt Stock Beta (Average of calculation and Yahoo) Expected Rate of Return (CAPM) % 3.100% 5.500% 1.27 10.084% http://www.bloomberg.com/markets/rates-bonds/government-bonds http://www.cxoadvisory.com/13357/equity-premium/the-2011-equity 5: Calculate Hyatt's WACC (3 pts) After-Tax Cost of Debt Cost of Equity Weighted Average Cost of Capital 6: Describe what Hyatt's WACC represents (4pts) Cost 1.73% 10.08% 8.379% Weight 20.40% 79.60% Hyatt WACC Activity Beta | CAPM | WACC Team Date Team Members Present 1: Determine Financial Structure (2 pts) Amount % Debt Equity http://quicktake.morningstar.com/StockNet/bonds.asp http://quicktake.morningstar.com/StockNet/bonds.asp 2: Determine Cost of Debt (5 pts) % Risk Free Rate of Return (5 Year T-Bond) Credit Rating Bond Rating Spread Total Cost of Debt (Before Tax) A Current Bond Yeild to Maturity B Average cost of debt before taxes (kdbt) Top Tier Corporate Tax Rate Cost of Debt (kd) http://www.bloomberg.com/markets/rates-bonds/gove http://quicktake.morningstar.com/StockNet/bonds.asp http://w4.stern.nyu.edu/~adamodar/New_Home_Page http://quicktake.morningstar.com/StockNet/bonds.asp (A+B)/2 http://en.wikipedia.org/wiki/Corporate_tax_in_the_Uni 3: Calculate Hyatt's Equity Beta (3 pts) Date 1st day after date if date is unavailable 11/1/2009 12/1/2009 1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010 6/1/2010 7/1/2010 8/1/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/1/2011 2/1/2011 3/1/2011 4/1/2011 5/1/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/1/2011 11/1/2011 12/1/2011 1/1/2012 2/1/2012 S&P 500 Hyatt 1095.63 1115.10 1073.87 1104.49 1169.43 1186.69 1089.41 1030.71 1101.60 1049.33 1141.20 1183.26 1180.55 1257.64 1286.12 1327.22 1325.83 1363.61 1345.20 1320.64 1292.28 1286.94 1204.42 1099.23 1218.28 1244.58 1277.06 1324.09 28.75 29.81 29.62 33.43 38.96 41.17 40.46 37.09 39.11 37.66 37.39 40.30 41.86 45.76 48.59 45.75 43.04 44.31 44.58 40.82 38.79 38.76 33.63 29.79 35.40 35.30 38.42 42.95 S&P 500 Hyatt 3/1/2012 4/1/2012 5/1/2012 6/1/2012 7/1/2012 8/1/2012 9/1/2012 10/1/2012 11/1/2012 12/1/2012 1/1/2013 2/1/2013 3/1/2013 1374.09 41.57 1419.04 42.80 1405.82 43.74 1278.04 35.39 1365.51 37.05 1375.14 36.79 1404.94 38.49 1444.49 39.65 1427.59 36.17 1409.46 36.46 1462.42 39.42 1513.17 40.23 1518.20 41.00 Standard Deviation Correlation Coefficient to S&P (=correl) Beta (xmx/m) Beta (Yahoo Finance) http://finance.yahoo.com/ 4: Determine Hyatt's Cost of Equity (3 pts) % Risk Free Rate of Return (30 Year T-Bond) Market Risk Premium for 2011 Hyatt Stock Beta (Average of calculation and Yahoo) Expected Rate of Return (CAPM) http://www.bloomberg.com/markets/rates-bonds/government-bonds http://www.cxoadvisory.com/13357/equity-premium/the-2011-equity 5: Calculate Hyatt's WACC (3 pts) After-Tax Cost of Debt Cost of Equity Weighted Average Cost of Capital 6: Describe what Hyatt's WACC represents (4pts) Cost 0.00% 0.00% Weight 0.00% 0.00%