Can someone help me with the Excel Solver part of this question? I have the LP below but having trouble setting up the Excel Solver spreadsheet witht he formulas for the optimal solutins with the constraints and everything.

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. Presently, Nortex is in possession of 14 tanks in Canadian, 13 tanks in Spearman, and 11 tanks in Vega. Each tank must be shipped by truck to one of its processing centers, which are located 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 following table provides the total costs (in dollars) of shipping one tank from the suppliers to the processing centers. While the three processing centers are capable of processing any number of tanks, the number transported to Pampa must equal the number transported to Canyon, due to an existing agreement between Nortex and its transportation provider. After processing, the tanks will be shipped to customers in Stratford, Stinnett, Perryton, Claude, Hereford, and Channing. The cost of shipment depends on metrics similar to those mentioned above. The following table provides the total costs (in dollars) of shipping one tank from the processing centers to the customers. Note that certain combinations of processing facilities and customers are not possible due to factors such as regional non-compete clauses; these are indicated by dashes. The current customer demands are: Stratford, 7 tanks; Stinnett, 9; Perryton, 5; Claude, 7; Hereford, 5; and Channing, 8 . Additionally, due to a competitor's bankruptcy, Nortex will be acquiring 6 tanks of processed byproducts that are currently located at a yard in Morse. Because these tanks are already processed, a third-party company can ship these tanks directly to customers in Stratford (at a cost of $306 per tank), Stinnett (at a cost of $262 per tank), and Perryton (at a cost of $222 per tank) without needing to go through one of the processing centers. Nortex has partnered with you to conduct an analysis and to make a recommendation that will help it to increase profitability by optimizing the performance of its natural gas byproducts distribution network. In summary, you must develop a distribution network and the 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. C1= number of tanks to ship from Canadian to Dalhart C2= number of tanks to ship from Canadian to Pampa C3=number of tanks to ship from Canadian to Canyon S1=number of tanks to ship from Spearman to Dalhart S2=number of tanks to ship from Spearman to Pampa S3=number of tanks to ship from Spearman to Canyon V1=number of tanks to ship from Vega to Dalhart V2=number of tanks to ship from Vega to Pampa V3 number of tanks to ship from Vega to Canyon D1=number of tanks to ship from Dalhart to Stratford D2=number of tanks to ship from Dalhart to Stinnett D3=number of tanks to ship from Dalhart to Perryton D5=number of tanks to ship from Dalhart to Hereford P1=number of tanks to ship from Pampa to Stratford P3=number of tanks to ship from Pampa to Perryton P4=number of tanks to ship from Pampa to Claude P6=number of tanks to ship from Pampa to Channing Cn2= number of tanks to ship from Canyon to Stinnett Cn4=number of tanks to ship from Canyon to Claude Cn5=number of tanks to ship from Canyon to Hereford Cn6=number of tanks to ship from Canyon to Channing G1=number of tanks to ship from Gruver to Stratford G2=number of tanks to ship from Gruver to Stinnett G3=number of tanks to ship from Gruver to Perryton Minimize 294C1+209C2+339C3+271S1+314S2+391S3+262V1+265V2+235V3+77D1+103D2+121D3+157D5+328P1+183P3+194P4+304P6+149Cn2+105Cn4+75C55+140Cn6+306G1+262G2+222G3 (total shipping cost) Constraints: C1+C2+C3=0 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. Presently, Nortex is in possession of 14 tanks in Canadian, 13 tanks in Spearman, and 11 tanks in Vega. Each tank must be shipped by truck to one of its processing centers, which are located 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 following table provides the total costs (in dollars) of shipping one tank from the suppliers to the processing centers. While the three processing centers are capable of processing any number of tanks, the number transported to Pampa must equal the number transported to Canyon, due to an existing agreement between Nortex and its transportation provider. After processing, the tanks will be shipped to customers in Stratford, Stinnett, Perryton, Claude, Hereford, and Channing. The cost of shipment depends on metrics similar to those mentioned above. The following table provides the total costs (in dollars) of shipping one tank from the processing centers to the customers. Note that certain combinations of processing facilities and customers are not possible due to factors such as regional non-compete clauses; these are indicated by dashes. The current customer demands are: Stratford, 7 tanks; Stinnett, 9; Perryton, 5; Claude, 7; Hereford, 5; and Channing, 8 . Additionally, due to a competitor's bankruptcy, Nortex will be acquiring 6 tanks of processed byproducts that are currently located at a yard in Morse. Because these tanks are already processed, a third-party company can ship these tanks directly to customers in Stratford (at a cost of $306 per tank), Stinnett (at a cost of $262 per tank), and Perryton (at a cost of $222 per tank) without needing to go through one of the processing centers. Nortex has partnered with you to conduct an analysis and to make a recommendation that will help it to increase profitability by optimizing the performance of its natural gas byproducts distribution network. In summary, you must develop a distribution network and the 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. C1= number of tanks to ship from Canadian to Dalhart C2= number of tanks to ship from Canadian to Pampa C3=number of tanks to ship from Canadian to Canyon S1=number of tanks to ship from Spearman to Dalhart S2=number of tanks to ship from Spearman to Pampa S3=number of tanks to ship from Spearman to Canyon V1=number of tanks to ship from Vega to Dalhart V2=number of tanks to ship from Vega to Pampa V3 number of tanks to ship from Vega to Canyon D1=number of tanks to ship from Dalhart to Stratford D2=number of tanks to ship from Dalhart to Stinnett D3=number of tanks to ship from Dalhart to Perryton D5=number of tanks to ship from Dalhart to Hereford P1=number of tanks to ship from Pampa to Stratford P3=number of tanks to ship from Pampa to Perryton P4=number of tanks to ship from Pampa to Claude P6=number of tanks to ship from Pampa to Channing Cn2= number of tanks to ship from Canyon to Stinnett Cn4=number of tanks to ship from Canyon to Claude Cn5=number of tanks to ship from Canyon to Hereford Cn6=number of tanks to ship from Canyon to Channing G1=number of tanks to ship from Gruver to Stratford G2=number of tanks to ship from Gruver to Stinnett G3=number of tanks to ship from Gruver to Perryton Minimize 294C1+209C2+339C3+271S1+314S2+391S3+262V1+265V2+235V3+77D1+103D2+121D3+157D5+328P1+183P3+194P4+304P6+149Cn2+105Cn4+75C55+140Cn6+306G1+262G2+222G3 (total shipping cost) Constraints: C1+C2+C3=0

