A) Set up a spreadsheet to calculate the 26-week moving average of the index. Pl

Question

A) Set up a spreadsheet to calculate the 26-week moving average of the index. Please show the formulas used.

Note: In order to create the 26-week moving average for the S&P 500, convert the weekly returns to weekly index values using a base of 100 for the week prior to the first week of the data set. Set the value of the index at the beginning of the sample period equal to 100. The index value in each week is then updated by multiplying the previous week's level by (1+rate of return over previous week). Then, graph the resulting S&P 500 values and the 26-week moving average, beginning with the 26th week of the data set. Use Excel's Chart Wizard and save the chart to a new page.

Week of S&P 500 Index S&P 500 return S&P 500 Index Value Moving Average 29-Apr-2005 0.41 22-Apr-2005 0.83 15-Apr-2005 -3.27 8-Apr-2005 0.71 1-Apr-2005 0.13 24-Mar-2005 -1.53 18-Mar-2005 -0.87 11-Mar-2005 -1.80 4-Mar-2005 0.89 25-Feb-2005 0.81 18-Feb-2005 -0.31 11-Feb-2005 0.19 4-Feb-2005 2.70 28-Jan-2005 0.30 21-Jan-2005 -1.41 14-Jan-2005 -0.14 7-Jan-2005 -2.12 31-Dec-2004 0.15 23-Dec-2004 1.33 17-Dec-2004 0.52 10-Dec-2004 -0.27 3-Dec-2004 0.72 26-Nov-2004 1.05 19-Nov-2004 -1.17 12-Nov-2004 1.54 5-Nov-2004 3.18 29-Oct-2004 3.14 22-Oct-2004 -1.12 15-Oct-2004 -1.24 8-Oct-2004 -0.83 1-Oct-2004 1.93 24-Sep-2004 -1.63 17-Sep-2004 0.41 10-Sep-2004 0.92 3-Sep-2004 0.53 27-Aug-2004 0.86 20-Aug-2004 3.15 13-Aug-2004 0.08 6-Aug-2004 -3.43 30-Jul-2004 1.43 23-Jul-2004 -1.38 15-Jul-2004 -1.03 9-Jul-2004 -1.12 2-Jul-2004 -0.80 25-Jun-2004 -0.05 18-Jun-2004 -0.13 10-Jun-2004 1.24 4-Jun-2004 0.16 28-May-2004 2.48 21-May-2004 -0.20 14-May-2004 -0.27 7-May-2004 -0.78 30-Apr-2004 -2.92 23-Apr-2004 0.53 16-Apr-2004 -0.41 8-Apr-2004 -0.22 2-Apr-2004 3.05 26-Mar-2004 -0.15 19-Mar-2004 -0.96 12-Mar-2004 -3.14 5-Mar-2004 1.04 27-Feb-2004 0.07 20-Feb-2004 -0.15 13-Feb-2004 0.27 6-Feb-2004 1.03 30-Jan-2004 -0.91 23-Jan-2004 0.15 16-Jan-2004 1.60 9-Jan-2004 1.21 2-Jan-2004 -0.31 31-Dec-2003 1.46 26-Dec-2003 0.66 19-Dec-2003 1.35 12-Dec-2003 1.19 5-Dec-2003 0.31 28-Nov-2003 2.21 21-Nov-2003 -1.43 14-Nov-2003 -0.27 7-Nov-2003 0.24 31-Oct-2003 2.12 24-Oct-2003 -1.00 17-Oct-2003 0.12 10-Oct-2003 0.80 3-Oct-2003 3.31 26-Sep-2003 -3.81 19-Sep-2003 1.73 12-Sep-2003 -0.27 5-Sep-2003 1.33 29-Aug-2003 1.51 22-Aug-2003 0.24 15-Aug-2003 1.34 8-Aug-2003 -0.26 1-Aug-2003 -1.86 25-Jul-2003 0.54 18-Jul-2003 -0.48 11-Jul-2003 1.26 3-Jul-2003 0.97 27-Jun-2003 -1.96 20-Jun-2003 0.72 13-Jun-2003 0.09 6-Jun-2003 2.51 30-May-2003 3.25 23-May-2003 -1.17 16-May-2003 1.17 9-May-2003 0.36 2-May-2003 3.48 25-Apr-2003 0.59 17-Apr-2003 2.91 11-Apr-2003 -1.20 4-Apr-2003 1.78 28-Mar-2003 -3.60 21-Mar-2003 7.50 14-Mar-2003 0.53 7-Mar-2003 -1.46 28-Feb-2003 -0.83 21-Feb-2003 1.59 14-Feb-2003 0.63 7-Feb-2003 -3.04 31-Jan-2003 -0.66 24-Jan-2003 -4.48 17-Jan-2003 -2.78 10-Jan-2003 2.09 3-Jan-2003 3.27 31-Dec-2002 0.50 27-Dec-2002 -2.27 20-Dec-2002 0.71 13-Dec-2002 -2.49 6-Dec-2002 -2.57 29-Nov-2002 0.62 22-Nov-2002 2.28 15-Nov-2002 1.69 8-Nov-2002 -0.69 1-Nov-2002 0.37 25-Oct-2002 1.50 18-Oct-2002 5.87 11-Oct-2002 4.34 4-Oct-2002 -3.24 27-Sep-2002 -2.13 20-Sep-2002 -4.99 13-Sep-2002 -0.46 6-Sep-2002 -2.42 30-Aug-2002 -2.63 23-Aug-2002 1.30 16-Aug-2002 2.22 9-Aug-2002 5.14 2-Aug-2002 1.34 26-Jul-2002 0.60 19-Jul-2002 -7.99 12-Jul-2002 -6.84 5-Jul-2002 -0.08 28-Jun-2002 0.07 21-Jun-2002 -1.80 14-Jun-2002 -1.97 7-Jun-2002 -3.71 31-May-2002 -1.54 24-May-2002 -2.06 17-May-2002 4.89 10-May-2002 -1.72 3-May-2002 -0.27 26-Apr-2002 -4.34 19-Apr-2002 1.27 12-Apr-2002 -1.04 5-Apr-2002 -2.15 28-Mar-2002 -0.11 22-Mar-2002 -1.50 15-Mar-2002 0.16 8-Mar-2002 2.87 1-Mar-2002 3.85 22-Feb-2002 -1.30 15-Feb-2002 0.73 8-Feb-2002 -2.32 1-Feb-2002 -0.98 25-Jan-2002 0.51 18-Jan-2002 -1.57 11-Jan-2002 -2.30 4-Jan-2002 2.13 31-Dec-2001 -1.11 28-Dec-2001 1.41 21-Dec-2001 1.94 14-Dec-2001 -3.04 7-Dec-2001 1.66 30-Nov-2001 -0.95 23-Nov-2001 1.03 16-Nov-2001 1.64 9-Nov-2001 3.05 2-Nov-2001 -1.58 26-Oct-2001 2.90 19-Oct-2001 -1.66 12-Oct-2001 1.89 5-Oct-2001 2.92 28-Sep-2001 7.78 21-Sep-2001 -11.60 10-Sep-2001 0.62 7-Sep-2001 -4.22 31-Aug-2001 -4.33 24-Aug-2001 1.98 17-Aug-2001 -2.37 10-Aug-2001 -1.99 3-Aug-2001 0.71 27-Jul-2001 -0.42 20-Jul-2001 -0.40 13-Jul-2001 2.11 6-Jul-2001 -2.76 29-Jun-2001 -0.08 22-Jun-2001 0.91 15-Jun-2001 -4.00 8-Jun-2001 0.34 1-Jun-2001 -1.35 25-May-2001 -1.09 18-May-2001 3.72 11-May-2001 -1.65 4-May-2001 1.08 27-Apr-2001 0.81 20-Apr-2001 5.03 12-Apr-2001 4.88 6-Apr-2001 -2.75 30-Mar-2001 1.80 23-Mar-2001 -0.93 16-Mar-2001 -6.72 9-Mar-2001 -0.06 2-Mar-2001 -0.94 23-Feb-2001 -4.28 16-Feb-2001 -1.01 9-Feb-2001 -2.57 2-Feb-2001 -0.40 26-Jan-2001 0.92 19-Jan-2001 1.82 12-Jan-2001 1.56 5-Jan-2001 -1.66 29-Dec-2000 1.10 22-Dec-2000 -0.47 15-Dec-2000 -4.21 8-Dec-2000 4.16 1-Dec-2000 -1.98 24-Nov-2000 -1.90 17-Nov-2000 0.13 10-Nov-2000 -4.26 3-Nov-2000 3.41 27-Oct-2000 -1.24 20-Oct-2000 1.66 13-Oct-2000 -2.47 6-Oct-2000 -1.92 29-Sep-2000 -0.84 22-Sep-2000 -1.17 15-Sep-2000 -1.92 8-Sep-2000 -1.73 1-Sep-2000 0.95 25-Aug-2000 0.99 18-Aug-2000 1.35 11-Aug-2000 0.61 4-Aug-2000 3.03 28-Jul-2000 -4.07 21-Jul-2000 -1.97 14-Jul-2000 2.10 7-Jul-2000 1.67 30-Jun-2000 0.91 23-Jun-2000 -1.57 16-Jun-2000 0.52 9-Jun-2000 -1.37 2-Jun-2000 7.20

Explanation / Answer

Ans;

Answer:

Ideal moving average length = Cycle length/2+1

= 26/2+1=14 week

Nil indicates negative sum of 14 week.

Week of S&P 500 Index S&P 500 Index Value Moving Average 4/29/05 0.41 0.41 nil 4/22/05 0.83 0.83 nil 4/15/05 -3.27 -3.27 nil 04/08/2005 0.71 0.71 nil 04/01/2005 0.13 0.13 nil 3/24/05 -1.53 -1.53 nil 3/18/05 -0.87 -0.87 0.017(0.24/14) 03/11/2005 -1.8 -1.8 0.06(0.84/14) 03/04/2005 0.89 0.89 0.24 (3.36/14) 2/25/05 0.81 0.81 2/18/05 -0.31 -0.31 02/11/2005 0.19 0.19 02/04/2005 2.7 2.7 1/28/05 0.3 0.3 1/21/05 -1.41 -1.41 1/14/05 -0.14 -0.14 01/07/2005 -2.12 -2.12 12/31/04 0.15 0.15 12/23/04 1.33 1.33 12/17/04 0.52 0.52 12/10/2004 -0.27 -0.27 12/03/2004 0.72 0.72

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.