Short Info on Excel Solver
Short Info On Excel Solver Excel Solver is a tool to model and solve linear and nonlinear programming problems. To access it, open Excel, choose the tab “Data” and select “Solver” from the “Analysis” group. If it is not there, you have to install it, by clicking the “File” tab (or the Office button), then “Options”, then “Add-Ins”, and then “Manage Add-Ins”. Check “Solver” there. To solve the model, you have to first program in on a spreadsheet. In the attached “excel-example.xls” we solve the linear programming problem max 3x1 + 2x2 s.t. 2x1 + x2 ≤ 3 x1 + 2x2 ≤ 4 x1 ≥ 0, x2 ≥ 0. The decision variables x1 , x2 are in cells B2:C2. They are called “Changing Variable Cells” in the Solver. You do not have to put any numbers there, but it is convenient to put something to see whether other calculations work well. The coefficients of the constraint matrix A are in B5:C6. The vector b is in E5:E6, and the vector c is in B9:C9. You have to put these data to the spreadsheet. The cells D3:D4 and D9 are calculated cells. They contain formulas to calculate the values of the constraint left hand side Ax and of the objective function cT x. See how they are coded. If you input different values to “changing cells” you get different values in the calculated cells. Now you can go to “Solver”. Specify “Objective” (or “Target Cell”) as D9 (by just clicking on D9). Check “Max”, because you want to maximize. Specify “Changing Cells” as B2:C2 (by selecting with the mouse). Go to the “Constraints” window. Click “Add”. On the left hand side put the cell(s) on the left hand side of the relation. On the right hand side put the cell(s) on the other side of the relation. Select the relation in the middle. This is your constraint. Add other constraints in the same way. Select “Assume Nonnegative”, because both x1 , x2 are greater than or equal to 0. Select “Simplex LP” because you solve a linear model. In the older version of Excel, these selections (“Nonnegative Variables” and “Assume Linear Model”)