Excel Templates to accompany Operations Management, Eleventh Edition
created by Lee Tangedahl
Copyright © 2012 by The McGraw Hill Companies, Inc. All rights reserved.
Chapter Twelve – MRP and ERP
Templates: Component Requirements Solved Problems: Solved Problem 1
Examples: Example 1
Example 2a
Example 2b
See Instructions template for complete instructions.
MRP Solved Problem 2
Capacity Requirements Planning (B) Solved Problem 3
Lecture Suggestions
Component Requirements
<Back X10
B 2 C
D 3 F 2
Clear
MRP
MRP
<Back
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 100 150
Shutters Gross requirements 0 0 0 100 000150
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1
Net requirements
000100 000150
Lot Size = Planned-order receipts 0 0 0 100 000150
On hand =
Planned-order releases
0 0 100 0 0 0 150 0
Clear
Page 3
Frames Gross requirements 0 0 200 0 0 0 300 0 Wood sections Gross requirements 0 0 400 000600 0 Gross requirements 0 0 0 0 0 0 0 0
Scheduled receipts Scheduled receipts 70 Scheduled receipts
Quantity = 2 Projected on hand 0 0 0 0 0 0 0 0 Quantity = 4 Projected on hand 70 70 70 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0
LT = 2
Net requirements
Net requirements
Net requirements
Lot Size = Planned-order receipts 0 0 200 0 0 0 300 0 Lot Size = Planned-order receipts 0 0 330 000600 0 Lot Size = Planned-order receipts 0 0 0 0 0 0 0 0
On hand =
Planned-order releases
Planned-order releases
Planned-order releases
Gross requirements 0 0 0 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0
Scheduled receipts Scheduled receipts Scheduled receipts
Quantity = Projected on hand 0 0 0 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0
LT =
Net requirements
Net requirements
Net requirements
Lot Size = Planned-order receipts 0 0 0 0 0 0 0 0 Lot Size = Planned-order receipts 0 0 0 0 0 0 0 0 Lot Size = Planned-order receipts 0 0 0 0 0 0 0 0
On hand =
Planned-order releases
Planned-order releases
Planned-order releases
Capacity Requirements Planning Basic
<Back
Hours/
Standard Time: unit Capacity
Labor 0.5 200
Production Schedule:
Week 1 2 3 4
Quantity 200 300 100 150
Labor Hours 100 ( 50% ) 150 ( 75% ) 50 ( 25% ) 75 ( 38% )
Machine Hours 200 ( 80% ) 300 ( 120% ) 100 ( 40% ) 150 ( 60% )
Lecture Suggestions – Chapter 14
<Back
Example 2: MRP
2. Clear worksheet (press Clear and confirm with OK).
3. Initialize MRP schedule with product structure:
4. Enter Scheduled receipts = 70 in period 1 for wood sections.
5. Enter orders in master schedule: 100 in week 4 and 150 in week 8
6. Trace orders down to planned order releases for each subassembly.
7. Enter lot size = 70 for wood sections and trace the effect in the table for wood sections..
1. Select the Example 2 worksheet
Component Requirements
<Back X10
B 2 C
Number On-Hand: Requirements:
B 4 Level 0: X 10 Level 2: D 40 Level 3: E 116
C10
Clear
Example 2a
MRP
<Back
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 100 150
Shutters Gross requirements 0 0 0 100 000150
Scheduled receipts
Scheduled receipts Scheduled receipts Scheduled receipts
Quantity = Projected on hand 0 0 0 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0
LT = Net requirements 0 0 0 0 0 0 0 0 LT = Net requirements 0 0 0 0 0 0 0 0 LT = Net requirements 0 0 0 0 0 0 0 0
Lot Size =
Planned-order receipts
0 0 0 0 0 0 0 0 Lot Size =
Planned-order receipts
0 0 0 0 0 0 0 0 Lot Size =
Planned-order receipts
00000000
On hand = Planned-order releases 0 0 0 0 0 0 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0
Clear
Page 7
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 100 000150
Lot Size =
Planned-order receipts
On hand = Planned-order releases 0 0 100 000150 0
Frames Gross requirements 0 0 200 000300 0 Wood sections Gross requirements 0 0 400 000600 0 Gross requirements 0 0 0 0 0 0 0 0
Scheduled receipts Scheduled receipts 70 Scheduled receipts
Quantity = 2 Projected on hand 0 0 0 0 0 0 0 0 Quantity = 4 Projected on hand 70 70 70 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0
LT = 2 Net requirements 0 0 200 000300 0 LT = 1 Net requirements 0 0 330 000600 0 LT = Net requirements 0 0 0 0 0 0 0 0
Lot Size =
Planned-order receipts
Planned-order receipts
Planned-order receipts
00000000
On hand = Planned-order releases 200 000300 0 0 0 On hand = Planned-order releases 0 330 000600 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0
Gross requirements 0 0 0 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0
Example 2b
MRP
<Back
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 100 150
Shutters Gross requirements 0 0 0 100 000150
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 100 000150
Lot Size =
Planned-order receipts
000100 000150
On hand = Planned-order releases 0 0 100 000150 0
Frames Gross requirements 0 0 200 000300 0 Wood sections Gross requirements 0 0 400 000600 0 Gross requirements 0 0 0 0 0 0 0 0
Scheduled receipts Scheduled receipts 70 Scheduled receipts
Quantity = 2 Projected on hand 0 0 0 120 120 120 120 140 Quantity = 4 Projected on hand 70 70 70 20 20 20 20 50 Quantity = Projected on hand 0 0 0 0 0 0 0 0
Clear
LT = 2 Net requirements 0 0 200 000180 0 LT = 1 Net requirements 0 0 330 000580 0 LT = Net requirements 0 0 0 0 0 0 0 0
Lot Size = 320
Planned-order receipts
Planned-order receipts
Planned-order receipts
On hand = Planned-order releases 320 000320 0 0 0 On hand = Planned-order releases 0 350 000630 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0
Gross requirements 0 0 0 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0
Scheduled receipts Scheduled receipts Scheduled receipts
Quantity = Projected on hand 0 0 0 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0
LT = Net requirements 0 0 0 0 0 0 0 0 LT = Net requirements 0 0 0 0 0 0 0 0 LT = Net requirements 0 0 0 0 0 0 0 0
Lot Size =
Planned-order receipts
Planned-order receipts
Planned-order receipts
On hand = Planned-order releases 0 0 0 0 0 0 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0
Component Requirements
<Back
W100
A B 2 C 4
E E 2 F G 2
D 2 D 3 D
Clear
Solved Problem 2
MRP
<Back
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 120
Item: E Gross requirements 0 0 0 0 120 0 0 0
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 0 120 0 0 0
Lot Size =
Planned-order receipts
0000120 0 0 0
On hand = Planned-order releases 0 0 0 120 0 0 0 0
Clear
Page 10
Item: M Gross requirements 0 0 0 360 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0
Scheduled receipts 60 Scheduled receipts Scheduled receipts
Quantity = 3 Projected on hand 0 60 60 60 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 300 0 0 0 0 LT = Net requirements 0 0 0 0 0 0 0 0 LT = Net requirements 0 0 0 0 0 0 0 0
Lot Size =
Planned-order receipts
000300 0 0 0 0 Lot Size =
Planned-order receipts
Planned-order receipts
On hand = Planned-order releases 0 0 300 0 0 0 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0
Item: R Gross requirements 0 0 600 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0 Gross requirements 0 0 0 0 0 0 0 0
Scheduled receipts 100 Scheduled receipts Scheduled receipts
Quantity = 2 Projected on hand 100 100 100 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0 Quantity = Projected on hand 0 0 0 0 0 0 0 0
LT = 2 Net requirements 0 0 500 0 0 0 0 0 LT = Net requirements 0 0 0 0 0 0 0 0 LT = Net requirements 0 0 0 0 0 0 0 0
Lot Size =
Planned-order receipts
Planned-order receipts
Planned-order receipts
On hand = Planned-order releases 500 0 0 0 0 0 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0 On hand = Planned-order releases 0 0 0 0 0 0 0 0
Capacity Requirements Planning
<Back
Hours/
Standard Time: unit Capacity
Production Schedule:
Week 1 2 3 4
Quantity 200 300 100 150
Labor Hours 100 ( 50% ) 150 ( 75% ) 50 ( 25% ) 75 ( 38% )
Machine Hours 200 ( 80% ) 300 ( 120% ) 100 ( 40% ) 150 ( 60% )
Chapter 12 – Problems 1-6
Note: This worksheet displays results only, you must copy the shaded
<Back area into the corresponding template to make additional calculations.
1. Component Requirements
Requirements:
Number On-Hand: Requirements:
2. Component Requirements
End 20
B 2 C
E 2 F 3 G 2 E 2 H 4
3. Component Requirements
End
40
L 2 C
B 2 J 3
G
2 B 2 H 4
L10 Level 0: End 40 Level 2: B 360 Level 3:
C15 J180
K20 Level 1: L 70
4. MRP
Master Schedule
Week Number 1 2 3 4 5 6 7 8
Quantity 80
E Gross requirements 0 0 0 0 0 80 0 0
Scheduled receipts
B Gross requirements 0 0 0 160 0 0 0 0 J Gross requirements 0 0 0 240 0 0 0 0
Scheduled receipts Scheduled receipts 30 30 30
On hand = 60 Planned-order releases 0 120 0 0 0 0 0 0 On hand = 20 Planned-order releases 0 0 180 0 0 0 0 0
J Gross requirements 0 480 0 0 0 0 0 0 F Gross requirements 0 240 0 0 0 0 0 0
Scheduled receipts Scheduled receipts
Quantity = 4 Projected on hand 0 0 0 0 0 0 0 0 Quantity = 2 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 480 0 0 0 0 0 0 LT = Net requirements 0 240 0 0 0 0 0 0
B30 C25 G45
Note: the MRP template is unable to combine the 2 entries for J.
5cd.
MRP
Master Schedule
Week Number 1 2 3 4 5 6 7 8
Quantity 100 100
P Gross requirements 0 0 0 0 0 100 100 0
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
K Gross requirements 0 0 0 0 100 100 0 0
Scheduled receipts 10 30
Quantity = 1 Projected on hand 0 0 10 10 10 30 0 0
Onhand = Planned-order releases 0 0 90 70 0 0 0 0
G Gross requirements 0 0 270 210 0 0 0 0 H Gross requirements 0 0 360 280 0 0 0 0
Scheduled receipts Scheduled receipts
Quantity = 3 Projected on hand 40 40 40 0 0 0 0 0 Quantity = 4 Projected on hand 200 200 200 0 0 0 0 0
Note: scrap allowance and minimum order not included in template.
6. MRP
Master Schedule
Week Number 1 2 3 4 5 6 7 8
Quantity 100 150 200
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 100 150 0200 0
Lot Size = 1 Planned-order receipts 0 0 0 100 150 0200 0
On hand = Planned-order releases 0 0 100 150 0200 0 0
Wood sections
Gross requirements 0 0 200 300 0400 0 0 Braces Gross requirements 0 0 300 450 0600 0 0
Scheduled receipts 100 Scheduled receipts
Chapter 12 – Problems 9-16 Note: This worksheet displays results only, you must copy the shaded
<Back area into the corresponding template to make additional calculations.
9. MRP
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 50
Saw Gross requirements 0 0 0 0 0 0 0 50
Scheduled receipts
Quantity = 1 Projected on hand 15 15 15 15 15 15 15 15
LT = 2 Net requirements 0 0 0 0 0 0 0 35
Lot Size = Planned-order receipts 0 0 0 0 0 0 0 35
On hand = 15 Planned-order releases 0 0 0 0 0 35 0 0
10. MRP
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 40
Robot Gross requirements 0 0 0 0 0 0 40 0
Scheduled receipts C Gross requirements 0 0 0 0 90 000
Quantity = 1 Projected on hand 10 10 10 10 10 10 10 0 Scheduled receipts
LT = 2 Net requirements 0 0 0 0 0 0 30 0 Quantity = 3 Projected on hand 20 20 20 20 20 000
Lot Size = 1 Planned-order receipts 0 0 0 0 0 0 30 0 LT = 1 Net requirements 0 0 0 0 70 000
On hand = 10 Planned-order releases 0 0 0 0 30 0 0 0 Lot Size = 1 Planned-order receipts 0 0 0 0 70 000
G Gross requirements 0 0 0 0 30 000
Scheduled receipts 20 G Gross requirements 0 0 0 140 0000
Quantity = 1 Projected on hand 15 15 15 35 35 5 5 5 Scheduled receipts -20
On hand = 15 Planned-order releases 0 0 0 0 0 0 0 0 Lot Size = 80 Planned-order receipts 0 0 0 160 0000
11a. MRP
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 120
E Gross requirements 0 0 0 0 120 000
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 0 120 000
Lot Size = 1 Planned-order receipts 0 0 0 0 120 000
On hand = 0 Planned-order releases 0 0 0 120 0000
I Gross requirements 0 0 0 240 0000
Scheduled receipts 40
On hand = 0 Planned-order releases 0 0 200 00000
N Gross requirements 0 0 800 0 0 0 0 0 V Gross requirements 0 0 200 00000
Scheduled receipts Scheduled receipts 10
On hand = 100 Planned-order releases 700 0 0 0 0 0 0 0 On hand = Planned-order releases 190 0000000
11b. MRP
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 100 55
E Gross requirements 0 0 0 0 100 055 0
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 0 100 055 0
Lot Size = 1 Planned-order receipts 0 0 0 0 100 055 0
On hand = 0 Planned-order releases 0 0 0 100 055 0 0
I Gross requirements 0 0 0 200 0110 0 0
Scheduled receipts 40
On hand = 0 Planned-order releases 0 0 160 0110 000
A Gross requirements 0 0 0 0 0 70 0 0 C Gross requirements 0 0 0 0 0 105 0 0
Scheduled receipts Scheduled receipts
On hand = 10 Planned-order releases 0 0 0 0 60 0 0 0 On hand = 30 Planned-order releases 0 0 0 75 0000
E Gross requirements 0 0 0 0 180 0 0 0 E Gross requirements 0 0 0 150 0000
Scheduled receipts Scheduled receipts
On hand = Planned-order releases 0 0 0 180 0 0 0 0 On hand = 10 Planned-order releases 0 0 140 00000
11c. MRP
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 100 55 100
E Gross requirements 0 0 0 100 055 0100
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 100 055 0100
Lot Size = 1 Planned-order receipts 0 0 0 100 055 0100
On hand = 0 Planned-order releases 0 0 100 055 0100 0
I Gross requirements 0 0 200 0110 0200 0
Scheduled receipts 40
On hand = 0 Planned-order releases 0 160 0110 0200 0 0
N Gross requirements 0 640 0440 0800 0 0 V Gross requirements 0 160 0110 0200 0 0
Scheduled receipts Scheduled receipts 10
On hand = 100 Planned-order releases 0 800 0800 0 0 0 0 On hand = Planned-order releases 0 200 0200 0000
MRP
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 100 55 100
E Gross requirements 0 100 055 0100 0 0
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 100 055 0100 0 0
Lot Size = 1 Planned-order receipts 0 100 055 0100 0 0
On hand = 0 Planned-order releases 100 055 0100 000
I Gross requirements 200 0110 0200 000
Scheduled receipts
N Gross requirements 0 440 0800 0 0 0 0 V Gross requirements 0 110 0200 0000
Scheduled receipts Scheduled receipts
On hand = 100 Planned-order releases 0 800 0 0 0 0 0 0 On hand = Planned-order releases 0 200 000000
15. MRP
Master Schedule Week Number 1 2 3 4 5 6 7 8
Quantity 180
565 Gross requirements 0 0 0 0 0 180 0 0
Scheduled receipts
Quantity = 1 Projected on hand 0 0 0 0 0 0 0 0
LT = 1 Net requirements 0 0 0 0 0 180 0 0
Lot Size = 1 Planned-order receipts 0 0 0 0 0 180 0 0
On hand = 0 Planned-order releases 0 0 0 0 180 000
Y36 Gross requirements 0 0 0 0 360 000
Scheduled receipts
On hand = 200 Planned-order releases 0 0 0 160 0000
Scheduled receipts
On hand = 0 Planned-order releases 0 640 000000
16. Capacity Requirements Planning
Hours/
Standard Time:
unit
Capacity
Labor 4 300
Machine 3 200
Production Schedule:
Machine Hours
N Gross requirements 0 0 640 0440 0 0 0 V Gross requirements 0 0 160 0110 000
Scheduled receipts Scheduled receipts 10
On hand = 100 Planned-order releases 800 0800 0 0 0 0 0 On hand = Planned-order releases 200 0200 00000