Question

The Shiny Star Clothing company has launched a new clothing line (SKU number OA694) for export out of the port of Ningbo. You have been tasked with securing the trucking services to handle all of the container freight moves (TEUs) from your four plants to the port of Ningbo. Your plants are located in Chongqing, Beijing, Nanjing, and Shijiazhuang.

The lane information is as follows:

Origin	Distance to Ningbo (Km)	Expected Weekly Demand (TEUs)
Chongqing	1750	19
Beijing	1400	20
Nanjing	450	28
Shijiazhuang	1300	13

Currently Shiny Star Clothing has no contracts of any sort with trucking companies to the Ningbo location. You have decided to run an auction and to only invite your top 3 carriers (Red, Blue, and Green) to participate.

Suppose that the carriers have submitted the following bids in Yuan Renminbi per TEU per km.

Origin	Red	Blue	Green
Chongqing	7.50	9.00	8.60
Beijing	6.40	8.30	8.00
Nanjing	8.80	8.90	9.20
Shijiazhuang	7.80	9.20	8.40

Your manager has another idea. She wants to ensure that each lane has at least two carriers assigned to it - with at least 5 loads per carrier (per lane).

Based on this new data, how many lanes will you award to each carrier in order to minimize total expected costs?

Red = ?

Blue = ?

Green = ?

What is the total expected weekly cost for this solution?

Answer 1

Formulation

B A C D E F G 1 Expected Weekly Demand (TEUS) Loads (Xj's) Red Blue Total Green 2 | Chongqing Beijing |Nanjing Shijiazhuang Total -SUM(C3:E3) |=SUM(C4:E4) |-SUM(C5.E5) -SUM(C6E6) 19 3 20 4 28 13 6 SUM(C3:C6) -SUM(D3D6) -SUM(E3 E6) 7 Сараcity 15 25 90 |Assigments (Yj's) Chongqing Beijing Nanjing Shijiazhuang 10 Red Blue Total -SUM(C11:E11) 2 -SUM(C12:E12) 2 |=SUM(C13-E13) 2 |=SUM(C14E14) 2 Green 1 1 11 1 1 12 1 1 13 0 1 1 14 15 Green 1750*8.6*E3 1400* 8*E4 16 Red = 1750* 7.5*C3 Blue =1750*9*D3 1400*8.3*D4 Cost 17 Chongging Beijing Nanjing Shijiazhuang = 1400*6.4*C4 18 450*8.8*C5 450*8.9*D5 =450*9.2*E5 19 1300*7.8*C6 1300*9.2*D6 1300*8.4*E6 20 21 -SUM(C17:E20) Total cost 22 23 |(Xi-5) - M.Yi C3-5-99*C11 =D3-5-99*D11 E3-5-99*E11 =E4-5-99*E12 24 C4-5-99*C12 =D4-5-99*D12 25 -C5-5-99*C13 D5-5-99*D13 =E5-5-99*E13 26 C6-5-99*C14 5*C11 - C3 -5*C12 - C4 =D6-5-99*D14 =E6-5-99*E14 - 5 *E11 - E3 27 5.Yj - Xj =5*D11 - D3 28 =5*D12 - D4 -5*E12 - E4 29 =5*D13 - D5 5*C13 - C5 -5*E13 - E5 30 5*C14 - C6 -5*D14 - D6 -5*E14 - E6 31

Set Objective: $C$22 To: OValue Of 0 Min Max By Changing Variable Cells: $C$3:$E$6,$C$11:$E$14 Subject to the Constraints: SF$3:SF$6>= $G$3: $G$6 $C$11:$E$14=binary $C$24:$E$31<=0 $C$7:$E$7 < $C$8: SE$8 SF$11:$F$14 >=$G$11: $G$14 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Simplex LP Select a Solving Method: Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth Help Solve Close

Solution

Loads (Xj's)	Red	Blue	Green	Total	Expected Weekly Demand (TEUs)
Chongqing	5	0	14	19	19
Beijing	10	0	10	20	20
Nanjing	0	20	8	28	28
Shijiazhuang	0	5	8	13	13
Total	15	25	40
Capacity	15	25	90
Assignments (Yj's)	Red	Blue	Green	Total
Chongqing	1	0	1	2	2
Beijing	1	0	1	2	2
Nanjing	0	1	1	2	2
Shijiazhuang	0	1	1	2	2
Totals	2	2	4
Cost	Red	Blue	Green
Chongqing	65625	0	210700
Beijing	89600	0	112000
Nanjing	0	80100	33120
Shijiazhuang	0	59800	87360
Total cost	738305

Red = 2 lanes

Blue = 2 lane

Green = 4 lanes

The total expected weekly cost = 738,305.