Consolidate all excel spreadsbeets from part1 in a single Exoel file. Please make a cover page (name and ID) in Sheet in your EXCEL file. To submit this project electronically, use Blackboard. DO NOT send me it via email. Please subanit yur project in a single EXCEL file. ONLY an EXCEL FILE will be accepted. You have write up with your own words. Do not copy fron others' work. In the last page, you have to show all your references for your project in the last sheet in your Excel. Part 1 Portfolio Return and Risk Instruction: You are a portfolio investment analyst at Goldman Sachs. Your investment unit manages two equity portolios one portfolio, named PI, consists of two stock assets (Bank of America (BAC) and Mc- Donald Ticker: MCD)) and the other portfolio, named P2, consists of five stocks (Aetnal Ticker:AET), Boe- inglTicker:BA), AmazonTicker AMZN), TeslalTicker:TSLA), DisoerTicker:DFS)). Now you are asked to compute two portfolio returns and risk measures. To do this, first download monthly stock prices from Jan 2010 to Dec.2017 from Bloomberg and compute monthly stock returns from Feb.2010 to Dec.2017.1 Problem 1. Compute the respective average, standard deviation, and covariance of monthly stock returns,2 Problem 2. Maloe two covariance matrices using two portfolio components. Note that you have to make a completed form of a matrix as below. ơhAC = Ơ BAC, BAC ƠMCD. BAC ƠBAC. MCD Ơ B AC. MCD = ƠMCD. BAC ơSdCD.: ƠMCD. MCD OXET ƠAET. BA ƠAET. A.l12N ƠAET. TLSA ƠAET, DFS ƠBA. AET Ơ A ƠBA. AMZN ƠBA. TLSA dyBA. DFS ΣΡ2 ƠAMZN. AET ƠAM ZN. BA ƠAMZN ƠAMZN. TLSA ƠAMZN. DFS (2) ƠTLSA. AET ƠTLSA. BA ƠTLSA. AM ZN ƠTLSA ƠTLSA..DFS ơDFS. AET ƠDPS, BA ƠDFS. AM ZN ƠDFS, TLSA ƠbFS
Problem 3. Using the obtained statistics from problem 1, caleulate an equal weighted portfolio return and portfolio variance for the first portfolio using the below equations. ch-tOBACƠBAC + urMCDƠNCD + 2UBACUMCOƠBAC. MCD R, = A where R, represents stock return at time t. P is a stock price at time t. Use STDEV P in Exoel, not STDEV. S. Note that the formula for standard deviation based a sample is given by amsée- , W1 LK while the formula for standard deviation based on a population is written as σ2pulation Σ Data Analysis Toolpak to compate a covariance matrix