UE is particularly concerned about the risk associated with its

endowment fund and has requested additional insights on this matter. UE$$s risky portfolio invests

in Caterpillar $($CAT$),$ Johnson and Johnson $($JNJ$),$ Walmart $($WMT$),$ Nvidia $($NVDA$),$ and Proctor

and Gamble $($PG$).$ All analyses will be conducted assuming a risk$-$free rate of $0\%$ and no exchange

rate fluctuations. Trading fees and transaction costs are assumed to be negligible given the size of

UE$$s portfolio. Use historical monthly returns from January $2016$ to December $2022$ for all your

analyses and historical averages as proxies for expected returns.

$1.$ Construct the following three portfolios using the abovementioned five stocks using excel; Share a screenshot of the portfolios and formulas plsss

a$.$ P$1$: the optimal risky portfolio with no trading constraints

b$.$ P$2$: the optimal risky portfolio constructed under the constraint that the $95\%$ monthly

portfolio return Value at Risk $($VaR$)$ is $5\%.$ Estimate the VaR using the variance$-$

covariance method, assuming normal distribution for monthly portfolio returns.

c$.$ P$3$: the market portfolio with the following asset weights:

CAT: $7\mathrm{.}35\%$ JNJ: $27\mathrm{.}32\%$ WMT: $22\mathrm{.}70\%$ NVDA: $21\mathrm{.}40\%$ PG: $21\mathrm{.}23\%$

Report the asset weights for each portfolio and calculate key performance measures using

historical monthly returns from $2016$ to $2022.$

$2.$ Estimate the $95\%$ and $99\%$ monthly return VaR for P$1,$ P$2,$ and P$3$ using both the variance$-$

covariance method $($with the normality assumption$)$ and the historical simulation method.

Report the estimation results.

$3.$ Estimate the $95\%$ and $99\%$ monthly return conditional value at risk $($CVaR$)$ for P$1,$ P$2,$ and

P$3$ using the historical simulation method. Report the estimation results.