2. Refer to the Simulation worksheet.This worksheet contains the historical monthly returns of the ASX200 stock index.Using the data provided, compute the:(

a) [1 mark] mean monthly returns of the ASX200 index and

(b) [1 mark] standard deviation of the ASX200 monthly returns.

Let the initial value of the ASX200 index be 6684.20. Using the statistics computed in parts (a) and (b),

(c) [15 marks] Use the Monte Carlo method to simulate 1000 values of the ASX200 index after 12 months. You must provide formulas in the cells of the arrayE9:Q1008.Otherwise, you will receive no credit for this part.

Based on your simulated ASX200 index values (after 12 months),

(d) [5 marks] Write a formula in cellK2that computes the proportion of those values that are above 8500.

Overall,

(e) [3 marks] Provide one limitation/shortcoming/caveat of your Monte Carlo simula-tion.

Prap. (Ending value 500) > Month 4 S ASX 200 Simulation round 1 O.SSN 1 Starting ASX200 value 10 11 12 12 Ending ASK200 value 2 2 -6.87 1.18 3 4 3.12 1.AN 5 1.44% G 7 1.99 7 1.65N 3.69 9 10 4.15 10 11 12 12 13 2.69 13 14 4.IN 14 15 2.SIN 15 16 16 17 1.82 17 18 2.60 18 19 1.96 19 20 1.30N 21 0.22 20 21 22 22 -3.045 4.98 23 24 0.SIN 24 25 1.32 25 0.17 26 26 27 1.59 27 3.53 28 0.7ON 29 30 5.59 30 11 2.90N 4.45 12 33 13 14 2.31 3.74 G.IN 0.82 35 16 36 37 37 2.20N 0.GON 5.IN 19 0.6IN 40 7.89 41 41 42 41 3.04 2.71N 44 0.87% 44 45 0.27 46 4.GEN 46 47 47 -1.95 2.47 4 49 4.ON 48 49 50 SD 51 52 S3 2.95 2.GON JORN 51 52 1.0IN 53 G.BEN 14 55 56 57 4.BIN 59 1.SIN 1.24N 1.64 3.SIN 0.04 2.79 55 56 57 58 59 60 61 62 63 64 0.11 61 62 61 64 65 GG 59 G 67 6 G9 70 71 72 73 OSSN 0.47% D.ON 2.90 1.4 1.42 0.20 1.00 3.2 1.ON 70 71 72 73 74 DEN 74 75 76 77 78 79 AD 1.26 0.74 1.47 -1.37% 7.6IN 2.21 0.11 2.70 75 76 77 71 79 10 TE ZE E 33 84 DEN 1.SON 36 86 87 1.99 87 1.SON HE 29 1.92 90 91 92 1.59N 1.16 2.14 90 91 92 36 94 4.19 15 95 96 7.IN -18.20 4.GEN 56 96 26 97 98 99 4.96 98 99 1.6IN TOD 0.25N 100 101 102N 101 Prap. (Ending value 500) > Month 4 S ASX 200 Simulation round 1 O.SSN 1 Starting ASX200 value 10 11 12 12 Ending ASK200 value 2 2 -6.87 1.18 3 4 3.12 1.AN 5 1.44% G 7 1.99 7 1.65N 3.69 9 10 4.15 10 11 12 12 13 2.69 13 14 4.IN 14 15 2.SIN 15 16 16 17 1.82 17 18 2.60 18 19 1.96 19 20 1.30N 21 0.22 20 21 22 22 -3.045 4.98 23 24 0.SIN 24 25 1.32 25 0.17 26 26 27 1.59 27 3.53 28 0.7ON 29 30 5.59 30 11 2.90N 4.45 12 33 13 14 2.31 3.74 G.IN 0.82 35 16 36 37 37 2.20N 0.GON 5.IN 19 0.6IN 40 7.89 41 41 42 41 3.04 2.71N 44 0.87% 44 45 0.27 46 4.GEN 46 47 47 -1.95 2.47 4 49 4.ON 48 49 50 SD 51 52 S3 2.95 2.GON JORN 51 52 1.0IN 53 G.BEN 14 55 56 57 4.BIN 59 1.SIN 1.24N 1.64 3.SIN 0.04 2.79 55 56 57 58 59 60 61 62 63 64 0.11 61 62 61 64 65 GG 59 G 67 6 G9 70 71 72 73 OSSN 0.47% D.ON 2.90 1.4 1.42 0.20 1.00 3.2 1.ON 70 71 72 73 74 DEN 74 75 76 77 78 79 AD 1.26 0.74 1.47 -1.37% 7.6IN 2.21 0.11 2.70 75 76 77 71 79 10 TE ZE E 33 84 DEN 1.SON 36 86 87 1.99 87 1.SON HE 29 1.92 90 91 92 1.59N 1.16 2.14 90 91 92 36 94 4.19 15 95 96 7.IN -18.20 4.GEN 56 96 26 97 98 99 4.96 98 99 1.6IN TOD 0.25N 100 101 102N 101