RELIABILITY, EXCEL, KETTELLE'S ALGORITHM AND INVENTORY OF REPAIRABLE SPARE PARTS

Objective: Development of Kettelle's algorithm with Microsoft Excel.

Method: Practical use of VBA for Kettelle's algorithm based on theory and example given in the book "Statistical Theory of Reliability and Life Testing" by R.E. Barlow and F. Proschan , 1975, chapter 7, item 4 "Optimal Spare Part Allocation", using Spanish Microsoft Excel 2000 (version 9.0.2812) for Spanish Windows NT Workstation (version 4).

Description:  Detail knowledge about theory shall be get reading the book in the chapter above indicated. The purpose is to solve system maintenance problem determining "best" (that is maximum total fill rate or alternatively minimum total expected back order) repairable spare parts set possible for a given amount of money. See detail here. The inventory model solution may be treated as "knapsack problem" for spare parts planning. This description is limited to how the algorithm was developed with Microsoft Excel. The presentation herein details and illustrates the numerical example of the book.

This method is a case of system approach in opposition to item approach explained here.

The understanding of Poisson process (probability applied to spare parts inventory), this algorithm (concept of dominating sequence), Little's law, Palm's theorem (1st demonstration and 2nd demonstration), probability convolution and binomial disaggregation allow the comprehension of further models like METRIC (Multi-Echelon Technique for Recoverable Item Control) and its derivatives that are useful to describe quantitatively the logistics of repairable spare parts.

The Kettelle's application was developed in Excel environment using worksheets for seeing results of calculation as the procedures were written in some modules inside Visual Basic project of workbook. This method allowed the check of computing (partial results) step by step and correction required was performed in Visual Basic program.

The application is limited to four part types, Excel precision and each part quantity of 100 units, and their allocation is obtained computing first two types, after other two types and finally four types. If someone  wants to experiment for only three parts please make the 4th type parts data all zero on the first worksheet. The VBA program can show so called "undominated allocation" (fill rate numerator and total cost) for each two parts on worksheets but this function is partially deactivated.

A) The first worksheet contains the parts data (part type, demand rate, mean time to repair, cost and budget) same as example of referenced book that the user can change with Excel. The cost or price may be replaced by volume of spare if the size of storage is considered.

To start calculation (after your data insertion) click "KETTELLE" button and wait up the application shows end message box. It can take some minutes if the budget is high.

B) The second worksheet shows after processing results for quantities of set types 1 and 2 with cost and fill rate numerator for it.

Steps followed:

1st) Generate costs and failure rates or failure rate numerators (C, FRN) for sets with type 1 and 2 (N1, N2) subject to budget constraint.

2nd) Sort de sequence (N1, N2, C, FRN) by ascending values of cost and after for each cost by ascending value of FRN.

3rd) Delete from the sequence all decreasing FRN.

4th) For repeated values of cost keep only one FRN with greatest value, deleting all other sets.

C) The third worksheet is similar to the second one containing result for types 3 and 4. 

Steps followed:

Similar for worksheet 2.

D) The fourth worksheet combines the sets of worksheets two and three and sums partial costs and fill rate numerators and gives the "best fill rate attainable for various spares budget" (please compare with figure of book if available).

Steps followed:

1st) Generate sequence (N1, N2, N3, N4, C, FRN) summing the values of cost and failure rate numerators from worksheets 2 and 3 subject to budget constraint.

2nd, 3rd and 4th) Similar for worksheet 2.

E) The fifth worksheet shows the allocation with graphical format. Seeing this graphic one can get interesting conclusion.

Excel Kettelle's Application: MS Excel file  For example given it takes approximately 25 minutes for my PC with Intel processor x86 of 331Mhz, with up to date PC it shall be 2 or 3 minutes. The Excel file has approximately 360 Kb. The author is not responsible for any result direct or indirect of use of this application.

Conclusion: Interchanging positions of 4 types and calculating sets for $1.500,00 the various results are same. The above first table shows that the type 2 is part with greatest value of mean quantity under repair (3.00), types 3 and 4 have intermediate and close mean values (1.80 and 2.00), type 1 has smallest mean (1.00) and this is the approximate order that the quantities grow on second table. One can note that as cost increases the fill rate numerators became close to the sum of demand rates of set component parts (that is a fill rate of 100%). The budget of book example of $1,500.00 was extended to $10,000.00 in this application and we can see by curve Fill Rate Numerator (FRN) versus Budget (C) on worksheet "GRAFICA" that any investment more than $4,000.00 corresponding to spares set (4, 7, 5, 4) and fill rate of 95% is not followed by significant increasing in Fill Rate.

For a second version the expected backorder (EBO) concept is here and its application with Kettelle's algorithm is in this MS-Excel file . The graphical result is shown on this output .

