Question

The following table, contains annual returns for the stocks of ABC Corp. (ABC) and Company B (B). The returns are calculated using end-of-year prices (adjusted for dividends and stock splits) retrieved from http://www.finance.yahoo.com/. Use the information to create an Excel spreadsheet that calculates the standard deviation of annual returns over the 10-year period for ABC, B, and of the equally-weighted portfolio of ABC and B over the 10-year period. (Hint: Review the Excel screenshot on page 173.) The average annual return for ABC Corp. over the 10-year period is %. (Enter as a percentage and round to two decimal places.) The standard deviation for ABC Corp. is over the 10-year period (Round to four decimal places.) 0 Data Table The average annual return for Company B over the 10-year period is %. (Enter as a percentage and round to two decimal places.) The standard deviation for Company B over the 10-year period is . (Round to four decimal places.) (Click on the icon located on the top-right corner of the data table below in order to copy its contents into a spreadsheet.) The average portfolio return for 2005 is %. (Enter as a percentage and round to two decimal places.) The average portfolio return for 2006 is %. (Enter as a percentage and round to two decimal places.) The average portfolio return for 2007 is %. (Enter as a percentage and round to two decimal places.) The average portfolio return for 2008 is %. (Enter as a percentage and round to two decimal places.) Year 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 ABC Returns -3.4% 1.9% - 31.2% - 12.9% 30.4% 27.7% 22.8% 50.4% 37.1% 30.6% B Returns 16.8% -8.8% - 26.3% -2.9% 9.6% 8.1% 4.4% 41.7% 42.1% 39.5% The average portfolio return for 2009 is %. (Enter as a percentage and round to two decimal places.) The average portfolio return for 2010 is %. (Enter as a percentage and round to two decimal places.) The average portfolio retum for 2011 is %. (Enter as a percentage and round to two decimal places.) The average portfolio retum for 2012 is %. (Enter as a percentage and round to two decimal places.) Print Done The average portfolio retum for 2013 is %. (Enter as a percentage and round to two decimal places.) The average portfolio retum for 2014 is %. (Enter as a percentage and round to two decimal places.)

Answer 1

As there are more than 4 sub parts, I will answer the first four questions as per HOMEWORKLIB RULES.

A)Average annual return is calculated as the total sum of all the returns of company ABC divided by the total number of years considered as shown in the image below:

C) Average return of company B is calculated with the same formula using in the A) as shown in the image below:

B) Standard deviation of return is calculated using the formula shown below:

Standard deviation of company ABC is calculated as follows by substituting respective values in the above shown formula:

D) Similar to B), standard deviation of company B is calculated as shown in the image below: