The Sentry Lock Corporation manufactures a popular commercial security lock at plants in Macon, Louisville, Detroit, and Phoenix. The per unit cost of production at each plant is $35.50, $37.50, $39.00, and $36.25, respectively, and the annual produc- tion 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 United States. The unit cost of shipping from each plant to each distributor is sum- marized in the following table along with the forecasted demand from each distrib- utor for the coming year.
Unit Shipping Cost to Distributor in
Plants
Tacoma
San Diego
Dallas
Denver
St. Louis
Tampa
Baltimore
Macon
$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
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 the total demand from distribu- tors exceeds the total production capacity for all the plants, Sentry realizes it will not be able to satisfy all the demand for its product, but wants to make sure each dis- tributor will have the opportunity to ï¬ll at least 80% of the orders they receive.
a. Create the spreadsheet model for this problem and solve it.
b. What is the optimal solution?
