How do you use Excel Solver to solve a transportation problem? (2024)

To begin, it's essential to define the problem precisely and identify the related data and parameters. This includes understanding the supply from each source or origin (e.g. the amount of products available at each factory), the demand of each destination or sink (e.g. the amount of products required by each customer), and the cost or benefit of transporting one unit of goods or resources from each source to each destination (e.g. the shipping cost or profit per product). You can organize this data into a table or matrix format, where the rows represent sources, columns represent destinations, and cells represent costs or benefits. Additionally, you need to decide whether you want to minimize or maximize the objective function, which is typically the total cost or profit of the transportation plan.

To set up the spreadsheet model in Excel, you need to create three main sections: a data section to enter supply, demand, and cost or benefit data; a decision variables section to enter the amount of goods or resources transported from each source to each destination; and an objective function section to calculate the total cost or profit of the transportation plan. To ensure consistency and accuracy, formulas or references can be used to link the sections. Additionally, it is important that the decision variables are non-negative and that the supply and demand constraints are satisfied, meaning that the sum of the decision variables in each row must be equal to the supply of that source, and the sum of the decision variables in each column must be equal to the demand of that destination.

The third step is to activate Excel Solver, which is an add-in that you can access from the Data tab in Excel. If you do not see the Solver button in the Data tab, you might need to install or enable it from the Excel Options menu. Once you activate Excel Solver, a dialog box will appear where you can specify the settings and parameters for solving the transportation problem.

The fourth step in solving a transportation problem is to specify the settings and parameters for the Excel Solver dialog box. This includes selecting the cell that contains the objective function value as the Set Objective box and choosing whether you want to minimize or maximize the objective function by selecting Min or Max in the Equal To box. Additionally, you should select the range of cells that contains the decision variables as the By Changing Variable Cells box, and add supply and demand constraints by clicking on the Add button and entering the appropriate formulas or references in the Cell Reference, Constraint, and Value boxes. Simplex LP is recommended as the Solving Method for linear programming problems. Other options and settings, such as precision, iteration, and tolerance can be adjusted depending on preferences and needs.

The fifth step is to run Excel Solver by clicking on the Solve button in the Excel Solver dialog box. Excel Solver will try to find the optimal solution for the transportation problem based on the settings and parameters you specified. If Excel Solver finds a feasible and optimal solution, it will display a message that says Solver found a solution. You can then choose whether you want to keep or restore the original values, or generate reports that show more details and information about the solution.

The final step is to interpret and analyze the solution that Excel Solver found. You can look at the values of the decision variables, which show how much goods or resources are transported from each source to each destination. You can also look at the value of the objective function, which shows the total cost or profit of the transportation plan. You can compare different scenarios and solutions by changing the data or parameters and running Excel Solver again. You can also use sensitivity analysis or what-if analysis to examine how changes in the data or parameters affect the solution and the objective function.

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

Linear Programming

+ Follow

Thanks for your feedback

Your feedback is private. Like or react to bring the conversation to your network.