Question

Complete each problem on a separate worksheet in a single Excel file. Rename the separate worksheets with the respective problem number. You may have to copy and paste the datasets into your homework file first. Name the file with your last name, first initial, and HW #2. Label each part of the question. When calculating statistics, label your outputs. Use the Solver add-in for these problems.

1. For a telephone survey, a marketing research group needs to contact at least 600 wives, 480 husbands, 400 single adult males, and 440 single adult females. It costs $3 to make a daytime call, and (because of higher labor costs) $5 to make an evening call. Problem #1 lists the results that can be expected. For example, 30% of all daytime calls are answered by a wife, 15% of all evening calls are answered by a single male, and 40% of all daytime calls are not answered at all. Due to limited staff, at most 40% of all calls can be evening calls.

1. Determine how to minimize the cost of completing the survey.
2. Use SolverTable 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. Then repeat the analysis by changing the cost of an evening call and keeping the cost of a daytime call fixed.

Person responding	% of daytime calls	% of evening calls
Wife	30	30
Husband	10	30
Single male	10	15
Single female	10	20
None	40	5

Answer 1

a). 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

X₁ = Daytime Calls, X₂ = Evening Calls

Objective

Minimize Z = 2X₁ + 5X₂

Constraints

0.30X₁ + 0.30X₂ ≥ 150

0.10X₁ + 0.30X₂ ≥ 120

0.10X₁ + 0.15X₂ ≥ 100

0.10X₁ + 0.20X₂ ≥ 110

1X₁ ≥ 1X₂

1X₁, 1X₂ ≥ 0

Here is a spreadsheet model for the problem:

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.

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.

SolverTable does the rest, creating the following output:

The SolverTable output can be used to create a chart:

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.