Question

JUST NEED HELP WITH PART 2 (EXCEL SOLVER)

Clearlake Marketing Group (CMG) is a full-service marketing research company. CMG is being hired to interview registered voters in a city to gain insights into their opinions about certain political issues. Each voter is to be interviewed in person. The costs of interviewing different types of voters vary due to the differences in proportion throughout the population. Based on previous studies, estimates of the interview costs for different types of voters are given in the table below.

	Cost Per Interview
Gender	Democrat	Republican	Independent
Male	$ 10	$ 9	$ 12
Female	$ 11.5	$ 11	$ 14

The contract called for CMG to conduct interviews under the requirements (as detailed in the constraints section) given below. Please help CMG to develop an interview plan.

Part 1. Linear Programming (LP) Model

Formulate a linear programming (LP) model for this problem. Please follow the steps below.

1. Define the decision variables.

X₁= Male Democratic voter

X₂=Male Republican voter

X₃= Male Independent voter

X₄= Female Democratic voter

X₅₌ Female Republican voter

X₆₌ Female Independent voter

2. Write the objective (mathematically).

Min Z = 10x₁+9x₂+12x₃+ 11.5x₄+11x₅+14x₆

3. Write the constraints (mathematically), which are the contract requirements.

1. There must be at least 4,500 total interviews.

10x₁+9x₂+12x₃+11.5x₄+11x₅+14x₆ ≥ 4,500

2. At least 1,500 Independent voters must be interviewed.

12x₃+14x₆ ≥ 1,500

3. At least 1,800 males must be interviewed.

x₁+x₂+x₃ ≥ 1,800

4. At least 2,000 females must be interviewed.

x₄+x₅+x₆ ≥ 2,000

5. At most 30% of those interviewed may be Democrats.

x₁+x₄ ≤ .3(x₁+x₂+x₃+x₄+₅+x₆)

6. At most 40% of those interviewed may be Republicans.

x₂+x₅ ≤ .40 (x₁+x₂+x₃+x₄+₅+x₆)

7. At most 20% of those interviewed may be Republican males.

x₁ ≤ .20(x₁+x₂+x₃+x₄+₅+x₆)

8. The Independent female voters must be represented by at least 10% of the total interviews.

x₆ ≥ .10 (x₁+x₂+x₃+x₄+₅+x₆)

9. Constraints on decision variables.

(x₁+x₂+x₃+x₄+₅+x₆) ≥ 0 Nonnegativity!

Part 2. Spreadsheet Model and Solver Model

Construct a spreadsheet (Excel) model and solve the problem by Excel Solver. Please generate the Answer Report and Sensitivity Report from Excel Solver (You don’t need to submit the Answer Report and Sensitivity Report). Answer the following questions based on the Answer Report and Sensitivity Report from Excel Solver.

1. What is the optimal interview plan?

2. How much is the optimal total interview cost?

3. Related to contract requirement #2 given in Part 1: If the minimum number of Independents interviewed increases by 1 (i.e., changes from 1,500 to 1,501) while the other parameters remain constant, how much will the total interview cost change? Please indicate whether it is an increase, a decrease, or no change.

4. Related to contract requirement #3 given in Part 1: If the minimum number of males interviewed increases by 1 (i.e., changes from 1,800 to 1,801) while the other parameters remain constant, how much will the total interview cost change? Please indicate whether it is an increase, a decrease, or no change.

Answer 1

Part 1

There are some incorrect constraint formulation. Please find the correct solution to part 1

Formulate a linear programming (LP) model for this problem. Please follow the steps below.

1. Define the decision variables.

X₁= Male Democratic voter

X₂=Male Republican voter

X₃= Male Independent voter

X₄= Female Democratic voter

X₅₌ Female Republican voter

X₆₌ Female Independent voter

2. Write the objective (mathematically).

Min Z = 10x₁+9x₂+12x₃+ 11.5x₄+11x₅+14x₆

3. Write the constraints (mathematically), which are the contract requirements.

1. There must be at least 4,500 total interviews.

x₁+x₂+x₃+x₄+x₅+x₆ ≥ 4,500 [Correction is done here as interview cost was included incorrectly whereas only number should be included]

2. At least 1,500 Independent voters must be interviewed.

x₃+x₆ ≥ 1,500 [Correction is done here[Correction is done here as interview cost was included incorrectly whereas only number should be included]

3. At least 1,800 males must be interviewed.

x₁+x₂+x₃ ≥ 1,800

4. At least 2,000 females must be interviewed.

x₄+x₅+x₆ ≥ 2,000

5. At most 30% of those interviewed may be Democrats.

x₁+x₄ ≤ .3(x₁+x₂+x₃+x₄+₅+x₆)

6. At most 40% of those interviewed may be Republicans.

x₂+x₅ ≤ .40 (x₁+x₂+x₃+x₄+₅+x₆)

7. At most 20% of those interviewed may be Republican males.

x₁ ≤ .20(x₁+x₂+x₃+x₄+₅+x₆)

8. The Independent female voters must be represented by at least 10% of the total interviews.

x₆ ≥ .10 (x₁+x₂+x₃+x₄+₅+x₆)

9. Constraints on decision variables.

(x₁+x₂+x₃+x₄+₅+x₆) ≥ 0 Nonnegativity!

Part 2

What is the optimal interview plan?

X₁= Male Democratic voter = 0

X₂=Male Republican voter = 1450

X₃= Male Independent voter = 1050

X₄= Female Democratic voter = 1200

X₅₌ Female Republican voter = 350

X₆₌ Female Independent voter = 450

How much is the optimal total interview cost?

Optimal total interview cost = 49600

Related to contract requirement #2 given in Part 1: If the minimum number of Independents interviewed increases by 1 (i.e., changes from 1,500 to 1,501) while the other parameters remain constant, how much will the total interview cost change? Please indicate whether it is an increase, a decrease, or no change.

From sensitivity report we can see shadow price of contract requirement #2 is 2.5 i.e with every unit increase of number of Independents interviewed, the total interview cost will change by 2.5

So here, total interview cost will change by $2.5

It is an Increase

Related to contract requirement #3 given in Part 1: If the minimum number of males interviewed increases by 1 (i.e., changes from 1,800 to 1,801) while the other parameters remain constant, how much will the total interview cost change? Please indicate whether it is an increase, a decrease, or no change.

Shadow price of the constraint is 0 as number of males to be interviewed has been turned out to be 2500 in the final solution  which is more than 1800 so increment of 1 unit   does not have any affect to the total interview cost

So, It is No Change