QUESTIONS 120 Question 2. Use the file finalexam q2.xlsx. The Grand Prix Automobile Company manufactures cars in three plants and then ships them to four regions of the country The plants can supply the amounts listed in the right column of Table below. Region 1 Region 2 Region 3 Region 4 Capacity Plant 1 131 218 266 450 Plant 2 250 116 263 278 600 Plant 3 178 132 122 180 Demand 450 200 300 300 Based on past data, previous year's customer demands by region are listed in the bottom row of this table (see: Demand row), and the unit costs of shipping an automobile from each plant to each region are listed in each cell (e.g., shipping one care from Plant 1 to Region 2 costs $218). However, due to changes in market conditions this year the demand at each region increased by 200 cars. Grand Prix wants to find the lowest-cost shipping plan for meeting the demands of the four regions without exceeding the capacities of the plants. Modify the original Grand Prix example by increasing the demand at each region by 200, so that total demand is well above total plant capacity. This means that some demands cannot be supplied. Suppose there is a unit "penalty cost at each region for not supplying an automobile. Let these unit penalty costs $600, $750, $625, and $550 for the four regions. 500 Question 2: Part a. Develop an optimization model and use Excel Solver to find the least-cost way of shipping the cars from plants to regions by minimizing the sum of shipping costs and penalty costs for unsatisfied demand, while staying within plant capacities and meeting regional demands. First, provide the mathematical model. Then, describe the objective function, decision variables and constraints in the mathematical model in your own words. After solving problem, what is the optimal solution? Interpret the results. Hint: Introduce a fourth plant and set its unit shipping costs to the regions equal to the unit penalty costs. Then interpret a car shipped from this fictitious plant to a region as a unit of demand not satisfied. QUESTIONS 120 Question 2. Use the file finalexam q2.xlsx. The Grand Prix Automobile Company manufactures cars in three plants and then ships them to four regions of the country The plants can supply the amounts listed in the right column of Table below. Region 1 Region 2 Region 3 Region 4 Capacity Plant 1 131 218 266 450 Plant 2 250 116 263 278 600 Plant 3 178 132 122 180 Demand 450 200 300 300 Based on past data, previous year's customer demands by region are listed in the bottom row of this table (see: Demand row), and the unit costs of shipping an automobile from each plant to each region are listed in each cell (e.g., shipping one care from Plant 1 to Region 2 costs $218). However, due to changes in market conditions this year the demand at each region increased by 200 cars. Grand Prix wants to find the lowest-cost shipping plan for meeting the demands of the four regions without exceeding the capacities of the plants. Modify the original Grand Prix example by increasing the demand at each region by 200, so that total demand is well above total plant capacity. This means that some demands cannot be supplied. Suppose there is a unit "penalty cost at each region for not supplying an automobile. Let these unit penalty costs $600, $750, $625, and $550 for the four regions. 500 Question 2: Part a. Develop an optimization model and use Excel Solver to find the least-cost way of shipping the cars from plants to regions by minimizing the sum of shipping costs and penalty costs for unsatisfied demand, while staying within plant capacities and meeting regional demands. First, provide the mathematical model. Then, describe the objective function, decision variables and constraints in the mathematical model in your own words. After solving problem, what is the optimal solution? Interpret the results. Hint: Introduce a fourth plant and set its unit shipping costs to the regions equal to the unit penalty costs. Then interpret a car shipped from this fictitious plant to a region as a unit of demand not satisfied
