Question

Nortex Chemicals, Inc. is involved in the natural gas processing industry in the Texas Panhandle. One of its primary operations involves purchasing large tanks, each roughly the size of a rail car, of particular byproducts from natural gas processing. It further purifies these byproducts into a usable form, and then resells the tanks to industrial customers. Recently, Nortex purchased several tanks of byproducts from natural gas companics. There are presently 17 tanks in Canadian, 15 tanks in Spearman, and 10 tanks in Vega. Each tank must be shipped by truck to one of its processing centers, in Dalhart, Pampa, and Canyon, for further refinement. The cost of shipment depends roughly on distance, but also takes into account the driver's salary (which is higher for routes that are difficult to navigate in a truck), the cost of fuel, and hazardous materials permit fees. The table below provides the costs of shipping one tank of byproducts from the suppliers to the processing centers Dalhart Pampa Canyon Canadian 261 $170 300 Spearman 227 247 $284 Vega S180 235 204 fter processing, the tanks of byproducts will be shipped to customers in Stratford, Stinnett, those mentioned above. The table below provides the costs of shipping one tank of byproducts ryton, Claude, Hereford, and Channing. The cost of shipment depends on metrics similar to from the processing centers to the customers. Dalhart Pampa Canyon Stratford Stinnett Perryton Claude Hereford Channing 89 94 $130 121 50 $ 245 $ 119 54 S 258 134 $177 $143 $249 s T2 168 142 $211 89 The demand for the customers is: Stratford, 8 tanks; Stinnct, 7; Perryton, 8; Claude, 9, Hereford, 6: and Channing, 7. Nortex has partnered with you to conduct an analysis and to make a recommendation that will help it to increuse profitability by optimizing the performance of its natural gus byproducts distribution network. In summary, you must develop a distribution ietwork and he corresponding integer programming model, perform an appropriate analysis using Excel, recommend a distribution system to be implemented by Nortex, and present a formal business report discussing your findings and conclusions.

Answer 1

Here we use Formula:

K3 =SUM(H3:J3) copy to K3:K5, K9:K14

H6 =SUM(H3:H5) copy to H6:J6

H15 =SUM(H9:H14) copy to H15:J!5

K18 =SUMPRODUCT(B3:D5,H3:J5)+SUMPRODUCT(B9:D14,H9:J14)

К 18 a fx SUMPRODUCT(B3:D5, H3:J5)+SUMPRODUCT(B9:D14,H9:314) 1 Parameter Tables Recommended distribution plan SSolver Parameters Dalhart Pampa Canyon Capacity Dalhart Pampa Canyon Row Total 390 250 43 341 16 3 Canadian 4 Spearman 5 Vega Canadian Spearman Vega Column Total 16 Set Objective: 361 14 14 0 Max Min Value Of: 0 20 16 By Changing Variable Cells: SHS3 SJS5,SHS9:SJS14 Subject to the Constraints SKS9:SKS14SES9:SES14 SKS3:SKS5 SES3:SES5 SH$15:SJS15 SHS6:SJS6 Dalhart Pampa Canyon Demand Dalhart Pampa Canyon Row Total 9 Stratford 10 Stinnett 11 Perryton 12 Claude 13 Hereford 14 Channing 15 16 17 18 19 Stratford Stinnett Perryton Claude Hereford Channing Column Total 325 206 166 175 118 164 148 174 107 Delete 317 Reset All 16 Make Unconstrained Variables Non-Negative Total Cost - |_16,6 Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems 21 Solve Close 25