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 a 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: the Transportation Problem and the Transshipment Problem.
Follow these steps to complete this assignment:
Review the step-by-step directions in the Excel Assignment Instructions and Rubric Download Excel Assignment Instructions and Rubricdocument and keep it handy while working on the assignment
Open a blank Microsoft Excel file
Follow the directions in the instructions document to complete the transportation problem and transshipment problem in your Excel file. NOTE: Each problem should be saved on a separate tab in the same excel file.
To submit your assignment, upload your saved completed Excel file.
qmwpl-SCM_Excel_assignment_instructions_rubric.docx.pdf
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
%
Interested in getting help with this assignment?
Get a professional writing team to work on your assignment!
Order Now
Recent postsFor this final assignment, you will prepare a brief paper detailing the steps undertaken to complete a presentation that disseminates information you assemble
Please choose to answer only one of the 2 following questions. Option 1: In your opinion and based on scientific, peer-reviewed published evidence, does child
At the beginning of the previous academic year, the institution announced it would drop football at the conclusion of the season. The announcement created pub
you will review current research in Personality and provide a critical evaluation of that personality research through an annotated bibliography. An annotated
In Module 5, we considered the third in our three-part series on research design. Specifically, the focus was on the longitudinal studies, in which the resear
Need help with a similar or different Task?
We have the best writers to help you. Hire Writer Now
