Columbia University in the City of New York



Solution to Bonus Problem 3: Telephone Survey[1]

For a telephone survey, a marketing research group needs to contact at least 150 wives, 120 husbands, 100 single adult males, and 110 single adult females. It costs $2 to make a daytime call and (because of higher labor costs) $5 to make an evening call. The table below lists the results that can be expected. For example, 30% of all daytime calls are answered by a wife, and 15% of all evening calls are answered by a single male. Because of a limited staff, at most half of all phone calls can be evening calls.

|Person Responding |Percentage of Daytime Calls |Percentage of Evening Calls |

|Wife |30 |30 |

|Husband |10 |30 |

|Single male |10 |15 |

|Single female |10 |20 |

|None |40 |5 |

Data for Phone Problem

1. Determine how to minimize the cost of completing the survey.

Managerial Problem Definition

We want to minimize the total cost of completing the survey, subject to the various probabilities of reaching certain types of people at certain times of the day, costs of making calls, and minimum requirements for numbers of calls to certain demographic groups.

Decision Variables

We need to decide how many evening calls and how many daytime calls to make.

Objective

Minimize the total cost.

Constraints

We need to contact 150 wives, 120 husbands, 100 single adult males, and 110 single adult females. At most half of all phone calls can be evening calls.

Mathematical Formulation:

Decision Variables

X1 = Daytime Calls, X2 = Evening Calls

Objective

Minimize Z = 2X1 + 5X2

Constraints

0.30X1 + 0.30X2 ≥ 150

0.10X1 + 0.30X2 ≥ 120

0.10X1 + 0.15X2 ≥ 100

0.10X1 + 0.20X2 ≥ 110

1X1 ≥ 1X2

1X1, 1X2 ≥ 0

Here is a spreadsheet model for the problem:

[pic]

[pic]

[pic]

[pic]

The optimal solution is to make 900 Daytime calls and 100 Evening calls, for a total cost of $2,300.

2. Starting with the optimal solution to the initial problem, use the SolverTable add-in to investigate changes in the unit cost of either type of call. Specifically, investigate changes in the cost of a daytime call, with the cost of an evening call fixed, to see when (if ever) only daytime calls or only evening calls will be made.

Select Data — SolverTable, pick Oneway Table, and click OK.

[pic]

The input cell is the value that we want to vary (in this case B9, the cost of a daytime call). We specify a range of values for this cell (here, $0.00 to $20.00 in increments of $1.00).

We also specify Output Cells (here, the numbers of each type of call — cells B12:C12, and the total cost — cell B22).

Finally, we tell SolverTable to write its output starting in cell F1.

[pic]

SolverTable does the rest, creating the following output:

[pic]

The SolverTable output can be used to create a chart:

[pic]

Conclusion: If daytime calls are very inexpensive, we can dispense with evening calls altogether. However, we will always have to make at least 400 daytime calls, no matter how expensive they are.

3. Repeat the analysis by changing the cost of an evening call and keeping the cost of a daytime call fixed.

These results are consistent with those from Part 2. We will always make at least 400 daytime calls.

[pic]

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

[1] Based on 3-20 and 3-21 (p. 101) in Practical Management Science (2nd ed., Winston and Albright, 2001 Duxbury Press). Solution by David Juran, 2001.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download