This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 1 Table 1: Bill of Material Parent Comp Q-P LLC LEARNING MANUFACTURING RESOURCES PLANNING INTRODUCTION Manufacturing resources planning (MRP II) involves various time-phased calculations such as master production scheduling (MPS), material requirement planning (MRP), lot sizing, and capacity requirement planning (CRP). These functions include many data item definitions and formulas. Since a data item affects data in other periods or in other tables, traditional statement of formula may not provide a clear concept for the learners. Spreadsheet such as Microsoft Excel enables students without computer programming background to establish a system of formulas and facilitates their understanding of related concepts. We discuss the application of spreadsheet to learning master production scheduling and material requirement planning. The related definitions and formulas are discussed and sample tables are established. An experiment was conducted: The spreadsheet tables are used in teaching a group of students and are compared with another group taught with traditional approach. Both groups are required to write a test. The result is analyzed by statistical tests. Previous research applied spreadsheets in determining kanban size  and production planning . An example is described in section 2. Fundamental data including bill of material (BOM), item master, and inventory files are established in spreadsheets. In section 3, MPS and MRP are formulated with spreadsheets and related definitions and formulas are discussed. The advantages of teaching MPS and MRP with spreadsheet are discussed in section 4. Section 5 concludes our research. FUNDAMENTAL DATA OF AN EXAMPLE Suppose we have two finished goods A and B. Product A consists of component C and D, and product B of E and C, as shown in Figure 1. The BOM is shown in Table 1. The attributes in item master file related to MRP calculation include part number, lead-time, safety stock, safety time, and low-level-code (LLC). LLC used in sequencing the MRP calculation can be automatically determined from BOM. The item master is shown in Table 2. A B C D E C E F E C E F E F Figure 1: Product Structures 2 A C 2 2 A D 1 1 D E 2 3 D C 1 2 C E 1 3 C F 1 3 B E 1 3 B C 1 2 Table 2: Item Master Part-No LT ST SS LLC LSR LS SUC CC A 1 25 FOQ/LFL 1 5 0.05 B 1 20 FOQ/LFL 1 5 0.05 C 1 5 2 FOQ/LFL 500 10 0.01 D 1 5 1 FOQ/LFL 200 10 0.01 E 2 50 3 POQ/LFL 3 10 0.01 F 2 1 100 3 POQ/LFL 2 10 0.01 Notes : FOQ : Fixed Order Quanity LSR : Lot Size Rule LFL : Lot For Lot LS : Lot Size LT : Lead Time ST : Safe Time ex Inspection Time SS : Safe Stock LLC : Low Level Code Table 3: Inventory Scheduled Receipts (On-Order) P-No OH AL 1 2 3 4 5 6 7 8 9 10 A 20 100 B 40 50 100 C 60 200 150 D 60 20 E 100 1500 F 100 1000 Notes : AL : Allocate Lot &#2; (&#2; )&#2; OH : On-Hand &#2; Table1, 2 and 3 are all spreadsheet tables. Quantity-per in BOM, lead time, safety time and safety stock in item master, and all the quantities in inventory file are adjustable. Whatever data are adjusted,stock in item master, and all the quantities in inventory file are adjustable....
View Full Document