jagomart
digital resources
picture1_Stock In And Out Excel Template 43090 | Inventory Simulation


 171x       Filetype XLS       File size 0.54 MB       Source: exinfm.com


File: Stock In And Out Excel Template 43090 | Inventory Simulation
sheet 1 introduction simulating inventory control with orders that cross during lead time ssxls version 14 032202 johnomcclain cornelledu johnson graduate school of management cornell university ithaca ny 14853 this ...

icon picture XLS Filetype Excel XLS | Posted on 16 Aug 2022 | 3 years ago
Partial file snippet.
Sheet 1: Introduction
Simulating Inventory Control with Orders that Cross during Lead Time
sS.xls Version 1.4 03/22/02
John.O.McClain@cornell.edu
Johnson Graduate School of Management
Cornell University
Ithaca NY 14853
This workbook is intended for teaching or research. You are welcome to use it in any manner,








and change it as you see fit. It comes without any guarantee whatsoever, and is distributed








free of charge. Changes are frequent, so check back frequently for a new version.


















Most inventory control systems use a formula for lead-time demand to set safety stock levels.








Research has uncovered situations where that method leads to large and expensive errors.*








In particular, if replenishment orders might not arrive in the same order in which they are placed, then








the above method will leave you with too much inventory if your objective is a high level of protection,








and too little inventory if you are aiming to run out of stock frequently. That is, the variance of the








inventory level is smaller than the variance of lead-time demand.









*See Robinson, L.R, J.R. Bradley and L.J. Thomas, "Consequences of Order Crossover under








Order-up-to Inventory Policies." M&SOM Manufacturing and Service Operations Management,








Volume 3, No. 3 (2001), pp.175-188.

















This workbook contains a macro, written in Visual Basic, that allows you to simulate the inventory control








system known variously as the Min-Max system, the (s,S) system, or the reorder-level, order-up-to system.








Two versions are available in the simulation:








> Periodic review: orders may be placed only at specific points of time, such as daily or weekly.







> Continuous review: orders are placed instantly, as soon as inventory reaches the reorder level.







In both cases, the state of the system is tracked at all times, so that accurate costs may be calculated.


















The word "order" refers to an action taken to replenish the supply of an item that is stocked in inventory








and sold to customers. The word "demand" refers to a customer wanting to buy one unit of the item.








A "backorder" is an unsatisfied demand for which the customer will take delivery at a later time.








The simulation assumes that all customers are willing to wait if their demand is backordered.


















The simulation allows orders to cross. It assumes that the lead time of one order is independent of








that for any other order, and therefore crossing occurs whenever the lead time for an order is longer








than the interval between orders plus the lead time for the next order.


















Please note that the independence assumption is not true in some real circumstances. For example,








if both orders are shipped by rail, and if one freight car cannot pass another, the orders cannot cross.








However, in that case lead times are also not independent, but rather are positively correlated, so








models that assume independence (i.e. most inventory models) are also incorrect.


















Contents: These are the sheets in this workbook.










Introduction (this sheet), with the following sections:







1. Measuring Inventory and Shortages at Time of Delivery



(a) Shortfall below Reorder Level at Delivery: Shortfall@Deliv



(b) Distribution of Shortfall@Delivery if Orders do not Cross




(i) Continuous Review




(ii) Periodic Review


2. Inventory and Shortages at Any Time



(a) Shortfall below Order-up-to Level



(b) Average Inventory and Shortages


3. Cost of Inventory, Backorders and Ordering:


4. Optimization

Simulate where you set up the model and run the simulation.






Graphs where simulation results are displayed in detail for any run stored on the Data sheet.






Trace where the first part of the most recent simulation run is shown in a table and a graph.






Data where the results of all simulation runs are stored, until you erase them.






Other sheets in this book, if any, may contain data and graphs from previous simulation experiments.

















1. Measuring Inventory and Shortages at Time of Delivery:









s reorder level, or Min


Inv On-hand inventory


S order-up-to level, or Max


BO Number of units backordered to customers


Q S - s


NetInv = Inv - BO (can be positive or negative)


L A value of lead time


InvPosition NetInv + Outstanding Orders


D A value of one-period demand


DL Demand that occurs during lead time


mL, VarL Average & Variance of L


mDL, VarDL Average & Variance of DL


mD, VarD Average & Variance of D
















(a) Shortfall below Reorder Level at Delivery: Shortfall@Deliv









Protection against shortages focuses attention on inventory at the time a replenishment order








arrives. Safety stock governs the likelihood that backorders will exist at that instant.








In the simulation, @Deliv refers to events that happen just before replenishments occur.








However, rather than tracking inventory, which can be positive or negative, the simulation monitors








"Shortfall below s at delivery," defined as








1) Shortfall@Deliv = s - NetInv@Deliv at the time (just before) replenishment occurs.







This is a non-negative variable since net inventory is at or below the reorder level, s, whenever any








order is outstanding (i.e. not yet received.)








From Shortfall@Deliv we may compute certain performance measures:








2) NetInv@Deliv = s - Shortfall@Deliv







3) Inv@Deliv = MAX(0, s - Shortfall@Deliv)







4) BO@Deliv = MAX(0, Shortfall@Deliv - s) = Inv@Deliv - s + Shortfall@Deliv







