Please show all work using excel and please show all formulas as well. Thank You.

A B B E F H K M N 0 P Calculate the 26-week moving average of the index. 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 retum over previous week). 1) Identify every instance in which the index crosses through its moving average from below. In how many of the weeks following a cross-through does the index increase? Decrease? Answer: 2) Identify every instance in which the index crosses through its moving average from above. In how many of the weeks following a cross-through does the index increase? Decrease? Answer: 3) How well does the moving average rule perform in identifying buy or sell opportunities? Answer S 1 Week 2 2 2004.01 3 3 2004.02 4 2004.03 5 2004.04 6 2004.OS 7 2004.06 8 2004.07 9 2004.08 10 2004.09 11 2004.1 12 2004.11 13 2004.12 14 2004.13 15 2004.14 16 2004.15 17 2004.16 18 2004.17 19 2004.18 20 2004.19 21 2004.2 22 2004.21 23 2004.22 24 2004.23 25 2004.24 26 2004.25 27 2004.26 28 2004.27 29 2004.28 30 2004.29 31 2004.3 32 2004.31 33 2004.32 34 2004.33 35 2004.34 36 2004.35 37 2004.36 38 2004.37 39 2004.38 40 2004.39 41 2004.4 42 2004.41 43 2004.42 44 2004.43 45 2004.44 46 2004.45 47 2004.46 48 2004.47 49 2004.48 50 2004.49 51 2004.5 52 2004.51 53 2004.52 54 2005.01 55 2005.02 56 2005.03 57 2005.04 58 2005.05 59 2005.06 60 2005.07 61 2005.08 62 2005.09 63 2005.1 64 2005.11 S&P 500 Return 0.0164 0.0017 -0.0082 0.0085 0.0059 -0.0021 0.0012 0.0119 -0.0327 -0.0100 -0.0003 0.0325 -0.0023 -0.0047 0.0047 -0.0297 -0.0091 0.0007 -0.0020 0.0277 0.0011 0.0121 -0.0027 0.0019 -0.0085 -0.0102 -0.0092 -0.0157 0.0172 -0.0360 0.0032 0.0307 0.0088 0.0059 0.0085 0.0050 -0.0149 0.0196 -0.0100 -0.0111 -0.0113 0.0291 0.0361 0.0129 -0.0086 0.0079 0.0076 0.0006 0.0057 0.0111 0.0008 -0.0201 -0.0017 -0.0124 0.0056 0.0238 0.0045 -0.0032 0.0087 0.0107 -0.0191 -0.0115 -0.0118 D S&P 500 Index 26-week moving average 100.00 101.64 101.82 100.98 101.84 102.44 102.22 102.34 103.55 100.17 99.17 99.14 102.36 102.12 101.64 102.11 99.08 98.18 98.25 98.05 100.77 100.88 102.10 101.83 102.02 101.16 100.90 100.13 99.21 97.65 99.33 95.75 96.06 99.01 99.88 100.47 101.32 101.83 100.31 102.27 101.24 100.12 98.98 101.87 105.54 106.90 105.98 106.82 107.63 107.70 108.31 109.52 109.61 107.41 107.23 105.89 106.49 109.02 109.52 109.17 110.12 111.30 109.17 107.92 A B C D E 65 2005.12 66 2005.13 67 2005.14 68 2005.15 69 2005.16 70 2005.17 71 2005.18 72 2005. 19 73 2005.2 74 2005. 21 75 2005.22 76 2005.23 77 2005.24 78 2005.25 79 2005. 26 80 2005.27 81 2005. 28 82 2005.29 83 2005.3 84 2005.31 85 2005.32 86 2005.33 87 2005.34 88 2005.35 89 2005.36 90 2005.37 91 2005.38 92 2005.39 93 2005.4 94 2005.41 95 2005.42 96 2005.43 97 2005.44 98 2005.45 99 2005.46 100 2005.47 101 2005.48 102 2005.49 103 2005.5 104 2005.51 105 2005.52 106 2006.01 107 2006.02 108 2006.03 109 2006.04 110 2006.05 111 2006.06 112 2006.07 113 2006.08 114 2006.09 115 2006.1 116 2006.11 117 2006.12 118 2006.13 119 2006.14 120 2006.15 121 2006.16 122 2006.17 123 2006.18 124 2006.19 125 2006.2 126 2006.21 127 2006.22 128 2006.23 0.0025 0.0048 -0.0326 0.0124 0.0016 0.0115 -0.0117 0.0293 0.0096 -0.0009 0.0005 0.0137 -0.0196 0.0046 0.0149 0.0126 0.0057 0.0016 -0.0069 0.0014 -0.0047 -0.0139 0.0124 0.0191 -0.0046 -0.0167 0.0132 -0.0279 -0.0078 -0.0045 0.0141 0.0193 0.0135 0.0110 0.0160 -0.0022 -0.0041 0.0055 0.0031 -0.0177 0.0315 0.0019 -0.0210 0.0204 -0.0176 0.0029 0.0172 0.0047 -0.0050 -0.0013 0.0199 -0.0032 -0.0029 -0.0023 -0.0064 0.0190 0.0024 0.0080 -0.0248 -0.0166 0.0101 0.0048 -0.0283 -0.0012 106.64 106.91 107.42 103.92 105.21 105.38 106.60 105.35 108.44 109.48 109.38 109.43 110.93 108.76 109.26 110.89 112.28 112.92 113.10 112.32 112.48 111.95 110.39 111.76 113.89 113.37 111.47 112.94 109.79 108.94 108.44 109.97 112.10 113.61 114.86 116.70 116.44 115.97 116.61 116.97 114.90 118.52 118.75 116.25 118.62 116.53 116.86 118.87 119.42 118.82 118.66 121.02 120.64 120.29 120.01 119.24 121.50 121.80 122.78 119.74 117.75 118.95 119.52 116.13 A B c D 156 129 2006.24 130 2006.25 131 2006.26 132 2006.27 133 2006.28 134 2006.29 135 2006.3 136 2006.31 137 2006.32 138 2006.33 139 2006.34 140 2006.35 141 2006.36 142 2006.37 143 2006.38 144 2006.39 145 2006.4 146 2006.41 147 2006.42 148 2006.43 149 2006.44 150 2006.45 151 2006.46 152 2006.47 153 2006.48 154 2006.49 155 2006.5 2006.51 157 2006.52 158 2007.01 159 2007.02 160 2007.03 161 2007.04 162 2007.05 163 2007.06 164 2007.07 165 2007.08 166 2007.09 167 2007.1 168 2007.11 169 2007.12 170 2007.13 171 2007.14 172 2007.15 173 2007.16 174 2007.17 175 2007.18 176 2007.19 177 2007.2 178 2007.21 179 2007.22 180 2007.23 181 2007.24 182 2007.25 183 2007.26 184 2007.27 185 2007.28 186 2007.29 187 2007.3 188 2007.31 189 2007.32 190 2007.33 191 2007.34 192 2007.35 -0.0017 0.0229 -0.0053 -0.0244 0.0034 0.0325 0.0017 -0.0093 0.0289 -0.0067 0.0123 -0.0086 0.0173 -0.0038 0.0161 0.0108 0.0120 0.0015 0.0078 -0.0100 0.0125 0.0158 -0.0005 -0.0009 0.0085 0.0065 -0.0111 0.0062 -0.0076 0.0192 -0.0029 -0.0048 0.0188 -0.0060 0.0124 -0.0029 -0.0457 0.0152 -0.0121 0.0351 -0.0097 0.0158 0.0074 0.0227 0.0061 0.0093 -0.0003 0.0116 -0.0061 0.0157 -0.0197 0.0178 -0.0164 -0.0008 0.0170 0.0122 -0.0087 -0.0546 -0.0090 0.0063 0.0000 0.0251 -0.0050 -0.0103 116.00 115.80 118.44 117.82 114.95 115.34 119.10 119.30 118.20 121.61 120.80 122.29 121.24 123.34 122.88 124.85 126.19 127.71 127.90 128.90 127.62 129.21 131.25 131. 18 131.06 132.18 133.04 131.56 132.37 131.36 133.89 133.49 132.85 135.35 134.54 136.21 135.81 129.61 131.59 130.00 134.56 133.25 135.36 136.36 139.46 140.31 141.62 141.57 143.22 142.35 144.59 141.74 144.25 141.88 141.77 144.17 145.93 144.66 136.76 135.53 136.37 136.37 139.79 139.09 193 2007.36 194 2007.37 195 2007.38 196 2007.39 197 2007.4 198 2007.41 199 2007.42 200 2007.43 201 2007.44 202 2007.45 203 2007.46 204 2007.47 205 2007.48 206 2007.49 207 2007.5 208 2007.51 209 2007.52 210 2008.01 211 2008.02 212 2008.03 213 2008.04 214 2008.05 215 2008.06 216 2008.07 217 2008.08 218 2008.09 219 2008.1 220 2008.11 221 2008.12 222 2008.13 223 2008.14 224 2008.15 225 2008.16 226 2008.17 227 2008.18 228 2008.19 229 2008.2 230 2008.21 231 2008.22 232 2008.23 233 2008.24 234 2008.25 235 2008.26 236 2008.27 237 2008.28 238 2008.29 239 2008.3 240 2008.31 241 2008.32 242 2008.33 243 2008.34 244 2008.35 245 2008.36 246 2008.37 247 2008.38 248 2008.39 249 2008.4 250 2008.41 251 2008.42 252 2008.43 253 2008.44 254 2008.45 255 2008.46 256 2008.47 257 2008.48 258 2008.49 259 2008.5 260 2008.51 261 2008.52 262 0.0194 0.0255 0.0040 0.0214 0.0031 -0.0426 0.0263 -0.0157 -0.0401 0.0045 -0.0114 0.0315 0.0152 -0.0248 0.0118 -0.0056 -0.0406 -0.0082 -0.0577 0.0075 0.0491 -0.0467 0.0156 0.0035 -0.0132 -0.0308 -0.0007 0.0241 -0.0043 0.0409 -0.0256 0.0382 0.0081 0.0137 -0.0185 0.0270 -0.0352 0.0196 -0.0289 -0.0011 -0.0287 -0.0308 -0.0096 -0.0195 0.0172 -0.0040 0.0055 0.0254 0.0062 -0.0040 -0.0066 -0.0340 0.0135 -0.0099 -0.0263 -0.0870 -0.1979 0.0533 -0.0663 0.1125 -0.0307 -0.0771 -0.0819 0.1329 -0.0239 0.0120 -0.0009 -0.0117 0.0666 137.66 140.32 143.90 144.48 147.57 148.03 141.73 145.46 143.17 137.43 138.05 136.47 140.77 142.90 139.36 141.01 140.22 134.52 133.41 125.71 126.65 132.87 126.67 128.64 129.10 127.39 123.47 123.38 126.35 125.81 130.95 127.60 132.47 133.55 135.38 132.88 136.47 131.67 134.26 130.38 130.24 126.50 122.61 121.44 119.07 121.12 120.64 121.30 124.38 125.15 124.64 123.83 119.62 121.23 120.02 116.86 106.69 85.57 90.14 84.16 93.63 90.76 83.76 76.90 87.11 85.03 86.05 85.97 84.96 A B B E F H K M N 0 P Calculate the 26-week moving average of the index. 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 retum over previous week). 1) Identify every instance in which the index crosses through its moving average from below. In how many of the weeks following a cross-through does the index increase? Decrease? Answer: 2) Identify every instance in which the index crosses through its moving average from above. In how many of the weeks following a cross-through does the index increase? Decrease? Answer: 3) How well does the moving average rule perform in identifying buy or sell opportunities? Answer S 1 Week 2 2 2004.01 3 3 2004.02 4 2004.03 5 2004.04 6 2004.OS 7 2004.06 8 2004.07 9 2004.08 10 2004.09 11 2004.1 12 2004.11 13 2004.12 14 2004.13 15 2004.14 16 2004.15 17 2004.16 18 2004.17 19 2004.18 20 2004.19 21 2004.2 22 2004.21 23 2004.22 24 2004.23 25 2004.24 26 2004.25 27 2004.26 28 2004.27 29 2004.28 30 2004.29 31 2004.3 32 2004.31 33 2004.32 34 2004.33 35 2004.34 36 2004.35 37 2004.36 38 2004.37 39 2004.38 40 2004.39 41 2004.4 42 2004.41 43 2004.42 44 2004.43 45 2004.44 46 2004.45 47 2004.46 48 2004.47 49 2004.48 50 2004.49 51 2004.5 52 2004.51 53 2004.52 54 2005.01 55 2005.02 56 2005.03 57 2005.04 58 2005.05 59 2005.06 60 2005.07 61 2005.08 62 2005.09 63 2005.1 64 2005.11 S&P 500 Return 0.0164 0.0017 -0.0082 0.0085 0.0059 -0.0021 0.0012 0.0119 -0.0327 -0.0100 -0.0003 0.0325 -0.0023 -0.0047 0.0047 -0.0297 -0.0091 0.0007 -0.0020 0.0277 0.0011 0.0121 -0.0027 0.0019 -0.0085 -0.0102 -0.0092 -0.0157 0.0172 -0.0360 0.0032 0.0307 0.0088 0.0059 0.0085 0.0050 -0.0149 0.0196 -0.0100 -0.0111 -0.0113 0.0291 0.0361 0.0129 -0.0086 0.0079 0.0076 0.0006 0.0057 0.0111 0.0008 -0.0201 -0.0017 -0.0124 0.0056 0.0238 0.0045 -0.0032 0.0087 0.0107 -0.0191 -0.0115 -0.0118 D S&P 500 Index 26-week moving average 100.00 101.64 101.82 100.98 101.84 102.44 102.22 102.34 103.55 100.17 99.17 99.14 102.36 102.12 101.64 102.11 99.08 98.18 98.25 98.05 100.77 100.88 102.10 101.83 102.02 101.16 100.90 100.13 99.21 97.65 99.33 95.75 96.06 99.01 99.88 100.47 101.32 101.83 100.31 102.27 101.24 100.12 98.98 101.87 105.54 106.90 105.98 106.82 107.63 107.70 108.31 109.52 109.61 107.41 107.23 105.89 106.49 109.02 109.52 109.17 110.12 111.30 109.17 107.92 A B C D E 65 2005.12 66 2005.13 67 2005.14 68 2005.15 69 2005.16 70 2005.17 71 2005.18 72 2005. 19 73 2005.2 74 2005. 21 75 2005.22 76 2005.23 77 2005.24 78 2005.25 79 2005. 26 80 2005.27 81 2005. 28 82 2005.29 83 2005.3 84 2005.31 85 2005.32 86 2005.33 87 2005.34 88 2005.35 89 2005.36 90 2005.37 91 2005.38 92 2005.39 93 2005.4 94 2005.41 95 2005.42 96 2005.43 97 2005.44 98 2005.45 99 2005.46 100 2005.47 101 2005.48 102 2005.49 103 2005.5 104 2005.51 105 2005.52 106 2006.01 107 2006.02 108 2006.03 109 2006.04 110 2006.05 111 2006.06 112 2006.07 113 2006.08 114 2006.09 115 2006.1 116 2006.11 117 2006.12 118 2006.13 119 2006.14 120 2006.15 121 2006.16 122 2006.17 123 2006.18 124 2006.19 125 2006.2 126 2006.21 127 2006.22 128 2006.23 0.0025 0.0048 -0.0326 0.0124 0.0016 0.0115 -0.0117 0.0293 0.0096 -0.0009 0.0005 0.0137 -0.0196 0.0046 0.0149 0.0126 0.0057 0.0016 -0.0069 0.0014 -0.0047 -0.0139 0.0124 0.0191 -0.0046 -0.0167 0.0132 -0.0279 -0.0078 -0.0045 0.0141 0.0193 0.0135 0.0110 0.0160 -0.0022 -0.0041 0.0055 0.0031 -0.0177 0.0315 0.0019 -0.0210 0.0204 -0.0176 0.0029 0.0172 0.0047 -0.0050 -0.0013 0.0199 -0.0032 -0.0029 -0.0023 -0.0064 0.0190 0.0024 0.0080 -0.0248 -0.0166 0.0101 0.0048 -0.0283 -0.0012 106.64 106.91 107.42 103.92 105.21 105.38 106.60 105.35 108.44 109.48 109.38 109.43 110.93 108.76 109.26 110.89 112.28 112.92 113.10 112.32 112.48 111.95 110.39 111.76 113.89 113.37 111.47 112.94 109.79 108.94 108.44 109.97 112.10 113.61 114.86 116.70 116.44 115.97 116.61 116.97 114.90 118.52 118.75 116.25 118.62 116.53 116.86 118.87 119.42 118.82 118.66 121.02 120.64 120.29 120.01 119.24 121.50 121.80 122.78 119.74 117.75 118.95 119.52 116.13 A B c D 156 129 2006.24 130 2006.25 131 2006.26 132 2006.27 133 2006.28 134 2006.29 135 2006.3 136 2006.31 137 2006.32 138 2006.33 139 2006.34 140 2006.35 141 2006.36 142 2006.37 143 2006.38 144 2006.39 145 2006.4 146 2006.41 147 2006.42 148 2006.43 149 2006.44 150 2006.45 151 2006.46 152 2006.47 153 2006.48 154 2006.49 155 2006.5 2006.51 157 2006.52 158 2007.01 159 2007.02 160 2007.03 161 2007.04 162 2007.05 163 2007.06 164 2007.07 165 2007.08 166 2007.09 167 2007.1 168 2007.11 169 2007.12 170 2007.13 171 2007.14 172 2007.15 173 2007.16 174 2007.17 175 2007.18 176 2007.19 177 2007.2 178 2007.21 179 2007.22 180 2007.23 181 2007.24 182 2007.25 183 2007.26 184 2007.27 185 2007.28 186 2007.29 187 2007.3 188 2007.31 189 2007.32 190 2007.33 191 2007.34 192 2007.35 -0.0017 0.0229 -0.0053 -0.0244 0.0034 0.0325 0.0017 -0.0093 0.0289 -0.0067 0.0123 -0.0086 0.0173 -0.0038 0.0161 0.0108 0.0120 0.0015 0.0078 -0.0100 0.0125 0.0158 -0.0005 -0.0009 0.0085 0.0065 -0.0111 0.0062 -0.0076 0.0192 -0.0029 -0.0048 0.0188 -0.0060 0.0124 -0.0029 -0.0457 0.0152 -0.0121 0.0351 -0.0097 0.0158 0.0074 0.0227 0.0061 0.0093 -0.0003 0.0116 -0.0061 0.0157 -0.0197 0.0178 -0.0164 -0.0008 0.0170 0.0122 -0.0087 -0.0546 -0.0090 0.0063 0.0000 0.0251 -0.0050 -0.0103 116.00 115.80 118.44 117.82 114.95 115.34 119.10 119.30 118.20 121.61 120.80 122.29 121.24 123.34 122.88 124.85 126.19 127.71 127.90 128.90 127.62 129.21 131.25 131. 18 131.06 132.18 133.04 131.56 132.37 131.36 133.89 133.49 132.85 135.35 134.54 136.21 135.81 129.61 131.59 130.00 134.56 133.25 135.36 136.36 139.46 140.31 141.62 141.57 143.22 142.35 144.59 141.74 144.25 141.88 141.77 144.17 145.93 144.66 136.76 135.53 136.37 136.37 139.79 139.09 193 2007.36 194 2007.37 195 2007.38 196 2007.39 197 2007.4 198 2007.41 199 2007.42 200 2007.43 201 2007.44 202 2007.45 203 2007.46 204 2007.47 205 2007.48 206 2007.49 207 2007.5 208 2007.51 209 2007.52 210 2008.01 211 2008.02 212 2008.03 213 2008.04 214 2008.05 215 2008.06 216 2008.07 217 2008.08 218 2008.09 219 2008.1 220 2008.11 221 2008.12 222 2008.13 223 2008.14 224 2008.15 225 2008.16 226 2008.17 227 2008.18 228 2008.19 229 2008.2 230 2008.21 231 2008.22 232 2008.23 233 2008.24 234 2008.25 235 2008.26 236 2008.27 237 2008.28 238 2008.29 239 2008.3 240 2008.31 241 2008.32 242 2008.33 243 2008.34 244 2008.35 245 2008.36 246 2008.37 247 2008.38 248 2008.39 249 2008.4 250 2008.41 251 2008.42 252 2008.43 253 2008.44 254 2008.45 255 2008.46 256 2008.47 257 2008.48 258 2008.49 259 2008.5 260 2008.51 261 2008.52 262 0.0194 0.0255 0.0040 0.0214 0.0031 -0.0426 0.0263 -0.0157 -0.0401 0.0045 -0.0114 0.0315 0.0152 -0.0248 0.0118 -0.0056 -0.0406 -0.0082 -0.0577 0.0075 0.0491 -0.0467 0.0156 0.0035 -0.0132 -0.0308 -0.0007 0.0241 -0.0043 0.0409 -0.0256 0.0382 0.0081 0.0137 -0.0185 0.0270 -0.0352 0.0196 -0.0289 -0.0011 -0.0287 -0.0308 -0.0096 -0.0195 0.0172 -0.0040 0.0055 0.0254 0.0062 -0.0040 -0.0066 -0.0340 0.0135 -0.0099 -0.0263 -0.0870 -0.1979 0.0533 -0.0663 0.1125 -0.0307 -0.0771 -0.0819 0.1329 -0.0239 0.0120 -0.0009 -0.0117 0.0666 137.66 140.32 143.90 144.48 147.57 148.03 141.73 145.46 143.17 137.43 138.05 136.47 140.77 142.90 139.36 141.01 140.22 134.52 133.41 125.71 126.65 132.87 126.67 128.64 129.10 127.39 123.47 123.38 126.35 125.81 130.95 127.60 132.47 133.55 135.38 132.88 136.47 131.67 134.26 130.38 130.24 126.50 122.61 121.44 119.07 121.12 120.64 121.30 124.38 125.15 124.64 123.83 119.62 121.23 120.02 116.86 106.69 85.57 90.14 84.16 93.63 90.76 83.76 76.90 87.11 85.03 86.05 85.97 84.96