IMPLEMENT THE OPTIMIZATION MODEL AND SOLVE IT IN EXCEL

Network design problem You have been asked to assist in the distribution design for Insulators Co. the premiere source for porcelain insulators used in electrical lines. Insulators Co. operates two plants (Plant 1, Plant 2). The plants produce a range of insulator types and sizes, but because the insulators are quite fragile, they are transported directly from the plants to the three distribution centers (DCs) located in Jeddah, Riyadh, and Dammam. The cost of transporting an insulator from each plant to each DC ($/insulator) is shown below: $/insulator Jeddah Riyadh Dammam Plant 1 1.90 2.49 2.50 Plant 2 1.01 0.86 5.27 According to Chief Operating Officer, there are type A (spool) and type B (cap and pin) insulators that have very different demand patterns. He has asked you to find the optimal flow of A and B insulators from the plants to the three DCs. Luckily, each plant produces both types and the transportation costs are the same for both insulator types as given in the Table above. The demand by type for each DC is different. Jeddah: 3,900 Type A insulators/week and 2,100 Type B insulators/week Riyadh: 220 Type A insulators/week and 330 Type B insulators/week Dammam: 632 Type A insulators/week and 2,528 Type B insulators/week Moreover, to manage a production line of one product type costs $5,000 per week. So, if a plant is producing both Type A and Type B insulators, they incur a $10,000 fixed cost. The plants have their differences, but they have the same production capacity of 5,000 insulators/week for both types of insulators together. Your objective is to minimize the total cost while satisfying all demands and respecting the production capacity constraint. Give the detailed mathematical problem formulation, including input variables, decision variables, objective function, and constraints. Input variables Decision variables (please be precise) Objective (please state each part of objective function verbally before you write it as a mathematical expression) Constraints (please number your constraints and state each constraint verbally before you write it as a mathematical expression) 1 Network design problem You have been asked to assist in the distribution design for Insulators Co. the premiere source for porcelain insulators used in electrical lines. Insulators Co. operates two plants (Plant 1, Plant 2). The plants produce a range of insulator types and sizes, but because the insulators are quite fragile, they are transported directly from the plants to the three distribution centers (DCs) located in Jeddah, Riyadh, and Dammam. The cost of transporting an insulator from each plant to each DC ($/insulator) is shown below: $/insulator Jeddah Riyadh Dammam Plant 1 1.90 2.49 2.50 Plant 2 1.01 0.86 5.27 According to Chief Operating Officer, there are type A (spool) and type B (cap and pin) insulators that have very different demand patterns. He has asked you to find the optimal flow of A and B insulators from the plants to the three DCs. Luckily, each plant produces both types and the transportation costs are the same for both insulator types as given in the Table above. The demand by type for each DC is different. Jeddah: 3,900 Type A insulators/week and 2,100 Type B insulators/week Riyadh: 220 Type A insulators/week and 330 Type B insulators/week Dammam: 632 Type A insulators/week and 2,528 Type B insulators/week Moreover, to manage a production line of one product type costs $5,000 per week. So, if a plant is producing both Type A and Type B insulators, they incur a $10,000 fixed cost. The plants have their differences, but they have the same production capacity of 5,000 insulators/week for both types of insulators together. Your objective is to minimize the total cost while satisfying all demands and respecting the production capacity constraint. Give the detailed mathematical problem formulation, including input variables, decision variables, objective function, and constraints. Input variables Decision variables (please be precise) Objective (please state each part of objective function verbally before you write it as a mathematical expression) Constraints (please number your constraints and state each constraint verbally before you write it as a mathematical expression) 1