OM 418: HM5 Excel-based problems: Problem Context: Gelido, a Mexican firm distributing frozen foods, is evaluating its distribution system. Currently, Gelido has three facilities (Linares, Monclova, Monterrey) and serving five market districts (Bustamante, Saltillo, Santa Catarina, Santa Carla, Montemorelos). The facilities has the following production capacities: Linares 23,000 Facilities Capacities Monclova 21,000 Monterrey 25,000 The annual demands at the market districts are as follows: Bustamante 6,200 Saltillo 6,600 Santa Catarina 5,800 Santa Carla 4,400 Montemorelos 3,600 Market Demand The distance between the production facilities and market places are as follows: Bustamante Saltillo Santa Catarina Santa Carla Montemorelos Distance (in miles) Linares Monclova Monterrey 165 90.8 84.2 132.5 118.5 51.6 92.7 139 11.9 32.4 176.7 49.5 75.6 140.2 37.2 Gelido has two possible carriers, competing to secure a dedicated or mixed contract from Gelido. Carrier 1: LogConsult has the following operational parameters: $0.78 per mile per unit $200 per lane (Origin-destination pair used) and a capacity of 4,800 per lane. Additionally, the frozen products may be subject to melting and spoilage. The following spoilage rates are reported for the possible paths: . Bustamante Saltillo Santa Catarina Santa Carla Montemorelos Spoilage rate (%) Linares Monclova Monterrey 0.22 0.25 0.18 0.31 0.28 0.15 0.19 0.24 0.1 0.34 0.38 0.17 0.32 0.4 0.13 Carrier 2: DesiredDestinations has the following operational parameters: : : $0.81 per mile per unit $175 per lane (Origin-destination pair used) and a capacity of 6,000 per lane. Additionally, the frozen products may be subject to melting and spoilage. The following spoilage rates are reported for the possible paths: Bustamante Saltillo Santa Catarina Santa Carla Montemorelos Spoilage rate (%) Linares Monclova Monterrey 0.18 0.15 0.11 0.21 0.18 0.11 0.15 0.14 0.07 0.14 0.18 0.08 0.22 0.24 0.13 Based on the data above, please answer the following questions. Q1. Considering no spoilage and no lane costs, what would be the costs and distribution plan for Gelido under Carrier 1 and Carrier 2. Q2. Considering spoilage but no lane costs, what would be the best cost distribution plan for Gelido? Please solve it using flow-balance model. Q3. Considering no spoilage but lane costs, determine distribution plan for Gelido using either Carrier 1 or Carrier 2. Which one would you choose? Q4. Considering no spoilage but lane costs, determine distribution plan for Gelido using Carrier 1 and Carrier 2 simultaneously. How would the results improve over Q3? Q5. Considering all of the problem data (including spoilage and lane costs), determine distribution plan for Gelido using Carrier 1 and Carrier 2 simultaneously. How do the results change compared to Q4? OM 418: HM5 Excel-based problems: Problem Context: Gelido, a Mexican firm distributing frozen foods, is evaluating its distribution system. Currently, Gelido has three facilities (Linares, Monclova, Monterrey) and serving five market districts (Bustamante, Saltillo, Santa Catarina, Santa Carla, Montemorelos). The facilities has the following production capacities: Linares 23,000 Facilities Capacities Monclova 21,000 Monterrey 25,000 The annual demands at the market districts are as follows: Bustamante 6,200 Saltillo 6,600 Santa Catarina 5,800 Santa Carla 4,400 Montemorelos 3,600 Market Demand The distance between the production facilities and market places are as follows: Bustamante Saltillo Santa Catarina Santa Carla Montemorelos Distance (in miles) Linares Monclova Monterrey 165 90.8 84.2 132.5 118.5 51.6 92.7 139 11.9 32.4 176.7 49.5 75.6 140.2 37.2 Gelido has two possible carriers, competing to secure a dedicated or mixed contract from Gelido. Carrier 1: LogConsult has the following operational parameters: $0.78 per mile per unit $200 per lane (Origin-destination pair used) and a capacity of 4,800 per lane. Additionally, the frozen products may be subject to melting and spoilage. The following spoilage rates are reported for the possible paths: . Bustamante Saltillo Santa Catarina Santa Carla Montemorelos Spoilage rate (%) Linares Monclova Monterrey 0.22 0.25 0.18 0.31 0.28 0.15 0.19 0.24 0.1 0.34 0.38 0.17 0.32 0.4 0.13 Carrier 2: DesiredDestinations has the following operational parameters: : : $0.81 per mile per unit $175 per lane (Origin-destination pair used) and a capacity of 6,000 per lane. Additionally, the frozen products may be subject to melting and spoilage. The following spoilage rates are reported for the possible paths: Bustamante Saltillo Santa Catarina Santa Carla Montemorelos Spoilage rate (%) Linares Monclova Monterrey 0.18 0.15 0.11 0.21 0.18 0.11 0.15 0.14 0.07 0.14 0.18 0.08 0.22 0.24 0.13 Based on the data above, please answer the following questions. Q1. Considering no spoilage and no lane costs, what would be the costs and distribution plan for Gelido under Carrier 1 and Carrier 2. Q2. Considering spoilage but no lane costs, what would be the best cost distribution plan for Gelido? Please solve it using flow-balance model. Q3. Considering no spoilage but lane costs, determine distribution plan for Gelido using either Carrier 1 or Carrier 2. Which one would you choose? Q4. Considering no spoilage but lane costs, determine distribution plan for Gelido using Carrier 1 and Carrier 2 simultaneously. How would the results improve over Q3? Q5. Considering all of the problem data (including spoilage and lane costs), determine distribution plan for Gelido using Carrier 1 and Carrier 2 simultaneously. How do the results change compared to Q4