Introduction to Management Science with Spreadsheets Stevenson and

Learning Objectives After completing this chapter, you should be able to: 1. Describe the

Learning Objectives (cont’d) After completing this chapter, you should be able to: 6. Formulate

Transportation Problems • Transportation Problem – A distribution-type problem in which supplies of goods

Formulating the Model • A transportation problem – Typically involves a set of sending

Transshipment Problems • Transshipment Problems – A transportation problem in which some locations are

Assignment Problems • The Assignment-type Problems – Involve the matching or pairing of two

Figure 6– 1 Schematic of a Transportation Problem Copyright © 2007 The Mc. Graw-Hill

Table 6– 1 Transportation Table for Harley’s Sand Gravel Copyright © 2007 The Mc.

Special Cases of Transportation Problems • Maximization – Transportation-type problems that concern profits or

Special Cases of Transportation Problems (cont’d) • Unequal Supply and Demand – Situations in

Exhibit 6 -1 Input and Output Worksheet for the Transportation (topsoil) Problem Copyright ©

Exhibit 6 -2 Parameter Specification Screen for the Topsoil Transportation Problem Copyright © 2007

Exhibit 6– 3 Solver Options Screen Copyright © 2007 The Mc. Graw-Hill Companies. All

Exhibit 6– 4 Solver Results Copyright © 2007 The Mc. Graw-Hill Companies. All rights

Exhibit 6– 5 Answer Report for the Topsoil Transportation Problem Copyright © 2007 The

Exhibit 6– 6 Sensitivity Report for the Topsoil Transportation Problem Copyright © 2007 The

Exhibit 6– 7 Input and Output Sheet for the Revised Transportation (topsoil) Problem When

Figure 6– 2 A Network Diagram of a Transshipment Problem Copyright © 2007 The

Example 6 -2 Transshipment Problem The manager of Harley’s Sand Gravel Pit has decided

Figure 6– 3 A Network Diagram of Harley’s Sand Gravel Pit Transshipment Example Copyright

Exhibit 6– 8 Excel Input and Output Screen for the Transshipment Problem Copyright ©

Exhibit 6– 9 Parameter Specifications Screen for the Transshipment Problem Copyright © 2007 The

Using the Transportation Problem to Solve Aggregate Planning Problems • Aggregate Planning – Involves

Table 6– 4 Transportation Table for Aggregate Planning Purposes Copyright © 2007 The Mc.

Example 6 -3 Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc.

Table 6– 5 Transportation Table for the Aggregate Planning Problem of Example 6 -3

Using the Transportation Problem to Solve Location Planning Problems • Location Analysis – Comparing

Table 6– 6 System with Chicago Warehouse Table 6– 7 System with Detroit Warehouse

Example 6 -4 A manager has prepared a table that shows the cost of

Table 6– 8 Numerical Example for the Assignment Problem Copyright © 2007 The Mc.

Exhibit 6– 10 Excel Input and Output Worksheet for the Assignment Problem Copyright ©

Exhibit 6– 11 Parameter Specifications Screen for the Assignment Problem Copyright © 2007 The

Exhibit 6– 12 Excel Worksheet for the Transportation Problem in Solved Problem 1 Copyright

Exhibit 6– 13 Parameter Specification Screen for Solved Problem 1 Copyright © 2007 The

Exhibit 6– 14 Excel Worksheet for the Assignment Problem in Solved Problem 2 Copyright

Exhibit 6– 15 Parameter Specification Screen for Solved Problem 2 Copyright © 2007 The

Exhibit 6– 16 Excel Worksheet for the Transportation Problem in Solved Problem 3 Copyright

Exhibit 6– 17 Parameter Specification Screen for Solved Problem 3 Copyright © 2007 The

Slides: 39

Download presentation

Introduction to Management Science with Spreadsheets Stevenson and Ozgur First Edition Part 2 Deterministic Decision Models Chapter 6 Transportation, Transshipment, and Assignment Problems Mc. Graw-Hill/Irwin Copyright © 2007 by The Mc. Graw-Hill Companies, Inc. All rights reserved.

Learning Objectives After completing this chapter, you should be able to: 1. Describe the nature of transportation, transshipment, and assignment problems. 2. Formulate a transportation problem as a linear programming model. 3. Use the transportation method to solve problems with Excel. 4. Solve maximization transportation problems, unbalanced problems, and problems with prohibited routes. 5. Solve aggregate planning problems using the transportation model. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 2

