Question

Nortex, the natural gas processing company for whom you previously determined a transshipment plan, has decided to expand its operations into the northeastern corner of Texas, via its subsidiary company Nortex East. To accommodate this expansion, the company has decided to hire several regional managers. Each manager will be based in one of the 25 counties in which Nortex East will operate and will provide oversight for the operations in that county and all adjacent counties that are directly accessible via a major roadway. If two or more managers have jurisdiction over a county, they will share the responsibility.

Nortex East would also like to minimize the total amount it spends on the monthly base salaries of its managers. Base salaries are dependent on the county in which the manager is based, and are roughly related to cost of living and the total geographic area over which a manager would have jurisdiction. (Relocation subsidies, bonuses, and other costs are not included in the base salary.) In the case of shared jurisdiction as mentioned above, the overlapping managers will still receive the full monthly salary indicated.

A map of Nortex East’s business region and a table of counties, which includes information on the adjacent counties and associated monthly salaries, are provided on the next page. Thanks to your excellent work on their previous case, Nortex East has once again chosen to partner with you to conduct an analysis and to make a recommendation that will help it to allocate its regional manager positions such that each county has at least one regional manager assigned to it, while keeping the total cost of salaries to a minimum. In summary, you must develop an integer programming model, perform an appropriate analysis using Excel, recommend the counties in which Nortex East should place its area managers, and present a formal business report discussing your findings and conclusions.

NEEDS LINEAR PROGRAMMING EQUATION WITH CONSTRAINTS. EXCEL SPREADSHEET WITH SOLVER OUTPUT/CONSTRAINTS.

3 4 2 5 8 10 9 6 7 16 1719 3 K 20 14 2122 25 24 23 Adjacent 8 6 1, 2, 7, 14, 15 72, 6, 8, 9, 15, 16, 21, 22 $9,980 Hopkins Frank 97, 8, 10, 16, 17 10 4, 9, 11, 17, 18 5 17 9, 10, 16, 18, 19, 22, 23$9,500 18 10, 17, 19 20 12, 13, 19, 25 23 17, 19, 22, 24 Upshur 19 12, 17, 18, 20, 23, 24, 25 $9,200 Kaufman 27, 14, 15, 22 Van Zandt 22 7, 16, 17, 21, 23

Answer 1

Linear Programming model is following:

Let

Xi = 1, if a regional manager is placed in county i, otherwise Xi = 0

Min 4830X1+8950X2+5980X3+7200X4+5970X5+8860X6+9980X7+7440X8+6990X9+7460X10+5570X11+7580X12+5170X13+5370X14+7130X15+6900X16+9500X17+5530X18+9200X19+6460X20+7130X21+8660X22+6800X23+5390X24+4940X25

s.t.

X1+X2+X6>=1

X1+X2+X3+X6+X7+X8>=1

X2+X3+X4+X8>=1

X3+X4+X5+X10+X11>=1

X4+X5+X12+X13>=1

X1+X2+X6+X7+X14+X15>=1

X2+X6+X7+X8+X9+X15+X16+X21+X22>=1

X2+X3+X7+X8+X9>=1

X7+X8+X9+X10+X16+X17>=1

X4+X9+X10+X11+X17+X18>=1

X4+X10+X11+X12+X18>=1

X5+X11+X12+X13+X19+X20>=1

X5+X12+X13+X20>=1

X6+X14+X15+X21>=1

X6+X7+X14+X15+X21>=1

X7+X9+X16+X17+X22>=1

X9+X10+X16+X17+X18+X19+X22+X23>=1

X10+X11+X17+X18+X19>=1

X12+X17+X18+X19+X20+X23+X24+X25>=1

X12+X13+X19+X20+X25>=1

X7+X14+X15+X21+X22>=1

X7+X16+X17+X21+X22+X23=1

X17+X19+X22+X23+X24>=1

X19+X23+X24+X25>=1

X19+X20+X24+X25>=1

Xi binary

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

Create Spreadsheet model as follows:

fxSUMPRODUCT(B3:23,$B$31:$Z$31) AA3 X1 X2 X3 X4 X5X6 X7 X8 X9 X10 X11 X12 X13 X14 X15 X16 X17 X18 X19 X20 X21 X22 X23 X24 X25 3 Cost: 4830 18950 15980 7200 15970 18860 9980 7440 6990 74605570 7580 517015370 7130 6900 9500 15530 9200 6460 17130 18660 6800 15390 49400 5 County 1 1 6 County 2 1 7 County 3 8 County 4 9 County 5 10 County 6 1 11 County 7 12 County 8 13 County 9 14 County 10 15 County 11 16 County 12 17 County 13 18 County 14 19 County 15 20 County 16 21 County 17 22 County 18 23 County 19 24 County 20 25 County 21 26 County 22 27 County 23 28 County 24 29 County 25 30 31 Result: 32

FORMULAS:

AA3 =SUMPRODUCT(B3:Z3,$B$31:$Z$31) copy to AA5:AA29

Enter Solver parameters:

S Solver Parameters SAAS3 Set Objective: O Max ⓔMín O Value Of: To: By Changing Variable Cells: SBS31:SZS31 Subject to the Constraints: SAAS5:SAA$29 > = SACS5:SAC$29 Add SBS31:SZS31 binary Change Delete Reset All Make Unconstrained Variables Non-Negative Simplex LP Select a Solving Method: Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems Solve Close

Click Solve to generate the optimal solution:

fxSUMPRODUCT(B3:23,$B$31:$Z$31) AA3 X1 X2 X3 X4 X5X6 X7 X8 X9 X10 X11 X12 X13 X14 X15 X16 X17 X18 X19 X20 X21 X22 X23 X24 X25 3 Cost: 4830 18950 15980 7200 15970 18860 9980 7440 6990 74605570 7580 517015370 7130 6900 9500 15530 9200 6460 17130 18660 6800 15390 4940 37350 5 County1 11 6 County 2 1 7 County 3 8 County 4 9 County 5 10 County 6 1 11 County 7 12 Countyt8 13 County 9 14 County 10 15 County 11 16 County 12 17 County 13 18 County 14 19 County 15 20 County 16 21 County 17 22 County 18 23 County 19 24 County 20 25 County 21 26 County 22 27 County 23 28 County 24 29 County 25 30 31 Result 010 0 0 000 0 0 10 1 1 0 1 000 0 00 0 10 32

Optimal solution:

X2=1,

X11=1

X13=1

X14=1

X16=1

X24=1

Regional manger should be placed in counties: 2, 11, 13, 14, 16, and 24

Total cost = 37,350