PLEASE CHECK THE NUMBERS ARE DIFFERENT PLEASE DO NOT SEND ME AN AUTOMATED ANSWER

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 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. 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.

Make sure to include your names on the first page.

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 2022.

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 2022.

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 2022 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.

Scheduled Receipts MRP Form - Results MPS -Approved BOMS Inventory Records BA 357 Work Assignment 2 - Worksheet 1: MPS Approved plan Rem: A Clodhopped Ld Size Rule: FOQ 2500 Lead Time: 1 week Safety Stock Level: Current Inventory: 1500 5500 Jan 2022 Week 2 Week 3 Feb 2022 Week 6 Week 7 Mar 2022 Week 9 Week 10 Week 11 Week 12 Week 1 Week 4 Week 5 Week 8 Forecast 450 450 450 450 600 600 600 600 2433 2432 2433 2432 Customer Orders Booked 150 350 500 600 200 1000 2500 2000 Projected On-Hand Inventory 5050 4600 4100 3500 2900 2300 3800 3200 3267 3267 3334 3402 MPS Sheduled Receipts MPS antity 2500 2500 2500 2500 2500 MPS Sart 2500 2500 2500 2500 2500 Available to Promise 3700 1500 2500 0 500 2500 Item: Clodbuster Le Size Rule: FOQ 1000 Lead Time: 1 week Safety Stock Level: Current Inventory: 750 40001 Jan 2022 Week 2 Week 3 Feb 2022 Week 61 Week 7 Week 41 Mar 2022 Week 10 Week 11 Week 1 Week 5 Week 8 Week 9 Week 12 Foreca 300 300 300 300 400 400 400 400 1043 1042 1043 1042 Customer Orders Booked 200 450 50 900 3000 Projected On-Hand Inventory 3700 3400 2950 2650 2250 1350 950 1550 1550 1508 1465 1423 MPS Stheduled Receipts MPS Quantity 1000 3000 1000 1000 1000 MPS Start 1000 3000 1000 1000 1000 Available to Promise 2400 1000 0 1000 1000 1000 BA 357 Work Assignment 2 - Worksheet 2: Bill of Materials Data ClodHopper BOM (Product A) Lead Time A D E F G Name ClodHopper Handle Bar Assembly Steel Tubing 1" dia Control cable Control lever assembly Grips Powerhead assembly Gearbox assembly Engine (Standard) Tiller blade unit Instruction Manual Quantity Assembly Time 1 1 week 1 2 weeks 10 feet 5 feet 1 2 1 1 week 1 1 1 1 3 weeks 2 weeks 1 week 1 week H 1 1 week 2 weeks 1 week 4 weeks K M ClodBuster BOM (Product B) 00 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 1 4 weeks 1 L N M BA 357 Work Assignment 2 - Worksheet 3: Inventory Records and Lot-Sizing Rules Record Date: 31-Dec-21 Lot Size 2500 1000 20.000 Item A B D E F G H 1 J 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 Inventory On-Hand 5500 4000 2600 50,000 5,000 2500 4000 700 650 500 750 450 6000 1250 900 Lot-Size Rule FOQ FOQ L4L POQ Period = 4 weeks FOQ L4L L4L FOQ FOQ FOQ POQ Period 2 weeks = FOQ FOQ POQ Period 3 weeks FOQ 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 650 3 weeks 500 4 weeks 2500 2 weeks 500 1 week 4000 5000 2500 1000 10,000 2000 BA 357 Work Assignment 2 - Worksheet 4: Scheduled Receipts Note: These are purchase order and production commitments scheduled in 2021 that are already scheduled for completion in 2022. 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." Week 1 Week 21 Week 31 Week 4 Week 5 Week 6 Week 7 Week 8 Woek 9 Week 101 Week 11 Week 12 Item A B C D E F 100,000 20.000 20,000 Name Clod Hopper ClodBuster Handle Bar Assembly Steel Tubing 1 dia feet) Control cable feet) Control lever assembly Gripe Powerhead assembly Gembok sembly Engine (Standard) Tiler blade und Engine (Heavy Duty) Instruction Manual HD Tiller Unit HD Powerhead assembly 40001 5000 2500 2500 2500 2500 1 J L M N P 1000 1000 1000 1000 1000 1000 10.000 2000 BA 357 Work Assignment 2 - Worksheet 5: MRP Form mer Panda Bar SRL LASER Butely love Cameron Wook Jan 2002 We Feb 2002 We We We W We Grosse Sched Recept recte On-Hand inventory and ecripta Controle Best FOOD La 10.000 Sabtyp Level Current story Jan 2022 Week 2 We Feb 2922 Wet W We Week We We Vio Groete Scheue Receita Projecte On Hand vertory decis Planned deres Powered As RO<.001 leadme soy l curatory wel fd weke week mar2002 work wok w wik gros scho rece projection hand inventory predeces tanned pride ank tamar badent pools led wwwpoke current jan w2 we feb m wv scheece proiecte on plannede pro item: p hd powerad assam le size rule fog lead time: safety sock level currentventory mar b gross requirements schedule receipts projecte planned recept prder releases hom: lingine howy d rue time entory woek weeks won scheduled mrp form results mps boms records ba assignment worksheet approved plan rem: a clodhopped ld rule: foq stock level: inventory: forecast customer orders booked projected on-hand sheduled antity sart available to promise clodbuster foreca stheduled quantity start bill of materials data clodhopper bom e f g name handle bar assembly steel tubing dia control cable lever grips powerhead gearbox engine tiller blade unit instruction manual feet h k duty n and lot-sizing rules record date: lot item j clod hopper lot-size l4l poq period="4" or desired units note: these are purchase order production commitments in that already for completion the sizes lots listed by delivery. include quantities inventory.do not them releases. c gripe gembok sembly tiler und mer panda srl laser butely love cameron wook grosse sched recte ecripta controle best food la sabtyp story wet vio groete scheue receita vertory decis deres powered as ro>