Learning Objectives (cont’d) After completing this chapter, you should be able to: 6. Formulate a transshipment problem as a linear programming model. 7. Solve transshipment problems with Excel. 8. Formulate an assignment problem as a linear programming model. 9. Use the assignment method to solve problems with Excel. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 3

Transportation Problems • Transportation Problem – A distribution-type problem in which supplies of goods that are held at various locations are to be distributed to other receiving locations. – The solution of a transportation problem will indicate to a manager the quantities and costs of various routes and the resulting minimum cost. – Used to compare location alternatives in deciding where to locate factories and warehouses to achieve the minimum cost distribution configuration. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 4

Formulating the Model • A transportation problem – Typically involves a set of sending locations, which are referred to as origins, and a set of receiving locations, which are referred to as destinations. – To develop a model of a transportation problem, it is necessary to have the following information: 1. Supply quantity (capacity) of each origin. 2. Demand quantity of each destination. 3. Unit transportation cost for each origin-destination route. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 5

Transshipment Problems • Transshipment Problems – A transportation problem in which some locations are used as intermediate shipping points, thereby serving both as origins and as destinations. – Involve the distribution of goods from intermediate nodes in addition to multiple sources and multiple destinations. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 6

Assignment Problems • The Assignment-type Problems – Involve the matching or pairing of two sets of items such as jobs and machines, secretaries and reports, lawyers and cases, and so forth. – Have different cost or time requirements for different pairings. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 7

Special Cases of Transportation Problems • Maximization – Transportation-type problems that concern profits or revenues rather than costs with the objective to maximize profits rather than to minimize costs. • Unacceptable Routes – Certain origin-destination combinations may be unacceptable due to weather factors, equipment breakdowns, labor problems, or skill requirements that either prohibit, or make undesirable, certain combinations (routes). Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 10

Special Cases of Transportation Problems (cont’d) • Unequal Supply and Demand – Situations in which supply and demand are not equal such that it is necessary to modify the original problem so that supply and demand are equalized. – Quantities in dummy routes in the optimal solution are not shipped and serve to indicate which supplier will hold the excess supply, and how much, or which destination will not receive its total demand, and how much it will be short. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 11

Exhibit 6– 7 Input and Output Sheet for the Revised Transportation (topsoil) Problem When the Shipping Route between Farm B and Project 1 Is Prohibited Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 18

Example 6 -2 Transshipment Problem The manager of Harley’s Sand Gravel Pit has decided to utilize two intermediate nodes as transshipment points for temporary storage of topsoil. The revised diagram of the transshipment problem is given in Figure 6 -3. Table 6– 2 Cost of Shipping One Unit from the Farms to Warehouses Table 6– 2 Cost of Shipping One Unit from the Warehouses to Projects Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 20

Using the Transportation Problem to Solve Aggregate Planning Problems • Aggregate Planning – Involves creating a long-term production plan for achieving a demand-supply balance. – Aggregate planners usually avoid in terms of thinking of individual products. – Planners are concerned about the quantity and timing of production to meet the expected demand. – Aggregate planners attempt to minimize the production cost over the planning horizon. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 24

Using the Transportation Problem to Solve Location Planning Problems • Location Analysis – Comparing transportation costs for alternative locations for new facilities to minimize total cost. – Provides planners an opportunity to assess the impact of each warehouse location on the total distribution costs for the system. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 28

Example 6 -4 A manager has prepared a table that shows the cost of performing each of five jobs by each of five employees (see Table 6 -8). According to this table, job I will cost $15 if done by Al. $20 if it is done by Bill, and so on. The manager has stated that his goal is to develop a set of job assignments that will minimize the total cost of getting all four jobs done. It is further required that the jobs be performed simultaneously, thus requiring one job being assigned to each employee. In the past, to find the minimum-cost set of assignments, the manager has resorted to listing all of the different possible assignments (i. e. , complete enumeration) for small problems such as this one. But for larger problems, the manager simply guesses because there are too many possibilities to try to list them. For example, with a 5 X 5 table, there are 5! = 120 different possibilities; but with, say, a 7 X 7 table, there are 7! = 5, 040 possibilities. Copyright © 2007 The Mc. Graw-Hill Companies. All rights reserved. Mc. Graw-Hill/Irwin 6– 30