The Lincoln Lock Company manufactures a commercial security lock at plants in Atlanta, Louisville, Detroit, and Phoenix. The unit cost of production at each plant is $35.50, $37.50, $37.25, and $36.25, and the annual capacities are 18,000, 15,000, 25,000, and 20,000, respectively. The locks are sold through wholesale distributors in seven locations around the country. The unit shipping cost for each plant-distributor combination is shown in the following table, along with the forecasted demand from each distributor for the coming year. Tacoma San Diego Dallas Denver St Louis Tampa Baltimore Atlanta 2.50 2.75 1.75 2.00 2.10 1.80 1.65 Louisville 1.85 1.90 1.50 1.60 1.00 1.90 1.85 Detroit 2.30 2.25 1.85 1.25 1.50 2.25 2.00 Phoenix 1.90 0.90 1.60 1.75 2.00 2.50 2.65 Demand 5500 11.500 10,500 9600 15.400 12.500 6600 (a) Determine the least costly way of shipping locks from plants to distributors. (b) List the shadow prices corresponding to each plant's capacity. Which capacity has the largest shadow price? For how large an increase does this value hold? (c) Describe the qualitative pattern in the solution of part (a). (d) Use the pattern in (c) to trace the effects of increasing the demands at Tacoma, San Diego and Dallas by 100 simultaneously. How will the shipping schedule change? What will be the change in the optimal total cost? (e) For how much of a change in demand in part (d) will the pattern persist? Optimization Modelling Using Excel Solver (with Sensitivity Analysis) Instructions: Answer the following questions using Excel Solver. Attach the actual results from the Excel Solver (including your linear programming formulation). 1. Coffee Blending and Sales Hill-O-Beans Coffee Company blends four component beans into three final blends of coffee: One is sold to luxury hotels, another to restaurants, and the third to supermarkets for store-label brands. The company has four reliable bean supplies: Argentine Abundo, Peruvian Colmado, Brazilian Maximo, and Chilean Saboro. The table below summarizes the very precise recipes for the final coffee blends, the cost and availability information for the four components, and the wholesale price per pound of the final blends. The percentages indicate the fraction of each component to be used in each blend. Component (lb) Hotel Rest Market Cost per pound Max weekly availability Abundo 20% 35% 10% $0.60 40,000 Colmado 40% 15% 35% $0.80 25,000 Maximo 15% 20% 40% $0.55 20,000 Saboro 25% 30% 15% S0.70 45,000 Wholesale price Per pound $1.25 $1.50 $1.40 The processor's plant can handle no more than 100,000 pounds per week, and Hill-O-Beans would like to operate at capacity. There is no problem in selling the final blends, although the Marketing Department requires minimum production levels of 10,000, 25,000, and 30,000 pounds, respectively, for the hotel, restaurant, and market blends. (a) In order to maximize weekly profit, how many pounds of each component should be purchased? (b) What is the shadow price (from the Sensitivity Report) on the availability of Maximo beans? (c) How much (per pound) should Hill-O-Beans be willing to pay for additional pounds of Maximo beans in order to raise total profitD