Site created in YahooGeocities and closed on October 26th, 2009 please go to new and revised site
RELIABILITY OF REPAIRABLE SYSTEM, SPARE PARTS, METRIC AND EXCEL
OBJECTIVE: Development of METRIC (MultiEchelon Technique for Recoverable Item Control), a nonMETRIC, MODMETRIC and VARIMETRIC models with Microsoft EXCEL.
MOTIVATION: the work with Algorithm of KETTELLE (see here) helped the comprehension of the METRIC and construction of this similar web page.
Models summary table :  
MODEL  CHARACTERISTIC  DISTRIBUTION 
A.1) Multiitem  
A) KETTELLE's algorithm  A.2) Singlelevel  Poisson 
A.3) Singleindenture  
B.1) Multiitem  
B) METRIC  B.2) Multilevel  Poisson 
B.3) Singleindenture  
C.1) Multiitem  
C) MODMETRIC  C.2) Multilevel  Poisson 
C.3) Multiindenture  
D.1) Multiitem  
D) VARIMETRIC  D.2) Multilevel  Negative binomial 
D.3) Multiindenture  
PART I "METRIC" The theory is known from text of Craig C. Sherbrooke and its essence (concepts of probability, backorder, expected number and variance of backorder, etc) is summarized on this table. The numerical example used is from paper by Wagner de Souza Borges of Sao Paulo University.
Basically this case describes a repair system with two levels (one depot and two bases) operating two repairable parts or equipment.
The purpose is to determine two sets of parts : one of type 1 N1 (N10, N11, N12) at three maintenance sites (Depot and two Bases) and other of type 2 N2 (N20, N21; N22) also at three maintenance sites, all sets restricted by budget and minimizing the total Expected Back Order.
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.
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:
The present computer program with VBA is experimental and does not have any other purpose than to show the METRIC (MultiEchelon 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 undirectly 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
EXCEL book herein presented has 5 worksheets:


Part 1 
Part 2 

Unit 

$5 
$3 

Budget 

$40 



λ_{1j} 
ν_{1j} 
ρ_{1j} 
τ_{1j} 
λ_{2j} 
ν_{2j} 
ρ_{2j} 
τ_{2j} 
Depot 
j=0 

0.02531 



0.01782 


Base 1 
j=1 
23.2 
0.01000 
0.5 
0.01 
35.2 
0.00700 
0.7 
0.01 
Base 2 
j=m=2 
20.1 
0.01500 
0.6 
0.02 
30.2 
0.01000 
0.6 
0.02 
Here is the schematic description of model and numerical example.
See here graphical illustration.
See here graphical presentation.
N10 
N11 
N12 
N20 
N21 
N22 
C 
EBO 
0 
0 
0 
0 
0 
0 
$0.00 
3.075646 
0 
0 
0 
1 
0 
0 
$3.00 
2.411676 
1 
0 
0 
0 
0 
0 
$5.00 
2.292245 
0 
0 
0 
1 
0 
1 
$6.00 
2.042374 
1 
0 
0 
1 
0 
0 
$8.00 
1.628275 
1 
0 
0 
1 
0 
1 
$11.00 
1.258972 
1 
0 
0 
1 
1 
1 
$14.00 
0.991385 
1 
0 
1 
1 
0 
1 
$16.00 
0.939530 
1 
0 
0 
2 
1 
1 
$17.00 
0.909466 
1 
0 
1 
1 
1 
1 
$19.00 
0.671943 
1 
0 
1 
2 
1 
1 
$22.00 
0.590024 
1 
1 
1 
1 
1 
1 
$24.00 
0.417037 
1 
1 
1 
2 
1 
1 
$27.00 
0.335118 
2 
1 
1 
1 
1 
1 
$29.00 
0.304102 
1 
1 
1 
2 
1 
2 
$30.00 
0.266018 
2 
1 
1 
2 
1 
1 
$32.00 
0.222183 
2 
1 
1 
2 
1 
2 
$35.00 
0.153083 
2 
1 
1 
2 
2 
2 
$38.00 
0.120005 
2 
1 
2 
2 
1 
2 
$40.00 
0.104837 
See graphical format here of sequence for parts type 1 and 2.
See graphical format for final result or here.
PART II "NONMETRIC" MODEL As the method becomes closer to real situation the mathematical expressions to describe the data relationship are too complex. However Rustenburg et al present another simple solution (with greedy algorithm, for not repairable items and limited budget) to "Singleperiod, constrained, multi item spare parts management", see MSExcel file here and graphical result here.
PART III "MODMETRIC" THE DEVELOPMENT OF MULTIINDENTURE MODMETRIC WITH MSEXCEL IS SHOWN HERE.
THIS FILE CONTAINS IN:
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 POISSON DISTRIBUTION.
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 MSEXCEL MODMETRIC WITH VBA IS AN APPLICATION OF GREEDY HEURISTIC OR MARGINAL ANALYSIS ALGORITHM AND WE HAVE THE RESULT SETS (BEARING, SEAL, HOUSING, PUMP, ROTOR, STATOR, MOTOR) FOR STOCK QUANTITIES AS (12; 16; 8; 8; 11; 21; 5) FOR DEPOT AND 5 x (0; 0; 0; 6; 1; 1; 4) FOR BASES SUMMING A BUDGET OF $2,310.20 AND EBO OF 1.89706, AN AVAILABILITY OF 62%, STARTING FROM A KIT OR SET WITH QUANTITIES ZERO FOR ALL ITEMS AND SITES; PLEASE SEE SET OF NUMBER 141 ON SHEETS "MMR" AND "RES" OF THE SPREADSHEET.
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 EXCEL MODMETRIC APPLICATION CAN CALCULATE 220 SUCCESSIVE SETS STARTING FROM STOCK QUANTITIES AS ALL ZEROS (OR ZEROS AND ONES) BUT MORE ITERATIONS ARE POSSIBLE CONSIDERING AS NEW START SET THE FINAL ONE OF PREVIOUS RUNNING AND APPLYING THE MACRO ONCE AGAIN. THE LIMITATION OF THIS PROCEDURE IS THE ERROR GIVEN BY EXCEL NATIVE POISSON FUNCTION AROUND A QUANTITY OF 130 UNITS.
PART IV "VARIMETRIC" These above three practical applications should be the bases for implementation with Excel and VBA of a general multiitem, multiindenture, multiechelon inventory system called VariMetric. 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 and examples of Poisson and Negative Binomial distribution adjusted to it are shown here .
Compare examples of Metric ( with marginal analysis ) and Varimetric. The expected values of backorder and its variances at depot, the expected backorders at the bases can be checked below.
PART V "TIME DEPENDENT FAILURE" The development of Poisson process applying MS Excel spreadsheet with not constant failure rate and dynamic Palm's theorem is presented here .
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 fill rates on table and graphical formats click here .
PART VI "METRIC LIKE MODELS" The
following seven examples detail the development of theoretical expressions with
spreadsheet.
A) METRIC (poisson random variable for both depot and
bases)
1^{st} step  calculation of EBOs for
depot (poisson distribution)
2^{nd} step  determination of pipeline
quantities for bases
3^{rd} step  finding of EBOs at bases
(poisson).
4^{th} step  presentation of total EBO (depot and two bases) and availability
B) QUEUEMETRIC (Metric with queue at depot)
1^{st} step  introduction of queue M/M/4 to limit repair capacity of depot and calculation of probability distribution per queuing theory.
2^{nd} step and on  similar to (A) Metric above.
C) VARIMETRIC (bases distribution approximated by
negative binomial)
1^{st} step  calculation of EBOs for
depot (poisson distribution)
2^{nd} step  variances for depot by
recursive formula
3^{rd} step  pipeline quantities for
bases (same as METRIC)
4^{th} step  variance for bases by
recursive expression
5^{th} step  EBOs of bases applying
negative binomial random variable (see table from MSExcel).
Note: EBOs of bases for depot quantity equals
to zero are got from poisson distribution (same as METRIC).
D) VARIMETRIC (exact or better precise procedure)
1st step  determination of backorder
probability, EBO and variance all for depot (see table of MSExcel spreadsheet)
2^{nd} step  disaggregation of depot backorder
probability across bases (see table of spreadsheet)
3^{rd} step  convolution of result of
previous step with probability (poissonian) of spares under transportation from
depot to base (see table of spreadsheet)
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^{th} step  finding of EBOs at bases
from probability of backorder at bases resulted by convolution (see table of
spreadsheet)
Note: this process directly finds all EBOs (of
depot and bases). See the numerical table of spreadsheet here.
E) VARIMETRIC WITH REPAIR QUEUE AT DEPOT (precise method)
1^{st} step  introduction of queue M/M/4 to limit repair capacity of depot and calculation of probability distribution per queuing theory.
2^{nd} step and on  similar to (D) Varimetric above. The numerical table of the MSExcel and functions or procedures generated with VBA are contained in these sheets.
F) VARIMETRIC WITH REPAIR QUEUE AT DEPOT (bases EBO approximated by Neg Bin Dist)
1^{st} step  Depot EBO and VBO similar to (C) above.
2^{nd} step  Pipeline quantities and variances similar to (C) above.
3^{rd} step  Bases EBO per Negative Binomial Distribution.
G) MODMETRIC
1^{st} step  calculation of subassemblies expected backorder and variances.
2^{nd} step  spreadsheet for backorder probabilities distribution of subassemblies by quantity.
3^{rd} step  convolution of PBO's of subassemblies.
4^{th} step  probability distribution of assembly (poisson).
5^{th} step  convolution of probabilities from steps 3 and 4.
6^{th} step  determination of assembly expected and variance values of backorder.
Several values of EBO at bases found during the development of this example are shown here.
Note that all MSExcel 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.
Jorge Fukuda jfukuda@zipmail.com.br The author earned Mechanical Aeronautical Engineer degree in 1979 by Brazilian Air Force College at Sao Jose dos Campos, Sao Paulo.
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 AERNNOVA) at Vitoria Spain as Spare Parts Engineer (for Airbus, Sikorsky, Bombardier and Embraer Aircraft Structural Segment).