Please show all excel formulas and thoroughly show work.

127 4 x X fx A D E F G G H K L M N O GOOG S&P 500 T-bill rate 6.83% 6.02% -0.87% 3.83% 5.66% 5.24% 1.30% 3.76% 1.96% 2.31% Use rate-of-retum data over 60-month period for Alphabet, the T-bill rate, and the S&P 500, which will be used as the market-index portfolio to compute Alphabet's excess return and the market risk premium for each month. Use Excel's regression function or trendline from Excel's scatter plot to identify the alpha, beta and R- squared of the index model. What was the standard deviation of retums for S&P 500 and Alphabet? What was the Sharpe ratio of the S&P 500 over this period? What was Alphabet's Sharpe ratio over this period? -1.36% 1.00% 5.07% -2.92% 3.17% 4.63% GOOG Alpha GOOG Beta: R-squared of Index Model: GOOG Average Excess Retur: GOOG Standard Deviation: GOOG Sharpe Ratio: S&P 500 Average Excess Retur: &: S&P 500 Standard Deviation: S&P 500 Sharpe Ratio: : What are the benefits of holding a portfolio entirely composed of the S&P 500 Index instead of investing only in Alphabet stock? Answer: Excess Retum GOOG 6.76% 5.92% -0.96% 3.77% 5.62% % 0.80% 4.64% 3.41% 17.61% 2.74% 5.70% 5.34% 2.89% -8.07% -5.73% 6.28% 2.71% -0.67% -0.03% 0.99% -3.19% -3.11% -2.88% 1.51% 4.45% -1.89% -1.70% -0.99% -2.20% 20.16% -1.25% -1.61% 1 2 201301 3 201302 4 201303 5 201304 6 201305 7 201306 8 201307 9 201308 10 201309 11 1 201310 12 201311 13 201312 14 201401 15 201402 16 201403 17 201404 18 201405 19 201406 20 201407 21 22 201409 23 201410 24 201411 25 201412 26 201501 27 201502 2304 28 201503 29 201504 30 201505 31 201506 32 201507 33 201508 34 201509 35 201510 36 201511 37 201512 38 201601 39 201602 40 201603 41 201604 42 201605 43 201606 44 201607 45 201608 46 201609 47 47 201610 48 201611 49 201612 50 201701 51 201702 52 201703 53 201704 201704 54 201705 55 201706 56 201707 57 201708 58 201709 sa 59 201710 60 201711 61 201712 62 201408 1.05% 0.84% 4.60% 3.43% 17.66% 2007 2.81% 5.77% 5.38% 2.94% -8.02% -5.70% 6.31% 2.75% -0.64% 0.00% 1.01% -3.17% -3.09% -2.85% 1.54% 4.47% -1.86% -1.68% -0.97% -2.18% 20.19% -1.18% -1.59% 16.83% 4.47% 2.19% -2.10% -6.08% 6.76% -6.97% 6.16% -5.93% 11.08% -0.23% 1.34% 0.93% -3.38% 1.82% 3.24% 3.32% 0.77% 9.21% 6.50% -5.82% 2.40% 0.95% 2.11% 6.00% 0.47% 2.45% 0.07% 0.10% 0.09% 0.06% 0.04% 0.05% 0.04% 0.04% 0.02% 0.05% 0.07% 0.07% 0.04% 0.05% 0.05% 0.03% 0.03% % 0.04% 0.03% 0.03% 0.02% 0.02% 0.02% 0.03% 0.03% 0.02% 0.03% 0.02% 0.02% 0.02% 0.03% 0.07% 0.02% 0.02% 0.12% 0.23% 0.26% 0.31% 0.29% 0.23% 0.27% 0.27% 0.30% 0.30% 0.29% 3.08% 2.60% -3.47% 4.58% 0.81% 0.66% 2.33% 2.06% -1.40% 3.98% -1.39% 2.40% 2.76% -0.25% -2.94% 5.70% -1.50% 0.91% 1.25% -1.93% 2.13% -6.01% -2.45% 8.34% 0.34% -1.52% -4.91% -0.17% 6.73% 0.37% 1.85% 0.27% 3.66% 0.12% 0.04% -1.80% 3.58% 1.92% 1.91% 3.97% 0.12% 1.03% 1.41% 0.62% 2.06% 0.31% 2.06% 2.33% Market Risk Premium 5.17% 1.20% 3.67% 1.90% 2.27% -1.41% % 5.03% -2.96% 3.15% 4.58% 3.01% 2.53% -3.51% 4.53% 0.76% 0.63% 2.30% 2.02% -1.43% 3.95% -1.41% 2.38% 2.74% -0.28% -2.97% 5.68% -1.53% 0.89% 1.23% -1.95% 2.10% sov -6.08% -2.47% 8.32% 0.22% -1.75% -5.17% -0.48% 6.44% 0.14% 1.58% 0.00% 3.36% -0.18% -0.25% -2.13% 3.13% 1.41% 1.40% 3.45% -0.62% 0.23% 0.52% -0.36% 0.99% -0.70% 1.03% 1.26% 1.84% -0.21% 16.81% 4.35% 1.96% -2.36% -6.39% 6.47% -7.20% 5.89% 20% 0.33% 10.78% -0.53% 1.05% 0.60% -3.83% 1.31% 2.73% 2.80% 0.03% % 5.61% -6.80% 1.33% -0.06% 1.08% 4.93% -0.76% 1.13% 0.45% 0.51% 0.51% 0.52% 0.74% 0.80% 0.89% 0.98% 1.07% 1.01% 1.03% 1.07% 1.23% 1.32% 8.41% 3.07% 1.11% 127 4 x X fx A D E F G G H K L M N O GOOG S&P 500 T-bill rate 6.83% 6.02% -0.87% 3.83% 5.66% 5.24% 1.30% 3.76% 1.96% 2.31% Use rate-of-retum data over 60-month period for Alphabet, the T-bill rate, and the S&P 500, which will be used as the market-index portfolio to compute Alphabet's excess return and the market risk premium for each month. Use Excel's regression function or trendline from Excel's scatter plot to identify the alpha, beta and R- squared of the index model. What was the standard deviation of retums for S&P 500 and Alphabet? What was the Sharpe ratio of the S&P 500 over this period? What was Alphabet's Sharpe ratio over this period? -1.36% 1.00% 5.07% -2.92% 3.17% 4.63% GOOG Alpha GOOG Beta: R-squared of Index Model: GOOG Average Excess Retur: GOOG Standard Deviation: GOOG Sharpe Ratio: S&P 500 Average Excess Retur: &: S&P 500 Standard Deviation: S&P 500 Sharpe Ratio: : What are the benefits of holding a portfolio entirely composed of the S&P 500 Index instead of investing only in Alphabet stock? Answer: Excess Retum GOOG 6.76% 5.92% -0.96% 3.77% 5.62% % 0.80% 4.64% 3.41% 17.61% 2.74% 5.70% 5.34% 2.89% -8.07% -5.73% 6.28% 2.71% -0.67% -0.03% 0.99% -3.19% -3.11% -2.88% 1.51% 4.45% -1.89% -1.70% -0.99% -2.20% 20.16% -1.25% -1.61% 1 2 201301 3 201302 4 201303 5 201304 6 201305 7 201306 8 201307 9 201308 10 201309 11 1 201310 12 201311 13 201312 14 201401 15 201402 16 201403 17 201404 18 201405 19 201406 20 201407 21 22 201409 23 201410 24 201411 25 201412 26 201501 27 201502 2304 28 201503 29 201504 30 201505 31 201506 32 201507 33 201508 34 201509 35 201510 36 201511 37 201512 38 201601 39 201602 40 201603 41 201604 42 201605 43 201606 44 201607 45 201608 46 201609 47 47 201610 48 201611 49 201612 50 201701 51 201702 52 201703 53 201704 201704 54 201705 55 201706 56 201707 57 201708 58 201709 sa 59 201710 60 201711 61 201712 62 201408 1.05% 0.84% 4.60% 3.43% 17.66% 2007 2.81% 5.77% 5.38% 2.94% -8.02% -5.70% 6.31% 2.75% -0.64% 0.00% 1.01% -3.17% -3.09% -2.85% 1.54% 4.47% -1.86% -1.68% -0.97% -2.18% 20.19% -1.18% -1.59% 16.83% 4.47% 2.19% -2.10% -6.08% 6.76% -6.97% 6.16% -5.93% 11.08% -0.23% 1.34% 0.93% -3.38% 1.82% 3.24% 3.32% 0.77% 9.21% 6.50% -5.82% 2.40% 0.95% 2.11% 6.00% 0.47% 2.45% 0.07% 0.10% 0.09% 0.06% 0.04% 0.05% 0.04% 0.04% 0.02% 0.05% 0.07% 0.07% 0.04% 0.05% 0.05% 0.03% 0.03% % 0.04% 0.03% 0.03% 0.02% 0.02% 0.02% 0.03% 0.03% 0.02% 0.03% 0.02% 0.02% 0.02% 0.03% 0.07% 0.02% 0.02% 0.12% 0.23% 0.26% 0.31% 0.29% 0.23% 0.27% 0.27% 0.30% 0.30% 0.29% 3.08% 2.60% -3.47% 4.58% 0.81% 0.66% 2.33% 2.06% -1.40% 3.98% -1.39% 2.40% 2.76% -0.25% -2.94% 5.70% -1.50% 0.91% 1.25% -1.93% 2.13% -6.01% -2.45% 8.34% 0.34% -1.52% -4.91% -0.17% 6.73% 0.37% 1.85% 0.27% 3.66% 0.12% 0.04% -1.80% 3.58% 1.92% 1.91% 3.97% 0.12% 1.03% 1.41% 0.62% 2.06% 0.31% 2.06% 2.33% Market Risk Premium 5.17% 1.20% 3.67% 1.90% 2.27% -1.41% % 5.03% -2.96% 3.15% 4.58% 3.01% 2.53% -3.51% 4.53% 0.76% 0.63% 2.30% 2.02% -1.43% 3.95% -1.41% 2.38% 2.74% -0.28% -2.97% 5.68% -1.53% 0.89% 1.23% -1.95% 2.10% sov -6.08% -2.47% 8.32% 0.22% -1.75% -5.17% -0.48% 6.44% 0.14% 1.58% 0.00% 3.36% -0.18% -0.25% -2.13% 3.13% 1.41% 1.40% 3.45% -0.62% 0.23% 0.52% -0.36% 0.99% -0.70% 1.03% 1.26% 1.84% -0.21% 16.81% 4.35% 1.96% -2.36% -6.39% 6.47% -7.20% 5.89% 20% 0.33% 10.78% -0.53% 1.05% 0.60% -3.83% 1.31% 2.73% 2.80% 0.03% % 5.61% -6.80% 1.33% -0.06% 1.08% 4.93% -0.76% 1.13% 0.45% 0.51% 0.51% 0.52% 0.74% 0.80% 0.89% 0.98% 1.07% 1.01% 1.03% 1.07% 1.23% 1.32% 8.41% 3.07% 1.11%