Open image in new page to view clearly

The following table, contains annual returns for the stocks of ABC Corp. (ABC) and Company Upper B (Upper B). The returns are calculated usingend-of-year prices (adjusted for dividends and stock splits). Use the information for ABC Corp. (ABC) and Company Upper B (Upper B) to create an Excelspreadsheet that calculates the average returns over the 10-year period for portfolioscomprised of ABC and Upper B using the following, respective, weightings: (1.0, 0.0), (0.9, 0.1), (0.8,0.2), (0.7,0.3), (0.5, 0.4), (0.5, 0.5), (0.4, 0.6), (0.3, 0.7), (0.2, 0.8), (0.1,0.9), and (0.0, 1.0). The average annual returns over the 10-year period for ABC and Upper Bare 14.98% and 12.63% respectively. Also, calculate the portfolio standard deviation over the 10-year period associated with each portfolio composition. The standard deviation over the 10-year period for ABC Corp. and Company Upper B and their correlation coefficient are 25.28%, 23.34%, and 0.82835 respectively. (Hint: Review Table 5.2. Year ABC Returns B Returns 2005 -5.4% 16.5% 2006 2.2% - 7.2% 2007 - 30.5% - 27.5% 2008 - 11.9% - 3.2% 2009 29.3% 9.7% 2010 25.8% 8.9% 2011 22.6% 4.1% 2012 51.5% 43.6% 2013 35.7% 42.2% 2014 30.5% 39.2% Enter the average return and standard deviation for a portfolio with 100% ABC Corp. and 0% Company B in the table below. (Round to two decimal places) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W B ABC = 14.98% = 12.63% 1.0 0.0 % Enter the average return and standard deviation for a portfolio with 90% ABC Corp. and 10% Company B in the table below. (Round to two decimal places.) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W B ABC = 14.98% = 12.63% 0.9 0.1 % Enter the average return and standard deviation for a portfolio with 80% ABC Corp. and 20% Company B in the table below. (Round to two decimal places.) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W B ABC = 14.98% 8 = 12.63% 0.8 02 % Enter the average return and standard deviation for a portfolio with 70% ABC Corp. and 30% Company B in the table below. (Round to two decimal places) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W B ABC = 14.98% = 12.63% 0.7 0.3 Enter the average return and standard deviation for a portfolio with 60% ABC Corp. and 40% Company B in the table below. (Round to two decimal places.) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W ABC = 14.98% = 12.63% 0.6 0.4 The following table, contains annual returns for the stocks of ABC Corp. (ABC) and Company Upper B (Upper B). The returns are calculated usingend-of-year prices (adjusted for dividends and stock splits). Use the information for ABC Corp. (ABC) and Company Upper B (Upper B) to create an Excelspreadsheet that calculates the average returns over the 10-year period for portfolioscomprised of ABC and Upper B using the following, respective, weightings: (1.0, 0.0), (0.9, 0.1), (0.8,0.2), (0.7,0.3), (0.5, 0.4), (0.5, 0.5), (0.4, 0.6), (0.3, 0.7), (0.2, 0.8), (0.1,0.9), and (0.0, 1.0). The average annual returns over the 10-year period for ABC and Upper Bare 14.98% and 12.63% respectively. Also, calculate the portfolio standard deviation over the 10-year period associated with each portfolio composition. The standard deviation over the 10-year period for ABC Corp. and Company Upper B and their correlation coefficient are 25.28%, 23.34%, and 0.82835 respectively. (Hint: Review Table 5.2. Year ABC Returns B Returns 2005 -5.4% 16.5% 2006 2.2% - 7.2% 2007 - 30.5% - 27.5% 2008 - 11.9% - 3.2% 2009 29.3% 9.7% 2010 25.8% 8.9% 2011 22.6% 4.1% 2012 51.5% 43.6% 2013 35.7% 42.2% 2014 30.5% 39.2% Enter the average return and standard deviation for a portfolio with 100% ABC Corp. and 0% Company B in the table below. (Round to two decimal places) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W B ABC = 14.98% = 12.63% 1.0 0.0 % Enter the average return and standard deviation for a portfolio with 90% ABC Corp. and 10% Company B in the table below. (Round to two decimal places.) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W B ABC = 14.98% = 12.63% 0.9 0.1 % Enter the average return and standard deviation for a portfolio with 80% ABC Corp. and 20% Company B in the table below. (Round to two decimal places.) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W B ABC = 14.98% 8 = 12.63% 0.8 02 % Enter the average return and standard deviation for a portfolio with 70% ABC Corp. and 30% Company B in the table below. (Round to two decimal places) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W B ABC = 14.98% = 12.63% 0.7 0.3 Enter the average return and standard deviation for a portfolio with 60% ABC Corp. and 40% Company B in the table below. (Round to two decimal places.) Portfolio Weights Portfolio Average Return Portfolio Standard Deviation WABC W ABC = 14.98% = 12.63% 0.6 0.4