Question

Market Insights Co. (MIC) is a full-service market research company. MIC is being hired to interview registered voters in a district to gain insight into their opinions about certain 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 as follows:

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

The contract called for MIC to conduct interviews under the guidelines given below. MIC’s goal is to develop an interview plan that will satisfy the contract requirements at a minimum total interview cost.

Part 1. Linear Programming (LP) Model

Please formulate a linear programming (LP) model. Please follow the steps below.

Define the decision variables.

Write the objective.

Write the constraints (which are the contract requirements).

There must be at least 4,500 total interviews.

At least 1,000 Independent voters must be interviewed.

At least 2,000 males must be interviewed.

At least 1,750 females must be interviewed.

No more than 40% of those interviewed may be Democrats.

No more than 35% of those interviewed may be Republicans.

No more than 25% of those interviewed may be Republican males.

Each of the six types of voters must be represented by at least 10% of the total interviews.

Constraints on decision variables.

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. Answer the following questions based on the output of Excel Solver.

What is the optimal interview plan?

How much is the minimum cost?

Answer 1

Part-1

Decision variables:

MD = No. of male democrats
MR = No. of male republicans
MI = No. of male independents
FD = No. of female democrats
FR = No. of female republicans
FI = No. of female independents

Objective function

Minimize Z = 10 MD + 9 MR + 13 MI + 12 FD + 11 FR + 14 FI

Constraints

MD + MR + MI + FD + FR + FI >= 4500

MD + MR + MI >= 2000

FD + FR + FI >= 1750

MD + FD <= 40%*(MD + MR + MI + FD + FR + FI) or, 0.6 MD - 0.4 MR - 0.4 MI + 0.6 FD - 0.4 FR - 0.4 FI <= 0

MR + FR <= 35%*(MD + MR + MI + FD + FR + FI) or, -0.35 MD + 0.65 MR - 0.35 MI - 0.35 FD + 0.65 FR - 0.35 FI <= 0

MR <= 25%*(MD + MR + MI + FD + FR + FI) or, -0.25 MD + 0.75 MR - 0.25 MI - 0.25 FD - 0.25 FR - 0.25 FI <= 0

MR >= 10%*(MD + MR + MI + FD + FR + FI) or, -0.1 MD + 0.9 MR - 0.1 MI - 0.1 FD - 0.1 FR - 0.1 FI >= 0
MD >= 10%*(MD + MR + MI + FD + FR + FI) or, 0.9 MD -0.1 MR - 0.1 MI - 0.1 FD - 0.1 FR - 0.1 FI >= 0
MI >= 10%*(MD + MR + MI + FD + FR + FI) or, -0.1 MD - 0.1 MR + 0.9 MI - 0.1 FD - 0.1 FR - 0.1 FI >= 0
FR >= 10%*(MD + MR + MI + FD + FR + FI) or, -0.1 MD - 0.1 MR - 0.1 MI - 0.1 FD + 0.9 FR - 0.1 FI >= 0
FD >= 10%*(MD + MR + MI + FD + FR + FI) or, -0.1 MD - 0.1 MR - 0.1 MI + 0.9 FD - 0.1 FR - 0.1 FI >= 0
FI >= 10%*(MD + MR + MI + FD + FR + FI) or, -0.1 MD - 0.1 MR - 0.1 MI - 0.1 FD - 0.1 FR + 0.9 FI >= 0

MR, MD, MI, FR, FD, FI >= 0

Part-2

Answer report

Objective Cell (Min)
	Cell	Name	Original Value	Final Value
	$I$4	Cost	0	49625
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	Value of MD	0	1175	Contin
	$D$3	Value of MR	0	1125	Contin
	$E$3	Value of MI	0	450	Contin
	$F$3	Value of FD	0	625	Contin
	$G$3	Value of FR	0	450	Contin
	$H$3	Value of FI	0	675	Contin
Constraints
	Cell	Name	Cell Value	Formula	Status	Slack
	$I$6	D <= 40% of total	-2.27374E-13	$I$6<=$K$6	Binding	0
	$I$7	R <= 35% of total	-1.98952E-13	$I$7<=$K$7	Binding	0
	$I$8	MR <= 25% of total	-1.13687E-13	$I$8<=$K$8	Binding	0
	$I$9	Total interview	4500	$I$9>=$K$9	Binding	0
	$I$10	Total male interview	2750	$I$10>=$K$10	Not Binding	750
	$I$11	Total female interview	1750	$I$11>=$K$11	Binding	0
	$I$12	MD >= 10% of total	725	$I$12>=$K$12	Not Binding	725
	$I$13	MR >= 10% of total	675	$I$13>=$K$13	Not Binding	675
	$I$14	MI >= 10% of total	9.9476E-14	$I$14>=$K$14	Binding	0
	$I$15	FD >= 10% of total	175	$I$15>=$K$15	Not Binding	175
	$I$16	FR >= 10% of total	-1.84741E-13	$I$16>=$K$16	Binding	0
	$I$17	FI >= 10% of total	225	$I$17>=$K$17	Not Binding	225

Sensitivity report

Variable Cells
			Final	Reduced	Objective	Allowable	Allowable
	Cell	Name	Value	Cost	Coefficient	Increase	Decrease
	$C$3	Value of MD	1175	0	10	1	0
	$D$3	Value of MR	1125	0	9	0	41
	$E$3	Value of MI	450	0	13	1E+30	1
	$F$3	Value of FD	625	0	12	0	1
	$G$3	Value of FR	450	0	11	1E+30	0
	$H$3	Value of FI	675	0	14	1	2
Constraints
			Final	Shadow	Constraint	Allowable	Allowable
	Cell	Name	Value	Price	R.H. Side	Increase	Decrease
	$I$6	D <= 40% of total	-2.27374E-13	-2	0	225	175
	$I$7	R <= 35% of total	-1.98952E-13	-3	0	0	175
	$I$8	MR <= 25% of total	-1.13687E-13	0	0	1E+30	0
	$I$9	Total interview	4500	10.25	4500	500	750
	$I$10	Total male interview	2750	0	2000	750	1E+30
	$I$11	Total female interview	1750	2	1750	725	175
	$I$12	MD >= 10% of total	725	0	0	725	1E+30
	$I$13	MR >= 10% of total	675	0	0	675	1E+30
	$I$14	MI >= 10% of total	9.9476E-14	1	0	225	175
	$I$15	FD >= 10% of total	175	0	0	175	1E+30
	$I$16	FR >= 10% of total	-1.84741E-13	0	0	175	0
	$I$17	FI >= 10% of total	225	0	0	225	1E+30

What is the optimal interview plan?

	Democrat	Republican	Independent
Male	1175	1125	450
Female	625	450	675

How much is the minimum cost?

$49,625