please answer the whole question and explain step by step include formulas and if you are calculating in excel please show all functions and steps

1. You are considering beginning a portfolio. You want to keep it simple however, by only having it contain two assets. Unfortunately, you have narrowed it down to FOUR, not two. So, now you are going to do the final cuts using an expected return analysis. You believe the best way to determine the state of the economy is to use the consumer confidence index (CCI). Those values, along with the retums on each of your four securities are included in the following table. Year Stock A (%) Stock B (%) Stock C (%) Stock D %) 1 2 3 4 5 6 7 8 9 10 SO 11.1 12.4 13.8 6.2 -3.1 -2.0 7.1 8.7 10.3 11.6 4.2 7.1 5.9 11.2 8.3 5.3 8.4 9.1 2.1 -5.4 9 11.2 8.2 15.3 12.0 Consumer Confidenc e Index 78.5 83.1 85.2 73.3 77.3 80.2 83.3 86.1 90.4 87.4 3.2 2.5 9.2 6.2 6.9 7.1 7.9 8.1 9.2 6.2 12.4 S&P 500 (Market) Return (% 12.5 11.8 16.1 6.4 -9.7 3.8 6.9 10.9 14.9 11.2 15.3 12.1 1.1 4.5 a You feel that any CCI value less than 80 is a bad period. Alternatively, any CCI value more than 86 is a good period. Anywhere in between is a normal period. a. If you believe that next year will be characterized by a 20% chance of a good period, a 50% chance of a bad period, and a 30% chance of a normal period, what is the expected return of each? (10 pts) A Expected Returns B D b. Now, suppose you want to use the CAPM to determine the expected return of Stock C. The correlation between stock and the market is.8327. You assume the risk-free rate is 1% and the expected return on the market is the average of the historical returns given. (10 pts) c. The correlation between Stocks A and B is -4794. If you only invest 25% in A and 75% in B, what is your portfolio Standard Deviation? (10 pts)