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."