(a) Following the example in Lecture 2, draw a distribution network based on the information given above and label as much information as possible on the network that you draw, including the unit shipping cost, demand from each destination.

(b) Formulate this problem as a linear programming model. You should formulate the problem algebraically. Clearly define the decision variables and their units. Express the objective function and the constraints as linear functions of the decision variablesthat you define. You should also identify each constraint by attaching a short descriptive label to it (e.g., Boston Demand, or Louisville Capacity, etc.).

(c) Implement the above formulation in an Excel spreadsheet and solve it using Excel Solver. Submit the Excel file with an answer report generated and reported.

The Sentry Lock Corporation manufacturers a popular commercial security lock at plants in Louisville, Detroit and Phoenix. The per unit cost of production at each plant is $35.50,$39.00, and $36.25, respectively, and the annual production capacity at each plant is 18,000,13,000, and 12,000 , respectively. Sentry's locks are sold to retailers through wholesale distributors in four cities across the United States. The unit cost of shipping from each plant to each distributor is summarized in the following table along with the forecasted demand from each distributor for the coming year. Sentry wants to determine the least expensive way of manufacturing and shipping locks from their plants to the distributor. Because the total demand from distributors exceeds the total production capacity of all plants, Sentry realized it will not be able to satisfy all the demand for its product, but wants to make sure each distributor will have the opportunity to fill at least 80% of the orders they receive. Create a spreadsheet model for this problem and solve it. What is the optimal solution? What is the optimal value? Problem 2 (Distribution Network): The business scenario is presented as follows. The Sentry Lock Corporation manufacturers a popular commerial security lock at plants in Lonisville. Detroit and Phoenix. The per unit cost of production at each plant is $35.50.$39.00, and $36.25, respectively, and the annual production capacity at each plant is 18,000,13,000, and 12,000 , respectively. Sentry's locks are wold to retailers through wholesale distributors in four cities across the United States. The uait cost of shipping from each plant to each distributor is sumamarized in the following table along with the forecasted demand from each distributor for the coming year. Sentry wants to determine the least expensive way of manufacturing and shipping locks from their plants to the distributor. Because the total demand from distributots exceeds the total production capacity of all plants. Sentry realized it will aot be able to satisfy all the demand for its product. but Waats to make stre each distributor will have the opportunity to fill at least 80 of the orders they receive. Create a spreadsheet model for this problem and solve it. What is the optimal solution? What is the optimal value? (a) Following the example in Lecture 2, draw a distribution network based on the information given above and label as much information as possible on the network that you draw, including the unit shipping cost, demand from each destination. (b) Formulate this problem as a linear programming model. You should formulate the problem algebraically. Clearly define the decision variables and their units. Express the objective function and the constraints as linear functions of the decision variables that you define. You should also identify each constraint by attaching a short descriptive label to it (e.g., Boston Demand, or Louisville Capacity, etc.). (c) Implement the above formulation in an Excel spreadsheet and solve it using Excel Solver. Submit the Excel file with an answer report generated and reported