In this assignment, you will work with real data to form a portfolio. You will use excel to organize data and calculate the expected return and risk of each stock, and the return of the portfolio.

1. Using the Internet, find the historical prices of the stock of five companies from the following list: - Alphabet (GOOG) - Amazon (AMZN) - Apple (AAPL) - AT&T (T) - Bank of America (BAC) - Boeing (BA) - Coca-cola (KO) - Exxon Mobil (XOM) - Facebook (FB) - Intel (INTC) - JPMorgan Chase & Co. (JPM) - Macys (M) - McDonald's (MCD) - Microsoft (MSFT) - Starbuck (SBUX) - Southwest (LUV) - Toyota (TM) - Walmart (WMT) Try to end up with weekly prices (open, high, low, close, adjusted close price) from Oct 22, 2012 to March 30, 2020. Your data should have date, open price, high price, low price, close price, adjusted close price. (10 points)

2. Copy your data to Company1, Company2,, Company5 worksheets (let call these worksheets as data worksheets) in the file Portfolio_Analysis.xlsx. Rename the worksheets to the names of the companies, and rename the file with your first and last name. (10 points)

3. In your data worksheets, create a new column next to your data and name it Weekly Return. Create formulas to calculate weekly returns in % of each companys stock using close price or adjusted close price. (10 points)

4. In your data worksheets, create formulas/functions in cells M2 and M4 to calculate the expected return (%) and risk of each companys stock accordingly, assuming that we will sell these stocks after a week (or on April 6 if the last date in your data is Mar 30)(10 points)

In Portfolio Worksheet

5. Enter the names of your companies in cells B2, C2, F2. Create formulas/functions to fill each company stock prices in cells B3, C3, , F3 using the most recent (March 30, 2020) close price or adjusted close in your data. (10 points)

6. Create formulas/functions to fill the return and risk of each companys stock in B4:F5. (10 points)

Note for #5, $6: You do not need to create complex functions, just simply use "=" to appropriate cells.

7. Enter any numbers from 100 to 999 in cells B6:F6. Create formulas to calculate the amount of money invested in each companys stock and the portfolio in cells B7:G7 and create formulas to calculate the weight of each stock in the portfolio in Cells B8:F8. (10 points)

8. Create formulas/functions to expected returns in term of $ for each companys stock in cells B9:F9

9. Create formulas/functions to calculate expected return in term of % and $for the portfolio in cells G5 and G9 (10 points)

10. Use Solver to find numbers of each stock in B6:F6 in the portfolio so that each stock has the same weight in the portfolio (20% each) (do not need constant the number of stock from 100 - 999, and integer) and create a solver report in a different worksheet if Solver does not find the solution.(10 points)

11. Save your file and submit it.

Hi. I need help with this question for my Business Problem Solving class. We are working with an excel sheet. Any guidance or formula help is appreciated, thank you!