Question

PLEASE ANSWER 5-9 USING THE GIVEN TABLE AND THE ABOVE QUESTION

5-8. Three refineries with daily capacities of 6, 5, and 8 million gallons, respectively, supply three distribution areas with daily demands of 4, 8, and 7 million gallons, respectively. Gasoline is transported to the three distribution areas through a network of pipelines. The transportation cost is 10 cents per 1000 gallons per pipeline mile. Table 5.26 gives the mileage between the refineries and the distribution areas. Refinery 1 is not connected to distribution area 3. (a) Construct the associated transportation model. (b) Determine the optimum shipping schedule in the network. *5-9. In Problem 5-8, suppose that the capacity of refinery 3 is 6 million gallons only and that distribution area 1 must receive all its demand. Additionally, any shortages at areas 2 and 3 will incur a penalty of 5 cents per gallon. (a) Formulate the problem as a transportation model. (b) Determine the optimum shipping schedule. 5-10. In Problem 5-8, suppose that the daily demand at area 3 drops to 4 million gallons. Surplus production at refineries 1 and 2 is diverted to other distribution areas by truck. The transportation cost per 100 gallons is $1.50 from refinery 1 and $2.20 from refinery 2. Refinery 3 can divert its surplus production to other chemical processes within the plant. (a) Formulate the problem as a transportation model. (b) Determine the optimum shipping schedule. TABLE 5.26 Mileage Chart for Problem 5-8 Distribution area 2 3 Refinery 1 Refinery2 Refinery 3 120 300 200 180 100 250 80 120

Answer 1

5-8.

(a)

Transportation cost from refinery to distribution area = Distance * (0.10 / 1000)*10^6 per million gallons

= 100 * distance per million gallons

Associated transportation model is following:

Transportation cost per million gallons	Distribution area 1	Distribution area 2	Distribution area 3	Capacity (million gallons)
Refinery 1	12,000	18,000	100000*	6
Refinery 2	30,000	10,000	8,000	5
Refinery 3	20,000	25,000	12,000	8
Demand (million gallons)	4	8	7

* Hypothetically high cost (100000) is assigned to cell 13 to prohibit allocation to this cell , as Refinery 1 is not connected to distribution area 3

-----------------------------------------------------

(b)

Set up spreadsheet model as follows:

C D E F 2 3 18,000 1,00,000 10,000 8,000 25,000 12,000 8 7 Capacity 6 5 8 A B 1 Transportation Table 2 1 3 Refinery 1 12,000 4 Refinery2 30,000 5 Refinery 3 20,000 6 Requirements 4 7 8 Optimal solution 9 1 10 Refinery 1 11 Refinery2 12 Refinery 3 13 Received 0 2 3 Sent 0 0 0 0 0 14 15 Total Cost 0 16 17

EXCEL FORMULA:

A B D E 1 Transportation Table 2 1 Capacity 6 2 18000 10000 25000 3 100000 8000 12000 5 8 3 Refinery 1 12000 4 Refinery2 30000 5 Refinery 3 20000 6 Requirements 4 7 8 Optimal solution 8 9 1 2 3 10 Refinery 1 11 Refinery2 12 Refinery 3 13 Received Sent =SUM(B10:010) =SUM(B11:011) |=SUM(B12:012) =SUM(B10:B12) =SUM(C10:012) =SUM(D10:012) 14 15 Total Cost =SUMPRODUCT(B3:05,B10:012) 16 17

Set up Solver Parameters as follows:

Solver Parameters X Set Objective: $B$15 1 To: Max Min O 0 Value of: By Changing Variable Cells: $B$10:$D$12 1 Subject to the Constraints: $B$13:$D$13 = $B$6:$D$6 $E$10:$E$12 = $E$3:$E$5 A Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Click Solve .

After that, values appear automatically in yellow colored cells

с D E F 2 3 Capacity 6 5 18000 10000 25000 8 100000 8000 12000 7 8 А B 1 Transportation Table 2 1 3 Refinery 1 12000 4 Refinery 2 30000 5 Refinery 3 20000 6 Requirements 4 7 8 Optimal solution 9 1 10 Refinery 1 4 11 Refinery2 0 12 Refinery 3 0 13 Received 4 14 15 Total Cost 243000 2 3 Sent 6 0 0 5 2 5 1 8 7 8 7 16

--------------------------------------------------------------

5-9.

(a)

Setup Transportation model as follows:

B с D E F 1 2 18000 10000 25000 50000 8 3 100000 8000 12000 50000 7 Capacity 6 5 6 2 A 1 Transportation Table 2 3 Refinery 1 12000 4 Refinery 2 30000 5 Refinery 3 20000 6 Shortage 100000 7 Requirements 4 8 9 Optimal shipping schedule 10 1 11 Refinery 1 12 Refinery2 13 Refinery 3 14 Shortage 15 Received 0 16 17 Total Cost 0 3 N Sent 0 0 0 0 0 0 18

* Hypothetically high cost (100000) has been assigned to cell 41 to prohibit allocation to this cell , because no shortage is allowed for area 1, as it must receive all its demand

EXCEL FORMULA:

с D E 2 Capacity 6 5 2 18000 10000 25000 =0.05*10^6 8 3 100000 8000 12000 =0.05*10^6 7 6 2 1 Transportation Table 1 3 Refinery 1 12000 4 Refinery 2 30000 5 Refinery 3 20000 6 Shortage 100000 7 Requirements 4 B 9 Optimal shipping schedule 10 1 11 Refinery 1 12 Refinery2 13 Refinery 3 14 Shortage 15 Received =SUM(B11:B14) 2 3 Sent =SUM(B11:011) -SUM(B12:012) =SUM(B13:013) =SUM(B14:014) =SUM(C11:C14) -SUM(D11:014) 16 17 Total Cost =SUMPRODUCT(B3:06,B11:014) 1B 19 20

Solver Parameters:

Solver Parameters Х Set Objective: $B$17 To: Max Min Value of: 0 By Changing Variable Cells: $B$11:$D$14 Subject to the Constraints: $B$15:$D$15 = $B$7:$D$7 $E$11:$E$14 = $E$3:$E$6 > Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

-------

(b)

Click Solve to generate the solution:

A B с D E F G H K L M N O 3 Capacity 6 Solver Results 1 Transportation Table 2 1 3 Refinery 1 12000 4 Refinery 2 30000 5 Refinery 3 20000 6 Shortage 100000 7 Requirements 4 2 18000 10000 25000 50000 5 Solver found a solution. All Constraints and optimality conditions are satisfied. 100000 8000 12000 50000 7 6 Reports Answer Sensitivity Limits 2 O Keep Solver Solution 8 O Restore Original Values 8 Return to Solver Parameters Dialog Outline Reports 2 3 Sent 2 0 6 9 Optimal shipping schedule 10 1 11 Refinery 1 4 12 Refinery2 0 13 Refinery 3 0 14 Shortage 0 15 Received 4 4 5 OK Cancel Save Scenario... 1 6 0 6 2 2 Solver found a solution. All Constraints and optimality conditions are satisfied. 0 7 8 When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. 16 17 Total Cost 304000 18 19 20

Click OK

.

Total cost = $ 304,000