Please answer all from (a) to (e)

2. A manufacturing firm produces diesel engines in four cities: Phoenix, Seattle. St. Louis, and Detroit. (50 pts) The company is able to produce the following numbers of engines per month: Table 2.1. Plant Production Location Phoenix Seattle St. Louis Detroit Monthly Production, units 10 20 15 25 Three trucking firms purchase the following numbers of engines for their plants in three cities: Table 2.2. Trucking Firm Demand Location Weekly Demand, units Greensboro 10 Charlotte 20 Louisville 15 The transportation costs per engine from sources to destinations are as shown: Table 2.3. Shipping Costs, $ per unit Plant Trucking Firm (customer) Greensboro Charlotte Louisville Phoenix 700 800 500 Seattle 600 1000 600 St. Louis 1000 400 500 Detroit 300 900 1100 a) Use Excel Solver to set up the objective function and constraints for this problem using a transportation model (linear programming) to minimize the total shipping costs. (10 pts) b) Report how many units from each plant should be distributed to each trucking firm based on the optimal solution. (10 pts) c) There is a possibility that, because of truckers' strikes, shipments might be prohibited from Detroit to Greensboro next month. Propose another distribution plan to achieve minimum total shipping costs and satisfy all demand with this possible restriction. (10 pts) d) Report how this restriction (truckers' strikes) impacts the total shipping costs and the plants' utilization. (10 pts) e) Discuss what other information, besides the total shipping costs, would be helpful to develop the effective distribution plan. Justify your opinion. (10 pts)