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 Plant capacity Los Angeles $5.00 $3.50 $4.20 $2.20 10.000 Atlanta $3.20 $2.60 $1.80 $4.80 12,000 New York $2.50 $3.10 $3.30 $5.40 14,000 City Regional 9,000 6,000 |6,000 13,000 34000 36000 demand a) Is there enough capacity to meet demand? (Answer with numbers.) A. There would be enough capacity to meet the demands (9000+6000+6000+13000=34000), Plant capacity for all the regions is 36000, so it would be enough. 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? A. Los Angeles (West= 2.20x10,000=22000) No, LA would need additional 12000 to accommodate West cheap price. B. Atlanta (South=1 80x6000=10800) Yes, Atlanta under capacity at 1200 to spare C. NY (East=2.50*9000=22500) No, NY would need additional 8500 to accommodate East cheap1.". ILL. 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 nds a quick and reasonable solution. (Best to use Excel to do this= but you can do on paper too}. For each plant g, in the order listed in Table, do For each region j sorted in low to high transportation cost from plant 5', Allocate remaining capacity of gm meet remaining demand in j Until all regions are allocated capacity to meet all their demand For example, the algorithm starts with LA with a remaining capacity of lK; (cheapest cost is to 1West region with demand 13K]; assigns capacity to meet as much demand as possible, so l'EIK is assigned to West; (non.r LA has 0 capacity le, West has 3K demand le)- Continue with Atlanta in the same manner. List your solution in the table below and compute the total cost. Your shi - .- entyolume solution is tabular format \" - - - d) Formulate this problem as a Linear Programing model. Decision variables (Hint: what do we want to decide about? Shipment volume from any plant to any region. Start with the following and complete) From plant To region X1 Shipment volume LA East X2 Shipment volume LA Midwest Shipment volume LA South X4 Shipment volume LA West Shipment volume NYC South X12 Shipment volume NYC WestObjective function How do we measure the cost of a complete solution? (Hint: write a function that measures transportation costs with the decision variables above and the given unit shipment costs.) Complete the partial function below. Minimize z = $5.0X, + $3.5X2 + $4.2X3 + ... Constraints How do we limit the decision variables so that we get feasible solutions, where all demand is satisfied and capacity not exceeded? Demand at all regions have to be satisfied (one for each region.) Complete the rest of the table. Region Total received from all plants Total demanded East Xi+ Xs + X9 = 9,000 Midwest South West Capacity cannot be exceeded at any plant (one for each plant.) Complete the rest of the table. Plant Total shipped to all plants Total capacity Los Angeles Xi+ X2+ X3+ X4 10,000 Atlanta New York City e) Implement the model in Excel and compute the optimal solution. How does the optimal solution compare to the quick heuristic in part (c)? (Copy and paste the answer report here.)e) Implement the model in Excel and compute the optimal solution. How does the optimal solution compare to the quick heuristic in part (c)? (Copy and paste the answer report here-) i) che company has budget to increase the total capacity by 1,000 units= at which plant would you recommend them to expand? 1|What would be total cost savings with this expansion. Refer to the sensitivity report and explain your answer. (Copy and paste the constraints section of the sensitivity report here.) g) Suppose that the Atlanta plant had to reduce capacity by 1,000 units to repair and renovate. How much would this cause the total (optimized) transportation costs to increase? Refer to the sensitivity report and explain your answer. A B C D E F G H Unit shipment costs Sales Regions Plant locations East Midwest South West Plant Capacity Los Angeles $5.0 $3.5 $4.2 $2.2 10,000 Atlanta $3.2 $2.6 $1.8 $4.8 12,000 New York City $2.5 $3.1 $3.3 $5.4 14,000 Regional Demand 9,000 6,000 6,000 13,000 34,000 36,000 0 Decision variables Shipment volume (in units) 4 Sales Regions Objective function 5 Plant locations East Midwest South West Minimize total shipment costs 6 Los Angeles $0 Atlanta 8 New York City 9 0 Capacity availability Demnd Satisfaction Total Shipped Total capacity Total received Total required Los Angeles 10,000 East >= 9,000 3 Atlanta = 6,000 New York City 14,000 South >= 6,000 5 West >= 13,000