Question

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 minimizinga) the minimizing equation, b) all the supply, demand
and any other required constraint equations and c) the minimum overall costs. You are also
Hits
(1,000s)
Orders
(1,000s)
Hits
(1,000s)
Orders
(1,000s)
36.7 9.1 48.3 9.3
38.5 6.2 43.5 6.2
35.1 9.0 52.6 10.0
24.5 5.7 54.2 8.7
27.9 6.2 38.5 5.1
31.4 4.8 28.9 4.4
29.4 5.1 26.4 5.2
25.5 6.0 39.4 6.0
52.3 10.8 44.3 8.4
35.2 7.5 46.3 7.9
Page 5 of 8
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.
Table 1.1 (Costs, Capacity and Supply)
Table 1.2 (Costs and Demand)
…….............. END ………………
Proposed Distribution Center
Costs Rotterdam (k) Hamburg (l)Antwerp (m) Bremen (n) Valencia (o) Lisbon (p) Le Havre (q) Supply
Center Cost 16,725,000 19,351,000 13,766,000 15,463,000 12,542,000 13,811,000 22,365,000
Asian Ports
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) 2,412 2,560 2,515 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
Proposed US Port
Dist. Cent New York (u) Savannah (v) Miami (w)N Orleans (x)
Rotterdam (k) 2,045 1,875 1,675 2,320
Hamburg (l) 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 (q) 3,056 2,280 2,065 2,425
Demand 440 305 190 365a) the minimizing equation, b) all the supply, demand
and any other required constraint equations and c) the minimum overall costs. You are also
Hits
(1,000s)
Orders
(1,000s)
Hits
(1,000s)
Orders
(1,000s)
36.7 9.1 48.3 9.3
38.5 6.2 43.5 6.2
35.1 9.0 52.6 10.0
24.5 5.7 54.2 8.7
27.9 6.2 38.5 5.1
31.4 4.8 28.9 4.4
29.4 5.1 26.4 5.2
25.5 6.0 39.4 6.0
52.3 10.8 44.3 8.4
35.2 7.5 46.3 7.9
Page 5 of 8
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.
Table 1.1 (Costs, Capacity and Supply)
Table 1.2 (Costs and Demand)
…….............. END ………………
Proposed Distribution Center
Costs Rotterdam (k) Hamburg (l)Antwerp (m) Bremen (n) Valencia (o) Lisbon (p) Le Havre (q) Supply
Center Cost 16,725,000 19,351,000 13,766,000 15,463,000 12,542,000 13,811,000 22,365,000
Asian Ports
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) 2,412 2,560 2,515 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
Proposed US Port
Dist. Cent New York (u) Savannah (v) Miami (w)N Orleans (x)
Rotterdam (k) 2,045 1,875 1,675 2,320
Hamburg (l) 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 (q) 3,056 2,280 2,065 2,425
Demand 440 305 190 365a) the minimizing equation, b) all the supply, demand
and any other required constraint equations and c) the minimum overall costs. You are also
Hits
(1,000s)
Orders
(1,000s)
Hits
(1,000s)
Orders
(1,000s)
36.7 9.1 48.3 9.3
38.5 6.2 43.5 6.2
35.1 9.0 52.6 10.0
24.5 5.7 54.2 8.7
27.9 6.2 38.5 5.1
31.4 4.8 28.9 4.4
29.4 5.1 26.4 5.2
25.5 6.0 39.4 6.0
52.3 10.8 44.3 8.4
35.2 7.5 46.3 7.9
Page 5 of 8
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.
Table 1.1 (Costs, Capacity and Supply)
Table 1.2 (Costs and Demand)
…….............. END ………………
Proposed Distribution Center
Costs Rotterdam (k) Hamburg (l)Antwerp (m) Bremen (n) Valencia (o) Lisbon (p) Le Havre (q) Supply
Center Cost 16,725,000 19,351,000 13,766,000 15,463,000 12,542,000 13,811,000 22,365,000
Asian Ports
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) 2,412 2,560 2,515 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
Proposed US Port
Dist. Cent New York (u) Savannah (v) Miami (w)N Orleans (x)
Rotterdam (k) 2,045 1,875 1,675 2,320
Hamburg (l) 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 (q) 3,056 2,280 2,065 2,425
Demand 440 305 190 365a) the minimizing equation, b) all the supply, demand
and any other required constraint equations and c) the minimum overall costs. You are also
Hits
(1,000s)
Orders
(1,000s)
Hits
(1,000s)
Orders
(1,000s)
36.7 9.1 48.3 9.3
38.5 6.2 43.5 6.2
35.1 9.0 52.6 10.0
24.5 5.7 54.2 8.7
27.9 6.2 38.5 5.1
31.4 4.8 28.9 4.4
29.4 5.1 26.4 5.2
25.5 6.0 39.4 6.0
52.3 10.8 44.3 8.4
35.2 7.5 46.3 7.9
Page 5 of 8
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.
Table 1.1 (Costs, Capacity and Supply)
Table 1.2 (Costs and Demand)
…….............. END ………………
Proposed Distribution Center
Costs Rotterdam (k) Hamburg (l)Antwerp (m) Bremen (n) Valencia (o) Lisbon (p) Le Havre (q) Supply
Center Cost 16,725,000 19,351,000 13,766,000 15,463,000 12,542,000 13,811,000 22,365,000
Asian Ports
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) 2,412 2,560 2,515 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
Proposed US Port
Dist. Cent New York (u) Savannah (v) Miami (w)N Orleans (x)
Rotterdam (k) 2,045 1,875 1,675 2,320
Hamburg (l) 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 (q) 3,056 2,280 2,065 2,425
Demand 440 305 190 365a) the minimizing equation, b) all the supply, demand
and any other required constraint equations and c) the minimum overall costs. You are also
Hits
(1,000s)
Orders
(1,000s)
Hits
(1,000s)
Orders
(1,000s)
36.7 9.1 48.3 9.3
38.5 6.2 43.5 6.2
35.1 9.0 52.6 10.0
24.5 5.7 54.2 8.7
27.9 6.2 38.5 5.1
31.4 4.8 28.9 4.4
29.4 5.1 26.4 5.2
25.5 6.0 39.4 6.0
52.3 10.8 44.3 8.4
35.2 7.5 46.3 7.9
Page 5 of 8
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.
Table 1.1 (Costs, Capacity and Supply)
Table 1.2 (Costs and Demand)
…….............. END ………………
Proposed Distribution Center
Costs Rotterdam (k) Hamburg (l)Antwerp (m) Bremen (n) Valencia (o) Lisbon (p) Le Havre (q) Supply
Center Cost 16,725,000 19,351,000 13,766,000 15,463,000 12,542,000 13,811,000 22,365,000
Asian Ports
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) 2,412 2,560 2,515 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
Proposed US Port
Dist. Cent New York (u) Savannah (v) Miami (w)N Orleans (x)
Rotterdam (k) 2,045 1,875 1,675 2,320
Hamburg (l) 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 (q) 3,056 2,280 2,065 2,425
Demand 440 305 190 365

Answer 1

Below is the screenshot of the formula applied -

Below is the screenshot of the solver -

Below is the result obtained -

I, J and K should be chosen.

Total Cost = 45363770

Note:  Please like the answer, if it is helpful to you. Please mention the reason in the comment section, before disliking. Thank You.......!