excel

1

Logistics Optimization with Excel Solver: The Transportation and Transshipment Problem Instructions and Rubric

Logistics Management is typically concerned with managing the flow of products across the supply chain efficiently and effectively. The task of planning and managing such flow is critical if the company is to compete in the marketplace. Transportation is one of the critical key activities of logistics and it constitutes a large proportion of the logistics cost. Almost every company deals in some way or another with transporting its products from where they are made to where they are consumed.

This assignment will introduce you to two critical issues in logistics management and distribution planning: Transportation Problem and the Transshipment Problem. You will turn in one Excel file. In this Excel file you will create two problems (on separate tabs of the same Excel file). The first problem will be a transportation problem. Then you will create the transshipment problem. We will first start with the Transportation Problem:

Transportation Problem The transportation problem is concerned with finding the minimum cost of transporting a single commodity from a given number of sources (e.g., factories) to a given number of destinations (e.g., retailers). There are two types of transportation problems. One is where the capacity of the factories is equal to the demand at the retailers (this is called supply = demand) and the other instance is when the capacity at the factories is not the same as the demand at the

retail locations (known as supply ≠ demand). We will only cover the instance here where supply = demand. Let’s do the example!

The Anderson Corporation manufactures bean bags at two locations: Dallas and New York City. The firm sells the bean bags through retail locations located in Kansas City and Cleveland. You can see the diagram of this simple supply chain below:

2

An estimate of the monthly production capacity at each factory and an estimate of the number of bean bags that are needed each month at each of the retail locations is shown in the following table. We are interested in meeting the demand at the retail locations with the capacity that is available at the Factories. Factory 1 (Dallas) and Factory 2 (New York) can product up to 150 and 200 bean bags per day, respectively. Each retailer requires at least 175 bean bags per day. How many bean bags will be shipped from Dallas to Kansas City, from Dallas to Cleveland, and from NYC to Cleveland, for example, will depend on the shipping cost. Transportation costs per bean bag from the following sources to destinations is given as:

Retailer (Kansas City) Retailer (Cleveland) Maximum Supply

Factory (Dallas) 25 28 150

Factory (NYC) 26 25 200

Minimum Demand 175 175

Table 1: Unit Shipping Cost ($/bean bag)

These costs are assumed to be constant regardless of the volume shipped. Our goal is to meet the needs at the retailer and minimize costs. To figure out how many units will be shipped from each source to each destination that meets the retailer needs and minimizes costs, we will use an Excel add-in, Solver.

Open a new Excel file (you will need to create the table below in a blank Excel file). Once you have Excel open, enable the Excel add-in called “Solver” by clicking File -> Options -> Add-Ins -> Go… ->Solver Add-in ->OK.

3

Fill in the information in cells A1 through E9. In cells B9, C9, D7, D8, and B12, use the formulas as in the figure below.

Your spreadsheet will look like the following figure:

4

We will now solve the problem. Click on Data -> Solver.

In the Solver window, we need to add constraints to make sure that the supply does not exceed the capacity at each factory and the demand is met at each Distributor. Click “Add” and the following window appears:

5

6

7

The solution to meet the demand at the retail locations is to ship 150 units from the Dallas factory to Cleveland, 25 units from NYC to Cleveland, and 175 units from NYC to Kansas City for a total cost of $8775.00.

8

Transshipment Problem The transshipment problem is a special case of the transportation problem. A transportation problem only allows shipments to go directly from a supplier to the demand points, such as from a manufacturer to a retailer. In a more complex supply chain network, however, there are usually intermediaries, such as distributors or Distributors, in which the product must pass through. The transshipment problem allows for the inclusion of these intermediaries. It may be cheaper to first ship to intermediaries then directly to the final demand points (e.g., retailers). Our objective, like in the transportation problem, is to minimize total shipping costs.

To solve the transshipment problem, we convert the problem into two transportation problems. The first transportation problem will solve the first “leg” of the supply chain which is the shipment of the product from the factory to the Distributor. The second transportation problem will solve the second “leg” of the supply chain which is the shipment of the product from the Distributor to the Retail locations.

Transportation costs per bean bag from the following sources to destinations is given as:

Distributor 1 Distributor 2

Factory (Dallas) 8 13

Factory (NYC) 15 12

Table 1: Unit Shipping Cost ($/bean bag) from the Factory to the Distributor stations

Retailer (Kansas City) Retailer (Cleveland)

Distributor 1 16 17

Distributor 2 14 16

Table 2: Unit Shipping Cost ($/bean bag) from the Distributor stations to the Retailers

The supply is the same as the original problem; that is, at factory 1 (Dallas) is < 150 and the supply at factory 2 (NYC) is < 200. The demand is the same as the original problem; that is, at retailer 1 (Kansas City) is > 175 and the demand at retailer 2 (Cleveland) is > 175.

9

Fill in the information in cells A1 through I10 and use the formulas as in the figure below.

Your spreadsheet should look as follows:

We will now solve the problem. Click on Data -> Solver.

10

In the Solver window, we need to add constraints to make sure that the supply does not exceed the capacity at each factory and the demand is met at each Distributor. Click “Add” and the following window appears:

11

The solver dialog box should look as follows:

12

The solution is as follows:

Here is a graphical depiction of the solution:

13

Rubric Guidelines for Submission: To submit your assignment, upload your saved completed Excel file with two

tabs (one for each the transportation and transshipment problems).

Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor

feedback in the Grade Center.

Criteria Exemplary (100%) Needs Improvement (75%) Incomplete (50%) Not Evident

(0%)

Value

Excel

Worksheet

Submitted an accurate

Excel worksheet for

each the

transportation and

transportation

problems.

Submitted an Excel

worksheet for each

transportation and

transportation problems that

may contain some

inaccuracies but

demonstrates sincere effort.

Submitted

incomplete Excel

worksheets for the

transportation and/or

transportation problems.

Did not submit

Excel

worksheets.

100

Total 100

%