5) P(BO@Deliv>0) = P( Shortfall@Deliv > s)







The latter may also be expressed as a rate, although the meaning is a little confusing. It is NOT the








rate at which backorders occur, but rather "occurrences per unit time" of the joint event








"replenishment arrives, backorders exist," or "replenishment arrives too late to prevent backorders."








That event is denoted "BO@Deliv>0" and its occurrence rate is








6) Rate(BO@Deliv>0) = P( Shortfall@Deliv > s)×(Replenishment Orders Per Unit Time)
















(b) Distribution of Shortfall@Delivery if Orders do not Cross









The following gives the classical argument for the distribution of shortfall, assuming that orders do








not cross, and also assuming that lead times are independent, two assumptions which are convenient








but contradictory. These numbers may be compared to the actual values from the simulation to see








how much is lost if the classical rules are used.








With no order crossing, when an order arrives, all prior replenishment orders have already arrived,








and no subsequent ones have. At the time that order was placed, Inventory Position included the prior








orders, so to compute inventory at delivery, we only have to account for the demand that occurs in the








lead time (or lag time) between placing and receiving the order. That is,








7) NetInv@Deliv = InvPosition@Ordering - DL and







8) Shortfall@Deliv = s - InvPosition@Ordering + DL if orders do not cross





(substitute 7 into 1).











(i) Continuous Review








Under continuous review, an order is placed the instant that inventory position reaches the reorder








level. That is,








9) InvPosition@Ordering = s for continuous review, so







10) Shortfall@Deliv = DL for continuous review if orders do not cross





(substitute 9 into 8).
>> Shortfall@Deliv equals lead-time demand for continuous review, if orders do not cross.








The probability that backorders occur before an order arrives is








11) P(BO@Deliv>0) = P( DL > s )





(substitute 10 into 5).

The following formulas assume that Lead Times are independent and identically distributed, and that








the same is true for Demands, and that Lead Times are independent of Demands. They are, in fact, the








well-known formulas for the mean and variance of lead-time demand.








12) E[Shortfall@Deliv] = mD mL and







13) Var[Shortfall@Deliv] = mL VarD + mD2 VarL for continuous review if orders do not cross.

















(ii) Periodic Review








Under periodic review, inventory position can reach the reorder point at a time t that is before the end








of the period, so inventory position will be at or below the reorder point when the order is placed.









If t is at the end of the period, the order is placed at the instant that the reorder level is reached.








If t is just after the beginning of the period, a one-period demand occurs before ordering.







This leads to the following inequality:








14) s - D ≤ InvPosition@Ordering ≤ s







Substituting 14 into 8,








15) DL ≤ Shortfall@Deliv ≤ DL + D = DL+1 for periodic review if orders do not cross.






>> Shortfall@Deliv is between the demand during lead time and the demand during one period longer








than lead time, if orders do not cross. Also, because the probability above s is a nonincreasing








function of s,








16) P(DL > s) ≤ P(Shortfall@Deliv>s) ≤ P( DL + D' - 1> s ) , and so







17) P(DL > s) ≤ P(BO@Deliv>0) ≤ P( DL + D' - 1> s )





(substitute 16 into 5).

The expected value of 15 yields








18) mD mL ≤ Shortfall@Deliv ≤ mD (1+mL)







The arguments leading to equation 15 also yield a lower limit for the variance:








19) Var[Shortfall@Deliv] ≤ mL VarD + mD2 VarL







The upper limit in equation 15 also yields a variance estimate, but it is not necessarily an upper limit:








20) Var[Shortfall@Deliv] ≈ (1 + mL) VarD + mD2 VarL
















2. Inventory and Shortages at Any Time









The simulation also measures the inventory level after every event. Inventory is constant between








events (by definition, since an event is defined as a change of state), so the distribution is








tabulated by accumulating the time that each state persists.

















(a) Shortfall below Order-up-to Level









"Shortfall below S" is defined at every time in the simulation as








20) Shortfall = S - NetInv.







Notice that Shortfall uses a different reference point than Shortfall@Delivery, namely S rather








than s. This is necessary to avoid negative values.








From Shortfall, we may compute more performance measures:








21) NetInv = S - Shortfall.







22) Inv = MAX(0, S - Shortfall)







23) BO = MAX(0, Shortfall - S) = Inv - S + Shortfall







24) P(BO>0) = P( Shortfall > S)







Since a demand is backordered if it arrives when inventory is zero, the average number of demands








backordered per unit time is








25) Rate(BO) = P( Shortfall ≥ S)×(Demand Rate)







We can also calculate the average time that a backorder endures which, according to Little's Law,








is proportional to the average number of backorders waiting.









Average duration of a Backorder = (Average # Backordered)¸(Rate of Backorders Occuring)







26) Av(Wait per BO) = Av(BO)¸{Av(DemandRate) × P{Shortfall≥S)}







If you want to include in this average the fact that many customers have zero backorder time, then








27) Av(Wait per Demand) = Av(BO)¸Av(DemandRate) (includes zero-length backorders.)
















(b) Average Inventory and Shortages









Average inventory is greater when computed over time than when computed just before a delivery.








Inventory just before delivery can never be above the reorder point, whereas it can at other times.








The average inventory over time will include the "sawtooth pattern" commonly seen in textbooks,








