Excel-based problems: Problem Context: Gelido, a Mexican firm distributing frozen foods, is evaluating its distribution system. Currently, Gelido has three facilities (Linares, Mondova, Monterrey) and serving five market districts (Bustamante, Saltillo, Santa Catarina, Santa Carla, Montemorelos). The facilities has the following production capacities: Monclova Monterrey Facilities Capacities 23,000 21,000 25,000 Linares The annual demands at the market districts are as follows: Bustamante Saltillo Santa Catarina Market 6,200 6,600 5,800 Demand Santa Carla 4,400 Montemorelos 3,600 Montemorelos The distance between the production facilities and market places are as follows: Distance Bustamante Saltillo Santa Catarina Santa Carla (in miles) Linares 132.5 92.7 32.4 Monclova 90.8 118.5 139 176.7 Monterrey 84.2 51.6 11.9 49.5 165 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; Spoilage rate Bustamante Saltillo Santa Catarina Santa Carla Montemorelos (%) Linares 0.22 0.34 0.32 Monclova Monterrey 0.25 0.18 0.31 0.28 0.15 0.19 0.24 0.1 0.38 0.17 0.4 0.13 Carrier 2: Desired Destinations 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: Spoilage rate Bustamante Saltillo Santa Catarina Santa Carla Montemorelos (%) Linares 0.18 0.21 0.15 0.14 0.22 Monclova 0.15 0.14 0.24 Monterrey 0.11 0.11 0.07 0.08 0.13 0.18 0.18 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? Excel-based problems: Problem Context: Gelido, a Mexican firm distributing frozen foods, is evaluating its distribution system. Currently, Gelido has three facilities (Linares, Mondova, Monterrey) and serving five market districts (Bustamante, Saltillo, Santa Catarina, Santa Carla, Montemorelos). The facilities has the following production capacities: Monclova Monterrey Facilities Capacities 23,000 21,000 25,000 Linares The annual demands at the market districts are as follows: Bustamante Saltillo Santa Catarina Market 6,200 6,600 5,800 Demand Santa Carla 4,400 Montemorelos 3,600 Montemorelos The distance between the production facilities and market places are as follows: Distance Bustamante Saltillo Santa Catarina Santa Carla (in miles) Linares 132.5 92.7 32.4 Monclova 90.8 118.5 139 176.7 Monterrey 84.2 51.6 11.9 49.5 165 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; Spoilage rate Bustamante Saltillo Santa Catarina Santa Carla Montemorelos (%) Linares 0.22 0.34 0.32 Monclova Monterrey 0.25 0.18 0.31 0.28 0.15 0.19 0.24 0.1 0.38 0.17 0.4 0.13 Carrier 2: Desired Destinations 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: Spoilage rate Bustamante Saltillo Santa Catarina Santa Carla Montemorelos (%) Linares 0.18 0.21 0.15 0.14 0.22 Monclova 0.15 0.14 0.24 Monterrey 0.11 0.11 0.07 0.08 0.13 0.18 0.18 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