Assignment Details: All four plans will be completed using the provided forecast, fill in the summary sheet with a half- page executive summary explaining your choicesin your optimized mixed plan. Include the recommended plan's total annual cost and cost per unit shipped. Your analysis should include quantitative factors (i.e.- Total Cost) and qualitative factors (i.e.- effects on morale and productivity) that drove your mixed plan. There is an Excel template in the assignment 2 item in Canvas. The completed assignment file should consist of five sheets in the supplied Excel template in the following order: Your names and the executive summary with recommended plan cost and cost per unit shipped Your Chase Plan Your Level-Utilization Plan Your Mixed Plan Plan formats: You are to use the spreadsheet template for your assignment. This form is now available as an Excel file in the Assignment 2 item in Canvas. Your aggregate plan is to be completed on its designated sheet. There will be sufficient space below the spreadsheet form for you to add any comments or explanations you care to add regarding your plans. Values within the main body of the spreadsheets (demand, production rates, staffing levels, etc.) are to be number of units or people (not $). They are to be expressed in whole numbers (no decimal values). Cost calculations will appear in the designated row at the bottom and the rightmost column of each spreadsheet only. Cost calculations, of course, will need to retain at least 2 decimal points (cents) but whole $ should be reported. The sum of the blue cells in the bottom row and the sum of the cells in the rightmost column should both equal the total cost of your aggregate plan and should be reported in the green cell in the lower right corner. Enter the executive summary in the text box on the first sheet Do not make the reader guess what you are doing include explanations on each page. Required format for aggregate plans is in the provided template. Use the following monthly forecast to create your aggregate plans: 2013 Forecast: Month Demand 1 6689 2 7094 3 9592 4 7458 5 8246 6 7 8 12003 13489 11135 9092 8875 9 10 11 7082 7500 12 . Assume 20 production days per month (4 weeks of 5 days each) The ending inventory (for December of 2012) is 3000 units Assume 0 units on Backorder at the end of 2012 The number of Units produced by each worker is 200 units per month The desired safety stock level is 2000 units (you can only fall below this level to avoid having backorders, even in the Chase Strategy) Average pay applies for the first 40 hours an employee works in a week. Overtime pay applies for any time over 40 hours in a given week. The maximum level of overtime any employee can work in a month is 40 hours. You must employ whole people there are no part time employees. (However, you can underutilize) Employees do not produce more per month than is required by the plan (ie- No efficiency gains), however than can under produce to satisfy the conditions of the type of plan that you choose. The firm wants exactly 4000 units to be left in inventory at the end of the fiscal year (after forecasted December 2013 demand is satisfied). This is a firm requirement for all plans. The staffing level at the beginning of 2014 is to be the same as the original staffing level given below (any staffing changes needed to achieve this are made after the forecasted December 2013 demand is satisfied). This is a firm requirement for all plans. Any backorder (negative on-hand inventory) is shown as a positive value in the backorder column instead of in the inventory column since it has a different monthly cost per unit. That is, if there is backorder for a period, there will be zero on-hand inventories for that period. Be sure to fulfill backorders as soon as possible in the plan. Assignment 2 Cost Information Table: Inventory holding cost $2 per unit per month based on ending inventory for month $10 an hour $15 an hour $3000 Average pay rate Overtime pay rate Costs of hiring one additional employee Costs of firing one employee Original Staffing Level (12/2012) Material cost to produce one unit Backorder Costs $2000 21 employees $14 unit per month held based on ending backorder level for month $5 per Assignment 2 Operating Constraints Table: Maximum inventory level 25,000 units due to limited warehouse capacity and increasing interest levels. Maximum production capacity 20,000 units/month using regular time Satisfying Demand Priorities Use Regular production time to meet demand if you can do so without violating the two previous constraints or the constraints of your plan type. After that, choose between using safety stock, anticipation inventory, Backorder or Overtime production. Choose the strategy that will lead to the lowest cost plan. A B C D E F G H J K L M N 0 P Q 1 2 Materials Regular Time Production (Units) Forecast Demand Staff (Headcount) 3 On-Hand Inventory Backorder Overtime Production (Units) Total Shipments Hire Fire (Units Made) Total Cost 4 Dec-12 3000 5 JAN 6689 6689 2000 6 FEB 7094 7094 7 MAR 9592 9592 8 APR 7458 7458 9 MAY 8246 8246 10 JUN 12003 12003 11 JUL 13489 13489 12 AUG 11135 11135 13 SEP 9092 9092 14 OCT 8875 8875 15 NOV 7082 7082 16 DEC 7500 7500 9500 4000 17 Year End Adj 18 Jan-14 19 Total Qty These two Cells should 20 Total Cost equal each other as a checksum 21 Total Plan Cost 22 Cost per unit shipped 23 24 Fill in white, blue and green cells only. Do not put values in the grey cells. 25 26. Executive Summary Chase Plan Level-Utilization Mixed Plan + A B C D E F G H . I J L M N O P a 1 N Materials Regular Time Production (Units) Forecast Demand Staff (Headcount) 3 On-Hand Inventory Backorder Overtime Production (Units) Total Shipments Hire Fire (Units Made) Total Cost 4 Dec-12 5 JAN 6 FEB 7 MAR 8 APR 9 MAY 10 JUN 11 JUL 12 AUG 13 SEP 14 OCT 15 NOV 16 DEC 17 Year End Adj 18 Jan-14 19 Total Qty These two Cells should 20 Total Cost equal each other as a checksum 21 Total Plan Cost 22 Cost per unit shipped 23 24 Fill in white, blue and green cells only. Do not put values in the grey cells. 25 26. Executive Summary Chase Plan Level-Utilization Mixed Plan B D E F G H J L M N 0 P Q 1 2 Materials Regular Time Production (Units) Forecast Demand Backorder 3 Overtime Production (Units) Staff (Headcount) Total Shipments Hire On-Hand Inventory Fire (Units Made) Total Cost 4 Dec-12 5 JAN 6 FEB 7 MAR 8 APR 9 MAY 10 JUN 11 JUL 12 AUG 13 SEP 14 OCT 15 NOV 16 DEC 17 Year End Adj 18 Jan-14 19 Total Qty These two Cells should 20 Total Cost equal each other as a checksum 21 Total Plan Cost Cost per unit shipped 22 23 Fill in white, blue and green cells only. Do not put values in the grey cells. 24 25 26 27 Executive Summary Chase Plan Level-Utilization Mixed Plan Assignment Details: All four plans will be completed using the provided forecast, fill in the summary sheet with a half- page executive summary explaining your choicesin your optimized mixed plan. Include the recommended plan's total annual cost and cost per unit shipped. Your analysis should include quantitative factors (i.e.- Total Cost) and qualitative factors (i.e.- effects on morale and productivity) that drove your mixed plan. There is an Excel template in the assignment 2 item in Canvas. The completed assignment file should consist of five sheets in the supplied Excel template in the following order: Your names and the executive summary with recommended plan cost and cost per unit shipped Your Chase Plan Your Level-Utilization Plan Your Mixed Plan Plan formats: You are to use the spreadsheet template for your assignment. This form is now available as an Excel file in the Assignment 2 item in Canvas. Your aggregate plan is to be completed on its designated sheet. There will be sufficient space below the spreadsheet form for you to add any comments or explanations you care to add regarding your plans. Values within the main body of the spreadsheets (demand, production rates, staffing levels, etc.) are to be number of units or people (not $). They are to be expressed in whole numbers (no decimal values). Cost calculations will appear in the designated row at the bottom and the rightmost column of each spreadsheet only. Cost calculations, of course, will need to retain at least 2 decimal points (cents) but whole $ should be reported. The sum of the blue cells in the bottom row and the sum of the cells in the rightmost column should both equal the total cost of your aggregate plan and should be reported in the green cell in the lower right corner. Enter the executive summary in the text box on the first sheet Do not make the reader guess what you are doing include explanations on each page. Required format for aggregate plans is in the provided template. Use the following monthly forecast to create your aggregate plans: 2013 Forecast: Month Demand 1 6689 2 7094 3 9592 4 7458 5 8246 6 7 8 12003 13489 11135 9092 8875 9 10 11 7082 7500 12 . Assume 20 production days per month (4 weeks of 5 days each) The ending inventory (for December of 2012) is 3000 units Assume 0 units on Backorder at the end of 2012 The number of Units produced by each worker is 200 units per month The desired safety stock level is 2000 units (you can only fall below this level to avoid having backorders, even in the Chase Strategy) Average pay applies for the first 40 hours an employee works in a week. Overtime pay applies for any time over 40 hours in a given week. The maximum level of overtime any employee can work in a month is 40 hours. You must employ whole people there are no part time employees. (However, you can underutilize) Employees do not produce more per month than is required by the plan (ie- No efficiency gains), however than can under produce to satisfy the conditions of the type of plan that you choose. The firm wants exactly 4000 units to be left in inventory at the end of the fiscal year (after forecasted December 2013 demand is satisfied). This is a firm requirement for all plans. The staffing level at the beginning of 2014 is to be the same as the original staffing level given below (any staffing changes needed to achieve this are made after the forecasted December 2013 demand is satisfied). This is a firm requirement for all plans. Any backorder (negative on-hand inventory) is shown as a positive value in the backorder column instead of in the inventory column since it has a different monthly cost per unit. That is, if there is backorder for a period, there will be zero on-hand inventories for that period. Be sure to fulfill backorders as soon as possible in the plan. Assignment 2 Cost Information Table: Inventory holding cost $2 per unit per month based on ending inventory for month $10 an hour $15 an hour $3000 Average pay rate Overtime pay rate Costs of hiring one additional employee Costs of firing one employee Original Staffing Level (12/2012) Material cost to produce one unit Backorder Costs $2000 21 employees $14 unit per month held based on ending backorder level for month $5 per Assignment 2 Operating Constraints Table: Maximum inventory level 25,000 units due to limited warehouse capacity and increasing interest levels. Maximum production capacity 20,000 units/month using regular time Satisfying Demand Priorities Use Regular production time to meet demand if you can do so without violating the two previous constraints or the constraints of your plan type. After that, choose between using safety stock, anticipation inventory, Backorder or Overtime production. Choose the strategy that will lead to the lowest cost plan. A B C D E F G H J K L M N 0 P Q 1 2 Materials Regular Time Production (Units) Forecast Demand Staff (Headcount) 3 On-Hand Inventory Backorder Overtime Production (Units) Total Shipments Hire Fire (Units Made) Total Cost 4 Dec-12 3000 5 JAN 6689 6689 2000 6 FEB 7094 7094 7 MAR 9592 9592 8 APR 7458 7458 9 MAY 8246 8246 10 JUN 12003 12003 11 JUL 13489 13489 12 AUG 11135 11135 13 SEP 9092 9092 14 OCT 8875 8875 15 NOV 7082 7082 16 DEC 7500 7500 9500 4000 17 Year End Adj 18 Jan-14 19 Total Qty These two Cells should 20 Total Cost equal each other as a checksum 21 Total Plan Cost 22 Cost per unit shipped 23 24 Fill in white, blue and green cells only. Do not put values in the grey cells. 25 26. Executive Summary Chase Plan Level-Utilization Mixed Plan + A B C D E F G H . I J L M N O P a 1 N Materials Regular Time Production (Units) Forecast Demand Staff (Headcount) 3 On-Hand Inventory Backorder Overtime Production (Units) Total Shipments Hire Fire (Units Made) Total Cost 4 Dec-12 5 JAN 6 FEB 7 MAR 8 APR 9 MAY 10 JUN 11 JUL 12 AUG 13 SEP 14 OCT 15 NOV 16 DEC 17 Year End Adj 18 Jan-14 19 Total Qty These two Cells should 20 Total Cost equal each other as a checksum 21 Total Plan Cost 22 Cost per unit shipped 23 24 Fill in white, blue and green cells only. Do not put values in the grey cells. 25 26. Executive Summary Chase Plan Level-Utilization Mixed Plan B D E F G H J L M N 0 P Q 1 2 Materials Regular Time Production (Units) Forecast Demand Backorder 3 Overtime Production (Units) Staff (Headcount) Total Shipments Hire On-Hand Inventory Fire (Units Made) Total Cost 4 Dec-12 5 JAN 6 FEB 7 MAR 8 APR 9 MAY 10 JUN 11 JUL 12 AUG 13 SEP 14 OCT 15 NOV 16 DEC 17 Year End Adj 18 Jan-14 19 Total Qty These two Cells should 20 Total Cost equal each other as a checksum 21 Total Plan Cost Cost per unit shipped 22 23 Fill in white, blue and green cells only. Do not put values in the grey cells. 24 25 26 27 Executive Summary Chase Plan Level-Utilization Mixed Plan