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 (Multi-Echelon Technique for Recoverable Item Control), a non-METRIC, MOD-METRIC and VARI-METRIC 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) Multi-item A) KETTELLE's algorithm A.2) Single-level Poisson A.3) Single-indenture B.1) Multi-item B) METRIC B.2) Multi-level Poisson B.3) Single-indenture C.1) Multi-item C) MOD-METRIC C.2) Multi-level Poisson C.3) Multi-indenture D.1) Multi-item D) VARI-METRIC D.2) Multi-level Negative binomial D.3) Multi-indenture

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.

• 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
• 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.
• 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:

1. The input values can be changed to see different outputs once the program is completed, inside limitation of final application and EXCEL itself.
2. During application development EXCEL allows to check the partial results step by step up to the final one.
3. 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 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:

1. The 1st worksheet contains input data that the user can change to get different results after running application.
2.  Part 1 i=1 Part 2 i=k=2 Unit price \$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.

3. 2nd sheet shows calculated values for μi0 and λi0.
4. 3rd sheet has the generated undominating sequence for part type1.
5. See here graphical illustration.

6. the 4th page is similar to previous one for part type 2.
7. See here graphical presentation.

8. 5th page presents final result that is the quantity required of each part type by repair level for total cost or budget.
 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.

PART II "NON-METRIC" 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 "Single-period, constrained, multi item spare parts management", see MS-Excel file here and graphical result here.

PART III "MOD-METRIC" THE DEVELOPMENT OF MULTI-INDENTURE MOD-METRIC WITH MS-EXCEL 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 MS-EXCEL MOD-METRIC 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 MOD-METRIC 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 "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 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.

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)

1st step - calculation of EBOs for depot (poisson distribution)

2nd step - determination of pipeline quantities for bases

3rd step - finding of EBOs at bases (poisson).

4th step - presentation of total EBO (depot and two bases) and availability

B)    QUEUE-METRIC (Metric with queue at depot)

1st step - introduction of queue M/M/4 to limit repair capacity of depot and calculation of probability distribution per queuing theory.

2nd step and on - similar to (A) Metric above.

C)     VARI-METRIC (bases distribution approximated by negative binomial)

1st step - calculation of EBOs for depot (poisson distribution)

2nd step - variances for depot by recursive formula

3rd step - pipeline quantities for bases (same as METRIC)

4th step - variance for bases by recursive expression

5th step - EBOs of bases applying negative binomial random variable (see table from MS-Excel).

Note: EBOs of bases for depot quantity equals to zero are got from poisson distribution (same as METRIC).

D)    VARI-METRIC (exact or better precise procedure)

1st step - determination of backorder probability, EBO and variance all for depot (see table of MS-Excel spreadsheet)

2nd step - disaggregation of depot backorder probability across bases (see table of spreadsheet)

3rd 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".

4th 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)    VARI-METRIC WITH REPAIR QUEUE AT DEPOT (precise method)

1st step - introduction of queue M/M/4 to limit repair capacity of depot and calculation of probability distribution per queuing theory.

2nd step and on - similar to (D) Vari-metric above. The numerical table of the MS-Excel and functions or procedures generated with VBA are contained in these sheets.

F)    VARI-METRIC WITH REPAIR QUEUE AT DEPOT (bases EBO approximated by Neg Bin Dist)

1st step - Depot EBO and VBO similar to (C) above.

2nd step - Pipeline quantities and variances similar to (C) above.

3rd step - Bases EBO per Negative Binomial Distribution.

G)    MOD-METRIC

1st step - calculation of subassemblies expected backorder and variances.

2nd step - spreadsheet for backorder probabilities distribution of subassemblies by quantity.

3rd step - convolution of PBO's of subassemblies.

4th step - probability distribution of assembly (poisson).

5th step - convolution of probabilities from steps 3 and 4.

6th 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 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.

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).

Hosting by WebRing.