ADM2302 M, N, P and Q Assignment # 3 Assignment # 3 Transportation Problem, Integer/Binary Programming and Goal Programming ADM2302 students are reminded that submitted assignments must be neat, readable, and well-organized. Assignment marks will be adjusted for sloppiness, poor grammar and spelling, as well as for technical errors. While working together is encouraged, plagiarism on assignments will not be accepted. The assignment is to be submitted electronically as a single PDF file via blackboard learn by Thursday March 24th prior to 23:59. Front page of the PDF document has to include title of the assignment, course code and section, student name and student number. Second page is the individual statement of integrity that must be signed. Note: Each student must provide an individual original submission of completed Assignment #3. Please also note: Assignment #3 copies that are submitted jointly (i.e., by more than one author) will not be graded. Solutions to problem 1, 2 and 4 on this assignment are to include \"managerial statements\" that communicate the results of the analyses. Problem 1 (20 points) A soft drink manufacturer, Sara Soda, Ltd., has recently begun negotiations with brokers in the areas where it intends to distribute its products. Before finalizing the agreements, however, Manager Dave Pepper wants to determine shipping routes and costs. The firm has three plants with capacities as shown below: Plant Metro (M) Ridge (R) Colby (C) Capacity (cases per week) 40,000 30,000 25,000 Estimated demands in each of the warehouse localities are: Demand Warehouse (cases per week) RS1 24,000 RS2 22,000 RS3 23,000 RS4 16,000 RS5 10,000 Winter 2016 Page 1 ADM2302 M, N, P and Q Assignment # 3 The estimated per unit shipping costs per case for the various routes are: To: From: Metro Ridge Colby RS1 RS2 RS3 RS4 RS5 .80 .75 .70 .75 .80 .75 .60 .85 .70 .70 .70 .80 .90 .85 .80 Formulate this problem algebraically and use Excel (and Solver) and determine the optimal shipping plan that will minimize total shipping cost under these conditions: a. b. c. Route Ridge-RS4 is unacceptable. All routes are acceptable. What is the additional cost of the Ridge-RS4 route not being acceptable? Problem 2 (15 points) Bradford Electronics produces a variety of DVD drives for installation into home-use DVD players. Bradford can assemble DVD drives on any or all five production stations, some of which are more automated than others, and thus, have lower variable costs of assembly but require higher one-time setup costs to convert to assembling a particular model of DVD drive. Bradford has received an order for assembling 2,500 DVD drives of a particular model. Assembly Station 1 2 3 4 5 Variable Assembly Cost/DVD Drive $62 $68 $72 $78 $85 Capacity In DVD Drives 500 600 700 450 1000 Setup Cost $12,000 $ 6,000 $ 3,000 $ 1,500 $ 500 Given the setup costs, capacities and variables assembly costs at each of the five production stations in the above table, the company needs to determine an optimal production plan that minimizes the total costs. Answer the following questions: (a) (b) Formulate this problem algebraically as an integer/binary programming model. (9 points) Use Excel Solver to determine how many DVD drives should be assembled at each of the five production stations to minimize total costs? ( 6 points) Winter 2016 Page 2 ADM2302 M, N, P and Q Assignment # 3 Problem 3 (15 points) The board of directors of a large manufacturing firm is considering a set of investments shown in the following table. Let Ri be the total revenue from investment i and Ci be the cost to make investment i. The board wishes to maximize the total revenue and invest no more than a total of M dollars. Formulate this model as a Binary Integer Programming Problem (BIP). Define your decision variables, and write down the algebraic formulation of the model. Investment 1 2 3 4 5 6 7 Condition None Only if 1 Only if 2 Must if 1 and 2 Not if 1 or 2 Not if 2 and 3 Only if 2 and Not 3 Problem 4 (20 points) Harris Segal, Marketing director for the Upper Canada Power Corporation is about to begin an advertising campaign promoting energy conversation. In trying to budget between television and newspaper advertisements, he sets the following goals and assigns the weights shown. 1. The total advertising budget of $135,000 should not be exceeded. Weight = 200 2. There should be a mix of TV and newspaper ads, with at least 10 TV spots (costing $5,000 each) and at least 20 newspaper ads (costing $2,000 each). Weight = 150 each. 3. The total number of people to read or hear the advertisements should be at least 10 million. Weight = 80 per 100,000 Each television spot reaches approximately 400,000 people. A newspaper advertisement is read by about 100,000 persons. (a) Formulate Segal's goal programming problem to find out how many of each type of ad to place. (13 points) (b) Solve using Excel to find out how many of each type of ad to place. How many people, in total, will read or hear the advertisements? (7 points) Source: Problem 1: Stevenson, W.J. and Ozgur, C. 2007. Introduction to Management Science with Spreadsheets. McGraw-Hill/Irwin. Boston, Mass. 812p. Problem 2 and 3: J. H. Moore, L. R. Weatherford. 2001. Decision Modeling with Microsoft Excel (6th edition). Prentice-Hall. New Jersey, Mass. 693p. Problem 4: Render, B., and R.M. Stair, Jr., N. Balakrishnan 2003. Managerial Decision Modeling. Prentice-Hall, Inc.: Upper Saddle River, New Jersey. 616p. Winter 2016 Page 3