caused by cycle stock represented by the order quantity. Therefore the exact theoretical expression








for average inventory and backorders is elusive, and I will not try to include it here. However the








simulation results yield averages from the distribution of Shortfall, using equations 22 through 25.

















3. Cost of Inventory, Backorders and Ordering:









The simplest model has linear inventory and backorder costs. However, what constitutes backorder








cost? There may be a cost per unit time for backorders, and a fixed cost whenever a backorder








occurs. There also might be a fixed cost per unit time that accrues as long as there are any








backorders. If the gap between s and S is changed, the number of orders placed will change, which








changes the cost of ordering. A model that covers all of these costs is








28) Average Cost per Period = C1 × Av(Inv) + C2 × Av(BO) + C3 × mD × P(BO ≥ 0)











+ C4 × P(BO>0) + C5 × Av(OrderRate)













4. Optimization









The value of Q (the gap between s and S) is held constant during a simulation. (In fact, it operates as








if the order-up-to level were S=0 with reorder level s = -Q.) However, the output may be used








to represent any (s,S) system that has S-s=Q. You can find the optimal value of s among all








systems that have the same Q as the one in your simulation, and then set S=s+Q.








On the Graphs sheet, an Excel Table calculates costs for a range of values of s. A graph shows the








results. You may input the first value of s and the interval between points. To home in on the








optimum, adjust the first value until the graph is U-shaped, and then lower the interval to 1.








However, the result is only optimal for the value of Q that you simulated. To find an overall








optimum, you must repeat the simulation for a series of values of Q, and use the table to find the








best reorder level for each Q. Record those values and select the one with lowest cost.









Sheet 2: Simulate
Simulation of a Min-Max (s,S) Inventory System in Continuous Time: Discrete or Continuous Review














Current Simulation Design:
Periodic Review. Q=30, D=10, varD=10










Gamma InterDemandTime: Mu= 0.1, Std=0.1. Discrete LT: Mu= 3, Std=1.41









Change the design by entering numbers in the yellow boxes, and by checking or unchecking the selection boxes.


























Periodic Review places orders only at the end of each unit of time. Continuous Review places orders the instant that the reorder level is reached. Use the checkbox below to toggle between review types. Type of Review
IDT = Inter-Demand Time, is the time between demands. Use the checkbox below to toggle between Gamma and Discrete distributions for Inter-demand time. Inter-demand time (IDT)
LT = Lead Time, or the lag between ordering and receiving a replenishment. Use the chedkbox below to toggle between Gamma and Discrete distributions for lead time. Lead Time (LT)
Theoretical Values Mean Var StDev
Periodic Review? 1
Gamma IDT? 1
Gamma LT? 0
LTD 30.00 230.00 15.166



LT+1 Dem 40.00 240.00 15.492
Periodic Review
If mean and standard deviation are equal, this is the exponential distribution. Gamma IDT
If mean and standard deviation are equal, this is the exponential distribution. Gamma LT (not in use)
LT 3.00 2.00 1.414



mean 0.1
mean 3
Inter-Demand Time 0.1 0.01 0.100



StDev 0.1
StDev 1.4142135623731
Demand/period 10.00 10.00 3.162 Variance is an approx?














Q = S-s is the gap between the reorder level and the order-up-to value. With Continuous Review, Q is the order quantity. With Periodic Review, the order quantity is always Q or more. Minimum Order Quantity
The values of IDT must be from smallest to largest. They do not need to be integers. The last probability is calculated automatically to make the total 1.0. Discrete IDT (not in use)
The values of LT must be from smallest to largest. They do not need to be integers. The last probability is calculated automatically to make the total 1.0. Discrete LT





Minimum Q = S-s 30
IDT f(IDT)
LT f(LT)
Conversion Formulas:






0 0.5
0


Mean Var StDev



0.033333333333333

1 0.2
Demand per Period: 10 10 3.162



0.066666666666667

2 0.2
Inter-demand Time: 0.1 0.01 0.100
Run Controls must be set before carrying out the simulation. Each cell has its own description. Run Controls
0.1

3 0.2





Runin Periods Runin Periods is the simulated time that will elapse with no data collection. It is sometimes known as the warmup time, and serves to allow the system to come to a representative state. 5,000
0.133333333333333

4 0.2
Inter-Demand Time 0.1 0.01 0.100
Run Periods Run Periods is the simulated time during which data will be collected. 50,000
0.166666666666667

5 0.2
Demand/period 10 10 3.162
RNSeed Random Number Seed is the number that Excel uses to initialize its random number generator. If you use the same seed, the same sequence of random numbers is used. 5235
0.2 0.5
6 0








mean 0.1
mean 3








StDev 0.1
StDev 1.4142135623731

















































Click here to view simulation results.














Click here to view graphs of the distributions














Sheet 3: Graphs
Periodic Review, Q=50. D=10 (Var=10). Discrete LT: Mu= 3, Std=1.41 View Performance Summary ¬ More at
these links.




Gamma InterDemandTime: Mu= 0.1, Std=0.1 Crossings/Delivery=0 View Graphs of Distributions








Links ® View Simulation Data



EOQ = 32

Number of columns of data available: 8 Go To Simulation Design



Min order Qty, Q = S-s = 50

Use data in column number: Data Sets for previous runs are stored on the worksheet named Data. Each column contains all of the information about a given run. You can select any value between 1 and the number given above, which tells you how many previous runs have been stored in this workbook. 8 ¬Choose your data set here







Order-up-to level, S = 80

Reorder trigger level (to vary): s = "Reorder Level" is defined as s. This may be changed without running a new simulation. However, the value of the order-up-to level, S, will also change so as to keep the gap, Q=S-s at the same value used in the simulation you are examining. Increasing s will increase inventory and decrease backorders, but will not affect how many orders occur per unit time. A method for finding the lowest cost value of s is given next to the Cost graph, below. 30 ¬Set the Reorder Level here






















Performance Statistics for s=30, S=80 "Demand during Lead Time" describes the drop in inventory before a just-placed order is received. If orders do not cross, inventory at delivery equals s - "LTDem" for continuous review, and may be lower for periodic review. LTDem "Shortfall At Delivery" is defined as s-NetInv observed just before a replenishment order arrives. Values larger than s indicate that NetInv is negative, and the number of units backordered is, therefore, MAX( 0, Shortfall@Deliv - s ). Similarly, on-hand inventory is MAX( 0, s - Shortfall@Deliv ). Hence, when replenishment arrives, s - Shortfall@Deliv = Inventory - Backorders. Shortfall @Deliv. "Demand during Lead Time plus 1" is the maximum possible drop in inventory before an order may be placed and received under periodic review. Hence inventory at delivery is at least s - "LT+1 Demand" if orders do not cross. For continuous review, this is not an important number. LT+1 Dem "Shortfall" is defined as S-NetInv at any given time. Values larger than S indicate that NetInv is negative, and the number of units backordered is MAX( 0, Shortfall - S ). Similarly, on-hand inventory is MAX( 0, S - Shortfall). That is,S - Shortfall = Inventory - Backorders. Shortfall "Unit Costs" apply only to the statistics derived in the column labeled "Time Average Shortfall". Unit Costs



Using Normal Distribution
to approximate:
LTDem Shortfall @Deliv. LT+1 Dem Shortfall




Mean = 29.980 35.014 41.017 57.046




Mean = 29.980 35.014 41.017 57.046
Variance = 230.033 242.520 239.770 489.283




Variance = 230.033 242.520 239.770 489.283
E[Inventory] = 6.476 4.293 2.138 24.736 $1.00 Inventory Cost per unit time


E[Inventory] = 6.061 4.025 2.170 24.668
E[Backorders] = 6.456 9.307 12.156 1.783 $9.00 Backorder Cost per unit time


E[Backorders] = 6.041 9.039 13.187 1.714
P[Backorders>0] = 0.478 0.577 0.697 0.158 $- Cost whenever Backorders > 0


P[Backorders>0] = 0.486 0.614 0.752 0.145
Backorder Rate = This uses the same formula as "Shortfall@ Delivery": Backorder Rate= probability of backorders multiplied by the number of replenishment orders per unit time. 0.087 When focusing on the instant an order arrives, (@Delivery) the "Backorder Rate" is defined as "Number of Replenishment Arrivals per unit time for which backorders exist at the time of arrival." This is the probability of backorders>0 at arrival, multiplied by the number of replenishment orders per unit time. 0.105 This uses the same formula as "Shortfall@ Delivery": Backorder Rate= probability of backorders multiplied by the number of replenishment orders per unit time. 0.127 For "Shortfall at any time" the Backorder Rate is "Number of demands that are backordered per unit time." This is the probability of zero inventory multiplied by the demand rate per unit time. 1.684 $- Cost per unit Backordered


Backorder Rate = 0.089 0.112 0.137 1.550



Demands=500784, Orders=9100, Periods=50000, Orders/Period= 0.182 $50.00 Fixed Cost of Ordering











For s=30, S=80, Total Cost per Unit Time = $49.88









Crossings=0

Crossings per Delivery = 0.000























Analysis of Service Level Accuracy.


Target Probability of No Backorders: Probability of no backorders is used to set the reorder level (s) when Shortfall at Delivery (s-NetInv) is the criterion, and to set the order-up-to level (S) when Time Av. Shortfall (S-NetInv) is the criterion. 0.95 ¬This target's meaning:







Using simulated distribution of: "Demand during Lead Time" describes the drop in inventory before a just-placed order is received. If orders do not cross, inventory at delivery equals s - "LTDem" for continuous review, and may be lower for periodic review. LTDem "Shortfall At Delivery" is defined as s-NetInv observed just before a replenishment order arrives. Values larger than s indicate that NetInv is negative, and the number of units backordered is, therefore, MAX( 0, Shortfall@Deliv - s ). Similarly, on-hand inventory is MAX( 0, s - Shortfall@Deliv ). Hence, when replenishment arrives, s - Shortfall@Deliv = Inventory - Backorders. Shortfall @Deliv. "Demand during Lead Time plus 1" is the maximum possible drop in inventory before an order may be placed and received under periodic review. Hence inventory at delivery is at least s - "LT+1 Demand" if orders do not cross. For continuous review, this is not an important number. LT+1 Dem "Shortfall" is defined as S-NetInv at any given time. Values larger than S indicate that NetInv is negative, and the number of units backordered is MAX( 0, Shortfall - S ). Similarly, on-hand inventory is MAX( 0, S - Shortfall). That is,S - Shortfall = Inventory - Backorders. Shortfall
For "Shortfall",


Using Normal Distribution
to approximate:
LTDem Shortfall @Deliv. LT+1 Dem Shortfall

it means "% of time during which


To achieve target probability, s= "Lead-time Demand" sets the value for s in continuous review if orders do not cross, because it represents the demand between placing and receiving an order. For periodic review, it gives a lower limit because additional demand may occur between reaching s and placing an order. 55 "Shortfall At Delivery" is the correct distribution for setting the reorder level (s) to match a target criterion for "probability of no shortages at delivery", because Shortfall at Delivery = s-NetInv, so whenever Shortfall@Deliv exceeds 0, there are backorders. 61 "LT+1" Demand sets an upper limit for s in periodic review, because it is the highest demand that can occur after the reorder level is reached and before the resulting order is placed and delivered, if orders do not cross. It is unimportant for continuous review. 66 44
there are some backorders."


To achieve target probability, s= 55 61 65 43
S=s+Q: 105 111 116 Time Average probability of no backorders sets the order-up-to level (S) because Shortfall = S-NetInv. 94
For "Shorfall@Delivery",


S=s+Q: 105 111 115 93
Actual P(no BO@Deliv): 0.895 0.956 0.984 0.696
it means "% of orders for


Actual P(no BO@Deliv): 0.895 0.956 0.979 1.000
Actual P(no BO, time av.): 0.989 0.997 0.999 0.952
which some backorders exist


Actual P(no BO, time av.): 0.989 0.997 0.999 0.946
Predicted by LTD 0.956 0.988 0.996 0.792
when the order arrives."


Predicted by LTD 0.956 0.988 0.996 1.000
Predicted by LT+1 Dem 0.795 0.895 0.949 0.580




Predicted by LT+1 Dem 0.795 0.895 0.941 1.000















Search for Minimum Cost Reorder Level (s) holding constant S-s=50














Make sure "Calculation" is set to "Automatic". If it is not, then press F9 to recalculate the cost curve whenever you change anything.














Table for Optimizing s







s Cost
Graph of Cost vs s, starting at




30 $49.88


First value of s for the graph: "Reorder Level," s is varied by using an Excel Table. Use this cell to set the lowest value of s. 35 ¬this value, and incrementing by




35 $47.96


Interval between values: "Reorder Level," s is varied by using an Excel Table. Use this cell to set value by which s is increased between each point on the graph. 1 ¬this amount.




36 $47.85
"Minimum Cost" is the lowest among those in the graph. If it is one of the end points, this cell says "Local Minimum Cost" indicating that you should change the "first value of s" so that the minimum occurs between the ends. Also, if the interval between values is larger than 1, the minimum might be between two of the points, so the label says "Constrained Minimum". Minimum Cost = $47.83
at s =37, S =87






37 $47.83






38 $47.89






39 $48.02












40 $48.22
Graphs of Distributions for simulation run number 8





41 $48.50












42 $48.84






Change which simulation




43 $49.24






is graphed by changing the




44 $49.70






column number in cell G5.




45 $50.22












46 $50.78












47 $51.40












48 $52.06












Minimum Cost = $ $47.83












at s = 37












, S = 87








































Page Down for cumulative distributions
¯



































































































































































































































































Description of Simulation Data being Viewed:














Inputs:

Outputs:










Runin Periods 5,000
SimTime 50,000









Run Periods 50,000
Demands 500,784









Q = S-s 50
Orders 9,100









Exp. Demand 10
Deliveries 9,100









Var. Dmd (approx) 10
Crossings 0









RNSeed 5235
Cross/Deliv 0









Periodic Review? 1












Gamma LT? 0












Gamma IDT? 1












Exp. Inter-Demand Time 0.1
Shortfall, Avg. 57.0462538231744









StDev Inter-Demand Time 0.1
Shortfall, var. 489.282705097935









Exp. LT 3
Shortfall@Deliv. Avg. 35.013956043956









StDev LT 1.4142135623731
Shortfall@Deliv. var. 242.520354679386









Exp LTD 30
LTD Avg. 29.9803296703297









Var LTD 230
LTD var. 230.032909781428









Exp D(LT+1) 40
LT+1 Dem Avg 41.0173626373626









Var D(LT+1) 240
LT+1 Dem Var 239.769808428935









Input Distributions: Below

Output Distributions: Farther Below
























Inter-Demand Time Distribution: Ignore Discrete. Gamma used.






Distribution Actually Used:








Gamma Parameters: 1.000 0.100

IDT: Mean = 0.1, CV = 1, Gamma





Discrete IDT F(IDT) f(IDT)
Gamma IDT f(IDT)
Gamma IDT f(IDT)





0.000 0.500 0.500
0.010 9.048
0.010 9.048





0.033 0.500 0.000
0.055 5.769
0.055 5.769





0.067 0.500 0.000
0.100 3.679
0.100 3.679





0.100 0.500 0.000
0.145 2.346
0.145 2.346





0.133 0.500 0.000
0.190 1.496
0.190 1.496





0.167 0.500 0.000
0.235 0.954
0.235 0.954





