Question

Complete the MRP record for a bicycle frame using a L4L lot-sizing strategy. Repeat using a fixed order quantity of 106 frames. Compare and contrast the results. What are the benefits and drawbacks to each approach? (Leave no cells blank - be certain to enter "O" wherever required.) picture Click here for the Excel Data File Part Name: Bicycle frame Week 6 Week 5 Week 7 Week 8 Week 1 Week 3 Week 2 Week 4 MRP Record Lead time = 2 week On hand = 0 Safety stock = 0 Order quantity: L4L Gross requirements Scheduled receipts Available inventory Net requirements Planned order receipts Planned order releases 86 8 6 76 66 86 86 000 000

Please show all work. Thanks in advance

Answer 1

Below is the screenshot of the formula applied -

mos - Excel Abhishek Rai - 0 ESSE File Home Insert Page Layout Formulas Data Review View Help * Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used - - Time Reference Trigº Functions Manager Function Library fx ? A .l le - O Calculate Now Tell me what you want to do Define Name bo Trace Precedents Show Formulas f Use in Formula - Trace Dependents ! Error Checking Create from Selection Remove Arrows - Evaluate Formula Defined Names Formula Auditing Watch Window Calculation Calculate Sheet Options Calculation D15 x foco for A B On Hand 1 70 86 86 70 LT 2 o Gross Requirement Scheduled Receipts Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases 56 56 =D4+E6+E3-E2 =IF((D4+E3-E2)>0,0,(D4+E3-E2)*(-1)) Q = L4L =C4+06+D3-D2 =1F((C4+D3-D2)>0,0,(C4+D3-D2)*(-1)) =E4+F6+F3-F2 =1F((E4+F3-F2)>0,0,(E4+F3-F2)*(-1)) 86 =F4+G6+G3-G2 =1F((F4+G3-G2)>0,0,(F4+G3-G2)*(-1)) 106 LT 2 Q = 106 Gross Requirement Scheduled Receipts Projected on Hand Net Requirements Planned Order Receipts Planned Order Releases On Hand 1 70 56 86 86 106 =C12+D14+D11-D10 =D12+E14+E11-E10 =E12+F14+F11-F10 =F12+G14+G11-G10 =1F((C12+011-010)>0,0,(C12+D11-010)*(-1)) =IF((D12+E11-E10)>0,0,(012+E11-E10)*(-1)) =1F((E12+F11-F10)>0,0,(E12-F11-F10)*(-1)) =1F((F12+G11-G10)>0,0,(F12+G11-G10)*(-1)) 106 106 106 106

Below is the result in tabular form -

LT 2 Q = L4L		On Hand	1	2	3	4	5	6	7	8
	Gross Requirement		70	56	86	86	76	66	86	86
	Scheduled Receipts		70	56	0	0	0	0	0	0
	Projected on Hand	0	0	0	0	0	0	0	0	0
	Net Requirements		0	0	86	86	76	66	86	86
	Planned Order Receipts		0	0	86	86	76	66	86	86
	Planned Order Releases		86	86	76	66	86	86	0	0
LT 2 Q = 106		On Hand	1	2	3	4	5	6	7	8
	Gross Requirement		70	56	86	86	76	66	86	86
	Scheduled Receipts		106	106	0	0	0	0	0	0
	Projected on Hand	0	36	86	0	20	50	90	4	24
	Net Requirements		0	0	0	86	56	16	0	82
	Planned Order Receipts		0	0	0	106	106	106	0	106
	Planned Order Releases		0	106	106	106	0	106	0	0

In L4l method, holding cost is minimum whereas ordering is high.

In Fixed Order quantity, holding cost is high whereas ordering cost is lower than L4L.