$1.$ Convert these stock prices to monthly percent change $($hint: create a separate worksheet within the Excel file$).$

$2.$ Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the statistics to annual for easier interpretation $($multiply the mean return by $12,$ and the standard deviation by square root of $12$: $12).$ You should now have an annual risk $($standard deviation$)$ vs$.$ return for each stock selected.

$3.$ Add a column in your Excel worksheet with the average return across stocks for each month $($not including SPY$).$ As a heading $($title$),$ label it $$Portfolio$.$ This is the monthly return of an equally weighted portfolio of these $10$ stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Convert these monthly statistics to annual $($see step $3).$

$4.$ In addition to the $10$ stocks, portfolio, and SPY calculated statistics, look up the symbol $^$TNX$.$ The current price of the $10-$year treasury can be used as a risk$-$free rate.

$5.$ With all of the statistics gathered, create an Excel plot with the annual standard deviation $($volatility$)$ on the x$-$axis and annual average return on the y$-$axis. You should create the Securities Market Line $($SML$)$ with the risk$-$free rate and market return $($SPY$).$ The SML should look similar to page $268$ of the textbook $($standard deviation on x$-$axis$).$ All the axis, data points, should be labeled and presented in a professional manner.

a$.$ Create three columns on your spreadsheet with the statistics you solved. The first column will have the ticker $($symbol$)$ and $$Portfolio$,$ the second will have annual standard deviation, and the third will have annual mean return. The table should include all $10$ stocks selected, the Portfolio, SPY$,$ and $^$TNX $($standard deviation $=0).$

b$.$ Highlight the data in the last two columns $($standard deviation and mean$),$ choose: $>$ Insert $>$ Chart $>$ XY Scatter Plot. Complete the chart wizard with labels, titles, and headings.

$6.$ Write a summary of findings:

$$ Introduction: Identify the strategy, the goal or objective, and what portfolio you selected.

$$ Body$/$Analysis: Compare and contrast the investments from a risk $/$ reward perspective over the last five years. Describe the graph provided in the spreadsheet created. What do you notice about the average of the volatilities with the individual stocks compared to the volatility of the equally weighted portfolio? What is the advantage of owning the portfolio? What is above, below, and on the SML$?$

$$ Conclusion: Summarize the findings from your portfolio analysis. Provide any insight about future investment.

Date AAPL JNJ PG V DIS VZ WMT AMZN HD ADBE SPY

$1/1/1839\mathrm{.}63319118\mathrm{.}45154673\mathrm{.}485558119\mathrm{.}524132105\mathrm{.}68210640\mathrm{.}09056996\mathrm{.}17936772\mathrm{.}544502174\mathrm{.}861176199\mathrm{.}759995255\mathrm{.}980545$

$2/1/1842\mathrm{.}163677111\mathrm{.}32849167\mathrm{.}339294118\mathrm{.}282982100\mathrm{.}32361635\mathrm{.}80032381\mathrm{.}21112175\mathrm{.}622498158\mathrm{.}645828209\mathrm{.}130005246\mathrm{.}672943$

$3/1/1839\mathrm{.}877972110\mathrm{.}54895867\mathrm{.}991066115\mathrm{.}28911697\mathrm{.}67839835\mathrm{.}8603180\mathrm{.}27278172\mathrm{.}366997155\mathrm{.}138153216\mathrm{.}080002238\mathrm{.}95459$

$4/1/1839\mathrm{.}279015109\mathrm{.}11695962\mathrm{.}039276122\mathrm{.}28625597\mathrm{.}57142637\mathrm{.}00765280\mathrm{.}28848378\mathrm{.}306503161\mathrm{.}765198221\mathrm{.}600006241\mathrm{.}151474$

$5/1/1844\mathrm{.}415279103\mathrm{.}19054463\mathrm{.}331768125\mathrm{.}98723696\mathrm{.}73506936\mathrm{.}19763274\mathrm{.}91534481\mathrm{.}481003163\mathrm{.}297089249\mathrm{.}279999247\mathrm{.}013687$

$6/1/1844\mathrm{.}166622105\mathrm{.}45063867\mathrm{.}564278127\mathrm{.}859718101\mathrm{.}92823838\mathrm{.}20227878\mathrm{.}22782184\mathrm{.}989998171\mathrm{.}737503243\mathrm{.}809998247\mathrm{.}323532$

$7/1/1845\mathrm{.}402561115\mathrm{.}16662670\mathrm{.}005104132\mathrm{.}001068110\mathrm{.}4376639\mathrm{.}212281\mathrm{.}49759788\mathrm{.}872002173\mathrm{.}867737244\mathrm{.}679993257\mathrm{.}637817$

$8/1/1854\mathrm{.}311764117\mathrm{.}05246772\mathrm{.}448364141\mathrm{.}799332109\mathrm{.}81569741\mathrm{.}7638487\mathrm{.}553032100\mathrm{.}635498176\mathrm{.}728531263\mathrm{.}51001265\mathrm{.}861542$

$9/1/1854\mathrm{.}049702120\mathrm{.}87699172\mathrm{.}692932145\mathrm{.}106171114\mathrm{.}63888541\mathrm{.}01105586\mathrm{.}270195100\mathrm{.}150002183\mathrm{.}279236269\mathrm{.}950012266\mathrm{.}237$

$10/1/1852\mathrm{.}402397122\mathrm{.}46918577\mathrm{.}452942133\mathrm{.}272583112\mathrm{.}57040443\mathrm{.}85318492\mathrm{.}12198679\mathrm{.}900497155\mathrm{.}612595245\mathrm{.}759995248\mathrm{.}960861$

$11/1/1842\mathrm{.}758022128\mathrm{.}51434383\mathrm{.}274231137\mathrm{.}004486113\mathrm{.}21741546\mathrm{.}83217689\mathrm{.}7059484\mathrm{.}508499159\mathrm{.}540985250\mathrm{.}889999253\mathrm{.}578873$

$12/1/1837\mathrm{.}900005113\mathrm{.}61737880\mathrm{.}992149127\mathrm{.}787872107\mathrm{.}4923443\mathrm{.}66343785\mathrm{.}57202175\mathrm{.}098503152\mathrm{.}929855226\mathrm{.}240005229\mathrm{.}909088$