Please help, I need the MRP form completed. If possible, can you also provide the formulas within the cells as well as the numbers? The assignment is due tonight and will be much appreciated if completed by then.

***The numbers are different than other chegg posts so please do not copy/paste them.

Clodhopper Clodbuster 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 and your team have 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. These are parts for the two products above. 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. BA 357 Work Assignment 2 - Worksheet 1: MPS Approved plan Item: A Clodhopper Lot Size Rule: FOQ 2500 Lead Time: 1 week Safety Curren 1500 5500 Jan 2021 Week 2 Week 3 450 350 500 4600 4100 Week 5 600 Week 4 450 600 3500 450 Feb 2021 Week 6 Week 7 600 600 200 1000 2300 3800 Week 8 600 Mar 2021 Week 9 Week 10 Week 11 Week 12 2433 2432 2433 2432 2500 2000 3267 3267 3334 3402 2900 3200 Week 1 Forecast 450 Customer Orders 150 Projected On-Har 5050 MPS Scheduled Receipts MPS Quantity MPS Start Available to Prom 3700 Item: B Clodbuster 2500 2500 2500 2500 1500 2500 2500 2500 2500 2500 2500 2500 0 500 Lot Size Rule: FOQ 1000 Lead Time: 1 week 2500 Safety Curren 750 4000 Jan 2021 Week 2 Week 3 300 300 450 3400 2950 Week 41 300 Feb 2021 Week 6 Week 7 400 400 900 1350 950 Week 5 400 50 2250 Week 8 400 Mar 2021 Week 9 Week 10 Week 11 Week 12 1043 1042 1043 1042 3000 1550 1508 1465 1423 2650 1550 Week 1 Forecast 300 Customer Orders 200 Projected On-Har 3700 MPS Scheduled Receipts MPS Quantity MPS Start Available to Prom 2400 1000 1000 1000 3000 1000 3000 1000 0 1000 1000 1000 1000 1000 1000 1000 BA 357 Work Assignment 2 - Worksheet 2: Bill of Materials Data ClodHopper BOM (Product A) Name Quantity Assembly Time Lead Time A ClodHopper 1 1 week C Handle Bar Assembly 1 2 weeks D Steel Tubing 1" dia 10 feet 3 weeks E Control cable 5 feet 2 weeks F Control lever assembly 1 1 week G Grips 2 1 week H Powerhead assembly 1 1 week 1 Gearbox assembly 1 1 week J Engine (Standard) 1 2 weeks K Tiller blade unit 1 1 week M Instruction Manual 1 4 weeks ClodBuster BOM (Product B) Name Quantity Assembly Time Lead Time B ClodBuster 1 1 week Handle Bar Assembly 1 2 weeks D Steel Tubing 1" dia 10 feet 3 weeks E Control cable 5 feet 2 weeks F Control lever assembly 1 1 week G Grips 2 1 week P HD Powerhead assembly 1 1 week 1 Gearbox assembly 1 1 week L Engine (Heavy Duty) 1 3 weeks N HD Tiller Unit 1 2 weeks M Instruction Manual 1 4 weeks BA 357 Work Assignment 2 - Worksheet 3: Inventory Records and Lot-Sizing Rules Record Date: 31-Dec.-20 Item A B D E F G H 1 J K L M N P Name ClodHopper 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 Inventory On-Hand Lot-Size Rule 5500 FOQ 4000 FOQ 2600 L4L 50,000 POQ Period = 4 weeks 5,000 FOQ 2500 L4L 4000 L4L 700 FOQ 650 FOQ 500 FOQ 750 POQ Period = 2 weeks 450 FOQ 6000 FOQ 1250 POQ Period = 3 weeks 900 FOQ Lead or Desired Assembly Safety Lot Size Time Stock Level Units 2500 1 week 1500 1000 1 week 750 2 weeks 1000 3 weeks 20,000 feet 20,000 2 weeks 10,000 feet 1 week 1 week 4000 1 week 5000 1 week 750 2500 2 weeks 750 1 week 650 1000 3 weeks 500 10,000 4 weeks 2500 2 weeks 500 2000 1 week Week 8 Week 9 Week 10 Week 11 Week 12 BA 357 Work Assignment 2 - Worksheet 4: Scheduled Receipts 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 these quantities in "Projected on-hand inventory" - do not include them in "Planned order releases." Item Name Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 A Clod Hopper B ClodBuster C Handle Bar Assembly D Steel Tubing 1" dia (feet) 100,000 E Control cable (feet) 20,000 20,000 F Control lever assembly G Grips H Powerhead assembly 4000 1 Gearbox assembly 5000 J Engine (Standard) 2500 2500 K Tiller blade unit L Engine (Heavy Duty) 1000 1000 1000 M Instruction Manual 10,000 N HD Tiller Unit P HD Powerhead assembly 2000 2500 2500 1000 1000 1000 BA 357 Work Assignment 2 - Worksheet 5: MRP Form Item: C Handle Bar Assembly Lot Size Rule: L4L Lead Time: 2 weeks Safety S Current 1000 2600 Jan 2021 Week 2 Week 3 Feb 2021 Week 6 Week Week 4 Week 5 Week 8 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week 1 Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: E Control Cable Lot Size Rule: FOQ 20,000 Lead Time: 2 weeks Safety S Current 10,000 5,000 Jan 2021 Week 2 Week 31 Feb 2021 Week 6 Week 7 Week 4 Week 5 Week 8 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: H Powerhead Assembly Lot Size Rule: FOQ 4,000 Lead Time: 1 week Safety S Current 0 700 Jan 2021 Week 2 Week Week 4 Week 5 Feb 2021 Week 6 Week Mar 2021 Week 10 Week 11 Week 8 Week Week 12 Week 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 Week 4 Feb 2021 Week 61 Week Week 5 Week 8 Mar 2021 Week 9 Week 10 Week 11 Week 12 Safety S 650 Current 750 Jan 2021 Week Week 2 Week Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: P HD Powerhead Assembly Lot Size Rule: FOQ 2,000 Lead Time: 1 week Safety s Current 0 900 Jan 2021 Week 2 Week Feb 2021 Week 6 Week Mar 2021 Week 10 Week 11 Week 12 Week 4 Week 5 Week 8 Week Week Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: L Engine (Heavy Duty) Lot Size Rule: FOQ 1,000 Lead Time: 3 weeks Safety S Current 500 450 Jan 2021 Week 2 Week 3 Feb 2021 Week 51 Week 61 Week 7 Week 4 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week 8 Week Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Clodhopper Clodbuster 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 and your team have 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. These are parts for the two products above. 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. BA 357 Work Assignment 2 - Worksheet 1: MPS Approved plan Item: A Clodhopper Lot Size Rule: FOQ 2500 Lead Time: 1 week Safety Curren 1500 5500 Jan 2021 Week 2 Week 3 450 350 500 4600 4100 Week 5 600 Week 4 450 600 3500 450 Feb 2021 Week 6 Week 7 600 600 200 1000 2300 3800 Week 8 600 Mar 2021 Week 9 Week 10 Week 11 Week 12 2433 2432 2433 2432 2500 2000 3267 3267 3334 3402 2900 3200 Week 1 Forecast 450 Customer Orders 150 Projected On-Har 5050 MPS Scheduled Receipts MPS Quantity MPS Start Available to Prom 3700 Item: B Clodbuster 2500 2500 2500 2500 1500 2500 2500 2500 2500 2500 2500 2500 0 500 Lot Size Rule: FOQ 1000 Lead Time: 1 week 2500 Safety Curren 750 4000 Jan 2021 Week 2 Week 3 300 300 450 3400 2950 Week 41 300 Feb 2021 Week 6 Week 7 400 400 900 1350 950 Week 5 400 50 2250 Week 8 400 Mar 2021 Week 9 Week 10 Week 11 Week 12 1043 1042 1043 1042 3000 1550 1508 1465 1423 2650 1550 Week 1 Forecast 300 Customer Orders 200 Projected On-Har 3700 MPS Scheduled Receipts MPS Quantity MPS Start Available to Prom 2400 1000 1000 1000 3000 1000 3000 1000 0 1000 1000 1000 1000 1000 1000 1000 BA 357 Work Assignment 2 - Worksheet 2: Bill of Materials Data ClodHopper BOM (Product A) Name Quantity Assembly Time Lead Time A ClodHopper 1 1 week C Handle Bar Assembly 1 2 weeks D Steel Tubing 1" dia 10 feet 3 weeks E Control cable 5 feet 2 weeks F Control lever assembly 1 1 week G Grips 2 1 week H Powerhead assembly 1 1 week 1 Gearbox assembly 1 1 week J Engine (Standard) 1 2 weeks K Tiller blade unit 1 1 week M Instruction Manual 1 4 weeks ClodBuster BOM (Product B) Name Quantity Assembly Time Lead Time B ClodBuster 1 1 week Handle Bar Assembly 1 2 weeks D Steel Tubing 1" dia 10 feet 3 weeks E Control cable 5 feet 2 weeks F Control lever assembly 1 1 week G Grips 2 1 week P HD Powerhead assembly 1 1 week 1 Gearbox assembly 1 1 week L Engine (Heavy Duty) 1 3 weeks N HD Tiller Unit 1 2 weeks M Instruction Manual 1 4 weeks BA 357 Work Assignment 2 - Worksheet 3: Inventory Records and Lot-Sizing Rules Record Date: 31-Dec.-20 Item A B D E F G H 1 J K L M N P Name ClodHopper 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 Inventory On-Hand Lot-Size Rule 5500 FOQ 4000 FOQ 2600 L4L 50,000 POQ Period = 4 weeks 5,000 FOQ 2500 L4L 4000 L4L 700 FOQ 650 FOQ 500 FOQ 750 POQ Period = 2 weeks 450 FOQ 6000 FOQ 1250 POQ Period = 3 weeks 900 FOQ Lead or Desired Assembly Safety Lot Size Time Stock Level Units 2500 1 week 1500 1000 1 week 750 2 weeks 1000 3 weeks 20,000 feet 20,000 2 weeks 10,000 feet 1 week 1 week 4000 1 week 5000 1 week 750 2500 2 weeks 750 1 week 650 1000 3 weeks 500 10,000 4 weeks 2500 2 weeks 500 2000 1 week Week 8 Week 9 Week 10 Week 11 Week 12 BA 357 Work Assignment 2 - Worksheet 4: Scheduled Receipts 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 these quantities in "Projected on-hand inventory" - do not include them in "Planned order releases." Item Name Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 A Clod Hopper B ClodBuster C Handle Bar Assembly D Steel Tubing 1" dia (feet) 100,000 E Control cable (feet) 20,000 20,000 F Control lever assembly G Grips H Powerhead assembly 4000 1 Gearbox assembly 5000 J Engine (Standard) 2500 2500 K Tiller blade unit L Engine (Heavy Duty) 1000 1000 1000 M Instruction Manual 10,000 N HD Tiller Unit P HD Powerhead assembly 2000 2500 2500 1000 1000 1000 BA 357 Work Assignment 2 - Worksheet 5: MRP Form Item: C Handle Bar Assembly Lot Size Rule: L4L Lead Time: 2 weeks Safety S Current 1000 2600 Jan 2021 Week 2 Week 3 Feb 2021 Week 6 Week Week 4 Week 5 Week 8 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week 1 Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: E Control Cable Lot Size Rule: FOQ 20,000 Lead Time: 2 weeks Safety S Current 10,000 5,000 Jan 2021 Week 2 Week 31 Feb 2021 Week 6 Week 7 Week 4 Week 5 Week 8 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: H Powerhead Assembly Lot Size Rule: FOQ 4,000 Lead Time: 1 week Safety S Current 0 700 Jan 2021 Week 2 Week Week 4 Week 5 Feb 2021 Week 6 Week Mar 2021 Week 10 Week 11 Week 8 Week Week 12 Week 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 Week 4 Feb 2021 Week 61 Week Week 5 Week 8 Mar 2021 Week 9 Week 10 Week 11 Week 12 Safety S 650 Current 750 Jan 2021 Week Week 2 Week Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: P HD Powerhead Assembly Lot Size Rule: FOQ 2,000 Lead Time: 1 week Safety s Current 0 900 Jan 2021 Week 2 Week Feb 2021 Week 6 Week Mar 2021 Week 10 Week 11 Week 12 Week 4 Week 5 Week 8 Week Week Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases Item: L Engine (Heavy Duty) Lot Size Rule: FOQ 1,000 Lead Time: 3 weeks Safety S Current 500 450 Jan 2021 Week 2 Week 3 Feb 2021 Week 51 Week 61 Week 7 Week 4 Mar 2021 Week 9 Week 10 Week 11 Week 12 Week 8 Week Gross Requirements Scheduled Receipts Projected On-Hand Inventory Planned Receipts Planned Order Releases