12 A1 3 4 5 6 7 A fx B D E F G H J K L M N P Excel Assignment 3: Portfolio return, risk, and Sharpe ratio Names of team members (if any): 1 Your name 2 Classmate 1 3 Classmate 2 66 9 10 You are interested in investing in a stock portfolio that consists of Boeing Co. and Eli Lilly and Co. stocks for the month of May, 2024. 11 Your goal for this Excel assignment is to utilize historical stock price data to determine the optimal portfolio weights for the stocks. 12 These weights should maximize the portfolio's return per unit of risk, known as the Sharpe ratio. 13 14 15 Part 1. The tables show the historical monthly stock price and dividend information for Boeing and Eli Lilly. Compute the monthly returns for each stock. 16 17 Boeing Co. 18 19 20 21 Date 30-Apr-23 31-May-23 30-Jun-23 31-Jul-23 31-Aug-23 30-Sep-23 31-Oct-23 30-Nov-23 31-Dec-23 31-Jan-24 29-Feb-24 31-Mar-24 24-Apr-24 StockPrice ($) $190.49 $213.00 Dividend($) $0.00 $201.55 $0.00 $212.43 $206.78 $205.70 $211.16 $0.00 $0.00 $0.00 $0.00 $238.85 $0.00 $224.03 $0.00 $191.68 $0.00 $186.82 $0.00 $231.63 $0.00 $233.87 $0.00 Monthly total return 22 23 Eli Lilly and Co. 24 25 26 Date StockPrice ($) Dividend($) 30-Apr-23 31-May-23 30-Jun-23 31-Jul-23 31-Aug-23 30-Sep-23 31-Oct-23 30-Nov-23 31-Dec-23 31-Jan-24 29-Feb-24 31-Mar-24 24-Apr-24 $362.27 $340.79 $0.00 $308.18 $0.00 $341.17 $1.13 $393.26 $0.00 $426.64 $0.00 $467.11 $1.13 $452.74 $0.00 $551.99 $0.00 $536.14 $1.13 $552.91 $0.00 $589.95 $0.00 $585.68 $1.13 Monthly total return 27 28 29 30 Part 2. Compute the monthly return for the portfolio, assuming that the portfolio weights for the stocks are 50% each. 31 32 1) Set Boeing's weight to 50% in cell C35. Then, set Eli lilly's weight to 1-C35 to ensure the sum of the weights for Boeing in C35 and Eli Lilly in C36 always equals 100%. 33 34 35 36 37 Boeing Co. Eli Lilly & Co. Weights 38 2) Now, generate the monthly returns using the weights and the stock returns, based on the portfolio return formula found on page 3 in Class Slide 12: 39 40 Portfolio 41 42 13 Rportfolio = WARA + WBRB Date Monthly total return* 30-Apr-23 31-May-23 30-Jun-23 31-Jul-23 31-Aug-23 30-Sep-23 31-Oct-23 30-Nov-23 31-Dec-23 31-Jan-24 29-Feb-24 31-Mar-24 24-Apr-24