I am trying to calculate the optimal portfolio in Excel and we were told to use the Solver Add-In. My stocks are different, but could someone just explain how the values were calculated in the following picture under the chart "Portfolio Optimization (keep all monthly)"

 

Transcribed Image Text:

AAPL AMZN COST PENN TSLA WMT Total MV AAPL AMZN COST PENN TSLA WMT Ticker Risk-free Rate Portfolio Expected Return Portfolio Variance Portfolio Standard Deviation Portfolio Sharpe Ratio Market Value (04/05/2023) 16,275.86 2,031.00 19,883.40 87.60 9,304.50 14,956.00 62,538.36 $ $ $ $ $ $ $ AAPL Variance-Covariance Matrix (*** Make sure it's in same order as above) AMZN COST PENN Portfolio Statistics monthly 0.0089 0.0064 0.0037 0.0082 0.0125 0.0023 Weight 26.03% 3.25% 31.79% 0.14% 14.88% 23.91% 0.3250% 2.4930% 0.5658% 7.5218% 28.8234% annual 0.0064 0.0100 0.0040 0.0067 0.0115 0.0016 Expected Return Variance SD 2.8088% 0.008940259 0.094552941 0.9526% 0.009970125 0.099850515 1.7926% 0.00428487 0.065458921 2.1349% 0.046151614 0.214829267 6.1585% 0.048186035 0.219513177 1.0115% 0.002861335 0.053491451 3.9000% 29.9163% 6.7892% 26.0562% 99.8471% 0.0037 0.0040 0.0043 0.0031 0.0058 0.0023 0.0082 0.0067 0.0031 0.0462 0.0194 0.0022 TSLA 0.0125 0.0115 0.0058 0.0194 0.0482 0.0039 WMT 0.0023 0.0016 0.0023 0.0022 0.0039 0.0029 AAPL AMZN COST PENN TSLA WMT Total MV AAPL AMZN COST PENN TSLA WMT Ticker Risk-free Rate Portfolio Expected Return Portfolio Variance Portfolio Standard Deviation Portfolio Sharpe Ratio Market Value (04/05/2023) 16,275.86 2,031.00 19,883.40 87.60 9,304.50 14,956.00 62,538.36 $ $ $ $ $ $ $ AAPL Variance-Covariance Matrix (*** Make sure it's in same order as above) AMZN COST PENN Portfolio Statistics monthly 0.0089 0.0064 0.0037 0.0082 0.0125 0.0023 Weight 26.03% 3.25% 31.79% 0.14% 14.88% 23.91% 0.3250% 2.4930% 0.5658% 7.5218% 28.8234% annual 0.0064 0.0100 0.0040 0.0067 0.0115 0.0016 Expected Return Variance SD 2.8088% 0.008940259 0.094552941 0.9526% 0.009970125 0.099850515 1.7926% 0.00428487 0.065458921 2.1349% 0.046151614 0.214829267 6.1585% 0.048186035 0.219513177 1.0115% 0.002861335 0.053491451 3.9000% 29.9163% 6.7892% 26.0562% 99.8471% 0.0037 0.0040 0.0043 0.0031 0.0058 0.0023 0.0082 0.0067 0.0031 0.0462 0.0194 0.0022 TSLA 0.0125 0.0115 0.0058 0.0194 0.0482 0.0039 WMT 0.0023 0.0016 0.0023 0.0022 0.0039 0.0029