Question

Curly Hair is a Brazilian start-up that offers a wide portfolio of hair products (shampoo, conditioner, foam, serum…) specifically designed to take care of curly hair.

Curly Hair manufactures its products in three different plants and sells them in five markets around the country. The plants have a certain manufacturing capacity. In the tables below, you can find the demand for each market, the capacity of each plant, and the distances (in miles) between plants and markets.

Demand per market (in liters)
M1	375
M2	230
M3	229
M4	246
M5	383

Plant capacity (in liters)
P1	510
P2	700
P3	620

Distance from plants to markets (in miles)
	M1	M2	M3	M4	M5
P1	28	22	21	38	44
P2	16	42	11	14	35
P3	24	45	42	31	49

The operations manager of the company proposes to redesign the transportation network and start using some distribution centers (DCs) as an intermediary step between plants and final markets. There are four DCs that could be used. These DCs have a certain capacity and they cannot be used as warehouses (they do not keep stock), products must just flow through them.

In the tables below you will find the maximum capacity of each DCs, the distances between plants and DCs, and the distances between DC and markets.

Capacity of each DC (in liters)
DC1	900
DC2	650
DC3	850
DC4	1000

Distance from plants to DCs (in miles)
	DC1	DC2	DC3	DC4
P1	53	20	36	24
P2	47	19	37	60
P3	59	29	14	52

Distance from DCs to markets (in miles)
	M1	M2	M3	M4	M5
DC1	21	31	26	17	27
DC2	28	12	27	43	39
DC3	22	49	16	39	50
DC4	25	45	44	47	18

The inbound transportation cost (from plants to DCs) is 2.61 Brazilian reals per liter per mile, and the outbound transportation cost (from DCs to markets) is 3.02 Brazilian reals per liter per mile. There is also a fixed cost of 5,000 Brazilian reals for each DC that the company decides to use.

Design a distribution network that can use these DCs. What is the optimal cost (transportation + fixed cost) under this new situation?

Answer 1

ANSWER:

FORMULAS:

Distance from plants to DCs (in miles)							Shipment schedule from plants to DCs
	DC1	DC2	DC3	DC4	Capacity			DC1	DC2	DC3	DC4	Total
P1	53	20	36	24	510		P1	0	0	0	383	=SUM(I3:L3)
P2	47	19	37	60	700		P2	0	460	0	0	=SUM(I4:L4)
P3	59	29	14	52	620		P3	0	0	620	0	=SUM(I5:L5)
Capacity	900	650	850	1000			Total	=SUM(I2:I5)	=SUM(J2:J5)	=SUM(K2:K5)	=SUM(L2:L5)
Logical	=B6*I7-I6	=C6*J7-J6	=D6*K7-K6	=E6*L7-L6			DC to use	0	1	1	1
Distance from DCs to markets (in miles)							Shipment schedule from DCs to markets
	M1	M2	M3	M4	M5			M1	M2	M3	M4	M5	Total
DC1	21	31	26	17	27		DC1	0	0	0	0	0	=SUM(I11:M11)
DC2	28	12	27	43	39		DC2	0	230	0	230	0	=SUM(I12:M12)
DC3	22	49	16	39	50		DC3	375	0	229	16	0	=SUM(I13:M13)
DC4	25	45	44	47	18		DC4	0	0	0	0	383	=SUM(I14:M14)
Demand	375	230	229	246	383		Total	=SUM(I11:I14)	=SUM(J11:J14)	=SUM(K11:K14)	=SUM(L11:L14)	=SUM(M11:M14)

Total cost (I18) =SUMPRODUCT(B3:E5,I3:L5)*2.61+SUMPRODUCT(B11:F14,I11:M14)*3.02+SUM(I7:L7)*5000

Optimal cost = $ 181,345