Follow the instructions in the assignment document linked below. Submissions should includeA word document written according to the APA style of formatting and containing the descriptions of the analysis and the conclusions. The report should explain the experiments and their respective conclusions, and additional information as indicated in each problem. Be sure to include all your findings along with important statistical issues.An Excel workbook or an R script file containing the details of the analysis. Submit both files as attachments of one individual submission. Please do not submit these two files separately. Assignment Document: Module One Project Descriptions:The project consists of three parts. The submission of this project will consist of two attachments:A Word document that is prepared according to the APA standards of formatting. In the Word document, explain the experiments and their respective conclusions, and additional information as indicated in each problem.Either an Excel workbook or an R script file (.R file) that contain all the work. If Excel is used, please complete each part in a separate worksheet but all worksheets should be located in the same workbook. Please save your Excel workbook in the following format:Excel: ALY6050_ModuleOneProject_Your Last Name_First initial.xlsx.R: ALY6050_ModuleOneProject_Your Last Name_First initial.R.Problem:Suppose that Boston Red Sox and New York Yankees (two American League baseball teams) are scheduled to play a best of three series. The winner of the series will be the first team that wins two of the three games. The probability that the Red Sox win a game in their home stadium is 0.6 and the probability that Yankees win their home game is 0.57. Next, suppose that you place a bet on each game played where you win $500 if the Red Sox win and you lose $520 if the Red Sox lose the game. In parts 1-3 below, assume that the outcomes of the games are independent of each other.Part 1: If the first game is played in Boston, the second game is played in New York, and the third game (if it becomes necessary) is in Boston, then complete parts (i)-(v) below.(Calculate the probability that the Red Sox will win the series.Construct a probability distribution for your net win (X) in the series. Calculate your expected net win (the mean of X) and the standard deviation of X.Use Excel or R to create 1000 random values for X. Let these random values be denoted by Y. Use these Y values to estimate your expected net win by using a 95% confidence interval. Does this confidence interval contain E(X)?Construct a frequency distribution for Y. Next, use the Chi-squared goodness of fit test to verify how closely the distribution of Y has estimated the distribution of X.Use your observations of parts (ii) and (iii) above to describe whether your betting strategy is favorable to you. Write a summary of your observations and analyses in the Word document. Part 2: Repeat part 1 above but assume that the first game is played in New York, the second game is played in Boston, and the third game (if it becomes necessary) is in New York.Part 3: Repeat part 1 above but now assume that the series is a best-of-five series where the first team that wins three games wins the series with games alternating between Boston and New York, with the first game being played in Boston.

Analysis of risk and return, portfolio diversification Here you will apply what you have learned about portfolio theory. Use the monthly-adjusted closing prices for IBM, MSFT, And the S&P500 during the five-year period from January 2013 – December 2017 in the file “Stock Project Stock Prices” posted on Canvas. Calculate returns for each month for each of these three assets (Stock 1; Stock 2; S&P 500). Exercise 1: Calculate the following for each asset (in Excel, using the statistical functions given in parentheses): average return (AVERAGE), standard deviation of returns (STDEV.S), and variance of returns (VAR.S). What is the covariance (COVAR.S) and correlation (CORREL) between the returns of stock 1 and stock 2? Exercise 2: Calculate the return and standard deviation of a portfolio that holds these two stocks in the following weights: 0%-100%; 10%-90%; 20%-80%; 30%-70%; 40%-60%; 50%-50%; 60%-40%; 70%-30%; 80%-20%; 90%-10%, 100%-0%. Plot these portfolio return / standard deviation combinations. Make sure return is on the vertical axis and standard deviation is on the horizontal axis. (Important: use a scatterplot) (You may use excel for this part) Exercise 3 1. Which specific combination would deliver the least amount of risk? Use the formula for the minimum variance portfolio (show your work) to get the exact weights, calculate its return, standard deviation and Sharpe ratio (show your work by hand), and mark it by hand on your plot printout. 2. Draw in the CAL (by hand) that gives you the best risk-return combinations, given that the monthly risk free rate is 0.15%. Mark the optimal risky portfolio. Calculate the optimal risky portfolio’s weights (show your work by hand) in the two stocks (using the book’s formula 6.10). For this optimal portfolio, calculate the average return, standard deviation, and Sharpe ratio (show your work). Mark the ORP on the plot printout by hand. 3. Mark the spot on your return / standard deviation plot where the market index (i.e. S&P 500) falls. 4. For a moment, assume the correlation between the two stocks equals exactly 1. Graph the investment opportunity set. (Hint: This does not require any additional excel work or calculations) 5. Now assume the correlation between the two stocks equals exactly –1. Again, graph the investment opportunity set. If you would like, you can perform steps 4 and 5 on the same graph. What to hand in: A hard copy of your plot, results, and detailed analysis --- this means you should clearly show all of your work! Write out all equations that you use, with the exception of those that are functions in excel such as AVG, COVAR, VAR, etc. For example, you need to write out the equations for portfolio return, portfolio standard deviation, and show all your calculations in determining the MVP and ORP.