Question

Answer the problem 2 only please. Also if you could upload the excel sheet as well will be very appreciated. thanks

OPR 3450 - Fall 2019 Assignment 1 due 09/09/19 page 1 of 1 Assignment 1 This is due in class on multiple dates. Check each Part. Each person must submit his/her own answer. If anyone submits a copy of an assignment, everyone with that submission will get a zero on the assignment. I will not accept e-mail assignments. Each student must submit a paper copy of the answers. Due Monday, September 9th 2019 Hand in both a summary sheet with your answers, and the needed computer backup work. Put your name on all of the work; the computer output should have your name printed by the computer. (Include both a printout of your spreadsheet and a printout of the ANSWER report generated by SOLVER. You are allowed to work in groups. However, each student must do his/her own work on the computer, and each student is required to submit his own summary sheets, problem solutions, computer work, etc.. If anyone submits a copy of an assignment, everyone with that submission will get a zero on the assignment. For problems 1 and 2 (Required) Formulate the decision question as a Linear Programming Model. Define the variables, write down the constraints and the objective function in mathematical terms (variables, inequalities, etc.) (Required) Find the optimal solution using the computer. Set up an Excel spread sheet and find an answer using SOLVER. (Hand in the final spreadsheet and the ANSWER report.) Problem 1 The Oak Works is a family-owned business that makes handcrafted dining room tables and chairs. They obtain the oak from local tree farm, which ships them 5,000 pounds of oak each Tronth. Each table uses 100 pounds of oak-while each chair uses 50 pounds of oak. The family builds all the furniture itself and has 480 hours of labor eraldic Sasha-month. Each table or chair requires six hours of labor. Each table nets Oak Works $40.0.in prefit, while each chair nets $100 in profit. Since chairs are oftensold with the tables, they want to produce at least twice as many chairs as tables. Oak Works would like to decide how many tables and chairs to produce suas to maximize profit. Problem 2 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 response rates to the calls are given in the table. For example, 30% of all daytime calls are answered by a wife,... - Because of a limited staff, at most half of the calls can be evening calls. Determine how to minimize the cost of completing the survey. Phone Data Person responding % of daytime calls % of evening calls Wife Single female Single male 10 Husband 10 None 40 30

Answer 1

Formulation

Let X1 and X2 be the number of calls made in the Daytime and in the Evening respectively.

Minimize Z = total cost = 2 * X1 + 5 * X2

Subject to,

0.30 X1 + 0.30 X2 >= 150 (Min no. of wives)

0.10 X1 + 0.20 X2 >= 120 (Min no. of husbands)

0.10 X1 + 0.15 X2 >= 100 (Min no. of single males)

0.10 X1 + 0.30 X2 >= 110 (Min no. of single females)

X2 <= 0.5 (X1 + X2) or, 0.5 X1 - 0.5 X2 >= 0 (Max limit for evening calls)

X1, X2 >=0

Excel formulation

A B Daytime Calls Evening Calls (X1) (X2) No. of Cost =SUMPRODUCT($C$3:$D$3,C4:D) COOL WN sit 0.3 Wives Husbands Single male Single female Max evening 0.1 0.1 0.5 0.3 0.2 0.15 0.3 -0.5 =SUMPRODUCT($C$3:$D$3.C6:D6) =SUMPRODUCT($C$3:$D$3,C7:D7) =SUMPRODUCT($C$3:$D$3,C8:08) =SUMPRODUCT($C$3:$D$3,C9:D9) =SUMPRODUCT($C$3:$D$3,C10:D10) >= >= >= >= 120 100 110 0 10

Solver inputs

Set Objective: SE54 To: O Max Min By Changing Variable Cells: $C$3:$D$3 Subject to the Constraints: $E$6:$E$10 >= $G$6:$G$10 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve close

Solution (Answer report)

Objective Cell (Min)
	Cell	Name	Original Value	Final Value
	$E$4	Cost	0	$2,400
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	No. of Daytime Calls (X1)	0	1200	Contin
	$D$3	No. of Evening Calls (X2)	0	0	Contin

So, at optimality, only 1,200 daytime clls will be made with a total cost of $2,400.