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

2 Step 2: Set up the spreadsheet model

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.

3 Step 3: Activate Excel Solver

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.

4 Step 4: Specify the settings and parameters

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.

5 Step 5: Run Excel Solver

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.

6 Step 6: Interpret and analyze 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.

7 Here’s what else to consider

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?