Other than that, Solver is also popular for solving smooth and non-smooth linear problems. It is also useful for solving linear programming models (linear optimization problems), and because of this, it is also known as a linear programming solver. The Excel Solver is essentially used for simulation and optimization of several business and engineering prototypes. It is a type of the 'What-if-analysis' that is useful when the user wants to find out the "best" outcome for a given set of two or more assumptions. It is an optimization tool that uses operational research techniques to determine the optimal solutions and fetch the desired outcomes by altering the assumptions for objective problems. Solver is an add-in programming tool supported by MS Excel. All constraints are satisfied.Next → ← prev Solver in Excel What is Solver? This solution gives the minimum cost of 26000. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.Ĭonclusion: it is optimal to ship 100 units from Factory 1 to Customer 2, 100 units from Factory 2 to Customer 2, 100 units from Factory 2 to Customer 3, 200 units from Factory 3 to Customer 1 and 100 units from Factory 3 to Customer 3. Click Add to enter the following constraint.ħ. Click Add to enter the following constraint.Ħ. Enter Shipments for the Changing Variable Cells.ĥ.
You have the choice of typing the range names or clicking on the cells in the spreadsheet.Ĥ. The result should be consistent with the picture below. Note: can't find the Solver button? Click here to load the Solver add-in.Įnter the solver parameters (read on). On the Data tab, in the Analyze group, click Solver. To find the optimal solution, execute the following steps.ġ. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.
#OPTIMIZATION MODELS IN EXCEL SOLVER EXAMPLES TRIAL#
It is not necessary to use trial and error. With this formulation, it becomes easy to analyze any trial solution.įor example, if we ship 100 units from Factory 1 to Customer 1, 200 units from Factory 2 to Customer 2, 100 units from Factory 3 to Customer 1 and 200 units from Factory 3 to Customer 3, Total Out equals Supply and Total In equals Demand. Total Cost equals the sumproduct of UnitCost and Shipments. Range NameĮxplanation: The SUM functions calculate the total shipped from each factory (Total Out) to each customer (Total In). To make the model easier to understand, create the following named ranges. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the shipments, so the objective is to minimize this quantity.Ģ. What are the constraints on these decisions? Each factory has a fixed supply and each customer has a fixed demand.Ĭ. What are the decisions to be made? For this problem, we need Excel to find out how many units to ship from each factory to each customer.ī. To formulate this transportation problem, answer the following three questions.Ī.