The Barcelona 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 of demand for Thomas Lee over the next four quarters: 380000, 630000, 220000 and 160000. Assume that there are currently 300 employees with the company. Employees are hired for at least one full quarter. Hiring costs amount to $1200 per employee and firing costs are $2500 per employee. Inventory costs are one dollar per gallon per quarter. It is estimated that one worker produces 1000 gallons of paint each quarter. Assume that Barcelona Paints currently has 80000 gallons of paint in inventory and would like to end the year with an inventory of at least 20000 gallons. 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 $7 per gallon of paint produced. Overtime is limited to three hours per employee per day, and no more than 120000 gallons can be subcontracted in any quarter. Barcelona Paints would like to determine a policy that meets the demand, and the cost of that policy.
(a) Formulate a linear program to solve this problem. Define all problem variables and include whatever constraints are necessary.
(b) Solve the linear program.
Need this solved on Excel!