Reference no: EM132081798

Can someone please help me with th following problem, If you can pleaseinclude all work show the constraints, and copy of Excel Screenshot. a) Create spreadsheet model for this problem and solve it (include copy of Excel Solver screenshot NOT just the data!)What is optimal solution? Explain how you came to that conclusion (show all work) Problem is below and table is attached containing the data. The problem is below. Problem: The TMC corporation manufactues locks at plants, A, B, C and D. The per unit cost of production at each plant is $35.50, $37.50, $39.00 and $36.25, respectively, while the annual production capacity at each plant is 18,000, 15,000, 25,000, and 20,000 respectively. Sentry's locks are sold to retailers through wholesale distributors in seven cities across the U.S. The unit cost of shipping from each plant to each distributor is summarized in the attached table along with the forecasted demand from each distributor for the coming year. Plants Phoenix Denver Princeton Lafayette Miami Tampa Reno A $2.50 $2.75 $1.75 $2.00 $2.10 $1.80 $1.65 B $1.85 $1.90 $1.50 $1.60 $1.00 $1.90 $1.85 C $2.30 $2.25 $1.85 $1.25 $1.50 $2.25 $2.00 D $1.90 $0.90 $1.60 $1.75 $2.00 $2.50 $2.65 Demand 8,500 14,500 13,500 12,600 18,000 15,000 9,000 Sentry wants to determine the least expensive way of manufacturing and shipping locks from their plants to the distributors. Because teh total demand from distributors exceeds the total production capacity for all the plants, Ssentry realizes they will not be able to satisfy all the demand for their product, but wants to make sure each distributor will have the opportunity to fill at least 80% of the orders they receive.