Thank you for your help on this project. Where did you get the values for sheet 2?

Instructions: 1. Insert portfolio weights in the yellow highlighted area. 2. Portfolio Standard Deviation is calculated below in grey. Standard Portfolio Deviation Weight x1 x2 8% 20.0% 0% 0 6% 13.0% 0% 0 13% 28.0% 0% 0 2% 16.0% 0% 0 6% 14.0% 0% 0 -6% 16.0% 0% 0 13% 32.0% 0% 0 18% 45.0% 0% 0 21% 37.0% 0% 0 10% 16.0% 0% 0 9% 11.0% 40% 0.001936 14% 18.0% 20% 0.001296 9% 15.0% 20% 0.0009 -1% 19.0% 0% 0 1% 1.0% 0% 0 4% 6.0% 0% 0 4% 13.5% 0% 0 7% 6.0% 20% 0.000144 8% 8.0% 0% 0 100% 0.004276 0.012975 Return IBM KO BMY ORCL MMM BAX BIG NFLX AKAM GE SPY IWM EFA EEM SHY IEF TLT LQD HYG Portfolio Standard Deviation 13.13% Part A IBM KO BMY ORCL MMM Average P/E Ratio EPS Price Earnings Formula= EPS*P/E benchmark 23.7 5.01 118.737 22.6 0.37 8.362 Dividend Growth Beta Rf Rm ke 0.39 0% 0.78 0.75% 9% 7.19% 0.76 2% 1.1 0.75% 9% 9.83% Cash flow/share Growth Beta Rf Rm ke 11.0137258145 7% 0.98 3% 9% 8.88% Part B Theory to be written by you Part C Beta IBM KO BMY ORCL MMM Rf 0.86 0.66 0.78 1.1 0.98 Rm 0.75% 0.75% 0.75% 0.75% 0.75% Expected return = Rf + beta*(Rm-Rf) 9% 7.85% 9% 6.20% 9% 7.19% 9% 9.83% 9% 8.84% IBM Analyst Opinion | Analyst Estimates | International Business Machines Stock - Yahoo Finance. (2017). Finance.yahoo.com. KO Analyst Opinion | Analyst Estimates | Coca-Cola Company (The) Common Stock - Yahoo Finance. (2017). Finance.yahoo.com Stock Value = Dividend*(1+g)/(ke-g) 5.427975 10.04479 Stock Value = cash flow*(1+g)/(ke-g) 626.845 + beta*(Rm-Rf) 17). Finance.yahoo.com. Retrieved 08 June 2017, from https://finance.yahoo.com/quote/IBM/analysts?p=IBM 017). Finance.yahoo.com. Retrieved 08 June 2017, from https://finance.yahoo.com/quote/KO/analysts?p=KO Correlation Matrix Security IBM KO IBM 1 KO 0.289 BMY 0.185 ORCL 0.589 MMM 0.524 BAX 0.368 BIG 0.215 NFLX 0.179 AKAM 0.255 GE 0.539 SPY 0.622 IWM 0.539 EFA 0.568 EEM 0.514 SHY -0.127 IEF -0.252 TLT -0.246 LQD 0.023 HYG 0.429 x2 IBM KO BMY ORCL MMM BAX BIG NFLX AKAM GE SPY IWM EFA EEM SHY IEF TLT LQD HYG 0.0130 IBM KO 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 BMY 0.289 1 0.162 0.316 0.447 0.283 0.184 0.187 0.194 0.393 0.52 0.359 0.444 0.491 0.002 0.012 0.021 0.135 0.417 0.185 0.162 1 0.262 0.242 0.247 0.155 0.143 0.117 0.283 0.348 0.313 0.264 0.198 -0.053 -0.147 -0.155 -0.08 0.197 ORCL MMM BAX BIG NFLX AKAM 0.589 0.524 0.368 0.215 0.179 0.255 0.316 0.447 0.283 0.184 0.187 0.194 0.262 0.242 0.247 0.155 0.143 0.117 1 0.535 0.432 0.249 0.311 0.404 0.535 1 0.425 0.231 0.135 0.228 0.432 0.425 1 0.086 0.265 0.257 0.249 0.231 0.086 1 0.092 0.187 0.311 0.135 0.265 0.092 1 0.225 0.404 0.228 0.257 0.187 0.225 1 0.5 0.672 0.415 0.259 0.254 0.372 0.739 0.703 0.569 0.376 0.372 0.479 0.636 0.585 0.476 0.483 0.317 0.417 0.664 0.617 0.516 0.275 0.284 0.437 0.628 0.586 0.487 0.268 0.314 0.412 -0.178 -0.136 -0.141 -0.054 -0.11 -0.193 -0.29 -0.218 -0.175 -0.092 -0.106 -0.21 -0.31 -0.249 -0.159 -0.074 -0.082 -0.203 -0.048 0.012 0.019 0.048 -0.052 -0.053 0.558 0.51 0.453 0.231 0.25 0.333 BMY ORCL MMM BAX BIG NFLX AKAM 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 GE SPY 0.539 0.393 0.283 0.5 0.672 0.415 0.259 0.254 0.372 1 0.744 0.651 0.635 0.603 -0.229 -0.319 -0.32 -0.089 0.504 GE 0.622 0.52 0.348 0.739 0.703 0.569 0.376 0.372 0.479 0.744 1 0.877 0.857 0.807 -0.276 -0.392 -0.375 -0.054 0.742 IWM EFA EEM SHY IEF 0.539 0.568 0.514 -0.127 0.359 0.444 0.491 0.002 0.313 0.264 0.198 -0.053 0.636 0.664 0.628 -0.178 0.585 0.617 0.586 -0.136 0.476 0.516 0.487 -0.141 0.483 0.275 0.268 -0.054 0.317 0.284 0.314 -0.11 0.417 0.437 0.412 -0.193 0.651 0.635 0.603 -0.229 0.877 0.857 0.807 -0.276 1 0.74 0.688 -0.271 0.74 1 0.856 -0.245 0.688 0.856 1 -0.118 -0.271 -0.245 -0.118 1 -0.386 -0.391 -0.208 0.821 -0.365 -0.396 -0.205 0.668 -0.072 -0.045 0.11 0.668 0.66 0.722 0.776 -0.106 TLT -0.252 0.012 -0.147 -0.29 -0.218 -0.175 -0.092 -0.106 -0.21 -0.319 -0.392 -0.386 -0.391 -0.208 0.821 1 0.929 0.81 -0.194 LQD -0.246 0.021 -0.155 -0.31 -0.249 -0.159 -0.074 -0.082 -0.203 -0.32 -0.375 -0.365 -0.396 -0.205 0.668 0.929 1 0.814 -0.198 0.023 0.135 -0.08 -0.048 0.012 0.019 0.048 -0.052 -0.053 -0.089 -0.054 -0.072 -0.045 0.11 0.668 0.81 0.814 1 0.179 HYG 0.429 0.417 0.197 0.558 0.51 0.453 0.231 0.25 0.333 0.504 0.742 0.66 0.722 0.776 -0.106 -0.194 -0.198 0.179 1 SPY IWM EFA EEM SHY IEF TLT LQD HYG 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0028 0.0023 0.0000 0.0000 0.0000 0.0000 -0.0001 0.0000 0.0000 0.0028 0.0000 0.0016 0.0000 0.0000 0.0000 0.0000 -0.0001 0.0000 0.0000 0.0023 0.0016 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 -0.0001 -0.0001 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 Microsoft Excel 15.0 Answer Report Worksheet: [fin340_spreadsheet Milestone Two.xlsx]Sheet4 Report Created: 09-06-2017 19:59:58 Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: GRG Nonlinear Solution Time: 0.437 Seconds. Iterations: 8 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling Convergence 0.0001, Population Size 100, Random Seed 0, Derivatives Forward, Require Bounds Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max) Cell Name $B$9 Expected Return Return Original Value 0.054 Final Value 0.0576539354 Variable Cells Cell Name $D$3 IBM Portfolio Weight $D$4 KO Portfolio Weight $D$5 BMY Portfolio Weight $D$6 ORCL Portfolio Weight $D$7 MMM Portfolio Weight Original Value 20% 20% 20% 20% 20% Final Value Integer 14% Contin 7% Contin 32% Contin 0% Contin 0% Contin Constraints Cell Name $B$10 Expected Standard deviation Return Cell Value Formula Status Slack 0.4000006076 $B$10=$D$9 Binding 0 Microsoft Excel 15.0 Sensitivity Report Worksheet: [fin340_spreadsheet Milestone Two.xlsx]Scenario 1 (2) Report Created: 09-06-2017 20:07:01 Variable Cells Cell $D$3 $D$4 $D$5 $D$6 $D$7 Name IBM Portfolio Weight KO Portfolio Weight BMY Portfolio Weight ORCL Portfolio Weight MMM Portfolio Weight Final Reduced Value Gradient 0.0734072699 0 0.0382199863 0 0.1679555349 0 0 0.4771775691 0 0.4621694915 Constraints Cell Name $B$9 Expected Return Return Final Lagrange Value Multiplier 0.0300000003 6.937959087 Scenario 1 Standard Portfolio Deviation Weight 8% 20.0% 7% 6% 13.0% 4% 13% 28.0% 17% 2% 16.0% 0% 6% 14.0% 0% Return IBM KO BMY ORCL MMM Expected Return Expected Standard deviation Total amount with Jacob and Rachel IBM KO BMY ORCL MMM 0.03 => 0.208139 Minimize 3% 900000 Return Standard DPortfolio WAmount invested 0.08 0.2 14.11% 126995.4 0.06 0.13 7.33% 65967.62 0.13 0.28 32.28% 290545 0.02 0.16 0.00% 0 0.06 0.14 0.00% 0 Security IBM KO BMY ORCL MMM IBM KO 1 0.289 0.185 0.589 0.524 BMY 0.289 1 0.162 0.316 0.447 ORCL MMM 0.185 0.589 0.524 0.162 0.316 0.447 1 0.262 0.242 0.262 1 0.535 0.242 0.535 1 Symbol IBM KO BMY ORCL MMM BAX BIG NFLX AKAM GE Estimated Beta 0.86 Risk Averse 0.66 Risk Averse 0.78 Risk Averse 1.1 Risk Taking 0.98 Nuetral 0.75 Risk Averse 1.04 Nuetral 1.57 Risk Taking 1.34 Risk Taking 1.12 Risk Taking Risk averse investor will invest in stocks with beta1 Risk nuetral investor will invest in stocks with beta value n n stocks with beta1 n stocks with beta value near to 1