You have been asked to build the aggregate planning schedule for your factory for the next six months and to determine the best option.

1. Use the Excel OM Aggregate Planning spreadsheet and the data to prepare your aggregate plan. Produce a graph of your plan. The intent is to use a level strategy (or level scheduling) with no overtime, no safety stock, and no subcontractors.

2. In the plan produced in Step 1, the production rate did not meet the total demand. If you were able to use overtime to meet the shortfall, what would your aggregate plan look like?

3. Instead of paying overtime, you might be able to outsource the shortfall in production. Use the Excel OM Aggregate Planning spreadsheet and the data to prepare an updated aggregate plan using outsources instead of paying overtime.

4. On a fourth tab of the spreadsheet, summarize your cost and determine which of the three options produces the lowest overall cost. Please include the formula, please.

This chart provides the variables and cost for each variable Variables Inventory carrying cost Subcontracting cost Average pay rate Overtime pay rate Labor-hours needed to produce one unit Units per day produced Beginning inventory Planned ending inventory Lost sales per unit This chart provides the demand for the product and the number of production days per month. Cost $6 per unit per month $25 per unit $12.50 per hour (8 hours per day) $18 per hour (above 8 hours per day) 1.5 hours per unit 10 0 0 $100 Months January February March April May June Demand 1200 800 600 1500 1400 1300 Production Days 18 21 21 20