Question

Marc Hernandez’s construction firm currently has three projects in progress. Each requires a specific supply of gravel. There are three gravel pits available to provide for Hernandez’s needs, but shipping costs differ from location to location.

To From Job 1 Job 2 Job 3 Tonnage Allowance

Central pit $9   $ 8    $ 7             3,000

Rock pit    $7   $11    $ 6            4,000

Acme pit   $4   $ 3     $12           6,000

Job requirements (tons) (Job1) 2,500 (Job 2) 3,750 (Job 3) 4,850

A. Determine Hernandez’s optimal shipping quantities so as to minimize total transportation costs.

B. It is the case that Rock Pit and Central Pit can send gravel by rail to Acme for $1 per ton. Once the gravel is relocated, it can be trucked to the jobs. Reformulate this problem to determine how shipping by rail could reduce the transportation costs for the gravel.

Problem 5-13
Shipments:	To					Flow balance equations
From	Job 1	Job 2	Job 3	Flow out		Location	Flow in	Flow out	Net flow	Sign	RHS
Central				0.0		Central	0	0	0	>=	-3000
Rock				0.0		Rock	0	0	0	>=	-4000
Acme				0.0		Acme	0	0	0	>=	-6000
Flow in	0.0	0.0	0.0			Job 1	0	0.0	0	=	2500
						Job 2	0	0.0	0	=	3750
Unit costs:	To					Job 3	0	0.0	0	=	4850
From	Job 1	Job 2	Job 3
Central	$9	$8	$7
Rock	$7	$11	$6
Acme	$4	$3	$12
Total cost =	$0		<--- Minimize total transportation costs. Formula = SUMPRODUCT(B5:D7,B12:D14)
Note:
Once all values are entered in the appropriate shaded areas, go to the DATA tab on the Excel sheet ribbon, click on the Data Analysis Group, and then choose Solver. Click SOLVE to run Excel's Solver add-in to obtain the optimized solution. Note that if Solver is not on the DATA tab, refer to the Help file (Solver) for instructions or pages 569–571 of Balakrishnan (2013) Managerial Decision Modeling With Spreadsheets. For more information on entering information in Solver, refer to pages 44–49 of Balakrishnan (2013). To learn more about how to set up and solve linear programming (LP) problems, refer to pages 40–51 of Balakrishnan (2013). For the changing variable cells (yellow shaded), the initial entries in the cells can be blank or any value of your choice based on the given constraints.

Problem 5-13 (Transshipment)
Shipments:	To						Flow balance equations
From	Job 1	Job 2	Job 3	Acme	Flow out		Location	Flow in	Flow out	Net flow	Sign	RHS
Central					0.0		Central	0	0	0	>=	-3000
Rock					0.0		Rock	0	0	0	>=	-4000
Acme					0.0		Acme	0.0	0	0.0	>=	-6000
Flow in	0.0	0.0	0.0	0.0			Job 1	0	0	0	=	2500
							Job 2	0	0	0	=	3750
Unit costs:	To						Job 3	0	0	0	=	4850
From	Job 1	Job 2	Job 3	Acme
Central	$9	$8	$7	$1
Rock	$7	$11	$6	$1
Acme	$4	$3	$12	$0
Total cost =	$0
Note:
Once all values are entered in the appropriate shaded areas, go to the DATA tab on the Excel sheet ribbon, click on the Data Analysis Group, and then choose Solver. Click SOLVE to run Excel's Solver add-in to obtain the optimized solution. Note that if Solver is not on the DATA tab, refer to the Help file (Solver) for instructions or pages 569–571 of Balakrishnan (2013) Managerial Decision Modeling With Spreadsheets. For more information on entering information in Solver, refer to pages 44–49 of Balakrishnan (2013). To learn more about how to set up and solve linear programming (LP) problems, refer to pages 40–51 of Balakrishnan (2013). For the changing variable cells (yellow shaded), the initial entries in the cells can be blank or any value of your choice based on the given constraints.

Answer 1

1.

Xij - the amount of gravel that is to be shipped from each gravel site i to a project j

Cij - the cost of shipment for transport from each gravel site i to a project j

Decision variables

To decide the amount of gravel that has to shipped from each gravel site i to a project j - Xij

Objective

To minimize transportation costs form gravesite to project

=Xij * Cij

Constraints

• Outflow from gravel site is less than or equal to capacity. i.e. total amount shipped form a gravel site should be less than or equal to its capacity

Flow out – flow in ?capacity

Or we can reverse the sign and explain them as

(flow in - Flow out) ? -(capacity)

• demand for each project should be met
• the amount shipped is non-negative(Xij?0)

2.

Here we have to decide the amount of gravel

• shipped from each gravel site i to a project j
• Shipped from each gravel site central and rock to acme. Let this amount be Y11 and Y12

Y11- amount shipped from each gravel site central to acme.

y12 - amount shipped from each gravel site rock to acme.

Decision variables

Xij , Y11 and Y12

Objective

To minimize

(Transportation costs form gravesite to project + transportation costs from Central and rock to acme)

=SXij * Cij   + S(Y11 +Y12)*1

Constraints

• Outflow from gravel site is less than or equal to capacity. i.e. total amount shipped form a gravel site should be less than or equal to its capacity
• demand for each project should be met
• the amount shipped is non-negative(Xij?0)

Additional constraint

• Acme flow out ?acme flow in +acme capacity

Note:

Acme now has

1. inflow from other gravel sites

2. acme’s own capacity)