CASE
Green Enterprises produces premium golf clubs. It is in need of a planning tool that will help it make decisions over the next eight quarters. The demand forecast for the upcoming eight quarter period is developed every quarter, and the production manager must determine how many people to hire and lay off and also how much overtime and subcontracting to schedule. Information relevant to the problem is shown. The demand for the upcoming eight quarters is for the following number of sets of clubs, respectively: 1920, 2160, 1440, 1200, 2040, 2400, 1740, and 1500. Clubs can be inventoried from one period to the next, incurring the holding cost. Back orders are also permitted; however, no backordering is allowed at the end of last quarter. The production manager would like a tool to be able to find the plan minimizing the total costs over the next eight quarters.
Cost Data
Regular-time labor cost per hour $12.50
Overtime labor cost per hour $18.75
Subcontracting cost per unit (labor only) $125.00
Back-order cost per unit per quarter $25.00
Inventory holding cost per unit per quarter $10.00
Hiring cost per employee $800.00
Firing cost per employee $500.00
Capacity Data
Beginning workforce: 90 Employees
Beginning inventory: 0 Units
Production standard per unit: 8 hours of labor per unit
Regular time available per period: 160 Hours per period per employee
Overtime available per period: 40 hours per period per employee
1) Provide a detailed Linear Programming formulation for the problem
2) Develop an Excel spreadsheet model and solve it using the appropriate Solver to find the minimal-cost plan.
(solution should involve Simplex LP solver in Excel)