Topic: Logistics and Supply chain management
Details:
Please provide the excel worksheet and show the method or formulas to the questions. Also don't worry about the word counts, as it is more analytical.
Problem 1:
ABC Corporation is a global distributor of electrical parts and components. Its customers are electronics companies in the United States, including computer manufacturers and audio/visual product manufacturers. The company contracts to purchase components and parts from manufacturers in Russia, Eastern and Western Europe, and the Mediterranean, and it has them delivered to warehouses in three European ports, Gdansk, Hamburg and Lisbon. The various components and parts are loaded into containers based on demand from US customers. Each port has a limited fixed number of containers available each month. The containers are then shipped overseas by container ships to the ports of Norfolk, Jacksonville, New Orleans and Galveston. From these seaports, the containers are typically coupled with trucks and hauled to inland ports in Front Royal (Virginia), Kansas City and Dallas. There are a fixed number of freight haulers available at each port each month. These inland ports are sometimes called "freight villages" or intermodal junctions, where the containers are collected and transferred from one transport mode to another. From the inland ports, the containers are transported to ABC's distribution centres in Tucson, Pittsburgh, Denver, Nashville and Cleveland. Following are the handling and shipping costs ($/container) between each of the embarkation and destination points along this overseas supply chain and the available containers at each port:
U.S. Port
|
European Port
|
|
|
|
|
Available
Containers
|
|
4. Norfolk
|
5. Jacksonville
|
6. New Orleans
|
7. Galveston
|
|
1. Gdansk
|
$1,725
|
S1,800
|
$2,345
|
$2,700
|
125
|
|
2. Hamburg
|
1,825
|
1,750
|
1,945
|
2,320
|
210
|
|
3. Lisbon
|
2,060
|
2,175
|
2,050
|
2,475
|
160
|
Inland Port
|
U.S. Port
|
|
|
|
Intermodal
Capacity (containers)
|
|
8.Dallas
|
9.Kansas City
|
10. Front Royal
|
|
4. Norfolk
|
$825
|
$545
|
$ 320
|
85
|
|
5. Jacksonville
|
750
|
675
|
450
|
110
|
|
6. New Orleans
|
325
|
605
|
690
|
100
|
|
7. Galveston
|
270
|
510
|
1,050
|
130
|
|
|
|
|
|
|
|
Intermodal Capacity (containers)
|
170
|
240
|
140
|
|
Distribution Center
|
Inland Port
|
11. Tucson
|
12. Denver
|
13. Pittsburgh
|
14. Nashville
|
15. Cleveland
|
|
8. Dallas
|
$ 450
|
$830
|
$ 565
|
$420
|
5960
|
|
9. Kansas City
|
880
|
520
|
450
|
380
|
660
|
|
10. Front Royal
|
1,350
|
390
|
1,200
|
450
|
310
|
|
Demand
|
85
|
60
|
105
|
50
|
120
|
1. Formulate the equations to determine the optimal shipments from each point of embarkation to each destination along this supply chain that will result in the minimum total shipment cost.
2. Solve the model using Excel Solver and provide the values of the variables used in the equations.
3. Assume that the Denver distribution centre becomes unoperational (due to a strike) and the responsibilities for this distribution centre are passed on to the Tucson distribution centre. Explain what changes you would make in the equations. Solve these equations using Solver and provide the values of the variables.
A separate Excel workbook with individual worksheets for each of 2 and 3 above would need to be provided.
Problem 2:
The time between arrivals of oil tankers at a loading dock at XYZ Bay is given by the following probability distribution:
| Time between ship arrivals (days) |
Probability |
| 1 |
0.05 |
| 2 |
0.1 |
| 3 |
0.2 |
| 4 |
0.3 |
| 5 |
0.2 |
| 6 |
0.1 |
| 7 |
0.05 |
The time required to fill a tanker with oil and prepare it for sea is given by the following probability distribution:
| Time to fill and prepare (days) |
Probability |
| 3 |
0.1 |
| 4 |
0.2 |
| 5 |
0.4 |
| 6 |
0.3 |
1. Simulate the movement of tankers to and from the single loading dock for the first 20 arrivals. Compute the average time between arrivals, average waiting time to load and average number of tankers waiting to be loaded.
2. Discuss any hesitation you might have about using the simulation results for decision making (2 marks).
Problem 3:
Bozo is a small company that produces and distributes beer under the same label. The company is examining the possibility of penetrating the North Shore city area market. A bottling plant location that would serve the area is sought. A grid overlay is placed over the selling area as shown below. North Shore city is area E. The suburbs surrounding E are designated as A to I.
A market research study shows the following potential demand for Bozo beer.
| Area |
Annual volume (cases) |
| A |
10,000 |
| B |
5,000 |
| C |
70,000 |
| D |
30,000 |
| E |
40,000 |
| F |
12,000 |
| G |
90,000 |
| H |
7,000 |
| I |
10,000 |
Demand comes primarily from dealers that are scattered uniformly over the area. While the cost for transporting to areas A and B is 30 pence per case per mile, it is 20 pence per case per mile for the other areas.
1. If the centre- of-gravity approach is used, where should the bottling plant be located?
Problem 4:
The Hendon casket company supplies caskets to funeral homes in and around London. The location of the funeral homes in relation to the company warehouse is as given below.
Suppose that the funeral home locations (•) and associated number of caskets for each funeral home represent a single, daily despatch. If the company has six trucks with capacities of 20 caskets each, develop a routeing plan using the sweep method with a due north start. Place your design on the map.
1. How many trucks are actually used and what is the total travel distance for the route design (you may scale distance from the diagram).
2. What do you think are the advantages of using the sweep method vis-à-vis if the alternative (savings) method
No# of Pages: 5 pages (1,250 words)
Subject Area: Business Management
Paper Style: Harvard
Language: English (U.K)