Question

Refer to the Excel speadsheet, "Inventory Management," to complete the "Analytics Exercise: An MRP Explosion - Brunswick Motors," at the end of Chapter 21 in the textbook. Answer Questions 1-3.

Given Spread Sheet: A-Topic 3 Part 1&2 AND  A-Topic 3 Part 3

Part 2 Gear Box Given Information Setu Invento Number of orders (count cells with values for planned order release Set-up Costs=# of Orders X Setup Costs 8 90 Inventoryl (2+2) "Inventory Carrying Cost Total r order 90.00 ing Cost per unit r $2.00 $0.00 Input Shaft Given Information Setu Invento $45.00 eri $1.00 r order- Setup Costs-5 orders 45 Invent Total ying Cost per unit 30+32+32+2)1 0.00 Total Cost $0.00 A-Topic 3 Part 1842 A-Topic 3 Part 3 Part 2 Gear Box Given Information A-Topic 3 Part Number of orders (count cells with values for Set-up Costs-l of Orders XS 3*90 order release Costs Cost per unit Cost of projected available balance Total $0.00 t Shaft Given Information r order- Costs 2 orders 45 74) 1 Cost per unit 1.00 Total $0.00 Total Cost So

Please use given information, show formulas/ calculations, and give reasons behind answer.

Answer 1

Information about On hand inventory and scheduled receipts is not given in the question. However, this schedule required beginning on hand iventory. Considering the following information about on hand inventory and scheduled receipts from textbook.

Item	Lead Time	On Hand	Scheduled Receipts (SOR)
Engine Assembly	0
Gear box	2	17	5(2)
Input shaft	3	40	22(2)

Resulting MRP plan is following:

Steps to create the MRP table 1) Enter the gross requirement as per Master Planning Schedule (MPS). Gross requirement of components is calculated as per usage in parent item. 2) Enter Schedule Order Receipts (SOR), if there are any 3) Project On hand is calculated by this formula = Previous period project on hand +scheduled receipts + Planned order receipts- gross requirement 4) Net requirement Gross requirement Projected on hand 5) Planned Order Release (POR) is manually entered such that time phased planned order receipt is equal to net requirement. For example, if lead time is 2, and there is net requirement of 10 units in period 4, then there is a planned order release for 10 units in period 2, i.e. 2 periods prior to the period in which requirement occurs. Planned Order Release quantity is as per the Lot size specified, i.e. LFL, or Fixed Order Quantity, or POQ or EOQ or in Multiples of a specified quantity 6) If there is safety stock specified for an item, then safety stock quantity is added in Planned Order Release so that Project On Hand remains greater than or equal to safety stock as specified

MRP Schedule 10 Item 16 10 15 20 10 Engine Assembly 15 10 Item Gear box SOR5(2) Scheduled Receipt 16 10 15 20 10 15 Gross Requirement 17 Proiected on Hand Net Requirement Planned Order Receipt Planned Order Release 15 15 10 10 10 16 16 20 10 10 15 20 16 10 10 Item Input shaft Gross Requirement SOR22(2) Scheduled Receipt 16 32 20 30 20 10 40 30 32 32 Proiected on Hand Net Requirement Planned Order Receipt Planned Order Release 16 16 32 32 20 38 38 16 20 32 38

	Setup	Inventory	Total
Gear Box	90x8=720	2x4=8	720+8=728
Input Shaft	45x5=225	1x96=96	225+96=321
Total Cost			728+321=1049