Assignment #4: Case Problem "Stateline Shipping and Transport Company"

assignment_4_template_with_some_formulas.xlsx

Answer Report 1

Microsoft Excel 12.0 Answer Report
Worksheet: [10 Assigment 4 solution-1.xls]Sheet1
Report Created: 8/26/2011 3:32:06 PM
Target Cell (Min)
Cell	Name	Original Value	Final Value
$C$13	Cost = Whitewater	0	2822
Adjustable Cells
Cell	Name	Original Value	Final Value
$C$5	Kingsport Whitewater	0	35
$D$5	Kingsport Los Canos	0	0
$E$5	Kingsport Duras	0	0
$C$6	Danville Whitewater	0	0
$D$6	Danville Los Canos	0	0
$E$6	Danville Duras	0	26
$C$7	Macon Whitewater	0	0
$D$7	Macon Los Canos	0	0
$E$7	Macon Duras	0	42
$C$8	Selma Whitewater	0	1
$D$8	Selma Los Canos	0	52
$E$8	Selma Duras	0	0
$C$9	Columbus Whitewater	0	29
$D$9	Columbus Los Canos	0	0
$E$9	Columbus Duras	0	0
$C$10	Allentown Whitewater	0	0
$D$10	Allentown Los Canos	0	28
$E$10	Allentown Duras	0	10
Constraints
Cell	Name	Cell Value	Formula	Status	Slack
$C$12	Shipped Whitewater	65	$C$12<=$C$11	Binding	0
$D$12	Shipped Los Canos	80	$D$12<=$D$11	Binding	0
$E$12	Shipped Duras	78	$E$12<=$E$11	Not Binding	27
$G$5	Kingsport Shipped	35	$G$5=$F$5	Not Binding	0
$G$6	Danville Shipped	26	$G$6=$F$6	Not Binding	0
$G$7	Macon Shipped	42	$G$7=$F$7	Not Binding	0
$G$8	Selma Shipped	53	$G$8=$F$8	Not Binding	0
$G$9	Columbus Shipped	29	$G$9=$F$9	Not Binding	0
$G$10	Allentown Shipped	38	$G$10=$F$10	Not Binding	0

Stateline Shipping and Transport Company
A model for shipping the waste directly from the 6 plants to the 3 waste disposal sites	Shipping Costs ($/per barrel)
Plants	Waste Disposal Sites	Supply	Shipped	Plants	Waste Disposal Sites
A. Whitewater	B. Los Canos	C. Duras	A.Whitewater	B. Los Canos	C. Duras
1. Kingsport	35	1. Kingsport	12
2. Danville	2. Danville
3. Macon	3. Macon
4. Selma	4. Selma
5. Columbus	5. Columbus
6. Allentown	6. Allentown
Demand	65
Shipped
Cost =
Finish this	Minimize Z=$12X1A+
Finish this	Subject to:	Solution:	Finish this
X1A+X1B+X1C	= 35	X1A = ?
=
=
=
=
=
<= 65
<=
<=	Z =
Xij >= 0, i=1,2,3,4,5,6; j=A,B,C.

Stateline Shipping and Transport Company
A transshipment model in which each of the plants and dispposal sites can be used as intermediate points
Plants	Waste Disposal Sites	Supply	Shipped
1.Kingsport	2.Danville	3.Macon	4.Selma	5.Columbus	6.Allentown	A.Whitewater	B.Los Canos	C. Duras
Plants	1.Kingsport	35	0
2.Danville	26	0
3.Macon	42
4.Selma	53
5.Columbus	29
6.Allentown	38
Waste disposal Sites	A.Whitewater
B.Los Canos
C. Duras
Demand	65	80	105
Shipped	0
Total Cost =	$ - 0
Shipping Costs ($/per barrel)
Plants	Waste Disposal Sites
1. Kingsport	2. Danville	3. Macon	4. Selma	5. Columbus	6. Allentown	A.Whitewater	B.Los Canos	C.Duras
Plants	1. Kingsport	6	4	9	7	8	12	15	17
2. Danville	6	11	10	12	7	14	9	10
3. Macon	5	11	3	7	15	13	20	11
4. Selma	9	10	3	3	16	17	16	19
5. Columbus	7	12	7	3	14	7	14	12
6. Allentown	8	7	15	16	14	22	16	18
Waste disposal Sites	A.Whitewater	12	10
B.Los Canos	12	15
C.Duras	10	15
Constraint at plant 1:	(x12+x13+x14+x15+x16+x1A+x1B+x1C) - (x21+x31+x41+x51+x61) = 35
out	in
Constraint at West site A:	(x1A+x2A+x3A+x4A+x5A+x6A+xBA+xCA) - (xAB+xAC) <= 65
in	out
Xij >= 0, i=1,2,3,4,5,6, A,B,C; j=1,2,3,4,5,A,B,C.