Complete Spreadsheet Exercise 7.2 7.2 The data below are annual returns for General Foods (GF) and Sigma Technology (ST)
Posted: Wed Mar 30, 2022 3:46 pm
Complete Spreadsheet Exercise 7.2 7.2 The data below are annual
returns for General Foods (GF) and Sigma Technology (ST) for the
period 2004-2018. ST is highly regarded by many investors for its
innovative products. It had returns more than twice as large as
that of GF. Assume an investor placed half her funds in GF and half
in ST during this 15-year period. Her objective was to earn a
larger return than that available in GF alone. Assess the
performance of the portfolio relative to the performance of each
individual security. a. Calculate the arithmetic mean return for
each stock. b. Calculate the standard deviation for each stock
using the STDEV function. c. Calculate the correlation coefficient
using the CORREL function. e. Calculate the portfolio return
assuming equal weights for each stock. f. Set up a formula for the
portfolio standard deviation that allows you to substitute
different values for the correlation coefficient or the standard
deviations of the stocks. Using equal weights for the two stocks,
calculate the standard deviation of the portfolio. g. How does the
portfolio return compare to the return on GF alone? How does the
risk of the portfolio compare to the risk of having held GP? h.
Assume that the correlation between the two stocks had been 0.10.
How much would portfolio risk have changed relative to the result
calculated in f?
GF ST 2018 -0.141 0.222 2017 0.203 0.079 2016 -0.036 -0.220 2015
-0.204 0.527 2014 0.073 -0.628 2013 -0.111 0.684 2012 0.023 1.146
2011 0.291 0.564 2010 0.448 0.885 2009 0.482 0.433 2008 0.196 0.516
2007 0.103 -0.056 2006 0.075 0.153 2005 0.780 1.207 2004 0.254
0.736 Use cell formulas wherever possible.
7.2 The data below are annual returns for General Foods (GF) and Sigma Technology (ST) for the period 2004-2018. ST is highly regarded by many investors for its innovative products. It had returns more than twice as large as that of GF. Assume an investor placed half her funds in GF and half in ST during this 15-year period. Her objective was to earn a larger return than that available in GF alone. Assess the performance of the portfolio relative to the performance of each individual security. a. Calculate the arithmetic mean return for each stock. b. Calculate the standard deviation for each stock using the STDEV function. c. Calculate the correlation coefficient using the CORREL function. e. Calculate the portfolio return assuming equal weights for each stock. f. Set up a formula for the portfolio standard deviation that allows you to substitute different values for the correlation coefficient or the standard deviations of the stocks. Using equal weights for the two stocks, calculate the standard deviation of the portfolio. g. How does the portfolio return compare to the return on GF alone? How does the risk of the portfolio compare to the risk of having held GP? h. Assume that the correlation between the two stocks had been 0.10. How much would portfolio risk have changed relative to the result calculated in f?
GF ST 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009 2008 2007 2006 2005 2004 -0.141 0.203 -0.036 -0.204 0.073 -0.111 0.023 0.291 0.448 0.482 0.196 0.103 0.075 0.780 0.254 0.222 0.079 -0.220 0.527 -0.628 0.684 1.146 0.564 0.885 0.433 0.516 -0.056 0.153 1.207 0.736
returns for General Foods (GF) and Sigma Technology (ST) for the
period 2004-2018. ST is highly regarded by many investors for its
innovative products. It had returns more than twice as large as
that of GF. Assume an investor placed half her funds in GF and half
in ST during this 15-year period. Her objective was to earn a
larger return than that available in GF alone. Assess the
performance of the portfolio relative to the performance of each
individual security. a. Calculate the arithmetic mean return for
each stock. b. Calculate the standard deviation for each stock
using the STDEV function. c. Calculate the correlation coefficient
using the CORREL function. e. Calculate the portfolio return
assuming equal weights for each stock. f. Set up a formula for the
portfolio standard deviation that allows you to substitute
different values for the correlation coefficient or the standard
deviations of the stocks. Using equal weights for the two stocks,
calculate the standard deviation of the portfolio. g. How does the
portfolio return compare to the return on GF alone? How does the
risk of the portfolio compare to the risk of having held GP? h.
Assume that the correlation between the two stocks had been 0.10.
How much would portfolio risk have changed relative to the result
calculated in f?
GF ST 2018 -0.141 0.222 2017 0.203 0.079 2016 -0.036 -0.220 2015
-0.204 0.527 2014 0.073 -0.628 2013 -0.111 0.684 2012 0.023 1.146
2011 0.291 0.564 2010 0.448 0.885 2009 0.482 0.433 2008 0.196 0.516
2007 0.103 -0.056 2006 0.075 0.153 2005 0.780 1.207 2004 0.254
0.736 Use cell formulas wherever possible.
- Complete Spreadsheet Exercise 7 2 7 2 The Data Below Are Annual Returns For General Foods Gf And Sigma Technology St 1 (132.28 KiB) Viewed 46 times
- Complete Spreadsheet Exercise 7 2 7 2 The Data Below Are Annual Returns For General Foods Gf And Sigma Technology St 2 (48.2 KiB) Viewed 46 times
GF ST 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009 2008 2007 2006 2005 2004 -0.141 0.203 -0.036 -0.204 0.073 -0.111 0.023 0.291 0.448 0.482 0.196 0.103 0.075 0.780 0.254 0.222 0.079 -0.220 0.527 -0.628 0.684 1.146 0.564 0.885 0.433 0.516 -0.056 0.153 1.207 0.736