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 Fr
0 0
Add a comment Improve this question Transcribed image text
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

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 Co

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

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

Add a comment
Know the answer?
Add Answer to:
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 com...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • After discussing the issue with you and others in the Finance department, the manager decided to...

    After discussing the issue with you and others in the Finance department, the manager decided to change the payment terms for Accounts Receivable. The payment time remained at 14 days, but the penalty for late payments was increased. After about 3 months under the new payment terms, you asked the manager to collect a second stratified random sample. The data is presented in the second column of the worksheet (see the tab titled, “Data – Case #2) under the Heading...

  • The manager asks you to Set up a single sample inspection plan. She wants the Probability...

    The manager asks you to Set up a single sample inspection plan. She wants the Probability of Acceptance to be 95% for lots with a reject rate of 0.05 and the Probability of Acceptance to be 10% for lots with a fraction defective of 0.15. (Please be detailed on how to come up with the plan- the process- I'm puzzled) Day Sample (n) Number (np) Proportion (p) 1 500 12 0.024 2 500 15 0.030 3 500 19 0.038 4...

  • For the data listed(assume sample). a.) Find the following: Mean Mode Median Midrange Range Quartiles Variance...

    For the data listed(assume sample). a.) Find the following: Mean Mode Median Midrange Range Quartiles Variance Standard Deviation BoxPlot b.) Create a frequency distribution When creating classes use the formula from the Notes on how classes to create. 17 23 14 16 12 26 20 22 14 15 22 18 18 21 21 19 15 21 18 17 15 25 14 30 16 10 20 12 16 17.44 16 14 15 20 20 16 17 16 15 15 19 48...

  • A doctor discovers a particular disease and determines the life expectancy is not good. He gathers...

    A doctor discovers a particular disease and determines the life expectancy is not good. He gathers the data for his patents and posts it in the table below. The claims that the average life expectancy once the disease presents itself is less than 15 days. Check his claim and make an appropriate report about your findings Patient Days till passing Days -Patient # 1-24 2 -23 3 -13 4 -20 5 -10 6 -18 7 -13 8 -14 9 -24...

  • A soft drink manufacturer uses fire agents to handle premium distribution for is various products. The marketing director desired to study the timeliness with which the premiums are distributed. Twent...

    A soft drink manufacturer uses fire agents to handle premium distribution for is various products. The marketing director desired to study the timeliness with which the premiums are distributed. Twenty transactions for each agent were selected at random and the time lapse (in days) for handling each transaction was determined. The results follow: Agent 1 Agent 2 Agent 3 Agent 4 Agent 5 24 18 10 15 33 24 20 11 13 22 29 20 8 18 28 20 24...

  • Game   Point_Differential   Assists   Rebounds   Turnovers   Personal_Fouls 1   15   15   38   11   9 2   36   20   43 &

    Game   Point_Differential   Assists   Rebounds   Turnovers   Personal_Fouls 1   15   15   38   11   9 2   36   20   43   8   13 3   16   21   29   7   13 4   45   22   46   11   11 5   12   11   40   7   22 6   -10   10   31   13   26 7   11   19   45   11   7 8   12   16   32   16   14 9   3   16   27   18   15 10   19   9   34   17   17 11   40   16   41   9   17 12   44   12   29   9   22 13   16  ...

  • 1 A measurement systems experiment involving 20 parts, three operators, and two measurements per ...

    1 A measurement systems experiment involving 20 parts, three operators, and two measurements per part is shown in Table 8E. 12. (a) Estimate the repeatability and reproducibility of the gauge (b) What is the estimate of total gauge variability? (c) If the product specifications are at LSL 6 and USIL - 60, what ca you say about gauge capa bility? TABLE 8E. 12 Measurement Data for Exercise 8.34 Operator 1 Operator 2 Operator 3 Measurements Measurements Measurements Part Number 1...

  • 1. You are the new data scientist for Fitbit. When viewing the Google Analytics account associate...

    1. You are the new data scientist for Fitbit. When viewing the Google Analytics account associated with the Fitbit website., you notice that the Average Time on Site is 82 seconds. - Describe 4 different ways you can provide context for this metric. - For the sub-activity, refer to the attachment below entitled, "Image_Module_11.jpg" (the image is also embedded within the text below). Imagine that you have created an email campaign designed to drive users to your site. The email...

  • s. QUESTION 2 Use minitab Suppose a sample of 20 students were given a diagnostic test...

    s. QUESTION 2 Use minitab Suppose a sample of 20 students were given a diagnostic test before studying a particular module and then again after completing the module. We want to find out if, in general, our teaching leads to improvements in students' knowledge/skills (i.e. test scores). We can use the results from our sample of students to draw conclusions about the impact of this module in general. 92.MPJ 1) check assumption (fort show your correct 3) find the t-value...

  • Assembly Language Programming Assignment program must be in: MASM assembly language / x86 architecture / irvine...

    Assembly Language Programming Assignment program must be in: MASM assembly language / x86 architecture / irvine library procedures Objectives: 1. using register indirect addressing 2. passing parameters 3. generating “random” numbers 4. working with arrays Description: Write and test a MASM program to perform the following tasks: 1. Introduce the program. 2. Generate ARRAYSIZE random integers in the range [LO = 10 .. HI = 29], storing them in consecutive elements of an array. ARRAYSIZE should be set to 200....

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT