Possible extension to be done to written Excel program quantitatively and/or qualitatively is modification of start hypothesis that be one or combination of items below listed. · inventory control by batches, here (S-1, S) that is order and repair demands are one-per-one basis · greater number of depot, bases and equipment · limited repair capacity (here considered unlimited) · addition of pool of spares, scrap rate, cannibalization · support between same repair level (the flow of equipment between bases not taken into account) · addition of more repair level (insertion of intermediate level between base and depot) · breakdown of equipment (repairable part composed by other parts) · use of other reliability function than Poisson (demand is always exactly one unit) for parts fail · use of other parameter than EBO (expected back order) to maximize or minimize. Fill rates measure the consumption of stocked spares while back orders indicate lack of them. The purpose of named Ao = MTBF/(MTBF+MTTR+ACWT) where · non stationary condition, lost sales, production, ... in place of steady state, backorder, inventory, ... The reasons that EXCEL was used here is that this MICROSOFT spreadsheet is useful for calculation of mathematical expressions defined by the algorithm, simplify its understanding. The factors are followings: A. The input values can be changed to see different outputs once the program is completed, inside limitation of final application and EXCEL itself. B. During application development EXCEL allows to check the partial results step by step up to the final one. C. EXCEL automatically converts numerical tables to graphical format to see what the algorithm does. The present computer program with VBA is experimental and does not have any other purpose than to show the METRIC (Multi-Echelon Technique for Recoverable Item Control) example and the codification with VBA is not optimized. This EXCEL project is protected to hide the code and it does not allow internal modification. However the author is not responsible directly or indirectly of any result from its use by anyone.(See here the XLS file) Symbols and mathematical expressions used are: λ = mean demand rate ν = mean time to repair ρ = repair probability τ = mean time between shipment and receipt μ = mean number of part under repair or resupply (pipeline) p = unit price B = budget EBO = expected back order i = part type (i = 1, 2, ..., k) j = repair level (j = 0, 1, 2, ..., m) Spares OnHand = [ InitialStock - ( InRepair + UnderTransportation ) ] Spares in BackOrder = [ ( InRepair + UnderTransortation ) - InitialStock ] Pipeline = PL = Spares InRepair + Spares UnderTransportation If If IR (repair process) and UT (transportation process) have poisson distribution then PL and BO also follow same distribution. An elementary simulation of the material flux with Excel is here. METRIC as expressed below does not think about variance. EXCEL book herein presented has 5 worksheets: 1. The 1
Here is the schematic description of model and numerical example. 2. 2 3. 3 See here graphical illustration. 4. the 4 See here graphical presentation. 5. 5
See graphical format here of sequence for parts type 1 and 2. See graphical format for final result or here.
THIS PAGE 1 THE INPUT DATA FOR TWO ASSEMBLIES, ONE DEPOT AND FIVE BASES. PAGE 2 THE ARRIVAL RATES EXPRESSION. PAGE 3 THE PIPELINE EXPRESSION. PAGE 4 ARRIVAL RATES ALLOCATION. PAGE 5 EXAMPLE OF PIPELINES AND EBO'S CALCULATION FOR A STOCK LEVEL (QUANTITIES) WITH THE START IS PIPELINES DETERMINATION FOR SUBASSEMBLIES (LOWEST INDENTURE) AT DEPOT (HIGHEST MAINTENANCE LEVEL). THE END IS MINIMIZATION OF ASSEMBLIES (HIGHEST INDENTURE) TOTAL EBO AT BASES (LOWEST MAINTENANCE LEVEL) AS ONLY EBO'S OF ASSEMBLIES (AND NOT OF SUBASSEMBLIES) AFFECT THE SYSTEM AVAILABILITY. THE IF THE START SETS COMPRISE QUANTITIES ZERO FOR DEPOT AND ONE FOR FIVE BASES, OR ONE FOR ALL, THE RESULTS ARE (12; 16; 8; 8; 12; 21; 5) FOR DEPOT, 5 x (1; 1; 1; 6; 1; 1; 4) FOR BASES, $2,372.70 FOR BUDGET AND 1.80253 FOR TOTAL EBO OF BASES. IN THE FIRST SHEET NAMED "MMR" OF EXCEL ONE CAN SEE THE INPUT DATA (REFERENCE ABOVE FILE PAGE 1), ARRIVAL RATES ALLOCATION (REFERENCE FILE PAGE 4 ), THE FOUR COLUMNS WITH VALUES OF PIPELINE (REFERENCE FILE PAGE 3 ) AND OTHER THREE COLUMNS FOR GREEDY ALGORITHM FOR VBA MACRO. THE RESULT FOR EACH STEP OR SET IS SHOWN ON THE REST OF THIS SHEET. THE SECOND SHEET "RES" BASICALLY SHOWS TOTAL EXPECTED BACKORDER AND COST/PRICE OF EACH SET AS WELL AS THE GRAPHICAL VARIATION OF TOTAL EBO WITH BUDGET. THIRD SHEET HAS EXCEL FORMULAS RELATING DEMAND RATES, PIPELINES AND EBO OF NUMERICAL EXAMPLE, IT WAS USED TO CHECK INTERMEDIATE VALUES FROM ALGORITHM DURING DEVELOPMENT TO VERIFY AND VALIDATE THE VBA PROGRAM. THIS PART IV "VARI-METRIC" These above three practical applications should be the bases for implementation with Excel and VBA of a general multi-item, multi-indenture, multi-echelon inventory system called Vari-Metric. It is observed that the Poisson often is not a good fit for experimental results as distribution mean is not constant and varies continuously with trials. The Negative Binomial series used in this method is an extension (as a summation of several processes ) of Poisson but the function has an added work that requires two parameters to be determined that are mean and variance for pipeline quantity calculation. A real sample of spares demand (reference B Brown) and examples of Poisson and Negative Binomial distribution adjusted to it are shown here .Compare examples of Metric ( with marginal analysis ) and Vari-metric. The expected values of backorder and its variances at depot, the expected backorders at the bases can be checked below.
Here is an example of general vari-metric method described.
1st step - Allocate
failure rates (FR's). Start with lower level and higher indenture (assemblies at bases).
Go to assemblies of depot.
Then go to subassemblies of bases.
Finally determine failure rates for subassemblies of depots.
2nd step - Calculate
pipeline values E(PL) (=V(PL)). Start with higher level and lower indenture (subassemblies of depot).
3rd step - Then calculate
EBO and VBO for subassemblies of depot.4th step - Determine E(PL) and V(PL) for assemblies of depot.
Note that EBO, VBO, E(PL) and V(PL) follow inverse sequence relevant to FR's allocation.
5th step - Determine EBO and VBO for assemblies of depot.
Observe that E(PL) = E[BO(S=0)] and V(PL) = V[BO(S=0)].
Check the values using this MS-Excel file with EBO and VBO functions.
The VBA codes for UDF (user defined function) are here.
6th step - Determine E(PL) and V(PL) for subassemblies of bases.
7th step - Determine EBO and VBO for subassemblies of bases.
8th step - Determine E(PL) and V(PL) for assemblies of bases.
9th step - Determine EBO (and VBO) for assemblies of bases.
10th step-Find out system
availability (here not shown). As conclusion see here the summary of recursion formulas, MS-Excel file with user defined functions for EBO and VBO developed to avoid summation to infinite, visual basic codes used to write these functions.
Failure rates are modeled by straight line bathtub form function and pipeline and expected backorder values are calculated in three different phases, infant mortality, random failure and wearout in this table . To see
A) METRIC (poisson random variable for both depot and bases) See the
simulation item (I) below.1 2 3 4 B) QUEUE-METRIC (Metric with queue at depot) See the
simulation item (K) below.1
^{st} step - introduction of queue M/M/4 to limit repair capacity of depot and calculation of probability distribution per queueing theory. For PBO and EBO computation see detail in (F) below.
2 C) VARI-METRIC (bases distribution approximated by negative binomial) See the
simulation item (I) below.1 2 3 4 5 Note: EBOs of bases for depot quantity equals to zero are got from poisson distribution (same as METRIC).
1 2 3 or n = 0, 1, 2, ..., M and ( n - m ) >= 0 that is etc, etc. This operation is performed by an UDF (user defined function) and it requires recalculation each time values change pressing keys combination "Control + Alt + F9". 4 Note: this process directly finds all EBOs (of depot and bases). See the numerical table of spreadsheet here.
1
^{st} step - introduction of queue M/M/4 to limit repair capacity of depot and calculation of probability distribution per queueing theory. For PBO, EBO and VBO computation see detail in (F) below.
2
1
^{st} step - Depot EBO and VBO similar to (C) above. For PBO, EBO and VBO computation see detail clicking here. Examine spreadsheet file and its codes.
2 3 See the simulation item (J) below. The theory is explained here. Note that EBO (NBD), VBO (NBD) and VBO (precise) refer to vari- metric model.
1 2 3 4 5 6
^{th} step - determination of assembly expected and variance values of backorder.H) MOD-METRIC with fill rate and availabilityExample similar to previous one comparing:
Fill Rate and EBO that are measures of spare or service parts supply support performance andAvailability that is a measure of system (or here assembly) operational performance.Note that P[BO=0], Fill Rate and Availability curves
start convex and become concave while EBO curve is convex.I) METRIC (Excel simulation)General comments
1) Depot stock quantity of cell A6 is an input, change it to see spreadsheet calculation.
2) Base stock quantity of cell G6 is another intput, change its value.
3) Press combination keys "Control"+"Alt"+F9 for recalculation to get different outputs.
4) Compare outputs mean (cells C2, D2, H2, I2, J2, K2) and Expected Backorder (E2, L2) calculated value with table contents of
example (A) above.5) Compare outputs variance (cells C4, D4, H4, I4, J4, K4) and Variance Backorder (E4, L4) calculated value with table contents of
example (C) above.6) For difference between workseets 1 and 2 see (6) below.
Worksheets description
1) Cell A6, see above.
2) Column B with 10,000 time period (for instance, weeks, days, ...).
3) Column C, generation of Poisson numbers with mean = 2.4 by UDF (user defined function).
4) Cell G6, see above.
5) Column H, quantity under transportation and generation of Poisson numbers by UDF.
6) Column I shows quantity of one base under repair at depot. The first procedure is the use of Poisson UDF with half of mean given in cell E2, this easy way causes incongruous numbers between columns E and I. The second one is to allocate half of quantities of column E to I, but odd numbers cause some difficulties as column H must contain integer numbers.
7) Column J with summation of columns H and I.
8) Column L for backorder quantity of one of two bases considered.
9) Columns for on hand quantities in depot D and one base K.
10) See
here extracts from simulation. J) MOD-METRIC (Excel simulation)1) Press "Control"+"Alt"+F9 combination keys for recalculation.
2) 10,000 sample times are simulated.
3) See
item (I) above for other comments.4) Stock quantities cells A6, F6 and K6 are inputs.
5) EBO's and VBO's of cells D2, D4, I2, I4, O2 and O4 are outputs.
6) Each time assembly has a fail it is caused by only one of two subassemblies. When we
cannibalize an assembly removing operating subassembly to repair another assembly the final EBO and VBO have different result than shown.7) Here is extract from simulation, see table of
item (G) above for comparison.K) METRIC WITH QUEUE (Excel simulation)Simulation similar to
item (I) above with queue ditribution probability.See extract here and compare with
items (B) and (F).Several values of EBO at bases found during the development of this example are shown here. Note that all MS-Excel applications and files herein have only purpose to demonstrate and explain the methods and the author is not anyway direct or indirectly responsible for any result from its use by anyone.
From 1980 to 1990 worked for Brazilian Aircraft Manufacturer EMBRAER at Sao Jose dos Campos in Sao Paulo as Engineer in Spare Parts Division. From 1991 to 1994 worked for JAMCO Co (JAL group company) in Tokyo Japan as Engineer with Technical Publication (for Galley and Lavatory for Boeing & Douglas Aircraft) . Since 1994 works for GAMESA AERONAUTICA (now |