Question

In: Finance

How do you find the optimal weights of a portfolio in excel using solver given a...

How do you find the optimal weights of a portfolio in excel using solver given a predefined risk (target standard deviation)?

Expert Solution

Step 1

Firstly calculate the expected return of the portfolio

Snap for reference with dummy data attached

- To start, the weights for all the assets in the portfolio is kept the same, 20%

Excel function used: MMULT - Matrix Multiplier - Returns the matrix product of 2 arrays.

I10:I14 is the column containing the weights of the assets and B2:B6 is the expected return for each of the asset.

Step 2

Calculate the standard deviation

Step 3

Calculate the Shape Ratio - (Expected Rate - Risk Free Rate/12)/stdev

Assuming Rf = 1.5%

Step 4

Select the solver

Select Sharpe Ratio as the objective function

Select Portfolio Weights as the inputs

Condition - Sum of the portfolio weights should be equal to 1.

Run

jeff jeffy answered 5 months ago

using Solver of excel please dont miss this part Solver of excel 11-34 OPTIMAL PRODUCTION MIX....

using Solver of excel please dont miss this part Solver of excel 11-34 OPTIMAL PRODUCTION MIX. Della Simpson Inc. sells two popular brands of cookies, Della’s Delight and Bonnie’s Bourbon. Della’s Delight goes through the Mixing and Baking Departments, and Bonnie’s Bourbon, a filled cookie, goes through the Mixing, Filling, and Baking departments. LO 2, 3, 5 Maximize $300D + $250 B where D = Della’s Delight and B = Bonnie’s Bourbon Michael Shirra, vice-president of sales, believes that...

What is Mirr? how do you find it using excel without using excel formula? Below is...

What is Mirr? how do you find it using excel without using excel formula? Below is cash flow. I found IRR is 15.59% and required return is 12% CF Y0 Y1 Y2 Y3 Y4 Y5 Y6 Capital spen $ (30,000,000.00) $ 15,062,400.00 Opp cost $ (5,000,000.00) NWC $ (1,120,000.00) $ (150,000.00) $ (170,000.00) $ 140,000.00 $ 1,300,000.00 OCF $ 9,015,600.00 $ 11,392,500.00 $ 12,193,200.00 $ 10,435,800.00 $ (2,640,000.00) $ (3,960,000.00) CF $ (36,120,000.00) $ 8,865,600.00 $ 11,222,500.00 $ 12,333,200.00 $...

Problem: Using Solver, solve the linear program to find the optimal number of batches to make of...

Problem: Using Solver, solve the linear program to find the optimal number of batches to make of each of the three cookies. Price per chocolate chip cookie $ 1.50 Price per sugar cookie $ 1.00 Price per snickerdoodle cookie $ 1.00 Recipes for one batch Number of cookies/batch 20 20 30 Ingredient Chocolate chip cookie recipe Sugar cookie recipe Snickdoodle recipe Butter (sticks) 2 2 2 Sugar (cups) 1 2 1 Eggs 2 3 1 Chocolate chips (cups) 1 0 0...

How to solve using Excel Solver: This is an assignment problem with 10 applicants and 5...

How to solve using Excel Solver: This is an assignment problem with 10 applicants and 5 positions. Determine the optimal assignments that Brenda Last should make. Use the data to determine the optimal assignments, but do not complete questions in the last two paragraphs of the text Formulate a linear programming model for this problem. List the objective function (minimize or maximize) and all model constraints. Solve problem using MS Excel’s Solver Hint: Use the “integer” constraint Highlight the "winning"...

Can you provide solution in the Excel using Solver for the below problem ? On Monday...

Can you provide solution in the Excel using Solver for the below problem ? On Monday morning, you have $3000 in cash on hand. For the next seven days, the following cash requirements must be met: Monday, $5000; Tuesday, $6000; Wednesday, $9000; Thursday, $2000; Friday, $7000; Saturday, $2000; Sunday, $3000. At the beginning of each day, you must decide how much money (if any) to withdraw from the bank. It costs $10 to make a withdrawal of any size. You...

Using the atomic weights of selected elements given below, find the formula weights for the following...

Using the atomic weights of selected elements given below, find the formula weights for the following compounds: Atomic weights of selected elements Ca = 40 g moL-1, C = 12 g moL-1, O = 16 g moL-1, H = 1 g moL-1, P = 31 g moL-1, K = 39 g moL-1 Si = 28 g moL-1, Al = 27 g moL-1, S = 32 g moL-1, N = 14 g moL-1 Example: (NH4)2SO4 Element no. of moles Atomic...

You are a manager that uses Excel to find the optimal solution to a linear programming...

You are a manager that uses Excel to find the optimal solution to a linear programming problem. Before implementing the solution, what should you do?

calculate the reactions conversion percent (by hand and solver in excel) to be able to find...

calculate the reactions conversion percent (by hand and solver in excel) to be able to find good result in aspen in producing Ethylene oxide. The given reactions are: C2H4+ 0.5O2 --> C2H4O C2H4+ 3O2 --> 2CO2+2H2O C2H4O + 2.5O2 --> 2CO2+2H2O Along with stream data (COMPONENT FLOWRATES (Kmol/h) related to first and second reactors as followed: First reactor input Ethylene --> 1047.91 Ethylene oxide --> 6.47 Carbon dioxide--> 31.71 Oxygen --> 6331.12 Nitrogen --> 28,191.39 Water --> 30.98 First reactor...

Practical portfolio optimization can be summarized as "finding optimal portfolio weights for available investment assets that...

Practical portfolio optimization can be summarized as "finding optimal portfolio weights for available investment assets that would minimize risk while maximizing returns given investor’s risk aversion”. However, pure portfolio optimization suffers from a flaw–it gives a lot of weight to the assets (stocks, ETFs, bonds, etc) that performed well recently. Explain why this is a flaw. Identify some practical solutions that could mitigate this flaw.

Given the following all-integer linear program: (COMPLETE YOUR SOLUTION IN EXCEL USING SOLVER AND UPLOAD YOUR...

Given the following all-integer linear program: (COMPLETE YOUR SOLUTION IN EXCEL USING SOLVER AND UPLOAD YOUR FILE. BE SURE THAT EACH WORKSHEET IN THE EXCEL FILE CORRESPONDS TO EACH QUESTION BELOW ) Max 15x1 + 2x2 s. t. 7x1 + x2 < 23 3x1 - x2 < 5 x1, x2 > 0 and integer a. Solve the problem (using SOLVER) as an LP, ignoring the integer constraints. b. What solution is obtained by rounding up fractions greater than or equal...