15-58 Problems We have inserted the symbol E* to the left of each problem (or its parts) where Excel should be ‘used (unless your instructor gives you contrary instructions). An asterisk on the problem number indicates that at least a partial answer is given at the end of the problems. 15.1. Consider the transportation problem having the following data: Unit Cost ($) Destination: 1 2 3 ‘Supply Source 1 9 6 8 4 2 7 2 10 3 3 6 7 6 2 Demand 4 2 ‘2. Draw the network representation of this problem E* A Display the problem on a spreadsheet ard then use the Excel Solver to obtain an optimal soletion, 15.2, Consider the transportation problem having the following dat Unit Cost (5) Destination: 1 2 3 ‘ ‘supply Source 1 3 7 6 4 5 2 2 ‘ 3 2 2 3 4 3 8 5 3 Demand 3 3 2 2 1a. Draw the network representation of this problems, E* Display the problem on a spreadsheet and then use the Excel Solver to obtain an optimal solution Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education,15-59 15.3. ‘The Cost-Less Corp. supplies its four retail outlets from its four plants. The shipping cost per shipment from each plant to each retail outlet is given below. Unit Shipping Cost Retail Outlet: 1 2 3 4 Plant 1 $500 $600 $400 $200 2 200 ‘900 100 300 3 300 400 200 100 4 200 100 300 200 Parts 1, 2,3, and 4 make 10, 20, 20, and 10 shipments per month, respectively Retail outlets 1,2, 3,and 4 need to receive 20,10, 10, and 20 shipments per month, respectively. ‘The distribution manager, Randy Smith, now wants to determine the hes plan for how many shipments to send from each plant tothe respective retail outlets exch month. Randy's ob- Jective 1 to mininnze the total dipzung coat 12 Formulate thie problem sea transportation protlem by’ constricting stable that dentifiex all the sources, supplies, destinations, demands, and uri costs. E* —b. Display the transportation problem on a spreadsheet and then use the Excel Solver to obtain. “optimal solubor. 15.4. ‘The Childfair Company has three plants producing child push chairs that are to be shipped to four distribution centers. Plants 1, 2, and 3 produce 12, 17, and 11 shipments per month, respectively. Each distribution center needs to receive 10 shipments per month, The distance from each plant to the respective distribution centers is given below: Distance to Distribution Center (Miles) 1 2 3 4 Plant 1 800 400 700 2 1,100 600 1,000 3 ‘600 800 ‘900 The freight coat foreach shipment 6 $100 plus $0 cents mle. How much should be chipec stom each plant to each ofthe eistnbution centers to minimize the total shipping cout? ‘2. Formulate ths problem at a transportation problem by constructing a table that identifies all the sources, supplies, destinations, demands, and unit costs. E* Display the transportation problem on a spresdthect and then use the Excel Solver to obtain ‘a optimal solution. E* 15.5.* Tom would like 3 pints of home brew today and an additional 4 pints of home brew tomorrow. Dick is willing to sell a maximum of 5 pints total at a price of $3.00/pint today Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education,E* 156, EM 15.7, EF 15.8, Be 159, 15-60 and $2.70/pint tomorrow. Harry is willing to sell a maximum of 4 pints total at a price of $2,90/pint today and $2.80/pint tomorrow. ‘Tom wishes to know what his purchases should be to minimize his cost while satisfying his thirst requirements. Formulate and solve a spreadsheet model for this problem. The Versatech Corporation has decided to produce three new products. Five branch plants now have excess product capacity. The unit manufacturing cost of the first product would be $31, $29, $32, $28, and $29 in plants 1, 2, 3, 4, and 5, respectively. The unit manufacturing cost of the second product would be $45, $41, $46, $42, and $43 in plants 1, 2,3, 4, and 5, respectively. The unit manufacturing cost of the third product would be $38, $35, and $40 in plants 1, 2, and 3, respectively, whereas plants 4 and 5 do not have the capability for producing this product. Sales forecasts indicate that 600, 1,000, and 800 units of products 1, 2, and 3, respectively, should be produced per day. Plants 1, 2, 3, 4, and 5 have the capacity to produce 400, 600, 400, 600, and 1,000 units daily, respectively, regardless of the product or combinations of products involved. Assume that any plant having the capability and capacity to produce them can produce any combination of the products in any quantity. ‘Management wishes to know how to allocate the new products to the plants to minimize total manufacturing cost. Formulate and solve a spreadsheet model for this problem. Suppose that England, France, and Spain produce all the wheat, barley, and oats in the world. The world demand for wheat requires 125 million acres of land devoted to wheat production. Similarly, 60 million acres of land are required for barley and 75 million acres of land are required for oats. The total amount of land available for these purposes in England, France, and Spain is 70 million acres, 110 million acres, and 80 million acres, respectively. The number of hours of labor needed in England, France, and Spain, respectively, to produce an acre of wheat is 18, 13, and 16; to produce an acre of batley is, 15, 12, and 12; and to produce an acre of oats is 12, 10, and 16. The labor cost per hour in England, France, and Spain, respectively, for producing wheat is $9.00, $7.20, and $9.90; for producing barley is $8.10, $9.00, and $8.40; and for producing oats is $6.90, $7.50, and $6.30. The problem is to allocate land use in each country so as to meet the world food requirements and minimize the total labor cost. Formulate and solve a spreadsheet model for this problem. A contractor, Susan Meyer, has to haul gravel to three building sites. She can purchase as much as 18 tons at a gravel pit in the north of the city and 14 tons at one in the south. She needs 10, 5, and 10 tons at sites 1, 2, and 3, respectively. The purchase price per ton at each gravel pit’ and the hauling cost per ton are given in the following table Hauling Cost per Ton at site it 1 2 3 Price per Ton North $30 $60 $50 $100 South cy 30 40 120 Susan wishes to determine how much to haul from each pit to each site to minimize the total cost for purchasing and hauling gravel. Formulate and solve a spreadsheet model for this problem. Reconsider the P & T Co, case study presented in Sections 15.1 and 15.2. Refer to the spreadshect in Figure 15.2, which shows the formulation as a transportation problem and Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.E* 15.10. E* 15.11 15-61 displays an optimal solution, You now leam that one or more of the unit costs in the data cells UnitCost (DS:G7) may change slightly before shipments begin. Use Solver to generate the sensitivity report for this problem. Use this report to determine the allowable range for each of the unit costs. What do these allowable ranges tell P & T management? Reconsider the Metro Water District problem presented in Scction 15.4. Refer to the spreadsheet in Figure 15.6, which shows the formulation as a variant of a transportation problem and displays an optimal solution The numbers given in the data cells are only estimates that may be somewhat inaccurate, so management now wishes to do some what-if analysis. Use Solver to generate the sensitivity report. Then use this report to address the following questions. (In each case, assume that the indicated change is the only change in the model.) a. Would the optimal solution in Figure 15.6 remain optimal ifthe cost per acre foot of shipping Calorie River water to San Go were actually $200 rather than $230? 5. Would this solution remain optimal if the cost per aere foot of shipping Sacron River water to Los Devils were actually $160 rather than $130? © Must this solution remain optimal if the costs considered in parts a and b were simultaneously changed from their original values to S215 and $145, respectively? 4. Suppose that the supply from the Sacron River and the demand at Hollyglass are decreased simultaneously by the same amount. Must the shadow prices for evaluating these changes remain valid if the dectease were 0.5 million acre feet? Reconsider the Metro Water District problem presented in Section 15.4, including the data given in Table 15.9. The numbers in this table for the amount of water needed by the respective cities actually represent the absolute minimum that each city must have. Each city would like to have as much as 2 million additional acre feet beyond this minimum amount. Since the amount of water available exceeds the sum of these minimum amounts by 3.5, nillion acre feet, Metro management has decided to distribute this additional water to the cities as well. The decisions on how much additional water the respective cities will receive beyond meeting their minimum needs will be based on minimizing Metro’s total cost Management wants to know which plan for distributing water from the rivers to the cities will achieve this objective, Formulate and solve a spreadsheet model for this problem. Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.15-62 E* 15.12, ‘The Onenote Co, produces a single product at three plants for four customers. The three plants will produce 60, 80, and 40 units, respectively, during the next week. The firm has made a commitment to sell 40 units to customer 1, 60 units to customer 2, and at least 20 units to customer 3. Both customers 3 and 4 also want to buy as many of the remaining Units as possible. The net profit associated with shipping a unit from plant i for sale to customer is given by the following table: Customer 1 2 3 4 Plant 1 $800 $700 $500 $200 2 500 200 100 300 3 600 +400 300 500 Management wishes to know how many units to sell to customers 3 and 4 and how many Units to ship from each of the plants to each of the customers to maximize profit. Formulate and solve a spreadsheet model for this problem. E* 15.13. The Move-It Company has two plants building forklift trucks that then are shipped to three distribution centers. The production costs are the same at the two plants, and the cost of shipping each truck is shown below for each combination of plant and distribution center: Distribution Center 1 2 3 Plant a $800 $700 $400 8 600 800 500 A total of 60 forklift trucks are produced and shipped per week. Each plant can produce and ship any amount up to a maximum of 50 trucks per week, so there is considerable flexibility on how to divide the total production between the two plants so as to reduce shipping costs. However, each distribution center must receive exactly 20 trucks per week Management's objective is to determine how many forklift trucks should be produced at each plant, and then what the overall shipping pattem should be to minimize total shipping cost. Formulate and solve a spreadsheet model for this problem. E* 15.14. Redo Problem 15.13 when any distribution center may receive any quantity between 10 and 30 forklift trucks per week in order to further reduce total shipping cost, provided only ‘that the total shipped to all three distribution centers must still equal 60 trucks per week, Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.15-63 E* 15.15, ‘The Build-Em-Fast Company has agreed to supply its best customer with three widgits during each of the next three weeks, even though producing them will require some ‘overtime work, The relevant production data are as follows: Maximum Production Production Cost per Unit, Week ____RegulerTime_Overtine_ _Regular Time 1 2 2 $300 2 3 2 500 3 1 2 400 The cost per unit produced with overtime for each week is $100 more than for regular time, ‘The cost of storage is $50 per unit for each week it is stored, There is already an inventory of two widgets on hand currently, but the company does not want to retain any widgets in inventory after the three weeks. Management wants to know how many units should be produced in each week to minimize the total cost of meeting the delivery schedule. Formulate and solve a spreadsheet model for this problem, Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education,Be 15.16. 15-64 The MJK Manufacturing Company must produce two products in sufficient quantity to ‘meet contracted sales in each of the next three months. The two products share the same production facilities, and cach unit of both products requires the same amount of production capacity. The available production and storage facilities are changing month by month, so the production capacities, unit production costs, and unit storage costs vary by month. Therefore, it may be worthwhile to overproduce one or both products in some months and store them until needed For each of the three months, the initialed columns of the following table give the maximum number of units of the two products combined that can be produced on regular ‘time (RT) and on overtime (OT). For each of the two products, the subsequent columns give (1) the number of units needed for the contracted sales, (2) the cost (in thousands of dollars) per unit produced on regular time, (3) the cost (in thousands of dollars) per unit produced on overtime, and (4) the cost (in thousands of dollars) of storing each extra unit that is held over into the next month, In each case, the numbers for the two products are separated by a slash /, with the number for product 1 om the left and the number for product 2 on the right. Product 1/Product 2 ‘Maximum. Unit Cost of Combined Production Production ($1,000s)_Unit Cost of Storage Month RT or Sales RT oT _ (81,0005) 10 3 53 1sn6 1820 m 2 8 2 wf 208 2 3 10 3 44 197 22122 The production manager wants a schedule developed for the number of units of each of the two products to be produced on regular time and, if regular time production capacity is, used up, on overtime in each of the three months. The objective is to minimize the total of the production and storage costs while meeting the contracted sales for each month. There is no initial inventory, and no final inventory is desired after the three months. Formulate and solve a spreadsheet model for this problem. Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.15-65 15.17. Consider the transportation problem having the following data: Unit Cost ($) Destination: 1 2 3 4 ‘Supply Source 1 ? 4 1 4 1 2 4 6 ? 2 1 3 8 s 4 6 1 4 6 7 6 3 1 Demand 1 1 7 1 12 What property ensures that this problem has feasible solutions? ‘A. What property ensures tht this problem has an optimal solution with values of 0 or I for all the shipment amounts? Explain how this problem can be interpreted to be an assignment problem. <4 Draw the network representation ofthis acsigrment problem. E* —_&. Display the problem on a spreadsheet and then use the Excel Solver to obtain an optimal soon 15.18. Consider the assignment problem having the following cost table: Job 1 2 3 Person A ss 7 % 8 3 6 S c 2 3 4 ‘The optimal solution is A-3, B-1, C2, witha total cost of $10. 42. Draw the network representation ofthis problems E* Formulate this problem on a spreadsheet and then use the Excel Solver to obtain the optimal solution sdentifted above Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education,15-66 15.19.* Consider the assignment problem having the following cost table: 15.20, E* 15.21 Task 1 2 3 4 Assignee A $8 $6 $s 7 8 6 5 3 4 c 7 e 4 6 . 6 7 5 6 2 Draw the network epresentaton ofthis assignment problem. E* Formulate this problem on a spreadhect ard then use the Excel Solver to obtain an optimal solution Four cargo ships will be used for shipping goods from one port to four other ports (labeled 1,2, 3,4), Any ship can be used for making any one of these four trips. However, because of differences in the ships and cargoes, the total cost of loading, transporting, and unloading ‘the goods for the different ship-port combinations varies considerably, as shown in the following table: Port 1 2 3 4 Ship 1 $500 $400 $600 $700 2 600 600 700 $00 3 700 500 700 600 4 500 400 600 600 The objective is to assign the four ships to four different ports in such a way as to ‘minimize the total cost for all four shipments. a. Describe how this problem fits into the format for an assignment problem. E* b, Formulate and solve this problem on a spreadsheet. Reconsider Problem 15.6. Suppose that the sales forecasts have been revised downward to 240, 400, and 320 units per day of products 1, 2, and 3, respectively. Thus, each plant now has the capacity to produce all that is required of any one product, Therefore, management has decided that each new product should be assigned to only one plant and that no plant should be assigned more than one product (so that three plants are each to be assigned one product, and two plants are to be assigned none). The objective is to make these assignments so as to minimize the ‘otal cost of producing these amounts of the three products. Formulate and solve a spreadsheet model for this problem. 15.22.* The coach of an age group swim team needs to assign swimmers to a 200-yard medley relay team to send to the Junior Olympics. Since most of his best swimmers are very fast in more than one stroke, it is not clear which swimmer should be assigned to each of the Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.Be 15.23. E* 15.24. 15-67 four strokes. The five fastest swimmers and the best times (in seconds) they have achieved in each of the strokes (for 50 yards) are Stroke carl Chris David Tony Ken Backstroke 377 329 338 370 354 Breaststroke 434 331 422 347 418 Butterfly 333 285 389 304 336 Freestyle 292 264 296 285 31.1 The coach wishes to determine how to assign four swimmers to the four different strokes to minimize the sum of the corresponding best times. 12 Detenbe Bow this problem fit sto the format fora vane of an astgrmert problem ver ‘though Ht doesnot sme cout. What plays the roe of costs? Eh Formulite and solve this problem ona spreadsheet. Reconsider Problem 15.8. Now suppose that trucks (and their drivers) need to be hired to do the hauling, where each truck can only be used once to haul gravel from a single pit to a single site. Enough trucks are available to haul all the gravel that can be purchased at each site. Each truck can haul five tons, and the cost per truck is five times the hauling cost per ton given carlier. Only full trucks are to supply each site. Formulate and solve a spreadsheet model for this problem. Reconsider Problem 15.13. Now distribution centers 1, 2, and 3 must receive exactly 10, 20, and 30 units per week, respectively. For administrative convenience, management has decided that each distribution center will be supplied totally by a single plant, so that one plant will supply one distribution center and the other plant will supply the other two distribution centers. The choice of these assignments of plants to distribution centers is to be made solely on the basis of minimizing total shipping cost. Formulate and solve a spreadsheet model for this problem. Partial Answers to Selected Problems 155. 15.19. 15.22. 3 pints from Harry today, 4 pints from Dick tomorrow. Total cost = $19.50. b. A-2, B-4, C-3, D-1, Total cost = $20. b. David-backstroke, Tony-breaststroke, Chrisbutterfly, Carl-freestyle. Total time = 126.20 seconds. Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.