Description: In this assignment, you will explore the concept of efficient frontier in

portfolio management by graphically representing the trade$-$off between risk and return

for a portfolio consisting of a single stock $($of your choice$)$ and the S&P $500$ index using

Microsoft Excel. The efficient frontier is a fundamental concept in modern portfolio

theory, illustrating the optimal portfolio combinations that offer the highest expected

return for a given level of risk or the lowest risk for a given level of return.

Requirements:

Data Download: $*$ Download Daily Adjusted Closing Price $($Adj Close$)$ from historical

data for a selected stock and the S&P $500$ index $({?}^{S}Px)$ from a financial data provider

or a designated source, such as Yahoo Finance. $-$ The data should include Daily

Adjusted Closing Price $($Adj Close$)$ for a period of at least one year.

Data Processing: $*$ Import the downloaded data into Excel and organize it into a sheet

for the stock and the S&P $500$ index. Calculate daily returns for both the stock and the

index. Calculate the mean $($average$)$ of the returns as the expected returns for each

asset. Calculate the standard deviations, correlation, and covariance of returns for

both assets using Excel functions.

Portfolio Construction: $*$ Construct a portfolio consisting of the selected stock and the

S&P $500$ index. Use investment proportions for the stock of $-20\%-120\%$ in increments

of $2\%.$ For example, you may consider portfolio allocations ranging from $0\%$ to $100\%$

for the stock and the remaining percentage for the index. $*$ They will determine different

portfolio allocations by varying the percentage weight of each asset in the portfolio.

Calculate the expected returns, standard deviations, and Sharpe ratio for each portfolio

and choose the optimal portfolio.

Efficient Frontier Analysis: $*$ Using the calculated mean returns and standard

deviations of returns, you will plot the efficient frontier on a scatter plot graph in Excel.

The x$-$axis of the graph will represent the standard deviation of portfolio returns, and the

$y-$axis will represent the mean portfolio returns. $-$ Each point on the efficient frontier will

correspond to a different portfolio allocation between the stock and the index.