Answers in excel are preferable

1. (30 points) For this question use the Excel file provided. A company ships their products from three different plants (one in Los Angeles, one in Atlanta, and one in New York City) to four regions of the United States (East, Midwest, South, West). Each plant has a limited capacity on how many products can be sent out, and each region has a demand of products that they must receive. There is a different unit transportation cost between each plant, or each city, and each region. The company wants to determine how many products each plant should ship to each region in order to minimize the total transportation cost. East Midwest South West Los Angeles $5.00 $3.50 $4.20 $2.20 Plant capacity 10,000 12,000 Atlanta $3.20 $2.60 $1.80 $4.80 New York City $2.50 $3.10 $3.30 $5.40 14,000 Regional demand 9,000 6,000 6,000 13,000 a) Is there enough capacity to meet demand? (Answer with numbers.) b) Management wants to ship to each region from the plant with the cheapest unit transportation cost to that region. Is there enough capacity at each plant to follow this policy? If not, how much additional capacity at each plant would the company need? c) Apply the following algorithm manually to create a feasible solution and compute the total cost. This is a so called greedy heuristic method that finds a quick and reasonable solution. (Best to use Excel to do this, but you can do on paper too). Unit shipment costs Sales Regions Plant locations East Los Angeles $5.0 Atlanta $3.2 New York City $2.5 Midwest $3.5 $2.6 $3.1 South $4.2 $1.8 $3.3 West $2.2 $4.8 $5.4 Plant Capacity 10,000 12,000 14,000 Regional Demand 9,000 6,000 6,000 13,000 34,000 36,000 1 Decision variables 2 8 Shipment volume (in units) Sales Regions 5 Plant locations East 6 Los Angeles 7 Atlanta B New York City Objective function Minimize total shipment costs Midwest South West Capacity availability 1 Total Shipped Los Angeles 3 Atlanta 4 New York City 5 5 Total capacity 10,000 12,000 14,000 Demnd Satisfaction Total received East Midwest South West