The Danish company JutTrans offers freight transportation from Shanghai to Denmark. The individual shipments are consolidated and packed in containers in Shanghai and shipped by vessel to Hamburg, Germany. From Hamburg, the containers are transported to one of three terminals in Denmark by truck: Esbjerg, Horsens, and Holstebro.
At the terminal, the containers are opened, and the individual shipments are delivered to the customers by local transporters.
In this assignment, we consider different strategies for consolidating the shipments in Shanghai in order to minimize the total transportation cost (the sum of the vessel transport, trucking, and local distribution).
The map below shows the three terminals along with Hamburg:
Costs:
A standard container has a volume of 55 cbm. The cost of shipment of each container from Shanghai to Hamburg is 14,000 DKK.
The trucking cost (per container) from Hamburg to each of the three terminals is given in the following table along with the zip codes for the terminal locations:
Finally, the cost of the final distribution to the customers is based on the distance as well and the volume of the shipment. For shipments less than 4 cbm, the cost is 7 DKK per kilometer, whereas for shipments above 4 cbm, the cost per kilometer is 9,5 DKK. The distances are determined based on zip codes only. (This means that a customer located in zip code 6700 has free delivery from the Esbjerg terminal because the zip code is the same).
A complete distance table based on the zip codes in Jutland is available in the file “distances.xlsx”, where row 1 and column A lists the zip codes. Distances are given as kilometers.
Daily list of shipments:
Each day, a list of shipments is given. This list dictates the shipments to be packed in the containers. There are as many containers available as needed. The number of daily shipments varies between 50 and 120 (uniformly at random). For each shipment, the destination zip code is determined uniformly at random based of zip codes available in “distance.xlsx”.
The volume is determined by a normal distributed random variable with mean 5 cbm and standard deviation 3 cbm (however, if a volume less than 0.3 is generated, a new volume should be determined. I.e. the distribution is truncated from below at 0.3).
In this assignment, you are asked to create one or several vba procedures to perform the following tasks:
· 1) Create a shipping list for one day based on the above specifications.
· 2) FF-consolidation: Create one or several procedures that can plan with the following strategy:
Consider the shipments in decreasing order by volume and pack them in this order. If there is enough space available in the currently open container, place the shipment there. Otherwise, close that container and open a new container, where the shipment is then placed. The container is sent to the terminal closest to the customer of the order that was first placed in the container.*
· 3) Geo-consolidation: Create one or several procedures that can plan with the following strategy: As long as there are unpacked shipments, do the following: Find the shipment which has the customer closest to one of the terminals. Let s be this shipment and let t be the terminal. Pack s in a new container which shall be sent to terminal t. Next, consider all remaining unpacked shipments by increasing distance to t. If there is enough space in the container, then pack the shipment in it. *
· 4) Create a simulation to compare the relative performance of FF-consolidation and Geo- consolidation. That is, repeatedly (R times, where R is read from a cell in your worksheet) generate a shipping list and plan with the two strategies. Your procedure shall NOT output the specific plans, but rather it shall present the relative costs of the strategies in a meaningful way.
*) When you are asked to plan, it means that you shall make a vba code to perform the consolidation based on the described rules. The plan stating which shipments are packed together in each container along with the costs (both the total cost and enough information for JutTrans to get an overview of the types of costs) shall be presented in an easily readable manner in a separate sheet.
/docProps/thumbnail.jpeg