I NEED #34 but #33 IS POSTED BECASUE YOU NEED INFO FROM THAT ONE> PLEASE SOLVE IN EXCEL AND SHOW FORMULAS AND SCREEN SHOT OF SOLVER
33. The Paris Paint Company is in the process of planning labor force requirements and production levels for the next four quarters. The marketing department has provided production with the following forecasts ofdemand for Paris Paint over the next year: Demand Forecast (in thousands of gallons) Quarter 380 630 220 160 Assume that there are currently 280 employees with the company. Employees are hired for at least one full quarter. Hiring costs amount to S1,200 per employee and fir ing costs are S2,500 per employee. Inventory costs are si per gallon per quarter. It is estimated that one worker produces 1,000 gallons ofpaint each quarter. Assume that Paris currently has 80,000 gallons ofpaint in inventory and would like to end the year with an inventory of at least 20,000 gallons. a. Determine the minimum constant workforce plan for Paris Paint and the cost of the plan. Assume that stock-outs are not allowed. b. If Paris were able to back-order excess demand at a cost of S2 per gallon per quar- ter, determine a minimum constant workforce plan that holds less inventory than the plan you found in part (a), but incurs stock-outs in quarter 2. Determine the cost of the new plan. c. Formulate this as a linear program. Assume that stock-outs are not allowed. d. Solve the linearprogram. Roundthe variablesanddeterminethe cost oftheresultingplan. 34. Consider the problem of Paris Paint presented in Problem 33. Suppose that the plant has the capacity to employ a maximum of 370 workers. Suppose that regular-time employee costs are $12.50 per hour. Assume seven-hour days, five-day weeks, and four-week months. Overtime is paid on a time-and-a-half basis. Subcontracting is available at a cost of S7 per gallon of paint produced. Overtime is limited to three hours per employee per day, and no more than 100,000 gallons can be subcontracted in any quarter. a. Formulate as a linear program b. Solve the linear program. Round the variables and determine the cost of the resulting plan. 33. The Paris Paint Company is in the process of planning labor force requirements and production levels for the next four quarters. The marketing department has provided production with the following forecasts ofdemand for Paris Paint over the next year: Demand Forecast (in thousands of gallons) Quarter 380 630 220 160 Assume that there are currently 280 employees with the company. Employees are hired for at least one full quarter. Hiring costs amount to S1,200 per employee and fir ing costs are S2,500 per employee. Inventory costs are si per gallon per quarter. It is estimated that one worker produces 1,000 gallons ofpaint each quarter. Assume that Paris currently has 80,000 gallons ofpaint in inventory and would like to end the year with an inventory of at least 20,000 gallons. a. Determine the minimum constant workforce plan for Paris Paint and the cost of the plan. Assume that stock-outs are not allowed. b. If Paris were able to back-order excess demand at a cost of S2 per gallon per quar- ter, determine a minimum constant workforce plan that holds less inventory than the plan you found in part (a), but incurs stock-outs in quarter 2. Determine the cost of the new plan. c. Formulate this as a linear program. Assume that stock-outs are not allowed. d. Solve the linearprogram. Roundthe variablesanddeterminethe cost oftheresultingplan. 34. Consider the problem of Paris Paint presented in Problem 33. Suppose that the plant has the capacity to employ a maximum of 370 workers. Suppose that regular-time employee costs are $12.50 per hour. Assume seven-hour days, five-day weeks, and four-week months. Overtime is paid on a time-and-a-half basis. Subcontracting is available at a cost of S7 per gallon of paint produced. Overtime is limited to three hours per employee per day, and no more than 100,000 gallons can be subcontracted in any quarter. a. Formulate as a linear program b. Solve the linear program. Round the variables and determine the cost of the resulting plan
