Posted on by Judy

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 fill at least 80% of the orders they receive. a. Create a spreadsheet model for this problem and solve it. b. What is the optimal solution? c. Is the solution unique? d. If Sentry wants to increase their production capacity to meet more of the demand for their product, which plant should they use? Explain. e. If the cost of shipping from Phoenix to Tacoma increased to $1.98 per unit, would the solution change? Explain. f. Could the company make more money if they relaxed the restriction that each distributor must receive at least 80% of the predicted demand? Explain. g. How much extra should the company charge the distributor in Tacoma if this distributor insisted on receiving 8,500 units?

1. Spreadsheet: (Copy paste in excel)
Plants
Production cost per units
Customers and Transportation Cost per units
Tacoma
San Diego
Dallas
Denver
St. Louis
Baltimore
Tampa
Macon
35.5
2.5
2.75
1.75
2
2.1
1.8
1.65
Louisville
37.5
1.85
1.9
1.5
1.6
1
1.9
1.85
Detroit
39
2.3
2.25
1.85
1.25
1.5
2.25
2
Phoenix
36.25
1.9
0.9
1.6
1.75
2
2.5
2.65
Customers and combined cost per units
Supply
Plants
Tacoma
San Diego
Dallas
Denver
St. Louis
Baltimore
Tampa
Macon
=+$B3+C3
=+$B3+D3
=+$B3+E3
=+$B3+F3
=+$B3+G3
=+$B3+H3
=+$B3+I3
18000
Louisville
=+$B4+C4
=+$B4+D4
=+$B4+E4
=+$B4+F4
=+$B4+G4
=+$B4+H4
=+$B4+I4
15000
Detroit
=+$B5+C5
=+$B5+D5
=+$B5+E5
=+$B5+F5
=+$B5+G5
=+$B5+H5
=+$B5+I5
25000
Phoenix
=+$B6+C6
=+$B6+D6
=+$B6+E6
=+$B6+F6
=+$B6+G6
=+$B6+H6
=+$B6+I6
20000
Demand
8500
14500
13500
12600
18000
15000
9000
Subject To:
Plants
Customer Plant (TO)
Tacoma
San Diego
Dallas
Denver
St. Louis
Baltimore
Tampa
Produced
Supply
Philadelphia, PA
69.0000000000002
0
0
0
0
=SUM(C19:I19)
=
>=
>=
>=
>=
>=
>=
Demand
=0.8*C14
=0.8*D14
=0.8*E14
=0.8*F14
=0.8*G14
=0.8*H14
=0.8*I14
Total Transportation + Production Cost
=SUMPRODUCT(C10:I13,C19:I22)
Excel Sheet and Solver Option:
https://d2vlcm61l7u1fs.cloudfront.net/media%2F6cd%2F6cd61963-2256-462e-86da-6272d17ef4e1%2FphpIctm0f.png
2. Optimal Solution:
Plants
Customer Plant (TO)
Tacoma
San Diego
Dallas
Denver
St. Louis
Baltimore
Tampa
Produced
Philadelphia, PA
0
0
0
0
0
12000
6000
18000
Atlanta, GA
0
0
600
0
14400
0
0
15000
St. Louis, MO
0
0
8600
10080
0
0
1200
19880
Salt Lake City, UT
6800
11600
1600
0
0
0
0
20000
Shipped
6800
11600
10800
10080
14400
12000
7200
Total Cost = $2,805,450
3.
Sensitivity Report:
Adjustable Cells
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
$C$19
Philadelphia, PA Tacoma
0
0.699999988
37.99999999
1E+30
0.699999988
$D$19
Philadelphia, PA San Diego
0
1.949999996
38.25
1E+30
1.949999996
$E$19
Philadelphia, PA Dallas
0
0.249999996
37.25
1E+30
0.249999996
$F$19
Philadelphia, PA Denver
0
1.1
37.5
1E+30
1.1
$G$19
Philadelphia, PA St. Louis
0
1.099999995
37.6
1E+30
1.099999995
$H$19
Philadelphia, PA Baltimore
12000
0
37.3
0.099999997
1E+30
$I$19
Philadelphia, PA Tampa
6000
0
37.15
0.249999996
0.099999997
$C$20
Atlanta, GA Tacoma
0
0.049999978
39.34999998
1E+30
0.049999978
$D$20
Atlanta, GA San Diego
0
1.099999996
39.39999999
1E+30
1.099999996
$E$20
Atlanta, GA Dallas
600
0
39
0.049999978
0.149999996
$F$20
Atlanta, GA Denver
0
0.699999999
39.1
1E+30
0.699999999
$G$20
Atlanta, GA St. Louis
14400
0
38.5
0.149999996
1E+30
$H$20
Atlanta, GA Baltimore
0
0.099999997
39.39999999
1E+30
0.099999997
$I$20
Atlanta, GA Tampa
0
0.199999979
39.34999998
1E+30
0.199999979
$C$21
St. Louis, MO Tacoma
0
0.14999997
41.29999997
1E+30
0.14999997
$D$21
St. Louis, MO San Diego
0
1.099999999
41.25
1E+30
1.099999999
$E$21
St. Louis, MO Dallas
8600
0
40.85
0.14999997
0.099999997
$F$21
St. Louis, MO Denver
10080
0
40.25
0.699999999
1E+30
$G$21
St. Louis, MO St. Louis
0
0.149999996
40.5
1E+30
0.149999996
$H$21
St. Louis, MO Baltimore
0
0.1
41.25
1E+30
0.1
$I$21
St. Louis, MO Tampa
1200
0
41
0.099999997
0.249999996
$C$22
Salt Lake City, UT Tacoma
6800
0
38.15
0.049999978
1E+30
$D$22
Salt Lake City, UT San Diego
11600
0
37.15
1.099999996
1E+30
$E$22
Salt Lake City, UT Dallas
1600
0
37.85
0.600000001
0.049999978
$F$22
Salt Lake City, UT Denver
0
0.749999999
38
1E+30
0.749999999
$G$22
Salt Lake City, UT St. Louis
0
0.9
38.25
1E+30
0.9
$H$22
Salt Lake City, UT Baltimore
0
0.600000001
38.75
1E+30
0.600000001
$I$22
Salt Lake City, UT Tampa
0
0.899999996
38.9
1E+30
0.899999996
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$C$23
Shipped Tacoma
6800
41.15
6800
1600
6800
$D$23
Shipped San Diego
11600
40.15
11600
1600
8600
$E$23
Shipped Dallas
10800
40.85
10800
5120
8600
$F$23
Shipped Denver
10080
40.25
10080
5120
10080
$G$23
Shipped St. Louis
14400
40.35
14400
600
8600
$H$23
Shipped Baltimore
12000
41.15
12000
5120
1200
$I$23
Shipped Tampa
7200
41
7200
5120
1200
$J$19
Philadelphia, PA Produced
18000
-3.85
18000
1200
5120
$J$20
Atlanta, GA Produced
15000
-1.850000002
15000
8600
600
$J$21
St. Louis, MO Produced
19880
0
25000
1E+30
5120
$J$22
Salt Lake City, UT Produced
20000
-3.000000001
20000
8600
1600
Unique solution: the solution is optimal since reduced cost for all the unallocated cells is greater than zero.
 
“Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!”

What Students Are Saying About Us

.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐
"Honestly, I was afraid to send my paper to you, but splendidwritings.com proved they are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"

.......... Customer ID: 14***| Rating: ⭐⭐⭐⭐⭐
"The company has some nice prices and good content. I ordered a term paper here and got a very good one. I'll keep ordering from this website."

"Order a Custom Paper on Similar Assignment! No Plagiarism! Enjoy 20% Discount"

Related posts:

  1. 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 fill at least 80% of the orders they receive. a. Create a spreadsheet model for this problem and solve it. b. What is the optimal solution? c. Is the solution unique? d. If Sentry wants to increase their production capacity to meet more of the demand for their product, which plant should they use? Explain. e. If the cost of shipping from Phoenix to Tacoma increased to $1.98 per unit, would the solution change? Explain. f. Could the company make more money if they relaxed the restriction that each distributor must receive at least 80% of the predicted demand? Explain. g. How much extra should the company charge the distributor in Tacoma if this distributor insisted on receiving 8,500 units?
  2. LG owns five production plants where 90″ high definition televisions are produced. LG can sell up to 25,000 televisions per year at the price of $4000 per television. For each plant, the production capacity, the production cost per television, and the fixed cost of operating a plant for year are given below. Production Plant Cost per Plant Capacity Fixed Cost Television 1 10000 $9 million $1,600 2 8000 $5 million $2,000 3 9000 $3 million $2,300 4 7000 $4 million $2,100 5 6000 $1 million $2,400 a. Use Solver to determine how LG can maximize its yearly profit from television production. b. Use SolverTable to determine how the optimal plant locations vary as the number of televisions that can be sold varies from 20,000 to 30,000 (in 1000 unit increments).
  3. Demand for Deskpro PC at SuperPC is 1000 units per month, fixed shipping cost for each order placement is $4000. Each Deskpro costs SuperPC $500 to purchase from its supplier, and the holding cost is 20% of the unit cost. 1)How many computers should SuperPC order each time? 2)What is the cycle inventory? 3)What is the number of orders per year? 4)Annual ordering and holding cost? 5)Average flow time?
  4. Given the following inventory information: Demand 10+ n units per day Purchase cost = $15 per unit Ordering cost = $200 + $n per order Holding cost = $0.12 per unit per day Lead time 4 days Number of working days in a year 250 days mpe:55 245 p Demand = 10 + n = 10 + 45 = 55 units per day Ordering cost = $200 + $n = $200 + $45 = $245 per order Use your n (the summation of all digits of your ID number) to answer the following questions: Round your non-integer answers to four decimal places. Given the order quantity of 600 units, what is the total daily inventory cost, including the item’s purchase cost? A. B. What is the EOQ? C. At EOQ, what is the total annual inventory cost, including the item’s purchase cost? What is the reorder point? At EOQ, what is the length of order cycle ? D. E. https://d2vlcm61l7u1fs.cloudfront.net/media%2F6b8%2F6b8e37ee-e97c-4a10-a8fa-ac1cf0d6b604%2FphpE53cxC.png