0.200 1.000 0.500
0.280 0.608
0.280 0.608









Will Gamma overflow? 0























Lead Time Distribution: Ignore Gamma. Discrete used.






Distribution Actually Used:








Gamma Parameters: 4.500 0.667

LT: Mean = 3, CV = 0.471, Discrete





Discrete LT Discrete F(LT) Discrete f(LT)
Gamma LT Gamma f(LT)
Discrete LT f(IDT)





0.000 0.000 0.000
0.300 0.005
0.000 0.000





1.000 0.200 0.200
1.650 0.259
1.000 0.200





2.000 0.400 0.200
3.000 0.277
2.000 0.200





3.000 0.600 0.200
4.350 0.134
3.000 0.200





4.000 0.800 0.200
5.700 0.046
4.000 0.200





5.000 1.000 0.200
7.050 0.013
5.000 0.200





6.000 1.000 0.000
8.400 0.003
6.000 0.000









Will Gamma overflow? 0

































































































































x LTDem Shortfall @Deliv. LT+1 Dem Shortfall Cumul LTDem Cumul Shortfall @Deliv. Cumul LT+1 Dem Cumul Shortfall





0 0.00% 0.01% 0.00% 0.00% 0.00% 0.01% 0.00% 0.00%





1 0.03% 0.01% 0.00% 0.00% 0.03% 0.02% 0.00% 0.00%





2 0.01% 0.00% 0.00% 0.00% 0.04% 0.02% 0.00% 0.00%





3 0.12% 0.01% 0.00% 0.01% 0.16% 0.03% 0.00% 0.01%





4 0.41% 0.07% 0.00% 0.02% 0.57% 0.10% 0.00% 0.03%





5 0.68% 0.13% 0.00% 0.03% 1.25% 0.23% 0.00% 0.06%





6 1.11% 0.19% 0.00% 0.05% 2.36% 0.42% 0.00% 0.11%





7 1.93% 0.43% 0.00% 0.09% 4.30% 0.85% 0.00% 0.20%





8 2.12% 0.59% 0.02% 0.12% 6.42% 1.44% 0.02% 0.32%





9 2.43% 0.87% 0.01% 0.15% 8.85% 2.31% 0.03% 0.46%





10 2.81% 1.23% 0.02% 0.22% 11.66% 3.54% 0.05% 0.68%





11 2.64% 1.34% 0.10% 0.26% 14.30% 4.88% 0.15% 0.94%





12 2.21% 1.62% 0.18% 0.31% 16.51% 6.49% 0.33% 1.25%





13 2.09% 1.55% 0.25% 0.35% 18.59% 8.04% 0.58% 1.60%





14 1.85% 1.98% 0.53% 0.38% 20.44% 10.02% 1.11% 1.98%





15 1.78% 1.76% 0.71% 0.40% 22.22% 11.78% 1.82% 2.38%





16 1.99% 1.95% 1.07% 0.45% 24.21% 13.73% 2.89% 2.83%





17 1.60% 2.18% 1.32% 0.46% 25.81% 15.90% 4.21% 3.29%





18 1.99% 2.07% 1.48% 0.52% 27.80% 17.97% 5.69% 3.81%





19 2.12% 1.90% 1.81% 0.55% 29.92% 19.87% 7.51% 4.36%





20 2.24% 2.00% 2.23% 0.58% 32.16% 21.87% 9.74% 4.94%





21 2.13% 2.26% 1.91% 0.63% 34.30% 24.13% 11.65% 5.57%





22 1.93% 2.12% 2.14% 0.67% 36.23% 26.25% 13.79% 6.23%





23 1.89% 1.90% 2.08% 0.69% 38.12% 28.15% 15.87% 6.93%





24 1.89% 2.05% 2.12% 0.72% 40.01% 30.21% 17.99% 7.65%





25 2.40% 1.92% 2.13% 0.76% 42.41% 32.13% 20.12% 8.41%





26 1.90% 2.05% 1.96% 0.82% 44.31% 34.19% 22.08% 9.23%





27 1.91% 2.05% 1.95% 0.84% 46.22% 36.24% 24.02% 10.07%





28 1.95% 1.93% 2.05% 0.88% 48.16% 38.18% 26.08% 10.95%





29 2.07% 2.11% 2.01% 0.90% 50.23% 40.29% 28.09% 11.85%





30 1.98% 2.04% 2.21% 0.94% 52.21% 42.33% 30.30% 12.79%





31 1.99% 1.77% 2.00% 0.99% 54.20% 44.10% 32.30% 13.78%





32 2.01% 1.96% 2.00% 1.01% 56.21% 46.05% 34.30% 14.79%





33 2.13% 2.13% 2.01% 1.06% 58.34% 48.19% 36.31% 15.86%





34 2.01% 1.84% 1.93% 1.11% 60.35% 50.02% 38.24% 16.97%





35 1.99% 1.78% 2.09% 1.13% 62.34% 51.80% 40.33% 18.10%





36 1.97% 1.95% 2.02% 1.16% 64.31% 53.75% 42.35% 19.27%





37 1.96% 2.16% 1.96% 1.19% 66.26% 55.91% 44.31% 20.46%





38 1.73% 2.07% 1.89% 1.24% 67.99% 57.98% 46.20% 21.70%





