Excel solver varying in time to solve - sometimes slow and sometimes fast
Hello, I am trying to use solver in an optimization issue. I have several sheets with cross-referencing and I am trying to solve by adjusting 104 variables. Most often, it is very slow using approximately 5 seconds to go through one subproblem. A few times I have experienced it being much faster completing around 100 subproblems each second. Can anyone shed some light on why it varies so much considering how long it takes Solver to solve the issue? It went through around 12 000 subproblems when it was fast. There are 4 constraints. I have uploadet the exact file I am using.Minimze standard deviation of portfolio using Solver - no feasible solution
Hi, Minimize standard deviation of portfolio with 104 assets - no solution according to Solver I hope someone can help with this issue. I have created in Excel a portfolio of 104 stocks where I have computed the historical annual deviation and return for each stock. Also, I have created a separate sheet using the analysis toolpack to create a matrix of each asset's correlation coefficient with one another. On the basis of this, I have created a formula computing the standard deviation of the portfolio depending on the weight of each asset in the portfolio. When I use Solver to minimize the standard deviation of the portfolio by adjusting the variables concerning the weight of each asset, it says that there is no feasible solution. In any case, there must be a standard deviation that is the minimum? The cells containing the weights is $J$2:$J$105 The formula I use to calculate the standard deviation of the portfolio is: =SQRT(MMULT(MMULT(TRANSPOSE(K2:K105),cor_edit!B2:DA105),K2:K105)) Where (K2:K105) is the standard deviation multiplied with the weight. The adjustable variables in Solver is not set to this, but the column with the weights only. cor_edit!B2:DA105 is the matrix with the correlation coefficients. My constraints are 1) $J$107 = 1 This cell is the sum of all the weights i.e. 100% must be invested. I have tried setting it to the following but with no improvement: $J$107 <= 1 $J$107 >= 0,9 2) $J$2:$J$105 <= 0,1 i.e. an asset cannot make up more than 10%. 3) $J$2:$J$105 >= 0 i.e. an asset cannot have a negative weight. I have tried using the GRG in Solver with multistart and evolutionary. In evolutionary I have tried (in addition to the default settings) with the following settings: Convergence: 0,1 Mutation speed: 0,1 Population size: 200 Maximum time: Has been trying with as high as 7200 (two hours). I have checked the box requiring bounds for variables What I have tried so far to make it work: 1) I have enabled 'Set precision as displayed' adjusting the weights to two decimals trying to reduce the number of options by reducing decimals. 2) I have copy and pasted all values to a new excel file only inserting numbers and not the references. Now, I only have the relevant references i.e. of the sum of all weights, standard deviaiton multiplied with weight, the historical return of the portfolio (weight multiplied with annual return) and of course the formula for calculating the standard deviation. I figured the formulas calculating the standard deviation, return etc might be increasing the workload of excel. As these are not to be adjusted, the formulas have been removed in these cells preserving only the actual value. Ideally, I would like to create a chart of the historical return of the portfolio depending on each value of standard deviation of the portfolio. Do you have any suggestions of how I can find the minimum standard deviation of the portfolio given the mentioned constraints? For your information, when I removed the constraint of the sum being equal to 1 or within a certain range, the Solver worked but only resulting in a sum of 40% which I would like to be 100%. Any help will be much appreciated!
EXCEL SOLVER: LP Simplex and dual Simplex method choose
ISSUE: In the Solver (Data Analysis add-on) isn't clear which of methods is uses: Simplex or dual Simplex method (in the https://www.solver.com/ described, that uses both methods - look below). In the offisial provider page (https://www.solver.com/excel-solver-algorithms-and-methods-used) - described, that using both methods, citate: The Microsoft Office Excel Solver tool uses several algorithms to find optimal solutions. ... The Simplex LP Solving Method for linear programming uses the Simplex and dual Simplex method with bounds on the variables, and problems with integer constraints use the branch and bound method, as implemented by John Watson and Daniel Fylstra, Frontline Systems, Inc. ... - but how can I choose concrete method (or know whith is using)? LP Simplex and dual Simplex method choose precondition: Add solver: https://support.office.com/en-us/article/Load-the-Solver-Add-in-in-Excel-612926fc-d53b-46b4-872c-e24772f078ca?ui=en-US&rs=en-US&ad=US Calculate: https://support.office.com/en-us/article/Define-and-solve-a-problem-by-using-Solver-5d1a388f-079d-43ac-a7eb-f63e45925040?omkt=en-US&ui=en-US&rs=en-US&ad=US / Example of a Solver evaluation To repoduce: Open MS Excel On the Data tab, in the Analysis group, click Solver On select a solving method: choose LP Simplex insert valid parameters and click Solve Actually: choosing only LP Simplex Expectation: choose LP Simplex + method type { Simplex, dual Simplex }
Simple Solver Problem - Can't get it to work
Hello I have a simple solver problem that I can't seem to get to work and I'm not sure why. It is a simple distribution of resources problem, and what I'm trying to solve for is the smallest number of exchanges required to meet the constraints. Here's a photo of the excel sheet: So in the above example, Brad is short 996 units, Matt is owed 294 units, etc. The red cells are the variable cells. The blue cells sum the totals next to each person and the corresponding red cells. The net should equal 0. So the blue cells are constraints that must = 0. Finally, the objective is the count cell. I want this to be the minimum number of "exchanges" that need to occur. I.e. Brad gives Matt 294 (now Matt's balance = 0), Brad gives John 143 (now Matt's balance = 0), and that's 2 exchanges. Here is a screenshot of the solver parameter setup: Here is a solution that I came up on my own...just plugging in numbers: See how the count = 9, the total number of exchanges. However, when I run the solver solution, no matter if I use GRG Non Linear or Evolutionary, I get something like this...which is certainly not the minimum since I was able to find a solution for 9 exchanges just guessing...(10 is greater than 9): What am I missing? This seems pretty simple for solver, but I can't get it to work. Please help! Thank you,Need help with Solver or a better option
I am not sure if this can be done in solver or if there is something better to use. I am querying a large data set of golf stats from multiple excel spreadsheet into 1 combined stats spreadsheet for fantasy golf. Now that I have that, I want to analyze the data to get the bet options. Below is my info. My objective - Maximum Score. I have 2 different custom scores I am looking at, I will decide on which one to use after analysis Item to Change - Golfers. Can be either the name, identifier or combo Constraints Must chose only 6 golfers Max $ spent Min $ spent Number of Lineups to create - 1 to XXX I will be choosing a subset of X number of golfers from X number of entries (i.e. 45 of 150) for consideration I would also like to set a maximum number of times to use each (i.e. Golfer A max 6 times, Golfer B max 3, etc.) When solved, I would like the results on a separate tab in separate cells: G1, G2, G3, G4, G5, G6, Total Score, Total Cost. Any help is greatly appreciated.Excel Solver finding min value of the difference between budget and actual expenses
Hi there, I have 38 receipts from a client funded travel, the budget was $6000, and actual spending is $6777.49, therefore I need to find a combination of receipts that the total amount is as close to $6000 as possible to recover from the client and put the rest in company expenses. I've tried Solver to solve this problem, but the solution is different every time I ran the Solver. There is once it came up with the perfect combination that total amount is exactly $6000 while other times not. How do I set up the Solver case to make sure I have the best result every time? Budget Cell B1=6000 Actual Cell B2 =SUMIF(Table1[Recoverable],1,Table1[Amount]) Difference Cell B3 = B1-B2 Table1[Recoverable] = 0 or 1, 1 means the expense will be recovered Solver parameters: Set Objective: $B$3 (Min) Changing cells: $E$6:$E$43 (which is the Table1[Recoverable]) Constrains: $B$2<=$B$1 $B$2>=0 $B$3>=0 $E$6:$E$43 = binary and I used Evolutionary solve method. I've also attached file with detailed data. If any one can provide possible solution or let me know how do I improve the constrains is much appreciated. Thank you.
