please i need help!
Background: Your company has just acquired a new subsidiary that makes two products, Clodhopper and Clodbuster. The Clodhopper is intended for the general consumer market and its demand is higher and more seasonal than the Clodbuster, which is intended for the commercial user market. Both products share a number of component parts and subassemblies, primarily differing in their engine and tiller subassemblies. All of the managers at the acquired company quit the day your company took possession. You are the person who has been assigned to run this acquisition. Since all of the managers quit, the only planning information you have is historical data. Assignment: Your task is to create material requirements plans for items C, E, H, K, P, and L. Use the MRP form supplied and print the spreadsheets (landscape) two to a page (3 pages total). Fill in all appropriate data (lot size, ordering rule, etc). Do not make any changes to the format/layout of the spreadsheet. This is one place in industry where creativity in presenting data is not appreciated since many of the cells in the forms are normally linked, and changes can lead to significant errors in the results, Use the comment function to show the formula for "projected on-hand inventory." The formula must be understandable (c.g. "beginning inventory + production..." NOT "D12+ E12..."). Use the header function to show your name in the upper right comer of all pages. Staple the three landscape pages in the upper right comer, in the order C & E on the first page, H & K on the second page, and P & L on the third page (no cover sheet). . Part 2 Data: The Assn - 3 Student" spreadsheet contains five different worksheets: MPS approved plans for Clodhopper and Clodbuster for the first three months of 2010. Bill of materials data for Clod Hopper and ClodBuster, including quantities required per product and assembly times/lead times Inventory records and lot-sizing rules for all subassemblies and component parts Scheduled receipts (production commitments and purchase orders that have been scheduled by previous MPS and MRP plans for delivery in 2010. MRP form to use for the items requested above . Additional information: Remember that the FOQ lot-sizing rule allows multiple orders. That is, if your plan requires 450 units in a given period, current inventory is 75 units, and the FOQ order size is 100 units, then you would request 400 units (four orders) from the vendor or assembly production line. For items purchased using the POQ lot-sizing rule, consider that the time between orders begins at the start of the plans. That is, if the POQ period is 3, you can only have planned receipts (or scheduled receipts) in periods 1,4,7, and so on. Unlike the other ordering schemes where you first determine when a new order must be received and then schedule the order earlier according to the lead time, for POQ you look at future needs that must be satisfied until another POQ order can be placed. Do not schedule MRP planned orders in the line for scheduled receipt items. Scheduled receipts have already been started or ordered on previous MRP pans for delivery in 2010 and you are not allowed to make any additional entries on this line. The values on this line are to be added to the projected on-hand inventory when each scheduled receipt arrives. Do not reflect the scheduled receipts into planned order releases. Some parts and subassemblies have desired safety stock levels. Be sure to order enough or early enough to ensure these levels are maintained, limited only by any lead-time constraints. When there is no safety stock, maintain the projected on-hand greater than or equal to zero. Use the power of Excel as much as possible for filling out your MRP forms. Doing so will save you considerable time in completing your assignment and is representative of how these processes are done in industry. Use the comment function to show the formula for "projected on-hand inventory." Hint: Entering new order quantities (planned receipts) when needed is best done manually whenever the on-hand inventory values indicate that you would not have enough unless an order is placed . Part 2 Grading: There are 75 points possible on this assignment. Grading criteria include proper application of the planning methods, following all directions, and professional appearance. Use the Excel file: Class Assn - 2 Student for the required information and MRP forms Clodhopper BOM Clede Handlebar Asambly C Powerhead Assembly Instruction Manual Steel Tubing Control Cable Control Assembly Gripe G Gearbox Assembly Engine standard Tiller Blade Unt K Clodbuster BOM Clodbuster HD Powerhead Assembly Instruction Manual Handlebar Assembly Steel Tubing Control Cable E Control Assembly Grips Gearbox Assembly Engine Dheavy dutyl HDT Blade Unit Odrapper 10 Lot Sira Rule: FOQ 2500 Land Time weak 1500 5500 Qurant van Jan 2010 Week Feb 2010 Week Week 7 Mar 2010 WoWeek 10 Week 11 Week Week Waka Week 12 450 450 800 450 450 600 600 2233 2233 2232 2232 1 F 150 300 2000 350 000 200 2500 1000 Car Orden Booked 4100 4600 2900 3487 3467 3734 2300 3800 5050 200 3600 4002 Projected On Hard Inventory 7 MPS Schedule Rosts ER MPS Oy 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500 MPS Sur 1500 2500 0 STOD 500 2500 24 Code F010 Lot Sure Rule: FOO 1000 Lead Time IT 29 Sately tok Lovat 2. minnventory 750 4000 Jan 2010 Week 2 Week Feb 2010 Week WoWeek 7 Mar 2010 Week Week 10 Week 11 Week 2 Week 41 Wed 300 300 300 300 400 400 400 Forca 943 400 942 942 200 450 55 Custom Orden god 50 900 3000 3700 3400 2950 2050 2250 1350 1550 1550 1723 IT Projected on taand wory 3 MP5 Schedule Recept 1000 3000 1000 1000 1000 1 MPS anity 1000 3000 MPS u 1000 1000 1000 2400 1050 0 1000 1000 1000 MPS Approved BOM. Inventory Records Scheduled Receita MAP Fonn - Het Sheets MacBook Air Clod Hopper BOM (Product A) A C | Name Clod Hopper Handle Bar Assembly Steel Tubing 1 dia Control cable Control lever assembly Grips Powerhead assembly Gearbox assembly Engine (Standard) Tiler blade unit Instruction Manual Quantity Assembly Time Lead Time 1 week 1 2 weeks 10 feet 3 weeks 5 feet 2 weeks 1 1 week 2 1 week 1 1 week 1 1 week 1 2 weeks 1 1 week 1 4 weeks H 1 J K M ClodBuster BOM (Product B) B 2 - D E F G Name ClodBuster Handle Bar Assembly Steel Tubing 1" dia Control cable Control lever assembly Grips HD Powerhead assembly Gearbox assembly Engine (Heavy Duty) HD Tiller Unit Instruction Manual Quantity Assembly Time Lead Time 1 1 week 1 2 weeks 10 feet 3 weeks 5 feet 2 weeks 1 1 week 2 1 week 1 1 week 1 1 week 1 3 weeks 1 2 weeks 4 weeks al L N M Record Date: 31-Dec-09 Current Inventory On-Hand 5500 4000 3100 50,000 5,000 2500 Item A B D E F G Lot-Size Rule FOQ FOQ L4L POQ Period 4 weeks FOQ Lot Size 2500 1000 20,000 LAL Name Clodkooper ClodBuster Handle Bar Assembly Steel Tubing die Control cable Controllerver assembly Gros Powerhead assembly Gearbox assembly Engine (Standard) Ter blade unit Engine (Heavy Duty Instruction Manual HD Tier Unit HD Powerhead assembly 4000 Lead or Desired Assembly Safety Stock Time Level Units 1 week 1500 1 week 750 2 weeks 1000 3 weeks 20,000 feet 2 weeks 10,000 feet 1 week 1 week 1 week 1 week 750 2 weeks 750 1 week 750 3 weeks 500 4 weeks 2500 2 weeks 500 1 week 4000 5000 2500 K 700 650 500 850 450 6000 1250 900 L4L FOQ FOQ FOQ POQ Period - 2 weeks FOQ FOQ POO Period 3 weeks FOQ 1000 10,000 M N P 2000 3 30 BA 37 Work Assignment 2 - Worksheet 4: Scheduled Receipts Note: There are purchase order and production commitments scheduled in 2009 that are already scheduled for Com in 2010. The sizes of the order or production lots ar teled by week of scheduled delivery include these quan "Projected on hand lewentory do not include them in Panned order masas Week Week 3 Wok 4 Week 5 Week Week 7 Week Week Week 10 Week 11 Week 12 A B 10 11 100,000 20.000 20 000 F 13 14 15 H 1 Name God Hopper Dedushar Hund Bar Amen Steel Tubingen Contact det Conversembly Cine Powered some Gearboxy Engine Standard) Terblade unde Enginevy Duty In Manual HD Trung Powerhead 2000 5000 2800 2500 2500 2600 18 1000 1000 1000 K L M N P 1000 1000 1000 20 10.000 2000 RAB BA 3:17 Work Assignment 2 - Worksheet 5: MRP Form 2 Handi's Bar Assembly 4 Hem 5 6 Safety Stock Lovat 7 Current Inventory Lot Size Rule: L4L Lead Time 2 weeks 1000 3100 Jan 2010 Week 2 Week 2 Feb 2010 Week Week 7 Week 1 Week 4 Week 6 Weeke Mar 2010 Week 10 Week 11 Waak 12 Week 9 2 SO 11 Gross Requirements 12 13 Scheduled Recepts 15 Projected On-Hand Inventory 16 17. Planned Receipts 16 19 Planned Order Releases 21 Control Cable 23 E 24 25 Safety Stock Lovel 26 Current Inventory Lot Sue Rule FOQ 20,000 Lead Time 2 weeks 10.000 5.000 Jan 2010 Week 2 Week 3 Week 1 Week 2 Week 5 Feb 2010 Week Workz Mar 2010 Week 10 Week 11 Week 8 Week 9 Week 12 25 30 Gross Requirements 31 33 Scheduled Recepts 54 Proded On- Hand Inventory 35 Planned Rocolos 0 Planned Order Releases 20 41 Powmad As 41 Lobio Rule FOQ 4,000 Lead Time 1 week Safety track Level MPS-Approved BOMS Inventory Records Scheduled Receipts MAP Form - Results Set She N Item H Powerhead Assembly Lot Size Ru: FOQ 4.000 Lead Time 1 week Safety Stock Levet Durant Inventory 0 700 Jan 2010 Week 2 Week 3 Week 1 Week 4 Feb 2010 Week 6 Week 7 Week 5 Mar 2010 Week 9 Week 10 Week 11 Week Week 12 Gross Regulaments Scheduled Root Projected On Hand Inventory Planned Recolo Planned Order Releases DK Ter Blade Und Lol Sure Rule: POQ 2 weeks Lead Time 1 week Safety Stock Lavet Current Inventory 750 850 Jan 2010 Week 2 Week 3 Week 1 Fob 2010 Week 6 Week 7 Week 4 Week 5 Mar 2010 Week 9 Week 10 Week 11 Week 12 Week 8 Gross Requirements * Scheduld coipts IT Projected On Hand Inventory To 19 Panned Receipts Planned Order Releases 30 HD Power AS Lot Szo RFOQ 3.000 RA 23 90 1 Ham P HO Powerhead Assembly Lot Size Rule: FOG 2,000 Lead Time 1 week Jan 2010 Week 2 Wank 3 Feb 2010 Weeke Week 7 Week 4 Mar 2010 Week 9 Week 10 Week 11 Week 1 Week 5 Weak 8 Week 12 sa Safety Stock Lavat 0 94 Cment Inventory 900 95 96 97 90 Gross Requirements 99 100 Scheduled Receipts 101 102 Projected On Hand Inventory toa 104 Planned Receipts 105 306 Planned Order Releases 107 108 110 ItemL Engine (Heavy Duty Lot Sue Rule: FOG 1.000 Lead Time: 3 weeks 112 Safety Stock Levet 113 Cumunt Inventory 500 450 115 Jan 2010 Week 2 Week 3 Week 1 Feb 2010 Week 6 Woek 7 Week 4 Week 5 Week 8 Week 9 Mar 2010 Week 10 Weak 11 Week 12 117 Gross Raqurements 119 Scheduled Receipts 120 101 Projected On Hand Inventory 122 123 Planned Receipts 125 Planned Order Releases 12 Background: Your company has just acquired a new subsidiary that makes two products, Clodhopper and Clodbuster. The Clodhopper is intended for the general consumer market and its demand is higher and more seasonal than the Clodbuster, which is intended for the commercial user market. Both products share a number of component parts and subassemblies, primarily differing in their engine and tiller subassemblies. All of the managers at the acquired company quit the day your company took possession. You are the person who has been assigned to run this acquisition. Since all of the managers quit, the only planning information you have is historical data. Assignment: Your task is to create material requirements plans for items C, E, H, K, P, and L. Use the MRP form supplied and print the spreadsheets (landscape) two to a page (3 pages total). Fill in all appropriate data (lot size, ordering rule, etc). Do not make any changes to the format/layout of the spreadsheet. This is one place in industry where creativity in presenting data is not appreciated since many of the cells in the forms are normally linked, and changes can lead to significant errors in the results, Use the comment function to show the formula for "projected on-hand inventory." The formula must be understandable (c.g. "beginning inventory + production..." NOT "D12+ E12..."). Use the header function to show your name in the upper right comer of all pages. Staple the three landscape pages in the upper right comer, in the order C & E on the first page, H & K on the second page, and P & L on the third page (no cover sheet). . Part 2 Data: The Assn - 3 Student" spreadsheet contains five different worksheets: MPS approved plans for Clodhopper and Clodbuster for the first three months of 2010. Bill of materials data for Clod Hopper and ClodBuster, including quantities required per product and assembly times/lead times Inventory records and lot-sizing rules for all subassemblies and component parts Scheduled receipts (production commitments and purchase orders that have been scheduled by previous MPS and MRP plans for delivery in 2010. MRP form to use for the items requested above . Additional information: Remember that the FOQ lot-sizing rule allows multiple orders. That is, if your plan requires 450 units in a given period, current inventory is 75 units, and the FOQ order size is 100 units, then you would request 400 units (four orders) from the vendor or assembly production line. For items purchased using the POQ lot-sizing rule, consider that the time between orders begins at the start of the plans. That is, if the POQ period is 3, you can only have planned receipts (or scheduled receipts) in periods 1,4,7, and so on. Unlike the other ordering schemes where you first determine when a new order must be received and then schedule the order earlier according to the lead time, for POQ you look at future needs that must be satisfied until another POQ order can be placed. Do not schedule MRP planned orders in the line for scheduled receipt items. Scheduled receipts have already been started or ordered on previous MRP pans for delivery in 2010 and you are not allowed to make any additional entries on this line. The values on this line are to be added to the projected on-hand inventory when each scheduled receipt arrives. Do not reflect the scheduled receipts into planned order releases. Some parts and subassemblies have desired safety stock levels. Be sure to order enough or early enough to ensure these levels are maintained, limited only by any lead-time constraints. When there is no safety stock, maintain the projected on-hand greater than or equal to zero. Use the power of Excel as much as possible for filling out your MRP forms. Doing so will save you considerable time in completing your assignment and is representative of how these processes are done in industry. Use the comment function to show the formula for "projected on-hand inventory." Hint: Entering new order quantities (planned receipts) when needed is best done manually whenever the on-hand inventory values indicate that you would not have enough unless an order is placed . Part 2 Grading: There are 75 points possible on this assignment. Grading criteria include proper application of the planning methods, following all directions, and professional appearance. Use the Excel file: Class Assn - 2 Student for the required information and MRP forms Clodhopper BOM Clede Handlebar Asambly C Powerhead Assembly Instruction Manual Steel Tubing Control Cable Control Assembly Gripe G Gearbox Assembly Engine standard Tiller Blade Unt K Clodbuster BOM Clodbuster HD Powerhead Assembly Instruction Manual Handlebar Assembly Steel Tubing Control Cable E Control Assembly Grips Gearbox Assembly Engine Dheavy dutyl HDT Blade Unit Odrapper 10 Lot Sira Rule: FOQ 2500 Land Time weak 1500 5500 Qurant van Jan 2010 Week Feb 2010 Week Week 7 Mar 2010 WoWeek 10 Week 11 Week Week Waka Week 12 450 450 800 450 450 600 600 2233 2233 2232 2232 1 F 150 300 2000 350 000 200 2500 1000 Car Orden Booked 4100 4600 2900 3487 3467 3734 2300 3800 5050 200 3600 4002 Projected On Hard Inventory 7 MPS Schedule Rosts ER MPS Oy 2500 2500 2500 2500 2500 2500 2500 2500 2500 2500 MPS Sur 1500 2500 0 STOD 500 2500 24 Code F010 Lot Sure Rule: FOO 1000 Lead Time IT 29 Sately tok Lovat 2. minnventory 750 4000 Jan 2010 Week 2 Week Feb 2010 Week WoWeek 7 Mar 2010 Week Week 10 Week 11 Week 2 Week 41 Wed 300 300 300 300 400 400 400 Forca 943 400 942 942 200 450 55 Custom Orden god 50 900 3000 3700 3400 2950 2050 2250 1350 1550 1550 1723 IT Projected on taand wory 3 MP5 Schedule Recept 1000 3000 1000 1000 1000 1 MPS anity 1000 3000 MPS u 1000 1000 1000 2400 1050 0 1000 1000 1000 MPS Approved BOM. Inventory Records Scheduled Receita MAP Fonn - Het Sheets MacBook Air Clod Hopper BOM (Product A) A C | Name Clod Hopper Handle Bar Assembly Steel Tubing 1 dia Control cable Control lever assembly Grips Powerhead assembly Gearbox assembly Engine (Standard) Tiler blade unit Instruction Manual Quantity Assembly Time Lead Time 1 week 1 2 weeks 10 feet 3 weeks 5 feet 2 weeks 1 1 week 2 1 week 1 1 week 1 1 week 1 2 weeks 1 1 week 1 4 weeks H 1 J K M ClodBuster BOM (Product B) B 2 - D E F G Name ClodBuster Handle Bar Assembly Steel Tubing 1" dia Control cable Control lever assembly Grips HD Powerhead assembly Gearbox assembly Engine (Heavy Duty) HD Tiller Unit Instruction Manual Quantity Assembly Time Lead Time 1 1 week 1 2 weeks 10 feet 3 weeks 5 feet 2 weeks 1 1 week 2 1 week 1 1 week 1 1 week 1 3 weeks 1 2 weeks 4 weeks al L N M Record Date: 31-Dec-09 Current Inventory On-Hand 5500 4000 3100 50,000 5,000 2500 Item A B D E F G Lot-Size Rule FOQ FOQ L4L POQ Period 4 weeks FOQ Lot Size 2500 1000 20,000 LAL Name Clodkooper ClodBuster Handle Bar Assembly Steel Tubing die Control cable Controllerver assembly Gros Powerhead assembly Gearbox assembly Engine (Standard) Ter blade unit Engine (Heavy Duty Instruction Manual HD Tier Unit HD Powerhead assembly 4000 Lead or Desired Assembly Safety Stock Time Level Units 1 week 1500 1 week 750 2 weeks 1000 3 weeks 20,000 feet 2 weeks 10,000 feet 1 week 1 week 1 week 1 week 750 2 weeks 750 1 week 750 3 weeks 500 4 weeks 2500 2 weeks 500 1 week 4000 5000 2500 K 700 650 500 850 450 6000 1250 900 L4L FOQ FOQ FOQ POQ Period - 2 weeks FOQ FOQ POO Period 3 weeks FOQ 1000 10,000 M N P 2000 3 30 BA 37 Work Assignment 2 - Worksheet 4: Scheduled Receipts Note: There are purchase order and production commitments scheduled in 2009 that are already scheduled for Com in 2010. The sizes of the order or production lots ar teled by week of scheduled delivery include these quan "Projected on hand lewentory do not include them in Panned order masas Week Week 3 Wok 4 Week 5 Week Week 7 Week Week Week 10 Week 11 Week 12 A B 10 11 100,000 20.000 20 000 F 13 14 15 H 1 Name God Hopper Dedushar Hund Bar Amen Steel Tubingen Contact det Conversembly Cine Powered some Gearboxy Engine Standard) Terblade unde Enginevy Duty In Manual HD Trung Powerhead 2000 5000 2800 2500 2500 2600 18 1000 1000 1000 K L M N P 1000 1000 1000 20 10.000 2000 RAB BA 3:17 Work Assignment 2 - Worksheet 5: MRP Form 2 Handi's Bar Assembly 4 Hem 5 6 Safety Stock Lovat 7 Current Inventory Lot Size Rule: L4L Lead Time 2 weeks 1000 3100 Jan 2010 Week 2 Week 2 Feb 2010 Week Week 7 Week 1 Week 4 Week 6 Weeke Mar 2010 Week 10 Week 11 Waak 12 Week 9 2 SO 11 Gross Requirements 12 13 Scheduled Recepts 15 Projected On-Hand Inventory 16 17. Planned Receipts 16 19 Planned Order Releases 21 Control Cable 23 E 24 25 Safety Stock Lovel 26 Current Inventory Lot Sue Rule FOQ 20,000 Lead Time 2 weeks 10.000 5.000 Jan 2010 Week 2 Week 3 Week 1 Week 2 Week 5 Feb 2010 Week Workz Mar 2010 Week 10 Week 11 Week 8 Week 9 Week 12 25 30 Gross Requirements 31 33 Scheduled Recepts 54 Proded On- Hand Inventory 35 Planned Rocolos 0 Planned Order Releases 20 41 Powmad As 41 Lobio Rule FOQ 4,000 Lead Time 1 week Safety track Level MPS-Approved BOMS Inventory Records Scheduled Receipts MAP Form - Results Set She N Item H Powerhead Assembly Lot Size Ru: FOQ 4.000 Lead Time 1 week Safety Stock Levet Durant Inventory 0 700 Jan 2010 Week 2 Week 3 Week 1 Week 4 Feb 2010 Week 6 Week 7 Week 5 Mar 2010 Week 9 Week 10 Week 11 Week Week 12 Gross Regulaments Scheduled Root Projected On Hand Inventory Planned Recolo Planned Order Releases DK Ter Blade Und Lol Sure Rule: POQ 2 weeks Lead Time 1 week Safety Stock Lavet Current Inventory 750 850 Jan 2010 Week 2 Week 3 Week 1 Fob 2010 Week 6 Week 7 Week 4 Week 5 Mar 2010 Week 9 Week 10 Week 11 Week 12 Week 8 Gross Requirements * Scheduld coipts IT Projected On Hand Inventory To 19 Panned Receipts Planned Order Releases 30 HD Power AS Lot Szo RFOQ 3.000 RA 23 90 1 Ham P HO Powerhead Assembly Lot Size Rule: FOG 2,000 Lead Time 1 week Jan 2010 Week 2 Wank 3 Feb 2010 Weeke Week 7 Week 4 Mar 2010 Week 9 Week 10 Week 11 Week 1 Week 5 Weak 8 Week 12 sa Safety Stock Lavat 0 94 Cment Inventory 900 95 96 97 90 Gross Requirements 99 100 Scheduled Receipts 101 102 Projected On Hand Inventory toa 104 Planned Receipts 105 306 Planned Order Releases 107 108 110 ItemL Engine (Heavy Duty Lot Sue Rule: FOG 1.000 Lead Time: 3 weeks 112 Safety Stock Levet 113 Cumunt Inventory 500 450 115 Jan 2010 Week 2 Week 3 Week 1 Feb 2010 Week 6 Woek 7 Week 4 Week 5 Week 8 Week 9 Mar 2010 Week 10 Weak 11 Week 12 117 Gross Raqurements 119 Scheduled Receipts 120 101 Projected On Hand Inventory 122 123 Planned Receipts 125 Planned Order Releases 12
