Explore BrainMass
Share

Optimization Modeling In Excel (using Solver)

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

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 (b/c of higher labor costs) $5 to make an evening call. Because of the limited staff, at most half of all phone calls can be evening calls. Determine how to minimize the cost of completing the survey.

Table 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.

DATA for Problem
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

Need the optimal solution only.
Please explain the results in plain, simple english - no technical jargon please. Thank you.

© BrainMass Inc. brainmass.com October 24, 2018, 6:39 pm ad1c9bdddf
https://brainmass.com/math/optimization/optimization-modeling-in-excel-using-solver-45559

Solution Summary

The solution provides an optimal solution in Excel for a given marketing problem, including the model formation in Word.

$2.19
See Also This Related BrainMass Solution

Network Flow Models; Supply, Demand, and Transshipment Nodes; Optimization in Excel; Solver

Problem:
A furniture manufacturer has warehouses in cities represented by nodes 1, 2, and 3 in Figures 5.34. The values on the arcs indicate the per unit shipping costs required to transport living room suites at each warehouse is indicated by the negative number next to nodes 1, 2, and 3. The demand for living room suites is indicated by the positive number next to the remaining nodes.

a) Identify the supply, demand, and transshipment nodes in this problem.
b) Use Solver to determine the least costly shipping plan for this problem and provide Excel spreadsheet with formulas and solution (Solver).

Network Models; Optimization Problem using Excel add-in Solver

This is a problem (5-14) from the textbook by Cliff Ragsdale, "Spreadsheet Modeling and Decision Analysis".

This problem requires the use of Excel and the add-in, called Solver. The course is Excel-based and Solver is the optimization application used for all problems.

The problem appears in this text box and is also attached as a MS Word file, so it is sure to transmit legibly. The MS Word version will likely need to be the document from which to work, as the text box does not allow information to be transmitted well (see attached MS Word doc below).

View Full Posting Details