Comparing two methods above the data sets obtained are:

i. (Budget=$1,500.00; Fill Rate=57%; N1=0; N2=6; N3=3; N4=0) and (Budget=$1,500.00; EBO=2.009; N1=1; N2=5; N3=1; N4=2)

ii. (Budget=$4,000.00; Fill Rate=95%; N1=4; N2=7; N3=5; N4=4) and (Budget=$4,050.00; EBO=0.077; N1=3; N2=7; N3=5; N4=5)

iii. For high values of budget the spare part sets are commonly same, for instance with budget = $5,000.00 the quantities are (N1=4; N2=9; N3=6; N4=6) and with budget = $10,000.00 the quantities are (N1=9; N2=16; N3=12; N4=12).

The comparison between EBO x FR results is here and we can see by this table that EBO method gives kits with spare parts better distributed.

An alternative way to general EBO solution is application of marginal analysis that here is developed using MS-Excel spread sheet and its formulas (not Excel VBA), the sets of spare units got are presented in two tables herein .

Checking resulted quantities of two methods it is noted that the marginal approximation analysis has a lack that did not obtain all and optimal kits as from general solution ( for instance the set (total cost = $1,050.00; total EBO = 3.140639; N1 = 1; N2 = 3; N3 = 1; N4 = 1) of the 2nd table is not in the 1st one ). The reason is the marginal value procedure in each step always increases the quantity by one unit and leaves to pick the kits with decreasing next quantity even they have ascending values of total cost and descending total EBO. The conclusion is that the marginal values should result in smaller and not complete number of solution data sets.

The third part shows application of queuing theory to repair shop, stock and operation system. The queue introduces limitation to repair and maintenance resources that up to now here were taken as infinite. The model M/M/s used is detailed in this file.

First page illustrates the system where repair shop is divided in queue and servers.

Second and third sheets have characteristic expressions for several type of queue.

Fourth page shows the poissonian distribution results changing numbers of servers and expected number of backorders obtained

Last sheet is similar to previous one but with precise values obtained and they can be checked through this MS-Excel table.

The probability distribution for one equipment type with failure rate fr = 8, time to repair tr = 10 in a repair queue with servers s =2 is:

p(k = 0) = 0.428571 p(1) = 0.342857 p(2) = 0.137143 p(3) = 0.054857 p(4) = 0.021943 p(5) = 0.008777 ���.

If we consider now two equipment types with fr1 = 3 and fr2 = 5 ( being fr = fr1 + fr2 = 3 + 5 = 8 ), time to repair tr = 10 in a repair queue with servers k =2, the disaggregation using the expressions:

and produces:

for type 1 p1(k=0) = 0.714286 p1(1) = 0.228571 p1(2) = 0.045714 p1(3) = 0.009143 p1(4) = 0.001828 p1(5) = 0.000366 ���.

relevant accumulated probability distribution for type 1 is:

P1(k=0) = 0.714286 P1(1) = 0.942857 P1(2) = 0.988571 P1(3) = 0.997714 P2(4) = 0.999543 P1(5) = 0.999909 ���.

for type 2 p2(k=0) = 0.579832 p2(1) = 0.296589 p2(2) = 0.087232 p2(3) = 0.025657 p2(4) = 0.007546 p2(5) = 0.002219 ���.

the accumulated probability distribution for type 2 is:

P2(k=0) = 0.579832 P2(1) = 0.876421 P2(2) = 0.963653 P2(3) = 0.989310 P2(4) = 0.996856 P2(5) = 0.999075 ���.

If quantity of equipment type 1 is n1= 4 and of type 2 is n2 = 3, the fill rate FR of system is:

FR = (fr1 / fr) * P1 (n1-1) + (fr2 / fr) *P2(n2-1) = (3 / 8) * P1(4-1) + (5 / 8)+ P2(3�1) = 0.375 * 0.997714 + 0.625 * 0.963653 = 0.374143 + 0.602283 = 0.976426 or approximately FR = 97.64%.

Finally it can be observed that splitting the distribution of fr2 = 5 as fr3 = 2 and fr4 =3, the result for fr4 = 3 is same as for fr1 = 3.

Using of spreadsheet tables allows understanding of mathematical equations developped by theory and becomes easy further programming with VBA.

About Author: Jorge Fukuda [email protected] 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).

He learned Excel VBA himself and implemented the algorithm in accordance with his understanding. His work areas of interest are Inventory Management (technical, engineering and scientific knowledge) to fill the gap between practitioner and researcher, Excel VBA and Internet.

Please access similar work regarding METRIC (Multi-Echelon Technique for Recoverable Item Control) model from book of Craig C. Sherbrooke see clicking here and an example.

Message from Mr. Kettelle See clicking here