Question

Can someone help me with the Excel Solver part of this question? I have the LP below but having trouble setting up the Excel Solver spreadsheet witht he formulas for the optimal solutins with the constraints and everything.

image text in transcribed

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. Presently, Nortex is in possession of 14 tanks in Canadian, 13 tanks in Spearman, and 11 tanks in Vega. Each tank must be shipped by truck to one of its processing centers, which are located 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 following table provides the total costs (in dollars) of shipping one tank from the suppliers to the processing centers. While the three processing centers are capable of processing any number of tanks, the number transported to Pampa must equal the number transported to Canyon, due to an existing agreement between Nortex and its transportation provider. After processing, the tanks will be shipped to customers in Stratford, Stinnett, Perryton, Claude, Hereford, and Channing. The cost of shipment depends on metrics similar to those mentioned above. The following table provides the total costs (in dollars) of shipping one tank from the processing centers to the customers. Note that certain combinations of processing facilities and customers are not possible due to factors such as regional non-compete clauses; these are indicated by dashes. The current customer demands are: Stratford, 7 tanks; Stinnett, 9; Perryton, 5; Claude, 7; Hereford, 5; and Channing, 8 . Additionally, due to a competitor's bankruptcy, Nortex will be acquiring 6 tanks of processed byproducts that are currently located at a yard in Morse. Because these tanks are already processed, a third-party company can ship these tanks directly to customers in Stratford (at a cost of $306 per tank), Stinnett (at a cost of $262 per tank), and Perryton (at a cost of $222 per tank) without needing to go through one of the processing centers. Nortex has partnered with you to conduct an analysis and to make a recommendation that will help it to increase profitability by optimizing the performance of its natural gas byproducts distribution network. In summary, you must develop a distribution network and the 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. C1= number of tanks to ship from Canadian to Dalhart C2= number of tanks to ship from Canadian to Pampa C3=number of tanks to ship from Canadian to Canyon S1=number of tanks to ship from Spearman to Dalhart S2=number of tanks to ship from Spearman to Pampa S3=number of tanks to ship from Spearman to Canyon V1=number of tanks to ship from Vega to Dalhart V2=number of tanks to ship from Vega to Pampa V3 number of tanks to ship from Vega to Canyon D1=number of tanks to ship from Dalhart to Stratford D2=number of tanks to ship from Dalhart to Stinnett D3=number of tanks to ship from Dalhart to Perryton D5=number of tanks to ship from Dalhart to Hereford P1=number of tanks to ship from Pampa to Stratford P3=number of tanks to ship from Pampa to Perryton P4=number of tanks to ship from Pampa to Claude P6=number of tanks to ship from Pampa to Channing Cn2= number of tanks to ship from Canyon to Stinnett Cn4=number of tanks to ship from Canyon to Claude Cn5=number of tanks to ship from Canyon to Hereford Cn6=number of tanks to ship from Canyon to Channing G1=number of tanks to ship from Gruver to Stratford G2=number of tanks to ship from Gruver to Stinnett G3=number of tanks to ship from Gruver to Perryton Minimize 294C1+209C2+339C3+271S1+314S2+391S3+262V1+265V2+235V3+77D1+103D2+121D3+157D5+328P1+183P3+194P4+304P6+149Cn2+105Cn4+75C55+140Cn6+306G1+262G2+222G3 (total shipping cost) Constraints: C1+C2+C3=0 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. Presently, Nortex is in possession of 14 tanks in Canadian, 13 tanks in Spearman, and 11 tanks in Vega. Each tank must be shipped by truck to one of its processing centers, which are located 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 following table provides the total costs (in dollars) of shipping one tank from the suppliers to the processing centers. While the three processing centers are capable of processing any number of tanks, the number transported to Pampa must equal the number transported to Canyon, due to an existing agreement between Nortex and its transportation provider. After processing, the tanks will be shipped to customers in Stratford, Stinnett, Perryton, Claude, Hereford, and Channing. The cost of shipment depends on metrics similar to those mentioned above. The following table provides the total costs (in dollars) of shipping one tank from the processing centers to the customers. Note that certain combinations of processing facilities and customers are not possible due to factors such as regional non-compete clauses; these are indicated by dashes. The current customer demands are: Stratford, 7 tanks; Stinnett, 9; Perryton, 5; Claude, 7; Hereford, 5; and Channing, 8 . Additionally, due to a competitor's bankruptcy, Nortex will be acquiring 6 tanks of processed byproducts that are currently located at a yard in Morse. Because these tanks are already processed, a third-party company can ship these tanks directly to customers in Stratford (at a cost of $306 per tank), Stinnett (at a cost of $262 per tank), and Perryton (at a cost of $222 per tank) without needing to go through one of the processing centers. Nortex has partnered with you to conduct an analysis and to make a recommendation that will help it to increase profitability by optimizing the performance of its natural gas byproducts distribution network. In summary, you must develop a distribution network and the 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. C1= number of tanks to ship from Canadian to Dalhart C2= number of tanks to ship from Canadian to Pampa C3=number of tanks to ship from Canadian to Canyon S1=number of tanks to ship from Spearman to Dalhart S2=number of tanks to ship from Spearman to Pampa S3=number of tanks to ship from Spearman to Canyon V1=number of tanks to ship from Vega to Dalhart V2=number of tanks to ship from Vega to Pampa V3 number of tanks to ship from Vega to Canyon D1=number of tanks to ship from Dalhart to Stratford D2=number of tanks to ship from Dalhart to Stinnett D3=number of tanks to ship from Dalhart to Perryton D5=number of tanks to ship from Dalhart to Hereford P1=number of tanks to ship from Pampa to Stratford P3=number of tanks to ship from Pampa to Perryton P4=number of tanks to ship from Pampa to Claude P6=number of tanks to ship from Pampa to Channing Cn2= number of tanks to ship from Canyon to Stinnett Cn4=number of tanks to ship from Canyon to Claude Cn5=number of tanks to ship from Canyon to Hereford Cn6=number of tanks to ship from Canyon to Channing G1=number of tanks to ship from Gruver to Stratford G2=number of tanks to ship from Gruver to Stinnett G3=number of tanks to ship from Gruver to Perryton Minimize 294C1+209C2+339C3+271S1+314S2+391S3+262V1+265V2+235V3+77D1+103D2+121D3+157D5+328P1+183P3+194P4+304P6+149Cn2+105Cn4+75C55+140Cn6+306G1+262G2+222G3 (total shipping cost) Constraints: C1+C2+C3=0