A company produces a variety of flash drives and uses sales dollars as its aggregate production measure. The company is planning its workforce and production levels over the next year. The demand is estimated as dollar sales for the next six months is given. Month Workdays Demand
January 23 350,000
February 22 390,000
March 24 230,000
April 25 130,000
May 23 480,000
June 22 600,000
The workforce level is 500 workers at the beginning of current year and the inventories will amount to $100,000. The firm would like to have at least $80,000 of inventory at the end of June. The inventory holding costs are based on a 25% annual interest charge. It was observed that each worker accounted for an average of $50,000 of production per year that consists of 250 working days in the previous year. The cost of hiring is $300 per worker, and the cost of firing is $500 per worker if this industry.
a) Formulate the problem with all variables, an objective function, and all required constraints as a linear programming (LP) problem.
b) Solve for the optimal solution using Excel Solver Simplex LP. Submit your Excel solution.