Side column, changes within the limits specified by the Allowable Increase and Allowable Decrease values. Source data Here is how our transportation optimization problem looks like: Formulating the model To define our linear programming problem for the Excel Solver, let's answer the 3 main questions: What decisions are to be made? It's probably no big deal to solve this puzzle by trial and error, but I bet the Solver will find the solution faster. Figure shows a typical Limits report. More complex optimization models of this kind are used by many companies to save thousands of dollars each year. This solution has a total profit of
Click on the image to see it full-size. Figure shows the sensitivity report for a nonlinear problem. For nonlinear problems, the sensitivity report is a little different. Answer reports show the initial and final values of the objective Target Cell and all variables Adjustable Cells. Solve the problem After you've configured all the parameters, click the Solve button at the bottom of the Solver Parameters window see the screenshot above and let the Excel Solver add-in find the optimal solution for your problem.
Figure shows a typical Limits report. These reports tell you how sensitive the solution is to small changes in variables and constraints. This report shows the original and final values of the objective function and the decision variables, as well as the status of each constraint at the optimal solution. The result should be consistent with the picture below. It finds the optimal value maximum, minimum or specified for the formula in the Objective cell by changing the values in the Variable cells, and subject to limitations in the Constraints cells. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.
For nonlinear problems, the sensitivity report is a little different. Or, type the ranges manually, separated with commas.
Conclusion: it is only profitable to order child seats if you can sell them for at least 70 units. For a more advanced explanation of linearity and sparsity in optimization problems, continue with our Advanced Tutorial. You've successfully set up and solved a simple optimization problem using Microsoft Excel. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize.
Shadow Price The shadow prices tell us how much the optimal solution can be increased or decreased if we change the right hand side values resources available with one unit.
Want to get more from the Excel Solver? Click on the image to see it full-size. Continue entering other constraints. Solver generates a different sensitivity report for nonlinear problems, as shown in Figure You can quickly see from these reports how close your initial guess was to the optimal solution. It's probably no big deal to solve this puzzle by trial and error, but I bet the Solver will find the solution faster.