Help me to solve my current work for assignment 4My teacher said we are supposed to use the solver on excel
https://support.office.com/en-us/article/load-the-solver-add-in-in-excel-612926fc-d53b-46b4-872c-e24772f078caMGMT 3150 FIN 3150 Fall 2019 Assignment 4 - constrained optimization of a portfolio You may do this assignment individually or with one other person. If you work with another, be sure that both know how to set up the analysis and calculate optimal portfolios. 1. Prices Obtain monthly closing prices for five stocks from different industries for the last twelve years. Each stock must have traded publicly for the last twelve (so no recent IPO’s and no stocks with a bankruptcy filing during that period). 2. Returns Calculate monthly returns for each stock. (Be sure to account for dividends, e.g., by using the “adjusted closing price” from yahoo finance.) 3. Average return and risk Determine: (a) the average monthly return for each stock, (b) the standard deviation of monthly returns for each stock, and (c) calculate the correlation coefficient for returns on each pair of stocks. 4. Equally weighted portfolio Determine the average return and standard deviation for an equally weighted portfolio of the five stocks. For each of 5-8 use constrained optimization (e.g., the “solver” in excel) to determine the investment in each stock needed to obtain the portfolio described. For each portfolio, specify: (a) the weight for each stock in the portfolio, (b) the average portfolio return and (c) the portfolio standard deviation. 5. Lowest risk portfolio Find the portfolio that has the lowest possible risk (that is, the lowest standard deviation). 6. Highest return portfolio Find the portfolio that has the highest average return. 7. Highest return portfolio with the same risk as the equally weighted portfolio Find the portfolio that has the highest possible return and the same risk as the equally weighted portfolio (i.e., the same standard deviation as in question 4). 8. Lowest risk portfolio with a return selected by you Select a specific monthly return that is between the monthly return on the equally weighted portfolio (i.e., from question 4) and the return on the highest return portfolio (i.e., from question 6). Find the portfolio with the selected return that has the lowest possible risk (standard deviation). 9. Comment (briefly) on your findings. (Turn in the spreadsheet you use to do the calculations with the answers to each question highlighted to make them easy to locate.) Optimization using Excel and Solver Optimization using Excel and Solver Presented By: Stephanie Bruninga and Sierra Argyle What is Solver? Solver is a Microsoft Excel add-in program you can use to find an optimal (maximum or minimum) value by changing one or more cells For example: How can we maximize our profit by slightly altering price and costs AND take into account specific constraints for costs and price? OR What should the inventory purchasing policy be if the warehouse has limited space, with a different demand for each product? How do I Install Solver for PC? 1. In Excel 2010 and later, go to File, select Options. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. 4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK. After you have loaded the Solver Add-in, the Solver command is available in the Analysis group on the Data tab. How do I Install Solver for Mac? 1. In Excel 2010 and later, go to Insert, select Add-ins. 2. Click Get Add-Ins. 3. In the Office Add-Ins box, , and then search “Solver” Click Add 4. To easily save Solver, go to Tools, select Excel Add-ins. Select Solver Add-in. After you have loaded the Solver Add-in, the Solver command is available in the Analysis group on the Data tab. How do I use Solver? When setting up the problem for the Solver add-in in your Excel worksheet, you will need to define the following items: Objective cell: The target cell in your worksheet whose value is to be maximized, minimized, or made to reach a particular value. Note that this cell must contain a formula. Variable cells: The changing cells in your worksheet whose values are to be adjusted until the answer is found. Constraint cells: The cells that contain the limits that you impose on the changing values in the variable cells and/or the target cell in the objective cell. **Note: Organize the data within your spreadsheet in a logical manner.** Example: A warehouse sells 4 products with a different demand for each product. Each product has a different holding cost and requires a certain amount of space. The warehouse has a limited storage capacity of 50,000 square ft. What is the most efficient ordering policy for the warehouse to minimize cost, while meeting demand? First: Identify Constants and Constraint Constraint Second: Identify Variables and Objective Variable Objective Constraint Run Solver: Questions?