The Sentry Lock Corporation manufactures popular commercial security locks at plants in Macon, Louisville, and Detroit. Sentry produces three different lines of security locks; Fortify, True-Lock, and Safety-First. The plant in Macon produces all three lines of locks while the one in Louisville only produces Fortify and True Lock lines, and the plant in Detroit only produces the True-Lock and Safety-First lines. Each line of locks requires varying amounts of three raw materials that are sometimes in short supply: titanium, aluminum, and stainless steel that Sentry uses in all its locks. The manufacturing process for each line of locks at each plant is identical. Thus, the amount of each of these materials required in each set of the different lines of locks is summarized in the following table:
Resources Required per Lock (in lbs)
Fortify True-Lock Safety-First
Titanium 2.9 2.7 2.5
Aluminum 4.5 4 5
Stainless Steel 5.4 5 4.8
The estimated amount of each of these key resources available at each plant during the coming month is given as:
Estimated Resources Required (in lbs)
Macon
Louisville
Detroit
Titanium
45,000
85,000
145,000
Aluminum
60,000
120,000
190,000
Stainless Steel
95,000
160,000
180,000
Sentry’s reputation for quality and affordability ensures that they can sell all the locks they can make. The Fortify, TrueLock, and Safety-First sell for $225, $195, and $165 each, respectively, regardless of where they are produced. The per unit cost of production at each plant is $39.50, $37.50, and $35.00, respectively.
Sentry’s locks are sold to retailers through wholesale distributors in three cities across the United States. The cost of shipping locks to each distribution point from each production facility is summarized in the following table. Note again that Louisville does not produce Safety-First locks and Detroit does not produce Fortify locks.
Shipping Costs
Fortify
True-Lock
Safety-First
From / To
Tacoma
Denver
Baltimore
Tacoma
Denver
Baltimore
Tacoma
Denver
Baltimore
Macon
$2.60
$2.10
$1.75
$2.50
$2.00
$1.65
$2.35
$1.85
$1.45
Louisville
$1.95
$1.70
$1.95
$1.85
$1.60
$1.85
—
—
—
Detroit
—
—
—
$2.30
$1.25
$2.00
$2.25
$1.20
$2.00
Sentry recently received the following distributor orders for the coming month:
Number of Locks Ordered
Fortify True-Lock Safety-First
Tacoma 7000 9000 9000
Denver 5500 10,000 15,000
Baltimore 9000 12,000 11,000
Sentry wants to determine the profitable way of manufacturing and shipping locks from its plants to the distributors. Because the total demand from distributors 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 distributor will have the opportunity to fill at least 90% of the orders received.
Hint: To solve this problem, the excel spreadsheet need three sections. The first section is a product-mix optimalization. The second section is a network flow shipping plan. The third section is an income statement tying the first two sections together. Also remember decision variables are unknown (until found by Solver) but can still be used in spreadsheets.
Create a spreadsheet model for this problem and solve it. What is the optimal solution?
Using the Sensitivity Report: if Sentry wanted to improve this solution, what additional resources would be needed and where would they be needed? Explain.
What would Sentry’s optimal profit be if the company was not required to supply at least 90% of each distributor’s order?
Suppose Sentry’s agreement included the option of paying a $10,000 penalty if the company cannot supply at least 90% of each distributor’s order but instead supply at least 80% of each distributor’s order. Comment of the pros and cons of Sentry exercising this option.
The post The Sentry Lock Corporation manufactures popular commercial security locks at plants in appeared first on PapersSpot.
