The Aggregate Production Planning Spreadsheet Assignment (Fall 2017)-MGMT 330: Production and Operations Management

The Aggregate Production Planning Spreadsheet Assignment (Fall 2017)-MGMT 330: Production and Operations Management

Production quantities and workforce levels need to be determined for the If you submit your plan by 11 PM, Friday, November 17, it will be graded and

production of three product families. The demands for each of the product returned within three class days. You then have the option to submit a revised

families for the next twelve months appear below, along with other plan before the due date.

important information.

DO NOT change the the layout of the template, including the number of rows

Your job is to construct a feasible, low cost, mixed strategy production plan or columns.

using the Excel template posted on D2L.

You may work alone or with up to two classmates. (Submit one copy of the

Your aggregate production plan is due by 11 PM, Friday, December 1. solution with the names of the group members.)

Submit your plan (with formulas) to the Assignments folder on D2L.

Demand 1 2 3 4 5 6 7 8 9 10 11 12

Family A 4,000 8,000 15,000 8,000 4,000 19,000 4,000 8,000 15,000 8,000 4,000 19,000

Family B 7,000 4,000 2,000 3,000 4,000 5,000 6,000 8,000 9,000 9,000 8,000 7,000

Family C 3,000 4,000 4,000 5,000 5,000 5,000 7,000 7,000 8,000 8,000 8,000 9,000

Production Holding Regulartime Hours Available = 160 hours per worker per month Hiring Cost = $6,500 per worker

Rate* Cost Overtime Hours Available = 40 hours per worker per month Layoff Cost = $4,500 per worker

Family A 2 $4

Family B 5 $1 Regulartime Labor Cost = $5,000 per worker per month Beginning Workforce Level = 35 workers

Family C 4 $2 Overtime Labor Cost = $45 per hour

* units/hour

The company budgets whole labor hours only.

SCORING: Production Plan Total Cost Points (%) To earn the designated number of points, your Three points (10%) will be deducted from

No Solution Submitted 0 (0%) production plan must be feasible (capacity is never your score if your plan is feasible but your

Over $3,400,000 18 (60%) exceeded and the inventory levels are never computed cost is incorrect.

$3,300,001 – $3,400,000 24 (80%) negative), the computed cost of your plan must be

$3,200,001 – $3,300,000 27 (90%) correct, AND all formulas must be included and Up to thirty points (100%) will be deducted

$3,100,001 – $3,200,000 30 (100%) correct. More directions will be given in class. from your score if your formulas are incorrect

$3,100,000 and lower 36 (120%) or missing.

HINT: There are no feasible solutions under $3,000,000.