Case Study on Distribution Planning of Ie Logistics Management

A Case Study on Distribution Planning of IE Logistics Management

TASK 1

Spreadsheet Programming

Dear Manager,

According to your request, a mathematical model is developed to schedule the logistics services and the distribution plan of the coming Winter 2012.

A spreadsheet is attached to show the important information for the planning. As the new road from Anke to Gianluigi proposed by the government is more costly and time-consuming than the original one, the route should be replaced by the route from other warehouse.

In addition, the demand of products in Heinz is predicted to reduce 50% of its original amount. So, the demand for pallets is forecasted to decrease from 40 to 20. Moreover, the demand for pallets in Luca is increased from 55 to 66.

Besides the two original warehouses in Anke and Dino, another two third-party warehouses in Eleanor and Florian are also included in the schedule planning. Due to the situations mentioned above, the capacity is predicted to be insufficient for the coming Winter 2012.

In the calculation, the several important parameters are included.

The distribution of pallets from IMSE to Anke, Dino Eleanor and Florian will not exceed the capacity of IMSE (capacity: 345 pallets).

The amount of pallets supplied by the four warehouses are limited and determined by those received from the IMSE. Also, the amount of pallets distributed to the six customer regions from the warehouses must fulfill the demand of the regions.

During the course of calculation, the amount of pallets is spread over the different combination of warehouses and customer regions. Thus, the optimal number of pallets of the four warehouses is obtained.

The operational cost is obtained by summing up total handling cost and total fixed administrative cost of the four warehouses. Total handling cost is calculated by the pallets of each warehouses multiple with the respective handling costs.

On the other hand, the cost of transportation from...