How do I formulate question 3 as a transportation problem?

ADM2302 Section M, N and P Solution Assignment # 2 SOLUTION Assignment # 2 LP Formulations and Applications ADM2302 students are reminded that submitted assignments must be neat, readable, and wellorganized. 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. Each student must provide an individual original submission of completed assignments. This assignment can be hand written. Solutions to all exercises in this assignment are to include \"managerial statements\" that communicate the results of the analyses. Problem 1 (2.5 points) The United Charities annual fund-raising drive is scheduled to take place next week. Donations are collected during the day and night, by telephone, and through personal contact. The average donation resulting from each type of contract is as follows. Phone ($) Personal ($) Day 2 4 Night 3 7 The charity group has enough donated gasoline and cars to make at most 300 personal contacts during one day and night. The volunteer minutes required to conduct each type of interview are as follows. Phone (min) Personal (min) Day 6 15 Night 5 12 The charity has 20 volunteer hours available each day and 40 volunteer hours available each night. The chairperson of the fund-raising drive wants to know how many different types of contacts to schedule in a 24-hour period (i.e., one day and one night) to maximize total donations. a. Formulate algebraically the Linear Programming (LP) model for this problem. b. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel solver (Provide a printout of the corresponding \"Excel Spreadsheet\" and the \"Answer Report\"). Solution Winter 2013 Page 1 ADM2302 Section M, N and P Solution Assignment # 2 (a) Algebraic formulation (1.5 points) 0.25 point: for decision variables and their definition 0.25 point: objective function 1 point: Constraints (for each mistake or missing constraint deduct 0.25 point) x1 = no. of day contacts by phone x2 = no. of day contacts in person x3 = no. of night contacts by phone x4 = no. of night contacts in person maximize Z = 2x1 + 4x2 + 3x3 + 7x4 subject to x2 + x4 = 0 (b) Solution using Excel Solver: 0.75 point: for setting the spreadsheet in \"Excel Solver\" and Solving. x1 = 200 x3 = 480 Z = 1,840 Managerial statement (0.25 point): the number of day contacts by phone is 200 and the number of night contact by phone is 480 (there no need to do any contact in person). This will lead to a maximum donation of $1,840. x1 = 200 x3 = 480 Z = 1,840 Problem 2 (4 points) The Mill Mountain Coffee Shop blends coffee on the premises for its customers. It sells three basic blends in 1-pound bags, Special, Mountain Dark, and Mill Regular. It uses four different types of coffee to produce the blends - Brazilian, mocha, Columbian, and mild. The shop used the following blend recipe requirements. Winter 2013 Page 2 ADM2302 Section M, N and P Blend Solution Assignment # 2 Mix Requirements Selling Price/pound ($) Special At least 40% Columbian, at least 30% mocha 6.50 Dark At least 60% Brazilian, no more than 10% mild 5.25 Regular No more than 60% mild, at least 30% Brazilian 3.75 The cost of Brazilian coffee is $2.00 per pound, the cost of mocha is $2.75 per pound, the cost of Columbian is $2.90 per pound, and the cost of mild is $1.70 per pound. The shop has 110 pounds of Brazilian coffee, 70 pounds of mocha, 80 pounds of Columbian, and 150 pounds of mild coffee available per week. The shop wants to know the amount of each blend it should prepare each week to maximize profit. a. b. Formulate algebraically the Linear Programming (LP) model for this problem. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel solver (Provide a printout of the corresponding \"Excel Spreadsheet\" and the \"Answer Report\"). Solution Algebraic formulation (2.75 points) 0.5 point: for decision variables and their definition 0.25 point: objective function 2 points: Constraints (for each mistake or missing constraint deduct 0.25 point) (a) xij = pounds of coffee i used in blend j per week, where i = b (Brazilian), o (Mocha), c (Columbian), m (mild) and j = s (special), d (dark), r (regular) maximize Z = 4.5xbs + 3.75xos + 3.60xcs + 4.8xms + 3.25xbd + 2.5xod + 2.35xcd + 3.55xmd + 1.75xbr + 1.00xor + 0.85xcr + 2.05xmr subject to .6xcs - .4xbs - .4xos - .4xms >= 0 -.3xbs + .7xos - .3xcs - .3xms >= 0 .4xbd - .6xod - .6xcd - .6xmd >= 0 -.1xbd - .1xod - .1xcd + .9xmd = 0 xbs + xbd + xbr = 0 (b) Solution using Excel Solver: 0.75 point: for setting the spreadsheet in \"Excel Solver\" and Solving. Managerial statement (0.5 point) Please note that this problem has multiple optimal solutions other than the one provided below. Each week prepare 200 pounds of special (by mixing 60 pounds of mocha with 80 pounds of Columbian with 60 pounds of mild). Prepare 72 pounds of Dark (by mixing 64.8 pounds of bold with 7.2 pounds of mild). Prepare 138 pounds of Regular (by mixing 45.2 pounds of bold with 10 pounds of mocha with 82.8 pounds mild). The maximum net profit is $1,296. xos = 60 xcs = 80 xms = 60 xbd = 64.8 xmd = 7.2 xbr = 45.2 xor = 10 xmr = 82.8 Z = $1,296 Alternative solution xos = 60 xcs = 80 xms = 60 xod =10 xbd = 54.8 xmd = 7.2 xbr = 55.2 xmr = 82.8 Special: xos + xcs + xms = 200 pounds Dark: xbd + xmd = 72 pounds Regular: xbr + xor + xmr = 138 pounds Special: 200 pounds Dark: 72 pounds Regular: 138 pounds Profit = $1,296 Winter 2013 Page 4 ADM2302 Section M, N and P Solution Assignment # 2 Problem 3 (3.5 points) The J. Mehta Company's production manager is planning a series of one-month production periods for stainless steel sinks. The forecasted demand for the next four months is as follows: Month 1 2 3 4 Demand for Stainless Steel Sinks 120 160 240 100 The Mehta firm can normally produce 100 stainless steel sinks in a month. This is done during regular production hours at a cost of $100 per sink. If demand in any one month cannot be satisfied by regular production, the production manager has three other choices: (1) he can produce up to 50 more sinks per month in overtime but at a cost of $130 per sink; (2) he can purchase a limited number of sinks from a friendly competitor for resale (the maximum number of outside purchases over the four-month period is 450 sinks, at a cost of $150 each); (3) Or, he can fill the demand from his on-hand inventory. The inventory carrying cost is $10 per sink per month. A constant workforce level is expected. Back orders are NOT permitted (e.g. order taken in period 3 to satisfy demand in period 2 is not permitted). Inventory on hand at the beginning of month 1 is 40 sinks. a. Formulate algebraically the Linear Programming (LP) model for the above \"production scheduling\" problem. b. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel solver (Provide a printout of the corresponding \"Excel Spreadsheet\" and the \"Answer Report\"). Winter 2013 Page 5 ADM2302 Section M, N and P Solution Assignment # 2 Solution Algebraic formulation (2.5 points) 0.5 point: for decision variables and their definition 0.25 point: objective function 1.75 point: Constraints (for each mistake or missing constraint deduct 0.25 point) Algebraic Formulation: x ri : the number of sinks the firm produce during its regular production hours for month i (i=1,2,3,4) xoi : the number of sinks the firm produce in overtime for month i (i=1,2,3,4) x pi : the number of sinks the firm purchase from its competitor for month i (i=1,2,3,4) I i : The firm's inventory at the end of month i (i=1,2,3,4) Cost Minimization: 4 4 4 4 i 1 i 1 i 1 i 1 min Cost 100 xri 130 xoi 150 x pi 10 I i subject to: xr1 xo1 x p1 I1 80 (Note: initial inventory of 40 reduces Month 1 demand to 80 from 120) I1 xr 2 xo 2 x p 2 I 2 160 I 2 xr 3 xo 3 x p 3 I 3 240 Month 4: I 3 xr 4 xo 4 x p 4 I 4 100 where I4=0 (since there is no required inventory at the end of the last period.) Monthly Capacity constraints for production, over-time, purchase, and inventory: Production: x ri 100 for i=1,2,3,4 4 Purchase: x pi 450 i 1 Over-time production: xoi 50 xri 0, xoi 0, x pi 0, I i 0 for i=1,2,3,4 for i=1,2,3,4 (b) Solution using Excel Solver: 0.75 point: for setting the spreadsheet in \"Excel Solver\" and Solving. Managerial statement (0.25 point) Please note that this problem have multiple optimal solution other than the one provided below (try to run solver many times and see how the solution changes). Managerial Statement The optimal minimum cost for the production plan is $ 65,300. Optimal Production Plan 1 Winter 2013 Page 6 ADM2302 Section M, N and P Solution Assignment # 2 Manufacture at regular time 100 units in months 1, 2, 3, and 4, respectively, as well as 20 units of overtime in month 1, and 50 units at over-time in month 2, 3 and 4. Subcontract 60 units. Optimal Production Plan 2: Manufacture at regular time 100 units in months 1, 2, 3, and 4, respectively, as well as 50 units at over-time in month 1, 2, 3. Subcontract 30 units. Source: Problem 1 and 2: Taylor, B.W. III 2007. Introduction to Management Science. (9th ed.) Prentice-Hall, Upper Saddle River, New Jersey. 771p. + appendices. Problem 3: Render, B., and R.M. Stair, Jr., N. Balakrishnan 2003. Managerial Decision Modeling. Prentice-Hall, Inc.: Upper Saddle River, New Jersey. 616p. Winter 2013 Page 7