108 CHAPTER 3 LINEAR PROGRAMMING MODELING APPLICATIONS WITH COMPUTER ANALYSES IN EXCEL. decided to invest no...

Transcribed Image Text:

108 CHAPTER 3 LINEAR PROGRAMMING MODELING APPLICATIONS WITH COMPUTER ANALYSES IN EXCEL. decided to invest no more than 25% of her invest- ment in T-bills. She wishes to have experience investing in differ- ent types of instruments, so she will invest at least 10% of her money in each of the six types of invest- ment choices. What is the optimal investment straf- egy for her to follow? 3-16 A couple has agreed to attend a "casino night" as part of a fundraiser for the local hospital, but they believe that gambling is generally a losing proposi tion. For the sake of the charity, they have decided to attend and to allocate $300 for the games. There are to be four games, each involving standard decks of cards. The first game, called Jack in 52, is won by selecting a Jack of a specific suit from the deck. The probability of actually doing this is, of course, 1 in 52 (0.0192), Gamblers may place a bet of $1, $2, or $4 on this game. If they win, the payouts are $12 for a $1 bet, $24.55 for a $2 bet, and $49 for a $4 bet. The second game, called Red Face in 52, is won by selecting from the dock a red face card (ie., red Jack, red Queen, or red King). The probability of t winning is 6 in 52 (-0.1154). Again, bets may be placed in denominations of $1, S2, and $4. Payouts are $8.10, $16.35, and $32.50, respectively. The third game, called Face in 52, is won by selecting one of the 12 face cards from the deck. The probability of winning is 12 in 52 (0.2308). Pay- outs are $4, $8.15, and $16 for $1, $2, and $4 bots. The last game, called Red in 52, is won by selecting a red card from the deck. The probability of winning is 26 in 52 (0.50). Payouts are $1.80, $3.80, and $7.50 for $1, $2, and $4 bets. Given that they can calculate the expected return (or, more appropriately, loss) for each type of game and level of wager, they have decided to see if they can minimize their total expected loss by planning their evening using LP. For example, the expected return from a $1 bet in the game Jack in 52 is equal to $0.2308 ($12 x 1/52 + $0 x 51/52). Since the amount bet is $1, the expected loss is equal to $0.7692 ($1$0.2308). All other expected losses can be calculated in a similar manner. They want to appear to be sociable and not as if they are trying to lose as little as possible. Therefore, they will place at least 20 bets (of any value) on each of the four games. Further, they will spend at least $26 an $1 bets, at least $50 on $2 bets, and at least $72 on $4 bets. They will bet no more than (and no less than) the agreed-upon $300. What should be their gambling plan, and what is their expected loss for the evening? 3-17 A hospital emergency room is open 24 hours a day. Nurses report for duty at 1 A.M., 5 A.M., 9 A.M., 1 P.M., 5 P.M., or 9 P.M., and each works an 8-hour shift. Nurses are paid the same, regardless of the shift they work. The following table shows the mini mum number of nurses needed during the six per ods into which the day is divided. How should the hospital schedule the nurses so that the total staff required for one day's operation is minimized? SHIFT 1 2 3 4 5 6 TIME 1-5 A.M. 5-9 A.M. 9AM-1 PM. 1-5 P.M. 5-9 P.M. 9PM-1AM SHIFT A B NURSES NEEDED 4 13 17 10 (3-18 A narsing home employs attendants who are needed around the clock. Each attendant is paid the same, regardless of when his or her shift begins. Each shift is 8 consecutive hours. Shifts begin at 6 A.M., 10 A.M., 2 PM, 6 P.M., 10 P.M., and 2 A.M. The fol lowing table shows the nursing home's requirements for the numbers of attendants to be on duty during specific time periods. TIME 2-6 A.M. 6-10 A.M. C 10 A.M. 2 P.M. D 2-6 P.M. 6-10PM. 10PM-2 AN B F 12 5 NUMBER OF ATTENDANTS 8 27 12 23 29 23 (a) What is the minimum number of atten- dants needed to satisfy the nursing home's requirements? (b) The nursing home would like to use the same. number of attendants determined in part (a) but would now like to minimize the total salary paid. Attendants are paid $16 per hour during 8 A.M.-8 P.M., and a 25% premium per hour dur- ing 8 P.M.-8 A.M. How should the attendants now be scheduled? 3-19 A hospital is moving from 8-hour shifts for its lab techs to 12-hour shifts. Instead of working five i 8-hour days, the lab techs would work three days on and four days off in the first week followed by four days on and three days off in the second week, for a total of 84 hours every two weeks. Because the peak demand times in the hospital appear to be between 5 A.M. and 7 A.M. and between 5 P.M. and 7 P.M., four 12-hour shifts will be arranged according to the table at the top of p. 109. SHIFTS A and A (al) B and B (alt) C and C (alt) D and D (all) WORK TIMES Agents Ecoded SAM-5PM. 7 A.M.-7.F.M. 5.P.M.-5 A.M. 7PM-7 A.M. Labtechs needed The shift pay differentials are based on the most and least desirable times to begin and end work. In any one week, techs on shift A might work Sunday through Tuesday, while techs on shift A (alt) would work at the same times but on Wednesday through Saturday. In the following week, techs on shift A would work Sanday through Wednesday, while techs on shift A (alt) would work the corresponding Thursday through Saturday. Therefore, the same number of techs would be sched- uled for shift A as for shift A (alt). PAY RATE/ WEEK The requirements for lab techs during the 24-hour day are shown in the following table. What is the most economical schedule for the lab techs? 5AM 7 A.M. 12 $756 $840 6AM 9AM 9 A.M. NOON 12 20 $882 $924 7AM SPM 5 P.M. 7 P.M. 8 14 3-20 An airline with operations in San Diego, Califor nia, must staff its ticket counters inside the airport. Ticket attendants work 6-hour shifts at the coun ter. There are two types of agents: those who speak English as a first language and those who are fully bilingual (English and Spanish). The requirements for the number of agents depend on the numbers of people expected to pass through the airline's ticket counters during various hours. The airline believes that the need for agents between the hours of 6 A.M. and 9 P.M. are as follows: 7PM NOON- 3PM- 3 P.M. 6 P.M. 16 24 5 A.M. 10 6 P.M.- 9 P.M. 12 Agents begin work either st 6 A.M., 9 A.M., DOCE, or 3 P.M. The shifts are designated as shifts A, B, C, and D, respectively. It is the policy of the airline that at least half of the agents needed in any time period will speak English as the first language. Further, at least one-quarter of the agents needed in any time period should be fully bilingual. (a) How many and what type of agents should be hired for each shift to meet the language and staffing requirements for the airline, so that the total number of agents is minimized? 3-21 A small trucking company is determining the com- position of its next tracking job. The load master has his choice of seven different types of cargo, which may be loaded in full or in part. The specifications of the cargo types are shown in the following table. The goal is to maximize the amount of freight, in terms of dollars, for the trip. The truck can hold up to 900 pounds of cargo in a 2,500-cubic-foot space. What cargo should be loaded, and what will be the total freight changed? CARGO FREIGHT TYPE PER POUND A B C D PROBLEMS 109 (b) What is the optimal hiring plan from a cost per spective of English-speaking agents are paid $25 per hour and bilingual agents are paid $29 per hour? Does the total number of agents needed change from that computed in purt (a)? E F G CARGO TYPE A B с D E $8.00 $6.00 $3.50 $5.75 $9.50 $5.25 $8.60 TONS AVAILABLE 970 850 VOLUME PER POUND (Cu.FT.) 1,900 2,300 3,600 3.0 2.7 6.3 8.4 5.5 4.9 3.1 3-22 The load master for a freighter wants to determine the mix of cargo to be carried on the next trip. The ship's volume limit for cargo is 100,000 - bic meters, and its weight capacity is 2,310 tons. The master has five different types of cargo from which to select and wishes to maximize the value of the selected shipment. However, to make sure that none of his customers are ignored, the load master would like to make sure that at least 20% of each cargo's available weight is selected. The specifications for the five cargoes are shown in the following table. VALUE PER TON 7 $1,350 $1,675 $1,145 $ 850 $1,340 POUNDS AVAILABLE 210 150 90 120 130 340 250 VOLUME PER TON (CU. M.) 25 54 28 37 3-23 A cargo transport plane is to be loaded to maximize the revenue from the load carried. The plane may carry any combination and any amount of cargoes A, B, and C. The relevant values for these cargoes are shown in the table at the top of p. 110. 110 CHAPTER 3 LINEAR PROGRAMMING MODELING APPLICATIONS WITH COMPUTER ANALYSES IN EXCEL CARGO TYPE A B С COMPARTMENT Right fore Right center Right aft Left fore Left center TONS AVAILABLE Left aft 10 12 17 The plane can carry as many as 32 tons of cargo. The plane is subdivided into compartments, and there are weight and volume limitations for each compartment. It is critical for safety reasons that the weight ratios be strictly observed. The requirements for cargo distribution are shown in the following table. CROP Com Tomato Potato Okra Available REVENUE PER TON MAXIMUM VOLUME (Cu, FT) 16,000 50 40 20,000 46 48 14,000 10,000 $700 $725 $685 20,000 12,000 VOLUME PER TON (CU.FT.) 2,000 3,500 3,000 COMPARTMENT WEIGHT/TOTAL WEIGHT RATIO Which cargoes should be carried, and how should they be allocated to the various compartments? 3-24 The owner of a private freighter is trying to decide which cargo he should carry on his next trip. He has two choices of cargo, which he can agree to carry in any combination. He may carry up to 15 tons of cargo A, which takes up 675 cubic feet per ton and cars revenue of $85 per ton. Or, he may carry up to 54 tons of cargo B, with a volume of 450 cubic feet per ton and revenue of $79 per ton. The freighter is divided into two holds, starboard and port. The starboard hold has a volume of 14,000 Must equal 18% of total weight loaded Must equal 25% of total weight loaded Must equal 7% of total weight loaded Must equal 18% of total weight loaded Mustequal 25% of total weight loaded Mustequal 7% of total weight loaded $55 $85 $57 $52 Table for Problem 3-25 YIELD REVENUE PLANTING TENDING HARVEST WATER FERTILIZER (BUSHELS/ACHE) BUSHEL (HOURS/ACRE) (HOURS/ACRE) (HOURS/ACHE) (UNITS/ACRE) (POUNDS/ACRE) 50 10 15 12 cubic feet and a weight capacity of 26 tons. The pat hold has a volume of 15,400 cubic feet and a weight capacity of 32 tons. For steering reasons, it is neces sary that the weight be distributed equally between the two sides of the freighter. However, the freight engines and captain's bridge, which together weight 6 tons, are on the starboard side of the freighter. This means that the poet side is usually loaded with 6 tons more cargo to equalize the weight. The owner may carry any combination of the two cargoes in the same hold without a problem. How should this freighter be loaded to maximize total revenue? 3-25 A farmer is making plans for next year's crop. Het is considering planting com, tomatoes, potatoes and okra. The data he has collected, along with the availability of resources, are shown in the table at the bottom of this page. He can plant as many as 60 acres of land. Determine the best mix of crops to maximize the farm's revenue. 18 775 3-26 The farmer in Problem 3-25 has an opportunity to take over the neighboring 80-acre farm. If he acquires this farm, he will be able to increase the amounts of time available to 1,600 hours for planting, 825 hours for trading, and 1,400 hours for harvest- ing. Between the two farms, there are 510 units of water and 6,000 pounds of fertilizer available. How- ever, the neighboring farm has not been cultivated in a while. Therefore, each acre of this farm will take an additional 4 hours to plant and an additional 2 hours to tend. Because of the condition of the new farm, the farmer expects the yields per acre planted there to be only 46 bushels, 37 bushels, 42 bush els, and 45 bushels, respectively, for com, tomato, potato, and okra. In order to make sure that both. farms are used effectively, the farmer would like at least 80% of each farm's acreage to be planted. What is the best combination of crops to plant at each farm in order to maximize revenue? 3-27 A family farming concern owns five parcels of farmland broken into a southeast sector, north sec tor, northwest sector, west sector, and southwest sector. The farm concern is involved primarily in growing wheat, alfalfa, and barley crops and is cur- rently preparing the production plan for next year. 2 8 2 12 550 6 20 9 20 775 25 3.0 2.0 3.0 300 45 35 2,500 Table for Problem 3-31 NUTRIENT Protein (%) Put (%) Fiber (%) Cost($/b) BEEF 16.9 26.0 29.0 0.52 NUTRIENT A B C D E Costilb The usage of each element is limited to 5,000 tons, and the total usage of all three elements is lim ited to 10,000 tons. Further, due to the relatively uncertain demand for alloy Z, the company would like to ensure that Z. constitutes no more than 30% of the total quantity of the three alloys produced. Determine the mix of the three elements that will maximize profit under these conditions. Table for Problem 3-32 PORK 12.0 4.1 3-31 An animal feed company is developing a new puppy food. Their natritionists have specified that the mixture must contain the following components by weight: at least 16% protein, 13% fat, and no more than 15% fiber. The percentages of each nutrient in the available ingredicats, along with their cost per pound, are shown in the table at the top of this page. What is the mixture that will have the mini- mum cost per pound and meet the stated nutritional requirements? 3-32) A boarding stable feeds and houses work horses used to pull tourist-filled carriages through the streets of a historic city. The stable owner wishes to strike a balance between a healthy nutritional standard for the horses and the daily cost of food. This type of horse mast consume exactly 5 pounds of feed per day. The food mixes available are an oat product, a highly enriched grain, and a mineral product. Each of these mixes contains a predictable amount of five ingredients needed daily to keep the average horse healthy. The table at the bottom of this page shows these minimum requirements, units of each nutrient per pound of feed mix, and costs for the three mixes. 8.3 0.49 OAT (UNITS/LB.) 2,0 0.5 3.0 1.0 0.5 $0.33 CORN 8.5 3.8 INGREDIENT LAMB 15.4 6.3 2,4 0,40 2.7 0.20 FEED MIX GRAIN (UNITS/LB.) 3.0 1.0 5.0 15 0.5 $0.44 RICE 8.5 3.8 2.7 0.17 Formulate this problem and solve for the optimal daily mix of the three foods. CHICKEN 18.0 17.9 28.8 3-33 Clint Hanks has decided to try a new diet that prom ises enhanced muscle tone if the daily intake of five essential nutrients is tightly controlled. After extensive research, Clint has determined that the recommended daily requirements of these nutrients for a person of his age, beight, weight, and activity level are as fol lows: between 69 grams and 100 grams of protein, at least 700 milligrams of phosphorus, at least 420 mil ligrams of magnesium, between 1,000 milligrams and 1,750 milligrams of calcium, and at least 8 milligrens of iron. Given his limited finances, Clint has identified seven inexpensive food items that he can use to meet these requirements. The cost per serving for each food item and its contribution to each of the five nutrients are given in the table at the top of p. 113. (a) Use LP to identify the lowest cost combination of food items that Clint should use for his diet. (b) Would you characterize your solution in (a) as a well-balanced diet? Explain your answer. 3-34 A steel company is producing steel for a new contract. The contract specifies the information in the follow- ing table for the steel. MINERAL (UNITS/LB.) 0.39 MATERIAL MINIMUM 2.10% Manganese Silicon 4.30% Carbon 1.05% 10 05 6.0 20 1.5 90.57 The steel company atches of eight differ ent available materials to produce each ton of steel NEEDED (UNITS/DAY) 6 2 9 8 5 MAXIMUM 3.10% 6.30% 2.05% SCREENSHOT 4-11B Solver Sensitivity Report for Good-to-Go Suitcase Company Microsoft Excel 14.0 Sensitivity Report Problems P4.22823. Good-to-Go Suitcase Company Variable Cella Cell Name SBS4 Solution value Standard SCS4 Solution valoa Dalco $0$4 Salution value Luxury Constraints Coll Name SE$10 Cutting & Coloring SES11 Assembly SES12 Finishing SE$13 Quality & Packaging (a) What is the optimal production plan? Which of the resources are scarce? (b) Suppose Good-to-Go is considering including a polishing process, the cost of which would be added directly to the price. Each Standard suit- case would require 10 minutes of time in this treatment, each Deluxe suitcase would need 15 minutes, and each Luxury suitcase would need 20 minutes. Would the current production plan change as a result of this additional process if 170 hours of polishing time were available? Explain your answer. (c) Now consider the addition of a waterproofing process where each Standard suitcase would use 1 hour of time in the process, each Deluxe suitcase would need 1.5 hours, and each Luxury suitcase would require 1.75 hours. Would this change the production plan if 900 hours were available? Why or why not? Source: Professors Mark and Judith McKnew, Clemson University. 4-23 Suppose Good-to-Go (Problem 4-22) is considering the possible introduction of two new products to its line of suitcases: the Compact model (for teenager) and the Kiddo model (for children). Market research suggests that Good-to-Go can sell the Compact model for no more than $30, whereas the Kiddo model would go for as much as $37.50 to specialty toy stores. The amount of labor and the cost of raw mate rials for each possible new product are as follows COST CATEGORY Cutting and coloring (hr.) Assembly (r.) Finishing (hr.) Quality and packaging (hr.) Raw materials COMPACT KIDDO 1.20 0.50 0.75 0.75 0.75 0.50 0.20 0.20 $5.00 $4.50 Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 3.50 640.00 252.00 0.00 0.00 4.00 -1.12 630.00 480.00 708.00 117.00 10.00 9.00 9.50 4.38 0.00 6.94 0.00 1.12 Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 2.55 1.61 1E+30 630 12.30 600 1E+30 708 182.00 135 1E+30 134.40 120.00 128.00 18.00 Use a pricing out strategy to check if either model would be economically attractive to make. 4-24 The Strollers-to-Go Company makes lightweight umbrella-type strollors for three different groups of children. The TiniTote is designed specifically for newborns who require extra neck support. The ToddleTote is for toddlers up to 30 pounds. Finally, the company produces a beavy-duty model called TubbyTote, which is designed to carry children up to 60 pounds. The stroller company is in the process of determining its production for each of the three types of strollers for the upcoming planning period. The marketing department has forecast the fol- lowing maximum demand for each of the strollers during the planning period: TiniTote 180, Tubby Tote 70, and ToddleTote 160. Strollers-to-Go sells TiniTotes for $63.75, Tubby Totes for $82.50, and Toddle Totes for $66. As a matter of policy, it wants to produce no less than 50% of the forecast demand for each product. It also wants to keep production of Toddle Totes to a maximum of 40% of total stroller production The production department has estimated that the material costs for TiniTote, Tubby Tote, and ToddleTate strollers will be $4, $6, and $5.50 per unit, respectively. The strollers are processed through fabrication, sewing, and assembly workstations. The metal and plastic frames are made in the fabrication station. The fabric seats are cut and stitched together in the sewing station. Finally, the frames are put to- gether with the seats in the assembly station. In the upcoming planning period, there will be 620 hours available in fabrication, where the direct labor cost is $8.25 per hour. The sewing station has 500 hours available, and the direct labor cost is $8.50 per hour. The assembly station has 480 hours available, and the direct labor cost is $8.75 per hour. The standard processing rate for TiniTotes is 3 hours in fabrication, 2 hours in sewing, and 154 CHAPTER 4 LINEAR PROGRAMMING SENSITIVITY ANALYSIS SCREENSHOT 4-12A Excel Layout for Strollers-to-Go Company A 1 Strollers-to-Go Company 2 3 4 Solution value 5 Selling price per unit 6 Mutarial cost per unit SCREENSHOT 4-12B Solver Sensitivity Report for Strollers-to-Go Company 7 Labor cost per unit B Profit 9 Constraints 10 Fabrication 11 Sewing 12 Assembly 13 Tinitota demand 14 Tubbytote demand 15 Toddletela demand 16 Toddletoto mx pred ratio 17 Tinitate min prod 18 Tubbytote min prod 19 Toddelate min prod 20 1 hour in assembly. TubbyTotes require 4 hours in fabrication, 1 hour in sewing and 3 hours in assem bly, whereas ToddleTotes require 2 hours in each station. BCL D Timote TubbyTate ToddleTota 100.00 $63.75 $4.00 The Excel layout and LP Sensitivity Report for Strollers-to-Go's problem are shown in Screenshots 4-12A and 4-12B, respectively. Each of the follow- ing questions is independent of the others. (a) How many strollers of each type should Strollers-to-Go make? What is the profit? Which constraints are binding? (b) How much labor time is being used in the fabri- cation, sewing, and assembly areas? 550.50 $9.25 Name 3.0 20 1.0 1.0 -0.4 1.0 Name Cell SB54 Solution value Tinilate SCS4 Solution value Tubby Tote SDS4 Solution value TaddlaTote Constraints Cell SE510 Fabrication SE$11 Sewing SE312 Assembly SES13 Tinitoto demand 35.00 $82.501 $6.00 $57.75 $8.75 SE$14 Tubbytote domand SES15 Toddletate demand SES16 Toddlatate max prod ratio SE$17 Tinitote min prod SES18 Tubbytote min prod SES19 Teddiatate min prod 4.0 1.0 3.0 1.0 -0.4 35.00 90.00 Microsoft Excel 14.0 Sensitivity Report Problems 4.24to27. Strollers-to-Go Company Variable Calls 100.00 35.00 90.00 0.00 1.0 100.00 35.00 90.00 90.00 $66.00 $15,202.50 $5.50 $1,105.00 $61.00 $12,011.25 $9.50 $2,005 25 E 20 620.00 201 415.00 20 386.00 100.00 1.0 35.00 90.00 m 0.5000 100.00 0.00 3.85 0.00 4.10 0.00 FIGH 35.00 > 1.0 90.00 LIS Coet 620 8.25 500 $8.50 480 $8.75 180 70 160 (e) How much would Strollers-to-Go be willing to pay for an additional hour of fabrication time? For an additional hour of sewing time? 425 Consider the Strollers-to-Go production problem (Problem 4-24). (a) Over what range of costs could the TiniTote materials vary and the current production plan (d) Is Strollers-to-Go producing any product at its maximum sales level? Is it producing any prod uct at its minimum level? 0 90 35 Source: Professors Mark and Judith McKaew, Clemson University Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 100.00 0.00 0.00 0.00 ap Sign RHS 0.00 90,00 35.00 80.00 Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 620.00 3.60 620.00 110.50 43.33 0.00 500.CO 1E+30 85.00 415.00 385.00 0.00 480.00 1E+30 0.00 180.00 1E430 0.00 70.00 1E+30 160.00 16:30 13.00 10.00 8.13 10.00 1+30 9.25 5.00 8.75 4.10 9.50 1E 30 3.33 1E+30 3.33 95.00 80.00 35.00 70.00 8.67 1E+30 35.00 remain optimal? (Hint: How are material costs reflected in the problem formulation?) (b) Suppose that Strollers-to-Go decided to pol- ish each stroller prior to shipping. The process is fast and would require 10, 15, and 12 min- utes, respectively, for TiniTote, TubbyTote, and Toddle Tote strollers. Would this change the current production plan if 48 hours of polishing time were available? 4-26 Consider the Strollers-to-Go production problem (Problem 4-24) (a) Suppose that Strollers-to-Go could purchase ad- ditional fabrication time at a cost of $10.50 per hour. Should it be interested? Why or why not? What is the most that it would be willing to pay for an additional hour of fabrication time? (b) Suppose that Strollers-to-Go could only pur- chase fabrication time in multiples of 40-hour bundles. How many bundles should it be willing to purchase then? (4-27) Suppose that Strollers-to-Go (Problem 4-24) is considering the production of TwinTotes for fami- lies who are doubly blessed. Each TwinTote would require $7.10 in materials, 4 hours of fabrication time, 2 hours of sewing time, and 2 hours to assem ble. Would this product be economically attractive to manufacture if the sales price were $86? Why or why not? 4-28 The Classic Furniture Company is trying to deter- mine the optimal quantities to make of six possible products: tables and chairs made of oak, cherry, and pine. The products are to be made using the follow- ing resources: labor hours and three types of wood. Minimum production requirements are as follows: at least 3 each of oak and cherry tables, at least 10 each of oak and cherry chairs, and at least 5 pine chairs. SCREENSHOT 4-13A Excel Layout for Classic Furniture Company 3 4 Numbar of units 5 Profe 6 Constraints 7 Labor hours BOak (pounds) 9 Cherry (pounds) 10 Pine (pounds) 11 Min oak tables 12 Min cherry tables 13 Min oak chairs 14 Min cherry chairs 15 Min pine chais 16 DISCUSSION QUESTIONS AND PROBLEMS 155 The Excel layout and LP Sensitivity Report for Classic Furniture's problem are shown in Screen- shots 4-13A and 4-13B, respectively. The objective function coefficients in the Screenshots refer to unit profit per item. Each of the following questions is independent of the others. (a) What is the profit represented by the objective function, and what is the production plan? (b) Which constraints are binding? (c) What is the range over which the unit profit for oak chairs can change without changing the production plan? 1 (d) What is the range over which the amount of available oak could range without changing the combination of binding constraints? 1 (e) Does this Sensitivity Report indicate the pres- ence of multiple optima? How do you know? (f) After production is over, how many pounds of cherry wood will be left over? (g) According to this report, how many more chairs were made than were required? 4-29 Consider the Classic Parniture product mix problem (Problem 4-28). For each of the following situations, what would be the impact on the production plan and profit? If it is possible to compute the new profit or production plan, do so. A BCDE 1 Classic Furniture Company 2 1 (a) The unit profit for oak tables increases to $83. (b) The unit profit for pine chairs decreases to $13. (c) The unit profit for pine tables increases by $20. (d) The unit profit for cherry tables decreases to $85. (e) The company is required to make at least 20 pine chairs. (f) The company is required to make no more than 55 cherry chairs. 4-30 Consider the Classic Furniture product mix problem (Problem 4-28). For each of the following situations, Oak Oak Cherry Cherry Pine Pine tables chairs tablas chairs tables chairs 3.00 51.67 3.00 85.56 42.26 33.00 $75 $35 $90 $60 $45 $20 $10,000.00 7.5 3.5 9.0 6.0 4.5 20 30 200 240 36 F G 1 180 27 H 1 1000.00 1,000 2,150 2150.00 3800.00 3.000 8500.00 8,500 3.00 >= 3.00 61.67 85.56 >= 33.00 J LHS E 10 10 Sign Rats 156 CHAPTER 4 LINEAR PROGRAMMING SENSITIVITY ANALYSIS SCREENSHOT 4-138 Solver Sensitivity Report for Classic Furniture Company Microsoft Excel 14.0 Sensitivity Report Problems 4-20to32. Classic Furniture Company Variable Cello Call Name $854 Number of units Oak tables SCS4 Number of units Osk chais SO54 Number of units Cheny tables SESA Number of units Cherry chairs SF54 Number of units Pine tables SGS4 Number of units Pine chairs Constraints Cell SHS7 Labor hours |SHB |Dak {pounda) SHS9 Cherry (pounds) SH510 Pina (pounda SHS11 Min oak tables SHS12 Min chary tablea SHS13 Min oak chaire SHS14 Min chany chairs SHS 16 Min pine chairs Name what would be the impact on the production plan and profit? If it is possible to compute the new profit. or production plan, do so. (a) The number of labor hours expands to 1,320. (b) The amount of cherry wood increases to 3,900. (c) The number of labur hours decreases to 950. (d) The company does not have a minimum require- ment for cherry chairs. 4-31 Consider the Classic Furniture product mix problem (Problem 4-28). For each of the following situations, what would be the impact on the production plan and profit? If it is possible to compute the new profit or production plan, do so. (a) OPCs for oak tables and cherry tables each decreases by $15. (b) OFCs for oak tables and oak chairs are reversed. (c) OFCs for pine tables and pine chairs are reversed. (d) OFC increases by $20 while at the same time the OFC ca decreases by $10. (e) Unit profits for all three types of chairs are increased by $6 each. 4-32 Consider the Classic Furniture product mix problem (Problem 4-28). In answering each of the following questions, be as specific as possible. If it is possible to compute a new profit or production plan, do so. (a) A part-time employee who works 20 hours per week decided to quit his job. How would this affect the profit and production plan? (b) Classic has been approached by the factory next door, CabinetsRUs, which has a shortage of both labor and oak. CabinetsRUs proposes to take one full-time employee (who works 30 hours) plus Final Reduced Objective Allowable Allowable Valve Cost Coefficient Increase Decrease 3.00 0.00 51.67 0.00 75.00 0.00 35.00 1E+30 0.00 90.00 0.00 60.00 1E+30 3.00 85,56 0.00 42.25 0.00 33.09 0.00 3.00 3.00 51.67 85.66 33.08 45.00 20.00 88.33 3.01 3.00 10.00 10.00 5.00 0.00 1E+30 0.00 Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 1009.09 10.00 1000.00 373.30 2150.00 0.00 2150,00 318.93 3800.00 8500.00 0.00 3000.00 223.25 0.00 8500,00 1488.33 0.00 6.25 0.00 11.33 0.00 41.67 1E430 0.00 75.56 1E+30 0.00 28.06 1E+30 1E+30 0.00 0.00 13.26 37.21 1250.00 2239.78 5039.50 2.35 1.20 900 pounds of oak. It has offered $560 as com pensation. Should Classic make this trade? (c) Classic is considering adding a new product, a cherry urmoire. The armoire would consume 200 pounds of cherry wood and take 16 hours of labor. Cherry wood costs $9 per pound, and labor costs $12 per hour. The armoire would sell for $2,180. Should this product be made? (d) What would happen to the solution if a con- straint were added to make sure that for every table made, at least two matching chairs were made? 4-33) The Tiger Catering Company is trying to determine the most economical combination of sandwiches to make for a tennis club. The club has asked Tiger to provide 70 sandwiches in a variety to include tuna, tuna and cheese, ham, ham and cheese, and cheese. The club has specified a minimum of 10 each of tuna and ham and 12 each of tuna/cheese and ham cheese. Tiger makes the sandwiches using the fol lowing resources: bread, tuna, ham, cheese, mayon- naise, mustard, lettuce, tomato, packaging material, and labor hours. The Excel layout and LP Sensitivity Report for Tiger Catering's problem are shown in Screenshots 4-14A and 4-14B, respectively. The objective func tion coefficients in the screenshots refer to unit cost per item. Each of the following questions is indepen- dent of the others. (a) What is the optimal cost represented by the objec tive function and what is the optimal sandwich- making plan? (b) Which constraints are binding? SCREENSHOT 4-14A Excel Layout for Tiger Catering Company B C D 1 Tiger Catering Company 2 SCREENSHOT 4-148 Solver Sensitivity Report for Tiger Catering Company NO 3 Tuna Tuna/Ch Ham Ham/Ch Cheese 4 Number to make 10.00 30.00 10.00 12.00 8.00 5 Cost $2.42 $2.12 $3.35 $3.02 6 Constraints 7 Bread (slices) 2 2 B Tuna (az.) 9 Ham (az) 10 Cheese (z.) 11 Mayo (z.) 12 Mustard (or) 13 Leuce (az) 14 Tomato (az) 15 Package (unit) 16 Labor (hra 17 Min total 18 Ma Tuta 19 Min Tuna'Ch 20 Min Ham 21 Min HaCh 22 2 4 1 1.2 0.9 0.25 0.5 1 0.00 1 (c) What is the range over which the cost for cheese sandwiches could vary without changing the production plan? Constraints (4) What is the range over which the quantity of tuna could vary without changing the combina- tion of binding constraints? 2 3 0.25 0.5 1 0.00 1 Name Cell S057 Bread (slicea) 5358 Tuna (oz.) $359 Han (az.) SGS10 Cheese (oz.) $G$11 Maya (az.) $G$12 Mustard (oz.) $G$13 Lettuce (oz) $G$14 Tomato (oz) SGS15 Package (unit) SGS16 Labor (hrs) SGS17 Min total $G$10 Min Tuna $G$19 Min Tuns/Ch $G$20 Min Ham SGS21 Min Ham/Ch Cell Name SUS4 Number to make Tuna SCS4 Number to make Tuna/Ch SDS4 Number to make Ham SE34 Number to make Hanch SFS4 Number to make Cheese 1 4 0.6 0,2 0.25 0.5 1 0.06 1 Microsoft Excel 14.0 Sensitivity Report Problems 4.33to37. Tiger Catering Company Variable Calls 10.00 30,00 10,00 EF G HI 12.00 8.00 3 1 4.5 0.2 0.25 0.5 35.00 70.00 5.60 70.00 10.00 30,00 10.00 12.00 DISCUSSION QUESTIONS AND PROBLEMS 157 1 0.08 1 0.00 0.00 $2.36 $176.42 0.00 0.00 0.00 love 0.00 -0.00 0.00 0.00 0.00 2 4 0.5 0,00 0.00 0.00 0.00 0.00 2.36 0.38 0.00 0.99 0.66 0.25 0.5 -8. 1 0.08 1 140.00 130.00 76.00 274.00 54.00 4.40< 4-34 Consider the Tiger Catering problem (Problem 4-33). For each of the following situations, what would be (e) Does this Sensitivity Report indicate the presence of multiple optimal solutions? How do you know? (f) After the sandwiches are made, how many labor hours remain? Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 17.50 36.00 70.00 5.60 70.00 10.00 30.00 10.00 12.00 LHS Sign RHS 140 130 100 888N NJE 3.36 3.02 2.36 20 140.00 130.00 100,00 80.00 2.42 1E+30 2.12 T2 40 70.00 10.00 12.00 10.00 12.00 1 Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 140.00 130.00 76.00 74.00 54.00 4.40 17.50 72 8 70 10 12 0.38 0.24 1E+30 0.99 0.56 0.24 1E+30 24,00 1+30 72.00 8.00 20.00 40.00 72.00 1E+30 8.00 1E+30 1E+30 1E430 0.66 1E+30 1E+30 1E+30 1E+30 1E+30 0.00 13.50 18.00 6.00 8.00 0.00 6.00 24.00 6.00 18.00 3.60 250 5.00 2.00 2.40 8.00 10.00 1E+30 1.50 2,00 108 CHAPTER 3 LINEAR PROGRAMMING MODELING APPLICATIONS WITH COMPUTER ANALYSES IN EXCEL. decided to invest no more than 25% of her invest- ment in T-bills. She wishes to have experience investing in differ- ent types of instruments, so she will invest at least 10% of her money in each of the six types of invest- ment choices. What is the optimal investment straf- egy for her to follow? 3-16 A couple has agreed to attend a "casino night" as part of a fundraiser for the local hospital, but they believe that gambling is generally a losing proposi tion. For the sake of the charity, they have decided to attend and to allocate $300 for the games. There are to be four games, each involving standard decks of cards. The first game, called Jack in 52, is won by selecting a Jack of a specific suit from the deck. The probability of actually doing this is, of course, 1 in 52 (0.0192), Gamblers may place a bet of $1, $2, or $4 on this game. If they win, the payouts are $12 for a $1 bet, $24.55 for a $2 bet, and $49 for a $4 bet. The second game, called Red Face in 52, is won by selecting from the dock a red face card (ie., red Jack, red Queen, or red King). The probability of t winning is 6 in 52 (-0.1154). Again, bets may be placed in denominations of $1, S2, and $4. Payouts are $8.10, $16.35, and $32.50, respectively. The third game, called Face in 52, is won by selecting one of the 12 face cards from the deck. The probability of winning is 12 in 52 (0.2308). Pay- outs are $4, $8.15, and $16 for $1, $2, and $4 bots. The last game, called Red in 52, is won by selecting a red card from the deck. The probability of winning is 26 in 52 (0.50). Payouts are $1.80, $3.80, and $7.50 for $1, $2, and $4 bets. Given that they can calculate the expected return (or, more appropriately, loss) for each type of game and level of wager, they have decided to see if they can minimize their total expected loss by planning their evening using LP. For example, the expected return from a $1 bet in the game Jack in 52 is equal to $0.2308 ($12 x 1/52 + $0 x 51/52). Since the amount bet is $1, the expected loss is equal to $0.7692 ($1$0.2308). All other expected losses can be calculated in a similar manner. They want to appear to be sociable and not as if they are trying to lose as little as possible. Therefore, they will place at least 20 bets (of any value) on each of the four games. Further, they will spend at least $26 an $1 bets, at least $50 on $2 bets, and at least $72 on $4 bets. They will bet no more than (and no less than) the agreed-upon $300. What should be their gambling plan, and what is their expected loss for the evening? 3-17 A hospital emergency room is open 24 hours a day. Nurses report for duty at 1 A.M., 5 A.M., 9 A.M., 1 P.M., 5 P.M., or 9 P.M., and each works an 8-hour shift. Nurses are paid the same, regardless of the shift they work. The following table shows the mini mum number of nurses needed during the six per ods into which the day is divided. How should the hospital schedule the nurses so that the total staff required for one day's operation is minimized? SHIFT 1 2 3 4 5 6 TIME 1-5 A.M. 5-9 A.M. 9AM-1 PM. 1-5 P.M. 5-9 P.M. 9PM-1AM SHIFT A B NURSES NEEDED 4 13 17 10 (3-18 A narsing home employs attendants who are needed around the clock. Each attendant is paid the same, regardless of when his or her shift begins. Each shift is 8 consecutive hours. Shifts begin at 6 A.M., 10 A.M., 2 PM, 6 P.M., 10 P.M., and 2 A.M. The fol lowing table shows the nursing home's requirements for the numbers of attendants to be on duty during specific time periods. TIME 2-6 A.M. 6-10 A.M. C 10 A.M. 2 P.M. D 2-6 P.M. 6-10PM. 10PM-2 AN B F 12 5 NUMBER OF ATTENDANTS 8 27 12 23 29 23 (a) What is the minimum number of atten- dants needed to satisfy the nursing home's requirements? (b) The nursing home would like to use the same. number of attendants determined in part (a) but would now like to minimize the total salary paid. Attendants are paid $16 per hour during 8 A.M.-8 P.M., and a 25% premium per hour dur- ing 8 P.M.-8 A.M. How should the attendants now be scheduled? 3-19 A hospital is moving from 8-hour shifts for its lab techs to 12-hour shifts. Instead of working five i 8-hour days, the lab techs would work three days on and four days off in the first week followed by four days on and three days off in the second week, for a total of 84 hours every two weeks. Because the peak demand times in the hospital appear to be between 5 A.M. and 7 A.M. and between 5 P.M. and 7 P.M., four 12-hour shifts will be arranged according to the table at the top of p. 109. SHIFTS A and A (al) B and B (alt) C and C (alt) D and D (all) WORK TIMES Agents Ecoded SAM-5PM. 7 A.M.-7.F.M. 5.P.M.-5 A.M. 7PM-7 A.M. Labtechs needed The shift pay differentials are based on the most and least desirable times to begin and end work. In any one week, techs on shift A might work Sunday through Tuesday, while techs on shift A (alt) would work at the same times but on Wednesday through Saturday. In the following week, techs on shift A would work Sanday through Wednesday, while techs on shift A (alt) would work the corresponding Thursday through Saturday. Therefore, the same number of techs would be sched- uled for shift A as for shift A (alt). PAY RATE/ WEEK The requirements for lab techs during the 24-hour day are shown in the following table. What is the most economical schedule for the lab techs? 5AM 7 A.M. 12 $756 $840 6AM 9AM 9 A.M. NOON 12 20 $882 $924 7AM SPM 5 P.M. 7 P.M. 8 14 3-20 An airline with operations in San Diego, Califor nia, must staff its ticket counters inside the airport. Ticket attendants work 6-hour shifts at the coun ter. There are two types of agents: those who speak English as a first language and those who are fully bilingual (English and Spanish). The requirements for the number of agents depend on the numbers of people expected to pass through the airline's ticket counters during various hours. The airline believes that the need for agents between the hours of 6 A.M. and 9 P.M. are as follows: 7PM NOON- 3PM- 3 P.M. 6 P.M. 16 24 5 A.M. 10 6 P.M.- 9 P.M. 12 Agents begin work either st 6 A.M., 9 A.M., DOCE, or 3 P.M. The shifts are designated as shifts A, B, C, and D, respectively. It is the policy of the airline that at least half of the agents needed in any time period will speak English as the first language. Further, at least one-quarter of the agents needed in any time period should be fully bilingual. (a) How many and what type of agents should be hired for each shift to meet the language and staffing requirements for the airline, so that the total number of agents is minimized? 3-21 A small trucking company is determining the com- position of its next tracking job. The load master has his choice of seven different types of cargo, which may be loaded in full or in part. The specifications of the cargo types are shown in the following table. The goal is to maximize the amount of freight, in terms of dollars, for the trip. The truck can hold up to 900 pounds of cargo in a 2,500-cubic-foot space. What cargo should be loaded, and what will be the total freight changed? CARGO FREIGHT TYPE PER POUND A B C D PROBLEMS 109 (b) What is the optimal hiring plan from a cost per spective of English-speaking agents are paid $25 per hour and bilingual agents are paid $29 per hour? Does the total number of agents needed change from that computed in purt (a)? E F G CARGO TYPE A B с D E $8.00 $6.00 $3.50 $5.75 $9.50 $5.25 $8.60 TONS AVAILABLE 970 850 VOLUME PER POUND (Cu.FT.) 1,900 2,300 3,600 3.0 2.7 6.3 8.4 5.5 4.9 3.1 3-22 The load master for a freighter wants to determine the mix of cargo to be carried on the next trip. The ship's volume limit for cargo is 100,000 - bic meters, and its weight capacity is 2,310 tons. The master has five different types of cargo from which to select and wishes to maximize the value of the selected shipment. However, to make sure that none of his customers are ignored, the load master would like to make sure that at least 20% of each cargo's available weight is selected. The specifications for the five cargoes are shown in the following table. VALUE PER TON 7 $1,350 $1,675 $1,145 $ 850 $1,340 POUNDS AVAILABLE 210 150 90 120 130 340 250 VOLUME PER TON (CU. M.) 25 54 28 37 3-23 A cargo transport plane is to be loaded to maximize the revenue from the load carried. The plane may carry any combination and any amount of cargoes A, B, and C. The relevant values for these cargoes are shown in the table at the top of p. 110. 110 CHAPTER 3 LINEAR PROGRAMMING MODELING APPLICATIONS WITH COMPUTER ANALYSES IN EXCEL CARGO TYPE A B С COMPARTMENT Right fore Right center Right aft Left fore Left center TONS AVAILABLE Left aft 10 12 17 The plane can carry as many as 32 tons of cargo. The plane is subdivided into compartments, and there are weight and volume limitations for each compartment. It is critical for safety reasons that the weight ratios be strictly observed. The requirements for cargo distribution are shown in the following table. CROP Com Tomato Potato Okra Available REVENUE PER TON MAXIMUM VOLUME (Cu, FT) 16,000 50 40 20,000 46 48 14,000 10,000 $700 $725 $685 20,000 12,000 VOLUME PER TON (CU.FT.) 2,000 3,500 3,000 COMPARTMENT WEIGHT/TOTAL WEIGHT RATIO Which cargoes should be carried, and how should they be allocated to the various compartments? 3-24 The owner of a private freighter is trying to decide which cargo he should carry on his next trip. He has two choices of cargo, which he can agree to carry in any combination. He may carry up to 15 tons of cargo A, which takes up 675 cubic feet per ton and cars revenue of $85 per ton. Or, he may carry up to 54 tons of cargo B, with a volume of 450 cubic feet per ton and revenue of $79 per ton. The freighter is divided into two holds, starboard and port. The starboard hold has a volume of 14,000 Must equal 18% of total weight loaded Must equal 25% of total weight loaded Must equal 7% of total weight loaded Must equal 18% of total weight loaded Mustequal 25% of total weight loaded Mustequal 7% of total weight loaded $55 $85 $57 $52 Table for Problem 3-25 YIELD REVENUE PLANTING TENDING HARVEST WATER FERTILIZER (BUSHELS/ACHE) BUSHEL (HOURS/ACRE) (HOURS/ACRE) (HOURS/ACHE) (UNITS/ACRE) (POUNDS/ACRE) 50 10 15 12 cubic feet and a weight capacity of 26 tons. The pat hold has a volume of 15,400 cubic feet and a weight capacity of 32 tons. For steering reasons, it is neces sary that the weight be distributed equally between the two sides of the freighter. However, the freight engines and captain's bridge, which together weight 6 tons, are on the starboard side of the freighter. This means that the poet side is usually loaded with 6 tons more cargo to equalize the weight. The owner may carry any combination of the two cargoes in the same hold without a problem. How should this freighter be loaded to maximize total revenue? 3-25 A farmer is making plans for next year's crop. Het is considering planting com, tomatoes, potatoes and okra. The data he has collected, along with the availability of resources, are shown in the table at the bottom of this page. He can plant as many as 60 acres of land. Determine the best mix of crops to maximize the farm's revenue. 18 775 3-26 The farmer in Problem 3-25 has an opportunity to take over the neighboring 80-acre farm. If he acquires this farm, he will be able to increase the amounts of time available to 1,600 hours for planting, 825 hours for trading, and 1,400 hours for harvest- ing. Between the two farms, there are 510 units of water and 6,000 pounds of fertilizer available. How- ever, the neighboring farm has not been cultivated in a while. Therefore, each acre of this farm will take an additional 4 hours to plant and an additional 2 hours to tend. Because of the condition of the new farm, the farmer expects the yields per acre planted there to be only 46 bushels, 37 bushels, 42 bush els, and 45 bushels, respectively, for com, tomato, potato, and okra. In order to make sure that both. farms are used effectively, the farmer would like at least 80% of each farm's acreage to be planted. What is the best combination of crops to plant at each farm in order to maximize revenue? 3-27 A family farming concern owns five parcels of farmland broken into a southeast sector, north sec tor, northwest sector, west sector, and southwest sector. The farm concern is involved primarily in growing wheat, alfalfa, and barley crops and is cur- rently preparing the production plan for next year. 2 8 2 12 550 6 20 9 20 775 25 3.0 2.0 3.0 300 45 35 2,500 Table for Problem 3-31 NUTRIENT Protein (%) Put (%) Fiber (%) Cost($/b) BEEF 16.9 26.0 29.0 0.52 NUTRIENT A B C D E Costilb The usage of each element is limited to 5,000 tons, and the total usage of all three elements is lim ited to 10,000 tons. Further, due to the relatively uncertain demand for alloy Z, the company would like to ensure that Z. constitutes no more than 30% of the total quantity of the three alloys produced. Determine the mix of the three elements that will maximize profit under these conditions. Table for Problem 3-32 PORK 12.0 4.1 3-31 An animal feed company is developing a new puppy food. Their natritionists have specified that the mixture must contain the following components by weight: at least 16% protein, 13% fat, and no more than 15% fiber. The percentages of each nutrient in the available ingredicats, along with their cost per pound, are shown in the table at the top of this page. What is the mixture that will have the mini- mum cost per pound and meet the stated nutritional requirements? 3-32) A boarding stable feeds and houses work horses used to pull tourist-filled carriages through the streets of a historic city. The stable owner wishes to strike a balance between a healthy nutritional standard for the horses and the daily cost of food. This type of horse mast consume exactly 5 pounds of feed per day. The food mixes available are an oat product, a highly enriched grain, and a mineral product. Each of these mixes contains a predictable amount of five ingredients needed daily to keep the average horse healthy. The table at the bottom of this page shows these minimum requirements, units of each nutrient per pound of feed mix, and costs for the three mixes. 8.3 0.49 OAT (UNITS/LB.) 2,0 0.5 3.0 1.0 0.5 $0.33 CORN 8.5 3.8 INGREDIENT LAMB 15.4 6.3 2,4 0,40 2.7 0.20 FEED MIX GRAIN (UNITS/LB.) 3.0 1.0 5.0 15 0.5 $0.44 RICE 8.5 3.8 2.7 0.17 Formulate this problem and solve for the optimal daily mix of the three foods. CHICKEN 18.0 17.9 28.8 3-33 Clint Hanks has decided to try a new diet that prom ises enhanced muscle tone if the daily intake of five essential nutrients is tightly controlled. After extensive research, Clint has determined that the recommended daily requirements of these nutrients for a person of his age, beight, weight, and activity level are as fol lows: between 69 grams and 100 grams of protein, at least 700 milligrams of phosphorus, at least 420 mil ligrams of magnesium, between 1,000 milligrams and 1,750 milligrams of calcium, and at least 8 milligrens of iron. Given his limited finances, Clint has identified seven inexpensive food items that he can use to meet these requirements. The cost per serving for each food item and its contribution to each of the five nutrients are given in the table at the top of p. 113. (a) Use LP to identify the lowest cost combination of food items that Clint should use for his diet. (b) Would you characterize your solution in (a) as a well-balanced diet? Explain your answer. 3-34 A steel company is producing steel for a new contract. The contract specifies the information in the follow- ing table for the steel. MINERAL (UNITS/LB.) 0.39 MATERIAL MINIMUM 2.10% Manganese Silicon 4.30% Carbon 1.05% 10 05 6.0 20 1.5 90.57 The steel company atches of eight differ ent available materials to produce each ton of steel NEEDED (UNITS/DAY) 6 2 9 8 5 MAXIMUM 3.10% 6.30% 2.05% SCREENSHOT 4-11B Solver Sensitivity Report for Good-to-Go Suitcase Company Microsoft Excel 14.0 Sensitivity Report Problems P4.22823. Good-to-Go Suitcase Company Variable Cella Cell Name SBS4 Solution value Standard SCS4 Solution valoa Dalco $0$4 Salution value Luxury Constraints Coll Name SE$10 Cutting & Coloring SES11 Assembly SES12 Finishing SE$13 Quality & Packaging (a) What is the optimal production plan? Which of the resources are scarce? (b) Suppose Good-to-Go is considering including a polishing process, the cost of which would be added directly to the price. Each Standard suit- case would require 10 minutes of time in this treatment, each Deluxe suitcase would need 15 minutes, and each Luxury suitcase would need 20 minutes. Would the current production plan change as a result of this additional process if 170 hours of polishing time were available? Explain your answer. (c) Now consider the addition of a waterproofing process where each Standard suitcase would use 1 hour of time in the process, each Deluxe suitcase would need 1.5 hours, and each Luxury suitcase would require 1.75 hours. Would this change the production plan if 900 hours were available? Why or why not? Source: Professors Mark and Judith McKnew, Clemson University. 4-23 Suppose Good-to-Go (Problem 4-22) is considering the possible introduction of two new products to its line of suitcases: the Compact model (for teenager) and the Kiddo model (for children). Market research suggests that Good-to-Go can sell the Compact model for no more than $30, whereas the Kiddo model would go for as much as $37.50 to specialty toy stores. The amount of labor and the cost of raw mate rials for each possible new product are as follows COST CATEGORY Cutting and coloring (hr.) Assembly (r.) Finishing (hr.) Quality and packaging (hr.) Raw materials COMPACT KIDDO 1.20 0.50 0.75 0.75 0.75 0.50 0.20 0.20 $5.00 $4.50 Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 3.50 640.00 252.00 0.00 0.00 4.00 -1.12 630.00 480.00 708.00 117.00 10.00 9.00 9.50 4.38 0.00 6.94 0.00 1.12 Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 2.55 1.61 1E+30 630 12.30 600 1E+30 708 182.00 135 1E+30 134.40 120.00 128.00 18.00 Use a pricing out strategy to check if either model would be economically attractive to make. 4-24 The Strollers-to-Go Company makes lightweight umbrella-type strollors for three different groups of children. The TiniTote is designed specifically for newborns who require extra neck support. The ToddleTote is for toddlers up to 30 pounds. Finally, the company produces a beavy-duty model called TubbyTote, which is designed to carry children up to 60 pounds. The stroller company is in the process of determining its production for each of the three types of strollers for the upcoming planning period. The marketing department has forecast the fol- lowing maximum demand for each of the strollers during the planning period: TiniTote 180, Tubby Tote 70, and ToddleTote 160. Strollers-to-Go sells TiniTotes for $63.75, Tubby Totes for $82.50, and Toddle Totes for $66. As a matter of policy, it wants to produce no less than 50% of the forecast demand for each product. It also wants to keep production of Toddle Totes to a maximum of 40% of total stroller production The production department has estimated that the material costs for TiniTote, Tubby Tote, and ToddleTate strollers will be $4, $6, and $5.50 per unit, respectively. The strollers are processed through fabrication, sewing, and assembly workstations. The metal and plastic frames are made in the fabrication station. The fabric seats are cut and stitched together in the sewing station. Finally, the frames are put to- gether with the seats in the assembly station. In the upcoming planning period, there will be 620 hours available in fabrication, where the direct labor cost is $8.25 per hour. The sewing station has 500 hours available, and the direct labor cost is $8.50 per hour. The assembly station has 480 hours available, and the direct labor cost is $8.75 per hour. The standard processing rate for TiniTotes is 3 hours in fabrication, 2 hours in sewing, and 154 CHAPTER 4 LINEAR PROGRAMMING SENSITIVITY ANALYSIS SCREENSHOT 4-12A Excel Layout for Strollers-to-Go Company A 1 Strollers-to-Go Company 2 3 4 Solution value 5 Selling price per unit 6 Mutarial cost per unit SCREENSHOT 4-12B Solver Sensitivity Report for Strollers-to-Go Company 7 Labor cost per unit B Profit 9 Constraints 10 Fabrication 11 Sewing 12 Assembly 13 Tinitota demand 14 Tubbytote demand 15 Toddletela demand 16 Toddletoto mx pred ratio 17 Tinitate min prod 18 Tubbytote min prod 19 Toddelate min prod 20 1 hour in assembly. TubbyTotes require 4 hours in fabrication, 1 hour in sewing and 3 hours in assem bly, whereas ToddleTotes require 2 hours in each station. BCL D Timote TubbyTate ToddleTota 100.00 $63.75 $4.00 The Excel layout and LP Sensitivity Report for Strollers-to-Go's problem are shown in Screenshots 4-12A and 4-12B, respectively. Each of the follow- ing questions is independent of the others. (a) How many strollers of each type should Strollers-to-Go make? What is the profit? Which constraints are binding? (b) How much labor time is being used in the fabri- cation, sewing, and assembly areas? 550.50 $9.25 Name 3.0 20 1.0 1.0 -0.4 1.0 Name Cell SB54 Solution value Tinilate SCS4 Solution value Tubby Tote SDS4 Solution value TaddlaTote Constraints Cell SE510 Fabrication SE$11 Sewing SE312 Assembly SES13 Tinitoto demand 35.00 $82.501 $6.00 $57.75 $8.75 SE$14 Tubbytote domand SES15 Toddletate demand SES16 Toddlatate max prod ratio SE$17 Tinitote min prod SES18 Tubbytote min prod SES19 Teddiatate min prod 4.0 1.0 3.0 1.0 -0.4 35.00 90.00 Microsoft Excel 14.0 Sensitivity Report Problems 4.24to27. Strollers-to-Go Company Variable Calls 100.00 35.00 90.00 0.00 1.0 100.00 35.00 90.00 90.00 $66.00 $15,202.50 $5.50 $1,105.00 $61.00 $12,011.25 $9.50 $2,005 25 E 20 620.00 201 415.00 20 386.00 100.00 1.0 35.00 90.00 m 0.5000 100.00 0.00 3.85 0.00 4.10 0.00 FIGH 35.00 > 1.0 90.00 LIS Coet 620 8.25 500 $8.50 480 $8.75 180 70 160 (e) How much would Strollers-to-Go be willing to pay for an additional hour of fabrication time? For an additional hour of sewing time? 425 Consider the Strollers-to-Go production problem (Problem 4-24). (a) Over what range of costs could the TiniTote materials vary and the current production plan (d) Is Strollers-to-Go producing any product at its maximum sales level? Is it producing any prod uct at its minimum level? 0 90 35 Source: Professors Mark and Judith McKaew, Clemson University Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 100.00 0.00 0.00 0.00 ap Sign RHS 0.00 90,00 35.00 80.00 Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 620.00 3.60 620.00 110.50 43.33 0.00 500.CO 1E+30 85.00 415.00 385.00 0.00 480.00 1E+30 0.00 180.00 1E430 0.00 70.00 1E+30 160.00 16:30 13.00 10.00 8.13 10.00 1+30 9.25 5.00 8.75 4.10 9.50 1E 30 3.33 1E+30 3.33 95.00 80.00 35.00 70.00 8.67 1E+30 35.00 remain optimal? (Hint: How are material costs reflected in the problem formulation?) (b) Suppose that Strollers-to-Go decided to pol- ish each stroller prior to shipping. The process is fast and would require 10, 15, and 12 min- utes, respectively, for TiniTote, TubbyTote, and Toddle Tote strollers. Would this change the current production plan if 48 hours of polishing time were available? 4-26 Consider the Strollers-to-Go production problem (Problem 4-24) (a) Suppose that Strollers-to-Go could purchase ad- ditional fabrication time at a cost of $10.50 per hour. Should it be interested? Why or why not? What is the most that it would be willing to pay for an additional hour of fabrication time? (b) Suppose that Strollers-to-Go could only pur- chase fabrication time in multiples of 40-hour bundles. How many bundles should it be willing to purchase then? (4-27) Suppose that Strollers-to-Go (Problem 4-24) is considering the production of TwinTotes for fami- lies who are doubly blessed. Each TwinTote would require $7.10 in materials, 4 hours of fabrication time, 2 hours of sewing time, and 2 hours to assem ble. Would this product be economically attractive to manufacture if the sales price were $86? Why or why not? 4-28 The Classic Furniture Company is trying to deter- mine the optimal quantities to make of six possible products: tables and chairs made of oak, cherry, and pine. The products are to be made using the follow- ing resources: labor hours and three types of wood. Minimum production requirements are as follows: at least 3 each of oak and cherry tables, at least 10 each of oak and cherry chairs, and at least 5 pine chairs. SCREENSHOT 4-13A Excel Layout for Classic Furniture Company 3 4 Numbar of units 5 Profe 6 Constraints 7 Labor hours BOak (pounds) 9 Cherry (pounds) 10 Pine (pounds) 11 Min oak tables 12 Min cherry tables 13 Min oak chairs 14 Min cherry chairs 15 Min pine chais 16 DISCUSSION QUESTIONS AND PROBLEMS 155 The Excel layout and LP Sensitivity Report for Classic Furniture's problem are shown in Screen- shots 4-13A and 4-13B, respectively. The objective function coefficients in the Screenshots refer to unit profit per item. Each of the following questions is independent of the others. (a) What is the profit represented by the objective function, and what is the production plan? (b) Which constraints are binding? (c) What is the range over which the unit profit for oak chairs can change without changing the production plan? 1 (d) What is the range over which the amount of available oak could range without changing the combination of binding constraints? 1 (e) Does this Sensitivity Report indicate the pres- ence of multiple optima? How do you know? (f) After production is over, how many pounds of cherry wood will be left over? (g) According to this report, how many more chairs were made than were required? 4-29 Consider the Classic Parniture product mix problem (Problem 4-28). For each of the following situations, what would be the impact on the production plan and profit? If it is possible to compute the new profit or production plan, do so. A BCDE 1 Classic Furniture Company 2 1 (a) The unit profit for oak tables increases to $83. (b) The unit profit for pine chairs decreases to $13. (c) The unit profit for pine tables increases by $20. (d) The unit profit for cherry tables decreases to $85. (e) The company is required to make at least 20 pine chairs. (f) The company is required to make no more than 55 cherry chairs. 4-30 Consider the Classic Furniture product mix problem (Problem 4-28). For each of the following situations, Oak Oak Cherry Cherry Pine Pine tables chairs tablas chairs tables chairs 3.00 51.67 3.00 85.56 42.26 33.00 $75 $35 $90 $60 $45 $20 $10,000.00 7.5 3.5 9.0 6.0 4.5 20 30 200 240 36 F G 1 180 27 H 1 1000.00 1,000 2,150 2150.00 3800.00 3.000 8500.00 8,500 3.00 >= 3.00 61.67 85.56 >= 33.00 J LHS E 10 10 Sign Rats 156 CHAPTER 4 LINEAR PROGRAMMING SENSITIVITY ANALYSIS SCREENSHOT 4-138 Solver Sensitivity Report for Classic Furniture Company Microsoft Excel 14.0 Sensitivity Report Problems 4-20to32. Classic Furniture Company Variable Cello Call Name $854 Number of units Oak tables SCS4 Number of units Osk chais SO54 Number of units Cheny tables SESA Number of units Cherry chairs SF54 Number of units Pine tables SGS4 Number of units Pine chairs Constraints Cell SHS7 Labor hours |SHB |Dak {pounda) SHS9 Cherry (pounds) SH510 Pina (pounda SHS11 Min oak tables SHS12 Min chary tablea SHS13 Min oak chaire SHS14 Min chany chairs SHS 16 Min pine chairs Name what would be the impact on the production plan and profit? If it is possible to compute the new profit. or production plan, do so. (a) The number of labor hours expands to 1,320. (b) The amount of cherry wood increases to 3,900. (c) The number of labur hours decreases to 950. (d) The company does not have a minimum require- ment for cherry chairs. 4-31 Consider the Classic Furniture product mix problem (Problem 4-28). For each of the following situations, what would be the impact on the production plan and profit? If it is possible to compute the new profit or production plan, do so. (a) OPCs for oak tables and cherry tables each decreases by $15. (b) OFCs for oak tables and oak chairs are reversed. (c) OFCs for pine tables and pine chairs are reversed. (d) OFC increases by $20 while at the same time the OFC ca decreases by $10. (e) Unit profits for all three types of chairs are increased by $6 each. 4-32 Consider the Classic Furniture product mix problem (Problem 4-28). In answering each of the following questions, be as specific as possible. If it is possible to compute a new profit or production plan, do so. (a) A part-time employee who works 20 hours per week decided to quit his job. How would this affect the profit and production plan? (b) Classic has been approached by the factory next door, CabinetsRUs, which has a shortage of both labor and oak. CabinetsRUs proposes to take one full-time employee (who works 30 hours) plus Final Reduced Objective Allowable Allowable Valve Cost Coefficient Increase Decrease 3.00 0.00 51.67 0.00 75.00 0.00 35.00 1E+30 0.00 90.00 0.00 60.00 1E+30 3.00 85,56 0.00 42.25 0.00 33.09 0.00 3.00 3.00 51.67 85.66 33.08 45.00 20.00 88.33 3.01 3.00 10.00 10.00 5.00 0.00 1E+30 0.00 Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 1009.09 10.00 1000.00 373.30 2150.00 0.00 2150,00 318.93 3800.00 8500.00 0.00 3000.00 223.25 0.00 8500,00 1488.33 0.00 6.25 0.00 11.33 0.00 41.67 1E430 0.00 75.56 1E+30 0.00 28.06 1E+30 1E+30 0.00 0.00 13.26 37.21 1250.00 2239.78 5039.50 2.35 1.20 900 pounds of oak. It has offered $560 as com pensation. Should Classic make this trade? (c) Classic is considering adding a new product, a cherry urmoire. The armoire would consume 200 pounds of cherry wood and take 16 hours of labor. Cherry wood costs $9 per pound, and labor costs $12 per hour. The armoire would sell for $2,180. Should this product be made? (d) What would happen to the solution if a con- straint were added to make sure that for every table made, at least two matching chairs were made? 4-33) The Tiger Catering Company is trying to determine the most economical combination of sandwiches to make for a tennis club. The club has asked Tiger to provide 70 sandwiches in a variety to include tuna, tuna and cheese, ham, ham and cheese, and cheese. The club has specified a minimum of 10 each of tuna and ham and 12 each of tuna/cheese and ham cheese. Tiger makes the sandwiches using the fol lowing resources: bread, tuna, ham, cheese, mayon- naise, mustard, lettuce, tomato, packaging material, and labor hours. The Excel layout and LP Sensitivity Report for Tiger Catering's problem are shown in Screenshots 4-14A and 4-14B, respectively. The objective func tion coefficients in the screenshots refer to unit cost per item. Each of the following questions is indepen- dent of the others. (a) What is the optimal cost represented by the objec tive function and what is the optimal sandwich- making plan? (b) Which constraints are binding? SCREENSHOT 4-14A Excel Layout for Tiger Catering Company B C D 1 Tiger Catering Company 2 SCREENSHOT 4-148 Solver Sensitivity Report for Tiger Catering Company NO 3 Tuna Tuna/Ch Ham Ham/Ch Cheese 4 Number to make 10.00 30.00 10.00 12.00 8.00 5 Cost $2.42 $2.12 $3.35 $3.02 6 Constraints 7 Bread (slices) 2 2 B Tuna (az.) 9 Ham (az) 10 Cheese (z.) 11 Mayo (z.) 12 Mustard (or) 13 Leuce (az) 14 Tomato (az) 15 Package (unit) 16 Labor (hra 17 Min total 18 Ma Tuta 19 Min Tuna'Ch 20 Min Ham 21 Min HaCh 22 2 4 1 1.2 0.9 0.25 0.5 1 0.00 1 (c) What is the range over which the cost for cheese sandwiches could vary without changing the production plan? Constraints (4) What is the range over which the quantity of tuna could vary without changing the combina- tion of binding constraints? 2 3 0.25 0.5 1 0.00 1 Name Cell S057 Bread (slicea) 5358 Tuna (oz.) $359 Han (az.) SGS10 Cheese (oz.) $G$11 Maya (az.) $G$12 Mustard (oz.) $G$13 Lettuce (oz) $G$14 Tomato (oz) SGS15 Package (unit) SGS16 Labor (hrs) SGS17 Min total $G$10 Min Tuna $G$19 Min Tuns/Ch $G$20 Min Ham SGS21 Min Ham/Ch Cell Name SUS4 Number to make Tuna SCS4 Number to make Tuna/Ch SDS4 Number to make Ham SE34 Number to make Hanch SFS4 Number to make Cheese 1 4 0.6 0,2 0.25 0.5 1 0.06 1 Microsoft Excel 14.0 Sensitivity Report Problems 4.33to37. Tiger Catering Company Variable Calls 10.00 30,00 10,00 EF G HI 12.00 8.00 3 1 4.5 0.2 0.25 0.5 35.00 70.00 5.60 70.00 10.00 30,00 10.00 12.00 DISCUSSION QUESTIONS AND PROBLEMS 157 1 0.08 1 0.00 0.00 $2.36 $176.42 0.00 0.00 0.00 love 0.00 -0.00 0.00 0.00 0.00 2 4 0.5 0,00 0.00 0.00 0.00 0.00 2.36 0.38 0.00 0.99 0.66 0.25 0.5 -8. 1 0.08 1 140.00 130.00 76.00 274.00 54.00 4.40< 4-34 Consider the Tiger Catering problem (Problem 4-33). For each of the following situations, what would be (e) Does this Sensitivity Report indicate the presence of multiple optimal solutions? How do you know? (f) After the sandwiches are made, how many labor hours remain? Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 17.50 36.00 70.00 5.60 70.00 10.00 30.00 10.00 12.00 LHS Sign RHS 140 130 100 888N NJE 3.36 3.02 2.36 20 140.00 130.00 100,00 80.00 2.42 1E+30 2.12 T2 40 70.00 10.00 12.00 10.00 12.00 1 Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 140.00 130.00 76.00 74.00 54.00 4.40 17.50 72 8 70 10 12 0.38 0.24 1E+30 0.99 0.56 0.24 1E+30 24,00 1+30 72.00 8.00 20.00 40.00 72.00 1E+30 8.00 1E+30 1E+30 1E430 0.66 1E+30 1E+30 1E+30 1E+30 1E+30 0.00 13.50 18.00 6.00 8.00 0.00 6.00 24.00 6.00 18.00 3.60 250 5.00 2.00 2.40 8.00 10.00 1E+30 1.50 2,00