39 1.95% 1.93% 1.56% 1.25% 69.93% 59.91% 47.76% 22.95%





40 1.99% 1.90% 2.22% 1.33% 71.92% 61.81% 49.98% 24.28%





41 1.98% 2.04% 1.92% 1.34% 73.90% 63.86% 51.90% 25.62%





42 1.73% 2.01% 1.84% 1.38% 75.63% 65.87% 53.74% 27.00%





43 1.81% 1.84% 2.41% 1.44% 77.44% 67.70% 56.14% 28.44%





44 1.73% 1.91% 1.85% 1.44% 79.16% 69.62% 57.99% 29.88%





45 1.90% 1.99% 2.02% 1.46% 81.07% 71.60% 60.01% 31.34%





46 1.85% 1.78% 1.81% 1.50% 82.91% 73.38% 61.82% 32.85%





47 1.87% 2.16% 2.05% 1.54% 84.78% 75.55% 63.88% 34.38%





48 1.85% 1.76% 1.87% 1.55% 86.63% 77.31% 65.75% 35.94%





49 1.77% 1.89% 2.07% 1.61% 88.40% 79.20% 67.81% 37.54%





50 1.63% 2.01% 2.15% 1.63% 90.02% 81.21% 69.97% 39.18%





51 1.45% 1.87% 1.92% 1.65% 91.47% 83.08% 71.89% 40.82%





52 1.34% 1.87% 2.08% 1.73% 92.81% 84.95% 73.97% 42.55%





53 1.09% 1.43% 1.70% 1.68% 93.90% 86.37% 75.67% 44.23%





54 0.92% 1.49% 1.81% 1.74% 94.82% 87.87% 77.48% 45.97%





55 0.75% 1.59% 2.01% 1.73% 95.57% 89.46% 79.49% 47.70%





56 0.70% 1.24% 1.67% 1.73% 96.27% 90.70% 81.16% 49.43%





57 0.64% 1.21% 1.79% 1.76% 96.91% 91.91% 82.96% 51.19%





58 0.45% 1.12% 1.55% 1.75% 97.36% 93.03% 84.51% 52.95%





59 0.52% 1.04% 1.86% 1.71% 97.88% 94.08% 86.36% 54.65%





60 0.51% 0.84% 1.54% 1.72% 98.38% 94.91% 87.90% 56.37%





61 0.41% 0.68% 1.59% 1.72% 98.79% 95.59% 89.49% 58.10%





62 0.21% 0.71% 1.33% 1.66% 99.00% 96.31% 90.82% 59.75%





63 0.24% 0.65% 1.27% 1.66% 99.24% 96.96% 92.10% 61.41%





64 0.21% 0.54% 1.09% 1.60% 99.45% 97.49% 93.19% 63.02%





65 0.13% 0.45% 0.92% 1.59% 99.58% 97.95% 94.11% 64.60%





66 0.05% 0.44% 0.82% 1.58% 99.64% 98.38% 94.93% 66.18%





67 0.10% 0.34% 0.76% 1.51% 99.74% 98.73% 95.69% 67.69%





68 0.09% 0.24% 0.60% 1.48% 99.82% 98.97% 96.30% 69.18%





69 0.04% 0.20% 0.60% 1.42% 99.87% 99.16% 96.90% 70.60%





70 0.07% 0.11% 0.66% 1.41% 99.93% 99.27% 97.56% 72.01%





71 0.03% 0.15% 0.46% 1.38% 99.97% 99.43% 98.02% 73.39%





72 0.00% 0.14% 0.37% 1.33% 99.97% 99.57% 98.40% 74.72%





73 0.02% 0.07% 0.27% 1.32% 99.99% 99.64% 98.67% 76.03%





74 0.00% 0.10% 0.30% 1.30% 99.99% 99.74% 98.97% 77.33%





75 0.00% 0.07% 0.18% 1.24% 99.99% 99.80% 99.14% 78.57%





76 0.01% 0.07% 0.18% 1.21% 100.00% 99.87% 99.32% 79.78%





77 0.00% 0.07% 0.14% 1.15% 100.00% 99.93% 99.46% 80.94%





78 0.00% 0.03% 0.18% 1.11% 100.00% 99.97% 99.64% 82.04%





79 0.00% 0.01% 0.05% 1.11% 100.00% 99.98% 99.69% 83.16%





80 0.00% 0.02% 0.10% 1.05% 100.00% 100.00% 99.79% 84.21%





81 0.00% 0.00% 0.04% 1.00% 100.00% 100.00% 99.84% 85.22%





82 0.00% 0.00% 0.03% 0.99% 100.00% 100.00% 99.87% 86.20%





83 0.00% 0.00% 0.03% 0.91% 100.00% 100.00% 99.90% 87.12%





84 0.00% 0.00% 0.05% 0.93% 100.00% 100.00% 99.96% 88.05%





85 0.00% 0.01% 0.00% 0.88% 100.00% 100.01% 99.96% 88.93%





86 0.00% 0.00% 0.02% 0.84% 100.00% 100.01% 99.98% 89.77%





87 0.00% 0.00% 0.01% 0.80% 100.00% 100.01% 99.99% 90.57%





88 0.00% 0.00% 0.00% 0.76% 100.00% 100.01% 99.99% 91.33%





