The Pandora Company, a U.S.-based manufacturer of furniture and appliances that offshores all of its manufacturing...

Transcribed Image Text:

The Pandora Company, a U.S.-based manufacturer of furniture and appliances that offshores all of its manufacturing operations to Asia, has distribution centers at various locations on the East Coast near ports where their items are imported on container ships. In many cases, their appliances and furniture arrive partially assembled, and they complete the assembly at their distribution centers before sending the finished products to retailers. For example, appliance motors, electric controls, housings, and furniture pieces might arrive from different Asian manufacturers in separate containers. Recently, the company began exporting its products to various locations in Europe, and demand steadily increased. As a result, the company determined that shipping items to the United States, assembling the products, and then turning around and shipping them to Europe was inefficient and not cost-effective. The company now plans to open three new distribution centers near ports in Europe, so that it will ship the items from Asian ports to its distribution centers at the European ports, offload some of the items for final product assembly, and then ship the partially filled containers on to its U.S. distribution centers. The table 1.1 shows the seven possible distribution center locations near container ports in Europe and their container capacities; the container shipments from each of its Asian ports; and the container shipping cost from each of its Asian ports to each possible distribution center location. The table 1.2 shows the demand from each of the U.S. ports and the cost for container shipments from each of the possible distribution center locations to each of the U.S. ports. Determine the three distribution center locations in Europe that Pandora should select, and the shipments from each of the Asian ports to these selected distribution centers, and from the European distribution centers to the U.S. ports that will result in the lowest overall cost for the 1st year of operations. You are expected to show a) the minimizing equation, b) all the supply, demand and any other required constraint equations and c) the minimum overall costs. You are also expected to show the tables that you have developed in Excel in order to use the Solver Add-In to compute the solution to the problem. Costs Center Cost Asian Ports Table 1.1 (Costs, Capacity and Supply) Proposed Distribution Center Rotterdam (k) Hamburg (1) Antwerp (m) Bremen (n) Valencia (0) Lisbon (p) Le Havre (q) Supply 16,725,000 19,351,000 13,766,000 15,463,000 12,542,000 13,811,000 22,365,000 | Hong Kong (a) 3,466 3,560 3,125 3,345 3,060 3,120 3,658 235 Shanghai (b) 3,190 3,020 3,278 3,269 2,987 2,864 3,725 170 Busan (c) 2,815 2,700 2,890 3,005 2,465 2,321 3,145 165 Mumbai (d) 2412 2560 2515 2.875 2,325 2,133 2.758 325 Kaoshiung (e) 2,600 2,800 2,735 2,755 2,473 2,410 2,925 405 Capacity 565 485 520 490 310 410 605 Table 1.2 (Costs and Demand) Proposed US Port Dist. Cent New York (u) Savannah (v) Miami (w) Orleans (x) Rotterdam (k) 2,045 1,875 1,675 2,320 Hamburg (1) 2,875 2,130 1,856 2,415 Antwerp (m) 2,415 2,056 1,956 2,228 Bremen (n) 2,225 1,875 2,075 2,652 Valencia (o) 1,865 1,725 1,548 1,815 Lisbon (p) 1,750 1,555 1,420 1,475 Le Harve (g) 3,056 2.280 2,065 2,425 Demand 440 305 190 365 The Pandora Company, a U.S.-based manufacturer of furniture and appliances that offshores all of its manufacturing operations to Asia, has distribution centers at various locations on the East Coast near ports where their items are imported on container ships. In many cases, their appliances and furniture arrive partially assembled, and they complete the assembly at their distribution centers before sending the finished products to retailers. For example, appliance motors, electric controls, housings, and furniture pieces might arrive from different Asian manufacturers in separate containers. Recently, the company began exporting its products to various locations in Europe, and demand steadily increased. As a result, the company determined that shipping items to the United States, assembling the products, and then turning around and shipping them to Europe was inefficient and not cost-effective. The company now plans to open three new distribution centers near ports in Europe, so that it will ship the items from Asian ports to its distribution centers at the European ports, offload some of the items for final product assembly, and then ship the partially filled containers on to its U.S. distribution centers. The table 1.1 shows the seven possible distribution center locations near container ports in Europe and their container capacities; the container shipments from each of its Asian ports; and the container shipping cost from each of its Asian ports to each possible distribution center location. The table 1.2 shows the demand from each of the U.S. ports and the cost for container shipments from each of the possible distribution center locations to each of the U.S. ports. Determine the three distribution center locations in Europe that Pandora should select, and the shipments from each of the Asian ports to these selected distribution centers, and from the European distribution centers to the U.S. ports that will result in the lowest overall cost for the 1st year of operations. You are expected to show a) the minimizing equation, b) all the supply, demand and any other required constraint equations and c) the minimum overall costs. You are also expected to show the tables that you have developed in Excel in order to use the Solver Add-In to compute the solution to the problem. Costs Center Cost Asian Ports Table 1.1 (Costs, Capacity and Supply) Proposed Distribution Center Rotterdam (k) Hamburg (1) Antwerp (m) Bremen (n) Valencia (0) Lisbon (p) Le Havre (q) Supply 16,725,000 19,351,000 13,766,000 15,463,000 12,542,000 13,811,000 22,365,000 | Hong Kong (a) 3,466 3,560 3,125 3,345 3,060 3,120 3,658 235 Shanghai (b) 3,190 3,020 3,278 3,269 2,987 2,864 3,725 170 Busan (c) 2,815 2,700 2,890 3,005 2,465 2,321 3,145 165 Mumbai (d) 2412 2560 2515 2.875 2,325 2,133 2.758 325 Kaoshiung (e) 2,600 2,800 2,735 2,755 2,473 2,410 2,925 405 Capacity 565 485 520 490 310 410 605 Table 1.2 (Costs and Demand) Proposed US Port Dist. Cent New York (u) Savannah (v) Miami (w) Orleans (x) Rotterdam (k) 2,045 1,875 1,675 2,320 Hamburg (1) 2,875 2,130 1,856 2,415 Antwerp (m) 2,415 2,056 1,956 2,228 Bremen (n) 2,225 1,875 2,075 2,652 Valencia (o) 1,865 1,725 1,548 1,815 Lisbon (p) 1,750 1,555 1,420 1,475 Le Harve (g) 3,056 2.280 2,065 2,425 Demand 440 305 190 365