Question

An office supply company makes notebooks with the following product structure:

NOTEBOOK------PAD----COVER. 

 The company uses an MRP system and has the following records for these three items

Notebook		Week
		1	2	3	4	5	6
Gross Requirements		25	25	25	25	25	25
Scheduled Receipts
Projected Balance Available	30	5
Planned Order Releases		20	25	25	25	25	0
Quantity = L4L, Lead-time = 1, Safety Stock = 0

Pad		Week
		1	2	3	4	5	6
Gross Requirements		20	25	25	25	25	0
Scheduled Receipts		15
Projected Balance Available	10	5	10	15	20	25	25
Planned Order Releases		30	30	30	30	0	0
Quantity = 30, Lead-time = 1, Safety Stock = 0

Cover		Week
		1	2	3	4	5	6
Gross Requirements		20	25	25	25	25	0
Scheduled Receipts		50
Projected Balance Available	10	40	15	40	15	40	40
Planned Order Releases		50	0	50	0	0	0
Quantity = 50, Lead-time = 2, Safety Stock = 0

The note books are fabricated in the following work centres listed with accompanying time requirements

Determine the weekly capacity requirements in each of the work centres.

For the two scheduled receipts, assume that the setup is complete and that in each case they are half complete.

Your Answer should fit in the following table:

WorkCentre	Week
	1	2	3	4	5	6
100
200
300

 PLEASE SHOW ALL THE POSSIBLE CALCULATIONS AS TO HOW THE ANSWER IS CALCULATED. DONT GIVE ONLY THE FINAL ANSWER IN THE TABLE. I WANT ALL THE CALCULATIONS SO I CAN UNDERSTAND HOW IT IS DONE. THANK YOU,

Answer 1

821 Q fx IF(B6>0,$C13,0)+B6*$D13/60+50.2 D13/60 1 Planned Order Releases Week 3 Item 4 Notebook Assembl 5 Pad Production 6 Cover Production 20 30 50 25 25 30 50 25 25 30 30 9 Fabrication time requirements Work Setup Center Time (hrs 10 Operation 11 Notebook Assembl 12 Pad Production 13 Cover Production Run Time 10 100 200 300 15 16 Weekly Capacity requirements 17 18 19 20 21 Week Work Center 100 200 300 5.33 6.13 17.03 6.17 5.50 0.00 6.17 5.50 9.50 6.17 5.50 0.00 6.17 0.00 0.00 0.00 0.00 0.00 25

EXCEL FORMULAS:

Weekly Capacity requirements
	Week
Work Center	1	2	3	4	5	6
100	=IF(B4>0,$C11,0)+B4*$D11/60	=IF(C4>0,$C11,0)+C4*$D11/60	=IF(D4>0,$C11,0)+D4*$D11/60	=IF(E4>0,$C11,0)+E4*$D11/60	=IF(F4>0,$C11,0)+F4*$D11/60	=IF(G4>0,$C11,0)+G4*$D11/60
200	=IF(B5>0,$C12,0)+B5*$D12/60+15/2*D12/60	=IF(C5>0,$C12,0)+C5*$D12/60	=IF(D5>0,$C12,0)+D5*$D12/60	=IF(E5>0,$C12,0)+E5*$D12/60	=IF(F5>0,$C12,0)+F5*$D12/60	=IF(G5>0,$C12,0)+G5*$D12/60
300	=IF(B6>0,$C13,0)+B6*$D13/60+50.2*D13/60	=IF(C6>0,$C13,0)+C6*$D13/60	=IF(D6>0,$C13,0)+D6*$D13/60	=IF(E6>0,$C13,0)+E6*$D13/60	=IF(F6>0,$C13,0)+F6*$D13/60	=IF(G6>0,$C13,0)+G6*$D13/60

Answer 2

Operation	Work Center	Setup time	Run time
Notebook assembly	100	2 hrs	10 min
Pad production	200	3 hrs	5 min
Cover production	300	2 hrs	9 min

Notebook assembly:

Weekly capacity requirement for work centre 100

Requirement for week 1 = 2+20*10/60 = 5.33 hours

Requirement for week 2 = 2+25*10/60 = 6.17 hours

Requirement for week 3 = 2+25*10/60 = 6.17 hours

Requirement for week 4 = 2+25*10/60 = 6.17 hours

Requirement for week 5 = 2+25*10/60 = 6.17 hours

Requirement for week 6 = 0+0*10/60 = 0 hours

Pad production:

Weekly capacity requirement for work centre 200

Requirement for week 1 = 3+30*5/60+5*0.5*15/60 = 6.13 hours

Requirement for week 2 = 3+30*5/60 = 5.5 hours

Requirement for week 3 = 3+30*5/60 = 5.5 hours

Requirement for week 4 = 3+30*5/60 = 5.5 hours

Requirement for week 5 = 0+0*5/60 = 0 hours

Requirement for week 6 = 0+0*5/60 = 0 hours

Cover production:

Weekly capacity requirement for work centre 300

Requirement for week 1 = 2+50*9/60+9*0.5*50/60 = 13.25 hours

Requirement for week 2 = 0+0*9/60 = 0 hours

Requirement for week 3 = 2+50*9/60 = 9.5 hours

Requirement for week 4 = 0+0*9/60 = 0 hours

Requirement for week 5 = 0+0*9/60 = 0 hours

Requirement for week 6 = 0+0*9/60 = 0 hours