89 0.00% 0.00% 0.01% 0.71% 100.00% 100.01% 100.00% 92.04%





90 0.00% 0.00% 0.00% 0.69% 100.00% 100.01% 100.00% 92.74%





91 0.00% 0.00% 0.00% 0.66% 100.00% 100.01% 100.00% 93.40%





92 0.00% 0.00% 0.00% 0.62% 100.00% 100.01% 100.00% 94.02%





93 0.00% 0.00% 0.00% 0.58% 100.00% 100.01% 100.00% 94.60%





94 0.00% 0.00% 0.00% 0.56% 100.00% 100.01% 100.00% 95.15%





95 0.00% 0.00% 0.00% 0.53% 100.00% 100.01% 100.00% 95.68%





96 0.00% 0.00% 0.00% 0.47% 100.00% 100.01% 100.00% 96.16%





97 0.00% 0.00% 0.00% 0.45% 100.00% 100.01% 100.00% 96.60%





98 0.00% 0.00% 0.00% 0.41% 100.00% 100.01% 100.00% 97.01%





99 0.00% 0.00% 0.00% 0.37% 100.00% 100.01% 100.00% 97.38%





100 0.00% 0.00% 0.00% 0.33% 100.00% 100.01% 100.00% 97.70%





101 0.00% 0.00% 0.00% 0.31% 100.00% 100.01% 100.00% 98.01%





102 0.00% 0.00% 0.00% 0.27% 100.00% 100.01% 100.00% 98.29%





103 0.00% 0.00% 0.00% 0.24% 100.00% 100.01% 100.00% 98.53%





104 0.00% 0.00% 0.00% 0.21% 100.00% 100.01% 100.00% 98.75%





105 0.00% 0.00% 0.00% 0.19% 100.00% 100.01% 100.00% 98.94%





106 0.00% 0.00% 0.00% 0.16% 100.00% 100.01% 100.00% 99.10%





107 0.00% 0.00% 0.00% 0.15% 100.00% 100.01% 100.00% 99.25%





108 0.00% 0.00% 0.00% 0.13% 100.00% 100.01% 100.00% 99.38%





109 0.00% 0.00% 0.00% 0.11% 100.00% 100.01% 100.00% 99.50%





110 0.00% 0.00% 0.00% 0.09% 100.00% 100.01% 100.00% 99.58%





111 0.00% 0.00% 0.00% 0.09% 100.00% 100.01% 100.00% 99.67%





112 0.00% 0.00% 0.00% 0.07% 100.00% 100.01% 100.00% 99.74%





113 0.00% 0.00% 0.00% 0.05% 100.00% 100.01% 100.00% 99.79%





114 0.00% 0.00% 0.00% 0.05% 100.00% 100.01% 100.00% 99.84%





115 0.00% 0.00% 0.00% 0.03% 100.00% 100.01% 100.00% 99.87%





116 0.00% 0.00% 0.00% 0.02% 100.00% 100.01% 100.00% 99.90%





117 0.00% 0.00% 0.00% 0.02% 100.00% 100.01% 100.00% 99.92%





118 0.00% 0.00% 0.00% 0.02% 100.00% 100.01% 100.00% 99.94%





119 0.00% 0.00% 0.00% 0.02% 100.00% 100.01% 100.00% 99.95%





120 0.00% 0.00% 0.00% 0.01% 100.00% 100.01% 100.00% 99.97%





121 0.00% 0.00% 0.00% 0.01% 100.00% 100.01% 100.00% 99.97%





122 0.00% 0.00% 0.00% 0.01% 100.00% 100.01% 100.00% 99.98%





123 0.00% 0.00% 0.00% 0.01% 100.00% 100.01% 100.00% 99.98%





124 0.00% 0.00% 0.00% 0.01% 100.00% 100.01% 100.00% 99.99%





125 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 99.99%





126 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 99.99%





127 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





128 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





129 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





130 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





131 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





132 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





133 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





134 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





135 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





136 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





137 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





138 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





139 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





140 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





141 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





142 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





143 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





144 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





145 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





146 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





147 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





148 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





149 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





150 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





151 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





152 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





153 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





154 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





155 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





156 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





157 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





158 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





159 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





160 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





161 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





162 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





163 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





164 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





165 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





166 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





167 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





168 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





169 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%





170 0.00% 0.00% 0.00% 0.00% 100.00% 100.01% 100.00% 100.00%






100% 100% 100% 100%











The words contained in this file might help you see if this file matches what you are looking for:

...Sheet introduction simulating inventory control with orders that cross during lead time ssxls version johnomcclain cornelledu johnson graduate school of management cornell university ithaca ny this workbook is intended for teaching or research you are welcome to use it in any manner and change as see fit comes without guarantee whatsoever distributed free charge changes frequent so check back frequently a new most systems formula leadtime demand set safety stock levels has uncovered situations where method leads large expensive errors particular if replenishment might not arrive the same order which they placed then above will leave too much your objective high level protection little aiming run out variance smaller than robinson lr jr bradley lj thomas quot consequences crossover under orderupto policies m amp som manufacturing service operations volume no pp contains macro written visual basic allows simulate system known variously minmax s reorderlevel two versions available simulat...

no reviews yet
Please Login to review.