1.The question type will be as exact the same format with your HW, to model the problem in the...

Question

1.The question type will be as exact the same format with your HW, to model the problem in the algebraic formulation (Please write down all the decision variables, objective function and all the constraints), spreadsheet formulation and solve it. For each problem, please put all your solution including the screenshot of your excel spreadsheet into one .doc/.docx file. You need to upload your solution including word or excel (or both) for each question. 2. The midterm will include 4 questions in total. 3. It is open book exam but requires individual work. Please prepare the textbook and everything you need before the exam. You will have 180 min to finish the exam.			Table of Contents Chapter 3 (Linear Programming: Formulation and Applications) Table of Contents Chapter 3 (Linear Programming: Formulation and Applications) Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.1 Super Grain Corp. Advertising-Mix Problem (Section 3.1)	3.2–3.5 Resource Allocation Problems (Section 3.2)	3.6–3.16 Cost-Benefit-Trade-Off Problems (Section 3.3)	3.17–3.22 Mixed Problems (Section 3.4)	3.23–3.28 Transportation Problems (Section 3.5)	3.29–3.33 Assignment Problems (Section 3.6)	3.34–3.37 The TBA Airlines Problem TBA Airlines is a small regional company that specializes in short flights in small airplanes. The company has been doing well and has decided to expand its operations. The basic issue facing management is whether to purchase more small airplanes to add some new short flights, or start moving into the national market by purchasing some large airplanes, or both. Question: How many airplanes of each type should be purchased to maximize their total net annual profit? Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.2 Data for the TBA Airlines Problem 		Small Airplane	Large Airplane	Capital Available 	Net annual profit per airplane	$7 million	$22 million	 	Purchase cost per airplane	25 million	75 million	$250 million 	Maximum purchase quantity	5	—	 Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.3 3 Table 3.2  Data for the TBA Airlines problem. Violates Divisibility Assumption of LP Divisibility Assumption of Linear Programming: Decision variables in a linear programming model are allowed to have any values, including fractional values, that satisfy the functional and nonnegativity constraints. Thus, these variables are not restricted to just integer values.  Since the number of airplanes purchased by TBA must have an integer value, the divisibility assumption is violated. Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.4 Spreadsheet Model Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.5 5 Figure 3.2  A spreadsheet model for the TBA Airlines integer programming problem where the changing cells, Units Produced (C12:D12), show the optimal airplane purchases obtained by Solver and the objective cell, Total Profit (G12), gives the resulting total profit in millions of dollars. Integer Programming Formulation Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.6 Let	S = Number of small airplanes to purchase 	L = Number of large airplanes to purchase Maximize Profit = 7S + 22L ($millions) subject to 	Capital Available:	25S + 75L ≤ 250  ($millions) 	Max Small Planes:	S ≤ 5 and 	S ≥ 0,  L ≥ 0 	S, L are integers. Template for Resource-Allocation Problems Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.7 7 Figure 3.4  A template of a spreadsheet model for pure resource-allocation problems. Summary of Formulation Procedure for Resource-Allocation Problems Identify the activities for the problem at hand. Identify an appropriate overall measure of performance (commonly profit). For each activity, estimate the contribution per unit of the activity to the overall measure of performance. Identify the resources that must be allocated. For each resource, identify the amount available and then the amount used per unit of each activity. Enter the data in steps 3 and 5 into data cells. Designate changing cells for displaying the decisions. In the row for each resource, use SUMPRODUCT to calculate the total amount used. Enter = and the minimum acceptable level in two adjacent cells. Designate an objective cell. Use SUMPRODUCT to calculate this measure of performance. Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.14 Types of Functional Constraints 	Type	Form*	Typical Interpretation	Main Usage 	Resource constraint	LHS ≤ RHS	For some resource,    Amount used ≤    Amount available	Resource-allocation problems and mixed problems 	Benefit constraint	LHS ≥ RHS	For some benefit,    Level achieved ≥    Minimum Acceptable	Cost-benefit-trade-off problems and mixed problems 	Fixed-requirement constraint	LHS = RHS	For some quantity,    Amount provided =    Required amount	Transportation problems and mixed problems 	 * LHS = Left-hand side (a SUMPRODUCT function).    RHS = Right-hand side (a constant).			 Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.15 15 Table 3.6  Types of Functional Constraints Sellmore Company Assignment Problem The marketing manager of Sellmore Company will be holding the company’s annual sales conference soon. He is hiring four temporary employees: Ann Ian Joan Sean Each will handle one of the following four tasks: Word processing of written presentations Computer graphics for both oral and written presentations Preparation of conference packets, including copying and organizing materials Handling of advance and on-site registration for the conference Question: Which person should be assigned to which task? Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.16 Data for the Sellmore Problem 		Required Time per Task (Hours)				 	Temporary Employee	Word Processing	Graphics	Packets	Registrations	Hourly Wage 	Ann	35	41	27	40	$14 	Ian	47	45	32	51	12 	Joan	39	56	36	43	13 	Sean	32	51	25	46	15 Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.17 17 Table 3.10  Data for the Sellmore Company problem. Spreadsheet Formulation Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.18 18 Figure 3.11  A spreadsheet formulation of the Sellmore Co. problem as an assignment problem, including the objective cell Total Cost (J30). The values of 1 in the changing cells Assignment (D24:G27) show the optimal plan obtained by Solver for assigning the people to the tasks. The Model for Assignment Problems Given a set of tasks to be performed and a set of assignees who are available to perform these tasks, the problem is to determine which assignee should be assigned to each task. To fit the model for an assignment problem, the following assumptions need to be satisfied: The number of assignees and the number of tasks are the same. Each assignee is to be assigned to exactly one task. Each task is to be performed by exactly one assignee. There is a cost associated with each combination of an assignee performing a task. The objective is to determine how all the assignments should be made to minimize the total cost. Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education. 3.19 1 2 3 4 5 6 7 8 9 10 11 12 13 14 ABCDEFG TBA Airlines Airplane Purchasing Problem Small AirplaneLarge Airplane Unit Profit ($millions)722 CapitalCapital SpentAvailable Capital ($millions)2575250=	48		TotalCost	J21 		8am-10am	1	1	0	0	0	79	>=	79		TotalWorking	H8:H17 		10am- 12pm	1	1	0	0	0	79	>=	65 		12pm-2pm	1	1	1	0	0	118	>=	87 		2pm-4pm	0	1	1	0	0	70	>=	64 		4pm-6pm	0	0	1	1	0	82	>=	73 		6pm-8pm	0	0	1	1	0	82	>=	82 		8pm-10pm	0	0	0	1	0	43	>=	43 		10pm-12am	0	0	0	1	1	58	>=	52 		12am-6am	0	0	0	0	1	15	>=	15 			6am-2pm	8am-4pm	Noon-8pm	4pm-midnight	10pm-6am 			Shift	Shift	Shift	Shift	Shift			Total Cost 		Number Working	48	31	39	43	15			$30,610 Sheet1 	 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 B C D E F G H I J 6am-2pm 8am-4pm Noon-8pm 4pm-midnight 10pm-6am Shift Shift Shift Shift Shift

1.The question type will be as exact the same format with your HW, to model the problem in the algebraic formulation (Please write down all the decision variables, objective function and all the...

Get Answer To This Question

Related Questions & Answers

Submit New Assignment