SPREADSHEET SIMULATOR FOR INVENTORY
MANAGEMENT IN A SUPPLY CHAIN
Sakir Esnaf
Istanbul University, Faculty of Business Administration
Department of Production Management
Avcilar,Istanbul 34850 Turkey
Phone:90 212 590 14 27 ext.269
Fax:90 212 590 40 00
e-mail:sesnaf@istanbul.edu.tr
ABSTRACT
The proper management of inventories in a supply chain is a complex problem. Recently, the use of directly related DRP and MRP systems in supply chains have been increasing. The spreadsheet-based simulator described in this paper was developed to integrate DRP and MRP systems for a proposed hypothetical company . A sample problem that considers all related costs was designed to illustrate how the interrelated excel worksheet-based simulator works. The spreadsheet-based simulator can also be used as an experiential learning activity for teaching inventory management, production planning and supply chain concepts.
Keywords:Supply Chain, Spreadsheet-based Simulator, DRP/MRP integration. INTRODUCTION
The supply chain encompasses all activities associated with the flow and transformation of goods from the raw materials stage , through to the end user, as well as the associated information flows. Material and information flow both up and down the supply chain (Handfield and Nichols 1999). Supply chain management (SCM) is a set of approaches utilized to efficiently integrate suppliers , manufacturers, warehouses, and stores, so that merchandise is produced and distributed at the right quantities , to the right locations, and at the right time , in order to minimize systemwide costs while satisfying service level requirements (Simchi-Levi et. al 2000). In this manner, for organizations that manage their inventories in their supply chains, performance can be improved by an integrated system that combines DRP and MRP. Tersine (1988), Ballou (1999), and Greene (1997) have discussed the benefits that can be accomplished by the integration of DRP and MRP. In this study, the spreadsheet-based simulator was proposed to integrate DRP and MRP systems which are more extensively used nowadays. Excel worksheets were developed for either open ended simulations (so called "what-if-scenarios") or to simulate the integrated system with or without Crystal Ball. A problem was designed to help improve the understanding of how integrated DRP/MRP systems work in a supply chain.
SUPPLY CHAIN OF THE HYPOTHETIC COMPANY
An hypothetic company for a which spreadsheet simulator was developed was assumed to be a produce-to-stock company that producing and selling single product through their own distribution network. The distribution system consists of a main distribution center that supplies three local distribution centers. The production system manufactures product A which requires 4 levels of operations, with assembly at two levels.
Company purchases parts C,D from the same supplier and part F, and raw material H are purchased from two different suppliers. The supply chain of the company is illusrated in Figure 1. Product A requires 11 units of purchased parts C, and D, alongside with the subassembly B which requires fabricated part E and 11 units of purchased part F. Fabricated part E requires a unit of fabricated part G which requires raw material H. Product A and subassembly B are assembled in the departments four and three respectively. Fabricated part E is processed in department two and fabricated part G is processed in department one.
Figure 1. Supply chain of the company.
As shown in Figure 2, DRP which plans distribution requirements and MRP that plans production requirements are linked through the Master Production Schedule (MPS). Capacity Requirements Planning (CRP) is used to test and to validate the material plan generated by MRP. Backorders are not allowed in DRP but are allowed in MRP. Both DRP and MRP plans are generated on a weekly basis. Weekly production capacity is assumed to be 40 hours (5 days). Overtime is considered and associated costs, other costs such as transportation, holding, stockout, backorder, purchasing, setup, and manufacturing costs are assigned. In order to understand and monitor frozen time period's performance of DRP and MRP, the run length for each replication or planning horizon is 8 weeks. Either chase or level strategy can be choosen for MPS.
Figure 2. Integration of distribution and production systems.
DESCRIPTION OF THE PROBLEM
It should be noted that like other parameters, types of distributions and their parameters used in this problem are arbitrarily chosen and can be changed to other known distributions. Demand for all local distribution centers are between 30 and 80 units of A ,uniformly distributed. Lead times are assumed to be uniformly distributed between 1 and 3 weeks. Other parameters of the distribution system of the example problem are given in Table 1.
Distribution Centers
Parameters LDC1 LDC2 LDC3 MDC
Selling price ($/unit) 54.5 54.5 54.5 45.5
Purchase price ($/unit) 45.5 45.5 45.5 36
Holding costs ($/unit/week) 0.625 0.6 0.8 1
Stockout costs ($/unit/week) 3 3 3 3
Safety stock (unit) 20 10 10 40
Lot size (unit) 5 5 5 50
Inventory on hand (unit) 100 60 75 434
Table 1. Parameters of the distribution system.
Transporting a unit of A from the main distribution center to the local distribution centers 1, 2, and 3 cost $3, $4.8 and $2.5 respectively. Transporting a unit of A from the plant to the main distribution center costs $4.
Initial inventory level and production quantity for MPS is assumed to be zero, which means chase strategy was chosen. Lead times of product A, subassembly B, purchased parts C, D, and F , fabricated parts E, and G , and raw material H are assumed to be one
LDC3 MDC LDC2 LDC1 B A E D(11) C(11) G H F(11) MPS
DRP for distribution network
MRP for Production system
CRP
week. Setup time for fabricated part E is assumed to be normally distributed with a mean of 240 and a standard deviation of 80 minutes. Setup time for fabricated part G is assumed to be normally distributed with a mean of 120 and a standard deviation of 40 minutes. Processing times of product A, subassembly B, fabricated parts, E and G are between 1.5 and 3.5, 3.5 and 6, 5 and 8, 1 and 2 minutes which are assumed to follow uniform distributions respectively. Selling price of a product A is $36 per unit. Material costs of purchased parts C,D, and F are $0.05, $0.05, and $0.07 per unit. Raw material H costs $7 per unit. Other parameters of the production system are given in Table 2.
Product and its components
Parameters A B C D E F G H
Order costs ($/order ) - - 10 10 - 10 - 125
Holding costs
($/unit/week) 0.15 0.2 0.05 0.05 - 0.07 - 7
Backordering costs
($/unit/week) 0.45 0.4 0.015 0.015 0.9 0.021 0.45 2.1
Setup costs ($/order ) 10 20 - - 475 - 250
-Safety stock (unit) 50 - - -
-Inventory on hand
(unit) 375 0 4000 3750 50 2500 0 500
Table 2. Parameters of the production system.
LFL or fixed-order quantity lot-sizing rules can be used to compute lot sizes of product A and its components.
Department one and two have two(identical) and one machines and deparment three and four have two workers respectively.Capacity paremeters of the production system are given in Table 3.
Departments
Parameters 1 2 3 4
Efficiency 0.8 0.9 0.7 0.8
Regular production costs
($/unit) 1.5 3 2 1.5
Overtime production costs
($/unit) 3 6 4 3
Table 3. Capacity parameters of the Production System
Transporting a unit of H from supplier of H to the plant costs $0.8 per unit. Transporting a unit of F from its supplier to the plant costs $0.05 per unit. Transporting a unit of C, and D from the same supplier to the plant costs $0.025 each.
SPREADSHEET-BASED SIMULATOR
The spreadsheet-based simulator developed by a modern spreadsheet Excel 7.0 consists of nine worksheets including content , BOM, and four graphs. Seven worksheets have been developed to generate DRP, MPS, MRP, CRP records, and cost reports of DRP, MRP and the integrated system and 4 graphs illustrate the capacity of the four departments. Seven worksheets and four graphs are linked. DRP worksheet generates the DRP records of the three local distribution centers and the main distribution center. Master production schedule worksheet takes the planned order releases of the main distribution center as an input in order to determine the master production schedule according to the chosen strategy. MRP worksheet schedules all the dependent components that are required to meet the
outputs of MPS worksheet through a materials "explosion" process. CRP worksheet, that uses the MRP worksheet's outputs, calculates capacity requirements and draws capacity graphs of the four departments in order to observe whether sufficient capacity resources exist to achieve MPS. DRP cost report worksheet prepares the costs of distribution operations in detail, using the data obtained by DRP worksheet. MRP cost report similarly prepares the costs of production operations in detail, using the data obtained by MRP worksheet. The final cost report worksheet summarizes the gross returns of DRP and MRP and shows the service level of DRP and the percentage of produced units without backordering in MRP. There are three types of simulator. The data required to operate the simulator is entered manually in the first type. If the tentative plan is not feasible , the planner can simulate to iteratively revise the desired parameters and recompute the integrated system until an appropriate plan is discovered. This is called open ended simulations or "what-if-scenarios". In the second type, the integrated system has been simulated by describing the demand or forecast for the product, lot sizes, lead times (up to three weeks) ,setup and processing times, using known statistical distributions in excel worksheets. Spreadsheet add-ins make the task of simulation much easier than doing it alone in spreadsheet. In order to easily perform multiple trials, Crystal Ball [student version 4.0c] spreadsheet add-in is used to carry out all computations in the third type.
Excel commands
It would take several pages if all the worksheets and their excel commands used to build the simulator were described. A part of DRP worksheet is shown in Figure 3. Some of the important commands are briefly given below.
In each run, demand at all distribution centers during 8 weeks is generated of uniform random variates with the following Excel commands:
E16=ROUND(RAND()*($K$21-$K$22)+$K$22;0) F16=ROUND(RAND()*($K$21-$K$22)+$K$22;0) . . . L16= ROUND(RAND()*($K$21-$K$22)+$K$22;0) E17=ROUND(RAND()*($K$21-$K$22)+$K$22;0) F17=ROUND(RAND()*($K$21-$K$22)+$K$22;0) . . . L17= ROUND(RAND()*($K$21-$K$22)+$K$22;0) E18=ROUND(RAND()*($K$21-$K$22)+$K$22;0) F18=ROUND(RAND()*($K$21-$K$22)+$K$22;0) . . . L18= ROUND(RAND()*($K$21-$K$22)+$K$22;0)
It should be noted that the above given commands are not used in the first type of the simulator mentioned in the previous section.
The following Excel commands are used to compute lead times of all distribution centers: E24=ROUND(RAND()*($K$24-$K$25)+ $K$25;0) F24=ROUND(RAND()*($K$24-$K$25)+ $K$25;0) . H24=ROUND(RAND()*($K$24-$K$25)+ $K$25;0)
The commands to compute the projected on hand of the first local distribution center during 9 weeks (including the initial value of the projected on hand) are:
D40=F26 E40= IF(D40>0;D40+E39+E42-E38;E39+E42-E38) F40= IF(E40>0;E40+F39+F42-F38;F39+F42-F38) . . . L40=IF(K40>0;K40+L39+L42-L38;L39+L42-L38)
The following Excel commands are used to compute the net requirements of LDC 1 during 8 weeks: E39-D40;IF(D40<0;E38+$D$33-E39;0)) F39-E40;IF(E40<0;F38+$D$33-F39;0)) . . . L39-K40;IF(K40<0;L38+$D$33-L39;0))
The commands to compute the planned order receipts and planned order releases of LDC1 during 8 weeks: E42=IF(AND(E41>0;$D$34>0;E37-$D$35>$E$37-1);$D$34*ROUNDUP(E41/$D $34;0);IF(AND($D$34=0;E37-$D$35>$E$37-1);E41;0)) F42=IF(AND(F41>0;$D$34>0;F37-$D$35>$E$37-1);$D$34*ROUNDUP(F41/$D $34;0);IF(AND($D$34=0;F37-$D$35>$E$37-1);F41;0)) . . . L42=IF(AND(L41>0;$D$34>0;L37-$D$35>$E$37-1);$D$34*ROUNDUP(L41/$D $34;0);IF(AND($D$34=0;L37-$D$35>$E$37-1);L41;0)) E43=IF(AND($D$35=1;F42>0);F42;IF(AND($D$35=2;G42>0);G42;IF(AND($D$ 35=3;H42>0);H42;0))) F43=IF(AND($D$35=1;G42>0);G42;IF(AND($D$35=2;H42>0);H42;IF(AND($D$ 35=3;I42>0);I42;0))) . . . L43=IF(AND($D$35=1;M42>0);M42;IF(AND($D$35=2;N42>0);N42;IF(AND($D
$35=3;O42>0);O42;0)))
The following Excel commands are used to compute the unfilled demands of LDC1 during the first three weeks.
E45=IF(E40<0;ABS(E40);0) F45=IF(F40<0;ABS(F40);0) G45=IF(G40<0;ABS(G40);0)
It is important to note that after each run in the first and the second type of simulator, the solution should be saved immediately before the next run so that it can be restarted with the initial conditions when desired.
CONCLUSIONS
The spreadsheet simulator for inventory management developed here helps to improve the understanding of how integrated DRP/MRP systems work in a supply chain. This simulator can be used as an experiential learning activity for teaching inventory management, production planning, and supply chain concepts. Future research will focus on the application of fuzzy rule discovery methods to the data generated by the simulator in order to explore supply chain dynamics.
REFERENCES
BALLOU, RONALD H. (1999): Business Logistics Management 4th ed., Prentice-Hall International ,
Upper Saddle River, New Jersey.
GREENE, JAMES H. (1997): Production and Inventory Control Handbook 3rd ed., McGraw-Hill, ,
New York.
HANDFIELD, ROBERT and NICHOLS, ERNEST L., JR. (1999): Introduction to Supply Chain Management, Prentice-Hall, Upper Saddle River, New Jersey.
SIMCHI-LEVI, DAVID, KAMINSKY, PHILIP, SIMCHI-LEVI, EDITH (2000) : Designing and Managing The Supply Chain, Concepts, Strategies, and Case Studies, Irwin McGraw-Hill .
TERSINE, RICHARD (1988): Principles of Inventory and Materials Management 3rd ed.,Elsevier
