A joint GCC Company manufactures and distributes 5kg Portable Gas Cylinders (PGC), used for outdoors and home cooking. The company started with a small production plant in Musaffah then gradually built-up a customer-base throughout the GCC region, via a distribution center in Khalifa Industrial Zone in Abu Dhabi (KIZAD). Later when business expanded, the company started using another distribution center in Jabal Ali- Dubai to distribute the PCs to its customers in the region. The company pays quarterly fixed admin costs of $40,000 and $35,000 for utilizing the distribution centers in Kizad and Jabal Ali, respectively. Just 18 months ago, the company opened two more production plant in Dubai (Al Quoz) and Ras Al Khaimah (RAK). Manufacturing costs differ between the company's production plants. The cost of each PGC produced at Musaffah plant is $105. However, the AlQuoz and RAK plant utilize newer and more efficient equipment; as a result, the manufacturing cost per PGC unit is $5 less than the Musaffah plant. Due to the company's rapid growth, not much attention has been paid to the efficiency of the distribution system, and management has decided it is time to address this issue in its three plants and two distribution centers. The cost of shipping a PGC to KIZAD from Musaffah and Al Quoz is $7 and $8 respectively. Similarly, the cost of shipping a PGC to Jabal Ali from Al Quoz and RAK is $4 and $12 respectively. There are no shipments allowed from RAK to KIZAD nor from Musaffah to Jabal Ali. The Quarterly production capacity is 30,000 PGCs at the Musaffah plant and 20,000 PGCs at the RAK and AlQouz plants. Note that no shipments are allowed from the RAK plant to the Kizad distribution center or from Musaffah to plant to Jabal Ali distribution center. The company serves nine customer zones from the two distribution centers. The forecast of the number of PCs needed in each customer zone for the next quarter is shown below. Customer Zone Demand Dhahran 6300 Manama 4880 Riyadh 2130 Kuwait 1210 Jeddah 6120 Salalah 4830 Muscat 2750 Nizwa 8580 Sohar 4460 The selling price is standardized at $200 per PGC and the unit costs of shipping from each distribution center to each customer zone are given in dollars in the table below. Nizwa Center KIZAD Jabal Ali Dhahran Manama Riyadh 14 19 18 16 17 20 Customer Zones Jeddah Kuwait Muscat 25 29 9 26 25 10 8 8 Sohar 5 6 Salalah 22 24 In the current distribution system, demand at the Dhahran, Manama, Riyadh, Jeddah, and Kuwait customer zones is satisfied by shipments from KIZAD distribution center. In a similar manner, the Muscat, Nizwa, Sohar, and Salalah customer zones are served by the Jabal Ali distribution center. The company would like to determine how many PGCs to ship from each plant, to satisfy the forecasted aggregated quarterly demand at the customer zones through the distribution centers to maximize the total profit. (a) Provide a Linear Programming Model (in both algebraic and spreadsheet formats) for the above case. You model should clearly state the Decision Variables, objective function, and constraints. (b) Use the SOLVER module on Microsoft Excel to solve your model in part (a) (c) Provide detailed comments on your results obtained in part (b) using your own words. A joint GCC Company manufactures and distributes 5kg Portable Gas Cylinders (PGC), used for outdoors and home cooking. The company started with a small production plant in Musaffah then gradually built-up a customer-base throughout the GCC region, via a distribution center in Khalifa Industrial Zone in Abu Dhabi (KIZAD). Later when business expanded, the company started using another distribution center in Jabal Ali- Dubai to distribute the PCs to its customers in the region. The company pays quarterly fixed admin costs of $40,000 and $35,000 for utilizing the distribution centers in Kizad and Jabal Ali, respectively. Just 18 months ago, the company opened two more production plant in Dubai (Al Quoz) and Ras Al Khaimah (RAK). Manufacturing costs differ between the company's production plants. The cost of each PGC produced at Musaffah plant is $105. However, the AlQuoz and RAK plant utilize newer and more efficient equipment; as a result, the manufacturing cost per PGC unit is $5 less than the Musaffah plant. Due to the company's rapid growth, not much attention has been paid to the efficiency of the distribution system, and management has decided it is time to address this issue in its three plants and two distribution centers. The cost of shipping a PGC to KIZAD from Musaffah and Al Quoz is $7 and $8 respectively. Similarly, the cost of shipping a PGC to Jabal Ali from Al Quoz and RAK is $4 and $12 respectively. There are no shipments allowed from RAK to KIZAD nor from Musaffah to Jabal Ali. The Quarterly production capacity is 30,000 PGCs at the Musaffah plant and 20,000 PGCs at the RAK and AlQouz plants. Note that no shipments are allowed from the RAK plant to the Kizad distribution center or from Musaffah to plant to Jabal Ali distribution center. The company serves nine customer zones from the two distribution centers. The forecast of the number of PCs needed in each customer zone for the next quarter is shown below. Customer Zone Demand Dhahran 6300 Manama 4880 Riyadh 2130 Kuwait 1210 Jeddah 6120 Salalah 4830 Muscat 2750 Nizwa 8580 Sohar 4460 The selling price is standardized at $200 per PGC and the unit costs of shipping from each distribution center to each customer zone are given in dollars in the table below. Nizwa Center KIZAD Jabal Ali Dhahran Manama Riyadh 14 19 18 16 17 20 Customer Zones Jeddah Kuwait Muscat 25 29 9 26 25 10 8 8 Sohar 5 6 Salalah 22 24 In the current distribution system, demand at the Dhahran, Manama, Riyadh, Jeddah, and Kuwait customer zones is satisfied by shipments from KIZAD distribution center. In a similar manner, the Muscat, Nizwa, Sohar, and Salalah customer zones are served by the Jabal Ali distribution center. The company would like to determine how many PGCs to ship from each plant, to satisfy the forecasted aggregated quarterly demand at the customer zones through the distribution centers to maximize the total profit. (a) Provide a Linear Programming Model (in both algebraic and spreadsheet formats) for the above case. You model should clearly state the Decision Variables, objective function, and constraints. (b) Use the SOLVER module on Microsoft Excel to solve your model in part (a) (c) Provide detailed comments on your results obtained in part (b) using your own words