YellowSmart Company is an online supermarket and sells more than 100,000 products and offers home delivery...

 

  

Transcribed Image Text:

YellowSmart Company is an online supermarket and sells more than 100,000 products and offers home delivery all year round. It is currently planning the daily routes for 8 of its trucks, which are located in different areas of Singapore. The trucks are responsible for delivering their loads whenever the customer orders are consolidated. The orders at all areas have to be fulfilled by the end of the day. Each truck can deliver the load to at most 4 areas within a day. The following table shows the mileage (in km) for a truck to travel to a customer region (indicated by Area A to Area L) and back to its original location. See table in Excel file. For example, if Truck 1 is assigned to Area A and Area C, Truck 1 will travel from its location to Area A and back to its location, and the total distance travelled is 125km. It will replenish the load and travel 155km from its location to Area C and back to its location. To simplify the problem, we do not consider the sequence of the delivery, i.e. it is fine to deliver to Area C before Area A in our example above. Truck 1 2 3 4 5 6 7 8 A 125 225 157.5 217.5 237.5 275 152.5 140 B 182.5 142.5 165 300 227.5 215 177.5 172.5 C 155 205 187.5 202.5 185 200 227.5 165 D 102.5 192.5 135 F 137.5 227.5 150 165 187.5 170 177.5 205 157.5 147.5 147.5 142.5 137.5 195 137.5 202.5 182.5 227.5 180 160 167.5 Area E 167.5 202.5 G 97.5 162.5 202.5 300 232.5 H 120 195 140 157.5 162.5 1 167.5 210 177.5 175 210 325 152.5 180 207.5 172.5 220 212.5 J 177.5 237.5 300 225 232.5 210 190 137.5 145 250 177.5 K 110 147.5 122.5 135 115 L 147.5 167.5 162.5 155 140 197.5 157.5 170 a) Formulate an assignment model to determine the optimal assignment of the trucks to the areas that will minimize the total mileage. Solve the problem in excel. b) Assume that the demand of each area is given in the form of truck capacity percentage loads. A Truck Capacity Percentage load 89 B 78 C 94 D 82 Area E 90 F 83 G 88 H 79 1 71 J You do not need to consider the average truck load capacity requirement mentioned in part b) Resolve a) and compare the difference (in objective function). 96 K 78 L 85 Determine the optimal assignment of trucks to customers that will minimize the total mileage while also achieving at least an average truck load capacity of 85%. For example, if a truck is assigned to Area A, B and C, the average truck load capacity is (89+78+94)/3. Formulate the model and solve it in excel. Present this in a separate worksheet from part a). c) To simplify the problem, Area A, B, C and D can be grouped into Region 1; Area E, F, G and H can be grouped into Region 2, and the remaining areas can be grouped into Region 3. Each truck can only deliver the load to at most 1 region within a day. Each region will require at least 2 trucks to fulfil its demand. YellowSmart Company is an online supermarket and sells more than 100,000 products and offers home delivery all year round. It is currently planning the daily routes for 8 of its trucks, which are located in different areas of Singapore. The trucks are responsible for delivering their loads whenever the customer orders are consolidated. The orders at all areas have to be fulfilled by the end of the day. Each truck can deliver the load to at most 4 areas within a day. The following table shows the mileage (in km) for a truck to travel to a customer region (indicated by Area A to Area L) and back to its original location. See table in Excel file. For example, if Truck 1 is assigned to Area A and Area C, Truck 1 will travel from its location to Area A and back to its location, and the total distance travelled is 125km. It will replenish the load and travel 155km from its location to Area C and back to its location. To simplify the problem, we do not consider the sequence of the delivery, i.e. it is fine to deliver to Area C before Area A in our example above. Truck 1 2 3 4 5 6 7 8 A 125 225 157.5 217.5 237.5 275 152.5 140 B 182.5 142.5 165 300 227.5 215 177.5 172.5 C 155 205 187.5 202.5 185 200 227.5 165 D 102.5 192.5 135 F 137.5 227.5 150 165 187.5 170 177.5 205 157.5 147.5 147.5 142.5 137.5 195 137.5 202.5 182.5 227.5 180 160 167.5 Area E 167.5 202.5 G 97.5 162.5 202.5 300 232.5 H 120 195 140 157.5 162.5 1 167.5 210 177.5 175 210 325 152.5 180 207.5 172.5 220 212.5 J 177.5 237.5 300 225 232.5 210 190 137.5 145 250 177.5 K 110 147.5 122.5 135 115 L 147.5 167.5 162.5 155 140 197.5 157.5 170 a) Formulate an assignment model to determine the optimal assignment of the trucks to the areas that will minimize the total mileage. Solve the problem in excel. b) Assume that the demand of each area is given in the form of truck capacity percentage loads. A Truck Capacity Percentage load 89 B 78 C 94 D 82 Area E 90 F 83 G 88 H 79 1 71 J You do not need to consider the average truck load capacity requirement mentioned in part b) Resolve a) and compare the difference (in objective function). 96 K 78 L 85 Determine the optimal assignment of trucks to customers that will minimize the total mileage while also achieving at least an average truck load capacity of 85%. For example, if a truck is assigned to Area A, B and C, the average truck load capacity is (89+78+94)/3. Formulate the model and solve it in excel. Present this in a separate worksheet from part a). c) To simplify the problem, Area A, B, C and D can be grouped into Region 1; Area E, F, G and H can be grouped into Region 2, and the remaining areas can be grouped into Region 3. Each truck can only deliver the load to at most 1 region within a day. Each region will require at least 2 trucks to fulfil its demand.

Answer rating: 100% (QA)

a Assignment model formulation Variables x11 x12 x13x81 1 if truck is assigned to area 0 o... View the full answer