I need help filling out H & K section.

Assignment:

• Use the MRP form supplied.
• 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 (e.g. beginning inventory + production NOT D12 + E12).
• Use the header function to show your name in the upper right corner of all pages.
• Staple the three landscape pages in the upper right corner, 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 2 Student spreadsheet contains five different worksheets:

• MPS approved plans for Clodhopper and Clodbuster for the first three months of 2020.
• Bill of materials data for ClodHopper 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 2020.
• 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 plans 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.

(PLEASE DON'T COPY AND PASTE FROM OTHER POSTS BECAUSE THE DATA IS NOT ACCURATE)

Item: H Powerhead Assembly Lot Size Rule: FOQ 4,000 Lead Time: 1 week Safety Stock Level: Current Inventory: 0 700 Jan 2021 Week 2 Week 3 Feb 2021 Week 6 Week 7 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week 1 Week 4 Week 5 Week 8 Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: K Tiller Blade Unit Lot Size Rule: POQ 2 weeks Lead Time: 1 week Safety Stock Level: Current Inventory: 650 750 Jan 2021 Week 1 Week 2 Week 3 Feb 2021 Week 6 Week 7 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week 4 Week 5 Week 8 Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases ClodHopper BOM (Product A) D E F G Name Quantity Assembly Time Lead Time ClodHopper 1 1 week Handle Bar Assembly 1 2 weeks Steel Tubing 1" dia 10 feet 3 weeks Control cable 5 feet 2 weeks Control lever assembly 1 1 week Grips 2 1 week Powerhead assembly 1 1 week Gearbox assembly 1 1 week Engine (Standard) 1 2 weeks Tiller blade unit 1 week Instruction Manual 1 4 weeks H 1 J M ClodBuster BOM (Product B) B D E F G Name Quantity Assembly Time Lead Time ClodBuster 1 1 week Handle Bar Assembly 1 2 weeks Steel Tubing 1" dia 10 feet 3 weeks Control cable 5 feet 2 weeks Control lever assembly 1 1 week Grips 2 1 week HD Powerhead assem 1 1 week Gearbox assembly 1 1 week Engine (Heavy Duty) 1 3 weeks HD Tiller Unit 1 2 weeks Instruction Manual 1 4 weeks P I L N M Record Date: 31-Dec-20 almolow> Desired Safety Stock Units 1500 750 1000 20,000 feet 10,000 feet Item A B D E F G H 1 J K L M N P Name Clod Hopper ClodBuster Handle Bar Assembly Steel Tubing 1" dia Control cable Control lever assembly Grips Powerhead assembly Gearbox assembly Engine (Standard) Tiller blade unit Engine (Heavy Duty) Instruction Manual HD Tiller Unit HD Powerhead assembly Current Inventor 5500 4000 2600 50,000 5,000 2500 4000 700 650 500 750 450 6000 1250 900 Lead or Assembl Lot-Size Rule Lot Size y Time FOQ 2500 1 week FOQ 1000 1 week L4L 2 weeks POQ Period = 4 weeks 3 weeks FOQ 20,000 2 weeks L4L 1 week L4L 1 week FOQ 4000 1 week FOQ 5000 1 week FOQ 2500 2 weeks POQ Period = 2 weeks 1 week FOQ 1000 3 weeks FOQ 10,000 4 weeks POQ Period = 3 weeks 2 weeks FOQ 2000 1 week 750 750 650 500 2500 500 Note: These are purchase order and production commitments scheduled in 2009 that are already scheduled for completion in 2010. The sizes of the orders or production lots are listed by week of scheduled delivery. Include hese quantities in "Projected on-hand inventory" - do not include them in "Planned order releases." Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12 100,000 20,000 20,000 Item A B D E F G H 1 J L M N Name ClodHopper ClodBuster Handle Bar Assembly Steel Tubing 1" dia (feet) Control cable (feet) Control lever assembly Grips Powerhead assembly Gearbox assembly Engine (Standard) Tiller blade unit Engine (Heavy Duty) Instruction Manual HD Tiller Unit HD Powerhead assembly 4000 5000 2500 2500 2500 2500 1000 1000 1000 1000 1000 1000 10,000 2000 Item: H Powerhead Assembly Lot Size Rule: FOQ 4,000 Lead Time: 1 week Safety Stock Level: Current Inventory: 0 700 Jan 2021 Week 2 Week 3 Feb 2021 Week 6 Week 7 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week 1 Week 4 Week 5 Week 8 Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: K Tiller Blade Unit Lot Size Rule: POQ 2 weeks Lead Time: 1 week Safety Stock Level: Current Inventory: 650 750 Jan 2021 Week 1 Week 2 Week 3 Feb 2021 Week 6 Week 7 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week 4 Week 5 Week 8 Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases ClodHopper BOM (Product A) D E F G Name Quantity Assembly Time Lead Time ClodHopper 1 1 week Handle Bar Assembly 1 2 weeks Steel Tubing 1" dia 10 feet 3 weeks Control cable 5 feet 2 weeks Control lever assembly 1 1 week Grips 2 1 week Powerhead assembly 1 1 week Gearbox assembly 1 1 week Engine (Standard) 1 2 weeks Tiller blade unit 1 week Instruction Manual 1 4 weeks H 1 J M ClodBuster BOM (Product B) B D E F G Name Quantity Assembly Time Lead Time ClodBuster 1 1 week Handle Bar Assembly 1 2 weeks Steel Tubing 1" dia 10 feet 3 weeks Control cable 5 feet 2 weeks Control lever assembly 1 1 week Grips 2 1 week HD Powerhead assem 1 1 week Gearbox assembly 1 1 week Engine (Heavy Duty) 1 3 weeks HD Tiller Unit 1 2 weeks Instruction Manual 1 4 weeks P I L N M Record Date: 31-Dec-20 almolow> Desired Safety Stock Units 1500 750 1000 20,000 feet 10,000 feet Item A B D E F G H 1 J K L M N P Name Clod Hopper ClodBuster Handle Bar Assembly Steel Tubing 1" dia Control cable Control lever assembly Grips Powerhead assembly Gearbox assembly Engine (Standard) Tiller blade unit Engine (Heavy Duty) Instruction Manual HD Tiller Unit HD Powerhead assembly Current Inventor 5500 4000 2600 50,000 5,000 2500 4000 700 650 500 750 450 6000 1250 900 Lead or Assembl Lot-Size Rule Lot Size y Time FOQ 2500 1 week FOQ 1000 1 week L4L 2 weeks POQ Period = 4 weeks 3 weeks FOQ 20,000 2 weeks L4L 1 week L4L 1 week FOQ 4000 1 week FOQ 5000 1 week FOQ 2500 2 weeks POQ Period = 2 weeks 1 week FOQ 1000 3 weeks FOQ 10,000 4 weeks POQ Period = 3 weeks 2 weeks FOQ 2000 1 week 750 750 650 500 2500 500 Note: These are purchase order and production commitments scheduled in 2009 that are already scheduled for completion in 2010. The sizes of the orders or production lots are listed by week of scheduled delivery. Include hese quantities in "Projected on-hand inventory" - do not include them in "Planned order releases." Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12 100,000 20,000 20,000 Item A B D E F G H 1 J L M N Name ClodHopper ClodBuster Handle Bar Assembly Steel Tubing 1" dia (feet) Control cable (feet) Control lever assembly Grips Powerhead assembly Gearbox assembly Engine (Standard) Tiller blade unit Engine (Heavy Duty) Instruction Manual HD Tiller Unit HD Powerhead assembly 4000 5000 2500 2500 2500 2500 1000 1000 1000 1000 1000 1000 10,000 2000