The Aggregate Production Planning Spreadsheet Assignment (Spring 2016) MGMT 330: Production and Operations Management Production quantities and workforce levels need to be determined for the production of three product families. The demands for each of the product families for the next ten months appear below, along with other important information. Your job is to construct a feasible, low cost, mixed strategy production plan using the template posted on D2L. Your aggregate production plan is due by 11 PM, Friday, April 22nd. It is to be submitted as an Excel file to the course Dropbox on D2L. If you submit your plan by 11 PM, Wednesday, April 13th, it will be graded and returned within three class days. You then have the option to submit a revised plan before the due date. You may work alone or with up to two classmates. (Submit one copy of the solution with the names of the group members.) Demand Family A Family B Family C Family A Family B Family C 1 1,000 20,000 0 Production Rate* 5 8 4 * units/hour 2 4,000 20,000 10,000 Holding Cost $2 $1 $3 3 5,000 20,000 40,000 4 9,000 20,000 10,000 5 13,000 20,000 0 Beginning & Backorder Ending** Cost Inventory $5 3,000 $4 10,000 $6 8,500 ** end of month 10 6 15,000 20,000 50,000 7 8,000 20,000 0 8 0 20,000 10,000 9 0 20,000 40,000 10 5,000 20,000 10,000 Regulartime Hours Available = 160 hours per worker per month Overtime Hours Available = 40 hours per worker per month Regulartime Labor Cost = $5,000 per worker per month Overtime Labor Cost = $40 per hour Hiring Cost = $4,000 per worker Layoff Cost = $2,000 per worker Beginning Workforce Level = 50 workers SCORING: Production Plan Total Cost No Solution Submitted Over $3,300,000 $3,200,001 - $3,300,000 $3,100,001 - $3,200,000 $3,000,001 - $3,100,000 $3,000,000 and lower Points (%) 0 (0%) 28 (70%) 32 (80%) 36 (90%) 40 (100%) 48 (120%) To earn the designated number of points, your production plan must be feasible (capacity is never exceeded and all demand is met by the end of Month 10), the computed cost of your plan must be correct, AND all formulas must be included and correct. More directions will be given in class. Up to four points (10%) will be deducted from your score if your plan is feasible but your computed cost is incorrect. HINT: There are no feasible solutions under $2,900,000. Up to forty points (100%) will be deducted from your score if your formulas are incorrect or missing