Using Excel to Formulate and Solve Transportation Problems

By -

Using Excel to Formulate and Solve Transportation Problems

As described in Sec. 3.5, the process of using a spreadsheet to formulate a linear programming model for a problem begins by developing answers to three questions. What are the decisions to be made? What are the constraints on these decisions? What is the overall mea- sure of performance for these decisions? Since a transportation problem is a special type of

Introduction to Operations Research-0110

Introduction to Operations Research-0111

linear programming problem, addressing these questions also is a suitable starting point for formulating this kind of problem on a spreadsheet. The design of the spreadsheet then revolves around laying out this information and the associated data in a logical way.

To illustrate, consider the P & T Co. problem again. The decisions to be made are the number of truckloads of peas to ship from each cannery to each warehouse. The con- straints on these decisions are that the total amount shipped from each cannery must equal its output (the supply) and the total amount received at each warehouse must equal its al- location (the demand). The overall measure of performance is the total shipping cost, so the objective is to minimize this quantity.

This information leads to the spreadsheet model shown in Fig. 9.4. All the data pro- vided in Table 9.2 are displayed in the following data cells: UnitCost (D5:G7), Supply (J12:J14), and Demand (D17:G17). The decisions on shipping quantities are given by the changing cells, ShipmentQuantity (D12:G14). The output cells are TotalShipped (H12:H14) and TotalReceived (D15:G15), where the SUM functions entered into these cells are shown near the bottom of Fig. 9.4. The constraints, TotalShipped (H12:H14) = Supply (J12:J14) and TotalReceived (D15:G15) = Demand (D17:G17), have been specified on the spread- sheet and entered into Solver. The objective cell is TotalCost (J17), where its SUMPROD- UCT function is shown in the lower right-hand corner of Fig. 9.4. The Solver parameters box specifies that the objective is to minimize this objective cell. Choosing the Make Vari-bles Nonnegative option specifies that all shipment quantities must be nonnegative. The Simplex LP solving method is chosen because this is a linear programming problem.

To begin the process of solving the problem, any value (such as 0) can be entered in each of the changing cells. After clicking on the Solve button, Solver will use the simplex method to solve the transportation problem and determine the best value for each of

Introduction to Operations Research-0112

Introduction to Operations Research-0113

Note that Solver simply uses the general simplex method to solve a transportation problem rather than a streamlined version that is specially designed for solving trans- portation problems very efficiently, such as the transportation simplex method presented in the next section. Therefore, a software package that includes such a streamlined version should solve a large transportation problem much faster than Solver.

We mentioned earlier that some problems do not quite fit the model for a transportation problem because they violate the requirements assumption, but that it is possible to re- formulate such a problem to fit this model by introducing a dummy destination or a dummy source. When using Solver, it is not necessary to do this reformulation since the simplex method can solve the original model where the supply constraints are in < form or the demand constraints are in > form. (The Excel files for the next two examples in your OR Courseware illustrate spreadsheet formulations that retain either the supply constraints or the demand constraints in their original inequality form.) However, the larger the problem, the more worthwhile it becomes to do the reformulation and use the transportation simplex method (or equivalent) instead with another software package.

The next two examples illustrate how to do this kind of reformulation.

Comments

Post a Comment

Popular posts from this blog

NETWORK OPTIMIZATION MODELS:THE MINIMUM SPANNING TREE PROBLEM

By -
Image
THE MINIMUM SPANNING TREE PROBLEM The minimum spanning tree problem bears some similarities to the main version of the shortest-path problem presented in the preceding section. In both cases, an undi r ecte d and connected network is being considered, where the given information includes some mea- sure of the positive length (distance, cost, time, etc.) associated with each link. Both problems also involve choosing a set of links that have the shortest total length among all sets of links that satisfy a certain property. For the shortest-path problem, this property is that the chosen links must provide a path between the origin and the destination. For the minimum spanning tree problem, the required property is that the chosen links must provide a path between ea c h pair of nodes. The minimum spanning tree problem can be summarized as follows: 1. You are given the nodes of a network but not the links. Instead, you are given the po- tential links and the positive length for each
Read more

DUALITY THEORY:THE ESSENCE OF DUALITY THEORY

By -
Image
THE ESSENCE OF DUALITY THEORY Given our standard form for the primal problem at the left (perhaps after conversion from another form), its dual problem has the form shown to the right. Thus, with the primal problem in maximizatio n form, the dual problem is in minimization form instead. Furthermore, the dual problem uses exactly the same pa r amete r s as the primal problem, but in different locations, as summarized below. 1. The coefficients in the objective function of the primal problem are the right-hand sides of the functional constraints in the dual problem. 2. The right-hand sides of the functional constraints in the primal problem are the coef- ficients in the objective function of the dual problem. 3. The coefficients of a variable in the functional constraints of the primal problem are the coefficients in a functional constraint of the dual problem. To highlight the comparison, now look at these same two problems in matrix notation (as introduced at the beginn
Read more

DECISION SUPPORT SYSTEMS:DIALOG GENERATION AND MANAGEMENT SYSTEMS

By -
Image
DIALOG GENERATION AND MANAGEMENT SYSTEMS In our efforts in this chapter, we envision a basic DSS structure of the form shown in Figure 12. This figure also shows many of the operational functions of the database management system (DBMS) and the model base management system (MBMS). The primary purpose of the dialog generation and management system (DGMS) is to enhance the propensity and ability of the system user to use and benefit from the DSS. There are doubtless few users of a DSS who use it because of necessity. Most uses of a DSS are optional, and the decision maker must have some motivation and desire to use a DSS or it will likely remain unused. DSS use can occur in a variety of ways. In all uses of a DGMS, it is the DGMS that the user interacts with. In an early seminal text, Bennett (1983) posed three questions to indicate this centrality: 1. What presentations is the user able to see at the DSS display terminal? 2. What must the user know about what is seen at the display
Read more