ANSWER BY USING SOLVER IN EXCEL

Q1.

. -------------------------------------------------------------------------------------------------------------------------------------------------------

Q2------------------------------------------------------------------------------------------------------------------------------------------------------------

Q3.----------------------------------------------------------------------------------------------------------------------------------------------------------------------

MW is a large discount store that operates seven days per week. The store needs the following number of full-time employees working each day of the week. unay Monday uesday Wednesday Thursday Fiday Saturda 140 Number of Employees 188 112 136 172 212 Each employee must work five consecutive days each week and then have two days off. For example, any employee who works Sunday through Thursday has Friday and Saturday off. The store currently has a total of 200 employees available to work. MW has developed the following set of prioritized goals for employee scheduling 1.The store would like to avoid hiring any additional employees 2.The most important day for the store to be fully staffed is Saturday, followed by Sunday, followed by Friday The store would like to fully staff the remaining four days of the week. a. Use preemptive goal programming to determine MV employee scheduling for the week. b. If each employee costs S800, each missing employee on Saturday costs S600, on Sunday S400, on Friday S300, and on Monday through Thursday $200 determine the new schedule which minimizes these costs (assuming there are no other priorities among the goals). GNO is a retail phone-catalog company that specializes in outdoor clothing and equipment. A phone station at the company will be staffed with either full-time operators of temporary operators eight hours per day Full-time operators, because of their experience and training, process more orders and make fewer mistakes than temporary operators. However, temporary operators are cheaper because of a lower wage rate and they are not paid benefits. A full-time operator can process about 360 orders per week, whereas a temporary operator can process about 270 orders per week. A full-time operator will average 1.1 defective orders per week and a part-time operator will incur about 2.7 defective orders per week. The company wants to limit defective orders to 200 per week. The cost of staffing a station with full-time operators is S610 per week and the cost of a station with part-time operators is $450 per week. Using historical data and forecasting techniques, the company has developed estimates of phone orders for an eight-week period as follows Week Orders19,500 21,00025,600 27,200 33,400 29,800 27,000 31,000 4 The company does not want to hire or dismiss full-time employees after the first week (i.e. the company wants a constant group of full-time operators over the eight-week period). The company wants to determine how many full-time operators it needs and how many temporary operators to hire each week to meet weekly demand while minimizing labor costs a. Formulate a linear programming problem for this problem b. Solve this problem using Excel. GNO 1S going to alter its staffing policy. Instead of hiring a constant group of full-time operators for the entire eight-week planning, it has decided to hire and add full-time operators as the eight-week period progresses, although once it hires full-time operators it will not dismiss them. Reformulate the linear programming to reflect this altered policy and solve to determine the cost savings (if any). c. GCE is ready to award contracts for printing their annual report. For the past several years, the report has been printed by JP and LL. A new firm, BP, has inquired into the possibility of doing a portion of the printing. The quality and service level provided by LL has been extremely high: in fact, only 0.5% of their reports have had to be discarded because of quality problems. JP has also had a high quality level historically, producing an average of only 1% unacceptable reports. Since GCE has had no experience with BP, they have estimated their defective rate to be 10%. GC would like to determine how many reports should be printed by each firm to obtain 75,000 acceptable-quality reports. To ensure that BP will receive some of the contract, management has specified that the number of reports awarded to BP must be at least 10% of the volume given to JP. In addition, the total volume assigned to BPJP, and LL should not exceed 30,000, 50,000, and 50,000 copies respectively. Because of the long-term relationship that has developed with LL, management has also specified that at least 30,000 reports should be awarded to LL. The cost per copy is $2.45 for BP, $2.50 for JP, and $2.75 for LL. Formulate a linear program for determining how many copies should be assigned to each printing firm to minimize the total cost of obtaining 75,000 acceptable-quality reports Solve this problem using Excel. Suppose that the quality level for BP is much better than estimated. How would this change your answer (in "b"). Suppose that management is willing to reconsider the requirement that LL be awarded at least 30,000 reports? How would this change your answer (in "b") a. b. c. d. Suppose you own 11 bronze coins worth a total of $150, 11silver coins worth a total of $160, and 11 gold coins worth a total of $170. Develop a linear programming integer programming model to find a combination of coins worth exactly $110. Submit your spreadsheet and the answer. 3.Amanufacturer can sell product 1 at a profit of $2 per unit and product 2 at a profit of $5 per unit. Three units of raw material are needed to manufacture 1 unit of product 1, and 6 units of raw material are needed to manufacture 1 unit of product 2. A total of 120 units of raw material are available. If any product 1 is produced a setup cost of $10 is incurred; if any product 2 is produced a setup cost of $20 is incurred a. Formulate this problem. b. Solve this problem using Excel. MW is a large discount store that operates seven days per week. The store needs the following number of full-time employees working each day of the week. unay Monday uesday Wednesday Thursday Fiday Saturda 140 Number of Employees 188 112 136 172 212 Each employee must work five consecutive days each week and then have two days off. For example, any employee who works Sunday through Thursday has Friday and Saturday off. The store currently has a total of 200 employees available to work. MW has developed the following set of prioritized goals for employee scheduling 1.The store would like to avoid hiring any additional employees 2.The most important day for the store to be fully staffed is Saturday, followed by Sunday, followed by Friday The store would like to fully staff the remaining four days of the week. a. Use preemptive goal programming to determine MV employee scheduling for the week. b. If each employee costs S800, each missing employee on Saturday costs S600, on Sunday S400, on Friday S300, and on Monday through Thursday $200 determine the new schedule which minimizes these costs (assuming there are no other priorities among the goals). GNO is a retail phone-catalog company that specializes in outdoor clothing and equipment. A phone station at the company will be staffed with either full-time operators of temporary operators eight hours per day Full-time operators, because of their experience and training, process more orders and make fewer mistakes than temporary operators. However, temporary operators are cheaper because of a lower wage rate and they are not paid benefits. A full-time operator can process about 360 orders per week, whereas a temporary operator can process about 270 orders per week. A full-time operator will average 1.1 defective orders per week and a part-time operator will incur about 2.7 defective orders per week. The company wants to limit defective orders to 200 per week. The cost of staffing a station with full-time operators is S610 per week and the cost of a station with part-time operators is $450 per week. Using historical data and forecasting techniques, the company has developed estimates of phone orders for an eight-week period as follows Week Orders19,500 21,00025,600 27,200 33,400 29,800 27,000 31,000 4 The company does not want to hire or dismiss full-time employees after the first week (i.e. the company wants a constant group of full-time operators over the eight-week period). The company wants to determine how many full-time operators it needs and how many temporary operators to hire each week to meet weekly demand while minimizing labor costs a. Formulate a linear programming problem for this problem b. Solve this problem using Excel. GNO 1S going to alter its staffing policy. Instead of hiring a constant group of full-time operators for the entire eight-week planning, it has decided to hire and add full-time operators as the eight-week period progresses, although once it hires full-time operators it will not dismiss them. Reformulate the linear programming to reflect this altered policy and solve to determine the cost savings (if any). c. GCE is ready to award contracts for printing their annual report. For the past several years, the report has been printed by JP and LL. A new firm, BP, has inquired into the possibility of doing a portion of the printing. The quality and service level provided by LL has been extremely high: in fact, only 0.5% of their reports have had to be discarded because of quality problems. JP has also had a high quality level historically, producing an average of only 1% unacceptable reports. Since GCE has had no experience with BP, they have estimated their defective rate to be 10%. GC would like to determine how many reports should be printed by each firm to obtain 75,000 acceptable-quality reports. To ensure that BP will receive some of the contract, management has specified that the number of reports awarded to BP must be at least 10% of the volume given to JP. In addition, the total volume assigned to BPJP, and LL should not exceed 30,000, 50,000, and 50,000 copies respectively. Because of the long-term relationship that has developed with LL, management has also specified that at least 30,000 reports should be awarded to LL. The cost per copy is $2.45 for BP, $2.50 for JP, and $2.75 for LL. Formulate a linear program for determining how many copies should be assigned to each printing firm to minimize the total cost of obtaining 75,000 acceptable-quality reports Solve this problem using Excel. Suppose that the quality level for BP is much better than estimated. How would this change your answer (in "b"). Suppose that management is willing to reconsider the requirement that LL be awarded at least 30,000 reports? How would this change your answer (in "b") a. b. c. d. Suppose you own 11 bronze coins worth a total of $150, 11silver coins worth a total of $160, and 11 gold coins worth a total of $170. Develop a linear programming integer programming model to find a combination of coins worth exactly $110. Submit your spreadsheet and the answer. 3.Amanufacturer can sell product 1 at a profit of $2 per unit and product 2 at a profit of $5 per unit. Three units of raw material are needed to manufacture 1 unit of product 1, and 6 units of raw material are needed to manufacture 1 unit of product 2. A total of 120 units of raw material are available. If any product 1 is produced a setup cost of $10 is incurred; if any product 2 is produced a setup cost of $20 is incurred a. Formulate this problem. b. Solve this problem using Excel