HI, I was wondering if anyone can help me with this assignment! Thank you in advance, please see attachment!

FNCE 451 Fall 2016 Assignment 2 This is going to take significant time and iteration 1. Use a Monte Carlo simulation to model the MLB Playoff bracket (October 3 timestamp: 10 team bracket). The output is a probability of winning the World Series for each team. The input is a measure of team quality such as an \"ELO score\" or similar score for each team. The key is that for any match-up the probability of one team winning is related to the difference of the two ELO scores. (We will model outcomes of series, not individual games) http://projects.fivethirtyeight.com/2016-mlb-predictions/ a. b. c. d. Notice the date drop down box Make a lookup table to connect the team name to its quality score. Create a scaled score on a scale of 0-1. Any difference of the scaled score is therefore a number on (-1,1) NORMDIST() is a function that maps onto (0%, 100%), with 0 mapping to 50%. This is how we use the score differences to make a win probability. For example for a team vs itself the score difference is 0 and the win probability is 50%. As the difference gets large the probability goes toward 0% or 100% (but never a sure thing). e. The STDEV parameter is a way to shape the likelihood of upsets. What kind of game is this- can anything happen or does the favorite nearly always prevail? Put that parameter in a cell and play with numbers around 0.5-4. (This is the 'art' of model building) f. RAND() is a uniform random variable on (0,1). Each call to RAND can be interpreted as a single random outcome of the matchup. If it falls above the win probability, one team wins. Below the win probability, the other team wins. Which is which? g. Build out the whole bracket so that each time you redraw the RAND (by pressing F9 in windows) you get a new World Series winner. h. Use the DataTable feature under Date>WhatIfAnalysis to perform a few thousand trials. Tip: set calcs to manual to prevent it re-evaluating everything all the time. Tip2: Set up a really long table of trials numbered 1, 2, 3, 4... 8000. To trigger the trials click the 'column input cell' on any empty cell. The idea is we are not varying any inputs, just retriggering the RAND. Cumulative Normal Distribution 2. Efficient Frontier for a Canadian balanced portfolio. a. I will give monthly return data for TSX and FTSE-TMX bond index from 1982. Construct a total return series for each that starts at 1 (monthly compounding). Consider also a monthly rebalanced balanced portfolio, say 60%/40%, and let the mix be a parameter you can control. Chart the total returns for all 3 columns. =SUMPRODUCT() is a good way to apply the mix. b. Compute the annualized arithmetic mean return and standard deviation of returns (multiply by 12 and square root of 12) for each asset class and for the blend. Compute the geometric means also and the correlation between bonds and equities. Using a 3year moving window you can check how the correlation has varied over time. c. Make an assumption about forward-looking returns for each asset class. For bonds the current yield is a very good estimate (even better than you think) http://www.cfapubs.org/doi/sum/10.2469/faj.v70.n1.5. For equities we normally use an equity risk premium approach. For example ERP=10% and many alternatives in this recent paper. http://www.newyorkfed.org/research/staff_reports/sr714.pdf d. We will have to use the historical data to estimate forward-looking volatility and correlations. Now you can consider the whole range of bond/equity asset mixes. From 100% bonds to 50/50 to 100% equity. For each mix (say steps of 10%) compute the standard deviation and return, based on the forward-looking returns. Note that the returns are just a linear combination of expected returns, but volatility is not linear because of the correlations. Use these risk/return pairs to make an efficient frontier dot plot (standard deviation versus expected return). e. You should notice that starting from all bonds we move very slightly to the left (lower risk) as we add a little equity to the portfolio. That equity also obviously increases the return too. Show this on the chart. f. Instead of standard deviation, another way to characterize risk is simply to look at a \"2008 stress test\". Invent a risk metric based on that market event (using the actual returns) and apply it to the sample balanced portfolios to present an alternative riskreward tradeoff chart. 3. Efficient Frontier for a Canadian balanced portfolio with hedged USD High Yield Bonds Download the BofAML High Yield Total Return data from FRED (monthly). www.fred.stlouisfed.org Use the High Yield data to optimize 3-asset portfolios of equity, bonds, and HY bonds (extending the previous exercise). Since there are more than 2 assets, the risk/return possibilities span an area instead of just a curve. Find portfolios on the efficient frontier. Find also the ERC (equal risk contribution) portfolio, give its risk/return, and plot on a risk/return chart. You need to run the Excel Solver here to optimize return for each risk. To repeat: set a risk level as a constraint, then find the best portfolio. Iterate on different set levels of risk