Introduction
I received a phone call from a panicked account manager at our headquarters last Thursday. A proposal needed to go out Friday and the proposal team did not have all the information that a customer had requested. The account manager needed reliability data on our products and an estimate on the number of spare parts that the customer would need to keep on hand for servicing their deployment. They needed an answer fast (< 24 hours). Normally, an engineer from our Quality department would answer these questions. Unfortunately, the engineer responsible for this work was on vacation and could not be reached. The question eventually came to me because I have worked on similar problems in the past. Could I help? Here is how I proceeded to answer the questions.
Problem Statement
Let's frame the problem a bit more precisely. The customer has requested the following information:
- Estimates of the Mean Time Between Failures (MTBF) data for the optical products that this customer was going to purchase.
There is a standardized way to estimate these numbers. I don't agree with the standard, but that is beside the point.
- MTBF estimates for products from other supplies that are used with our equipment.
This is basically an information gathering task plus a little bit of algebra.
- Estimates for the number of spare parts required to be kept on hand to provide a 95% confidence level that the customer would have adequate spares on hand.
This customer only replenished his spares once a quarter and wanted ensure that they had adequate quantities on hand.
Analysis
Let's take the questions on one at time.
MTBF Estimates for Our Products
The customer had asked for reliability estimates based on an industry standard, specifically Telcordia's SR332. This approach is straightforward:
- Obtain estimates for the MTBF of each part used in each product.
These estimates are available from the vendor for each part. The vendors often do not like to give you the MTBF estimate because they do not want the estimate to imply a warranty of operational life, but they all have the estimate.
- Combine these estimates using
MTBFAssembly is the MTBF of the entire assembly, MTBFi is the MTBF of the ith component in the assembly, and N is the number of components in the assembly.
This approach produces what is called a "parts count" MTBF estimate. It assumes that an assembly's failure rate is dominated by random part failures. It turns out that all these calculations were already performed and were in our document control system. The folks at headquarters did not know where to look for the data. So I sent them an email with a table summarizing the data.
I mentioned earlier that I do not agree with this approach to estimating MTBF. In my experience, I have seen little correlation between the MTBF estimate using the parts count method and actual field failure rates. The reason is simple -- field failures are primarily due to factors that have nothing to do with random part failures. For example, lightning is the single largest cause of field failures that we encounter. The part count MTBF estimate does not model lightning failures. If I were king of the world, I would estimate spare parts requirements using the regional field failure rates. This number is usually about 0.1 % of the installed based per year, but varies by the level of lightning strikes and average temperature.
Lightning severity and frequency varies by geographic region. For the US, it is worst in the Gulf Coast region, especially Florida. Where do we see the highest product failure rates? The Gulf Coast region, with Florida being the worst.
MTBF Estimates for Other Components
Unfortunately, gathering this information was a bit of a problem. If a part vendor had provided an MTBF estimate, I used it. However, not all vendors provided this information. Since I only had a small a few hours, I decided to work by analogy. There is actually little difference in the computed reliability of the parts from different vendors - we all use similar parts from similar vendors. For each part that did not have a stated MTBF, I found a comparable part that did have a specified MTBF and I used that for my analysis.
Once I had all the MTBF values, I could create a table with MTBF values for each part that the customer would be using. I will use this table to create an estimate of the spare parts inventory requirements.
Spare Parts Requirements
There was some serious math to be done here. The following assumptions here are reasonable for the products under consideration by this customer:
- The products fail and cannot be repaired.
This certainly is true for lightning failures. The term "lightning failure" really does not due justice to what happens. Frequently the electronics are destroyed. Other types of failures can be repaired, but it may not be economically worth it. In many cases, it is cheaper to just replace the failed item.
- We can model the failure rate using a Poisson probability distribution.
You have to assume some distribution and the Poisson is a analytically tractable.
- The inventory of spare parts is replenished at regular intervals.
This is because companies tend to periodically perform inventory checks and replenish their inventories when they see their stocks are low.
- The customer wants a 95% confidence that his spare inventory will be adequate.
This is a common assumption. You normally see confidence levels of 90%, 95%, or 99%.
- The customer wanted us to use our MTBF prediction from the parts count method.
We have real field data, but the customer wanted us to use the MTBF prediction by the part count method. I salute smartly and use that estimate.
We can use Equation 1 to compute probability of using a given number of spares, PSpares(s), during an interval of time. Equation 1 simply sums the Poisson probabilities of using different numbers of spares.
| Eq. 1 |  |
where
- PSpares(s) is the probability of using s spares.
- s is the largest number of spares allowed.
- N is number of assemblies deployed
- k is an index variable for the number of spares used.
- λ is failure rate of the assembly,
.
- TRis the replenishment time.
The replenishment time varies by customer. In this case, TR=3 months
My plan is to use Mathcad to solve for the number of spares s that I require to meet the 95% reliability requirement. You can see this calculation in Figure 1.
Figure 1: Mathcad Routine for Computing Required Spares.
While Figure 1 shows an exact calculation, the Normal distribution can be used to approximate the Poisson distribution when 
| Eq. 2 |  |
where kCL is a constant corresponding to the confidence level desired. For a 95% confidence interval, we observe that 
We illustrate the results of this calculation in Figure 2. The results obtained from the approximation are almost identical to those of Figure 1.
Figure 2: Illustration of Normal Approximation to the Poisson Distribution.
Conclusion
Catastrophe averted. It was a nice illustration of the use of the Poisson distribution for a real-world problem. It also shows how a well planned day can be upset with a single phone call.
Addendum
I have had a request for an Excel version of this calculation. I have included two tabs:
- array formula-based, which provides a very short solution in Excel 2010
- data table-based, which works for Excel 2003
I have also been asked to make a version available that does not use named variables. Here you go.
I have also been asked to make my Mathcad 15 worksheet available. Here you go.
Mathcad Version
Just save the file to your computer and load it into Mathcad. Your browser will try to view it as an XML file.
There is a Mathcad Prime 2.0 version as well. Here is a sheet for that version.
Mathcad Version
Great post with a real world example that makes it easy to relate to. How would one perform the calculation in MS Excel?
Hi JonC,
I attached an Excel version of the calculation to the post as an addendum. Download it and give it a try. Because I am doing this over lunch, I did not have much time. The quickest thing for me to do was to use an array formula. It seemed to work well. If you have any issues with it, send me a note.
mathscinotes
Thanks for the quick response. Supplementary question to improve my knowledge of Excel functions: what function does the underscore in front of "CL" and "R" perform?
Thanks again for your example.
Hi JonC,
That is simply my convention for named variables. I always put in an underscore on the front of named variables. That way I can easily identify them. Nothing special there.
I am interested in your opinions on what I can do to make this material as useful as possible. Keeping asking questions.
mathscinotes
Thanks agin for clarifying the use of the underscore...
The array formula is driving me nuts at the moment. I was able to modify the Normal Distribution method you proposed to allow for different Spares Availability targets to be assessed. However, the systems our customers purchase are moderately sized and widely distributed, so I think a Poisson Distribution would be the better probability assessment?
Anyways - I receive a "#NAME?" error when I try to duplicate the Poisson-based formula. Is this because I am using a SUMMED value for Quantity? Otherwise, the only other difference I have made is to allow for different Spares Availability targets to be assessed, similar for the Normal Distribution method above.
Finally - how would one account for repairable items? If the item is repairable, would it not be reasonable to expect that the number of 'unique' spares be less than if all items were discarded once replaced?
Thanks again
JonC
I will send you a private note. Just send me your spreadsheet and I will take a look at it.
There is a way to handle repairable items. I was planning on writing that up, but I just have not had time.
mathscinotes
I have now added a data table-based version of the routine that should work in Excel 2003 (I only have Excel 2010).
I have been trying to use your excel example but also get name error when I try to use formula. How can I duplicate this to use against my own quantities and mtbfs?
Thanks looks like great way to solve my spare quantity required dilemma...
/ss
It works on my machines, but they all have Excel 2010. What version of Excel do you have? Do both the DateTableBased and ArrayFormulaBased approaches Fail?
Mathscinotes
Mathscinotes
I am using MS Excel 2007 on my machine. Happens in both the table and array based examples. Would it be possible to get the example without any variables so I can use?
/Sammy
I will take a look at it this weekend.
Mathscinotes
I have now included a "no names" version.
Excellent your workbook now works for me when I change the values for quanity and MTBF.
I am now trying to take this array formula into my worksheet and get again the #N/A error. Is this something to do with values in formula noted as ROW($A$1:$A$101)-1 in "Exact Calculation "=MATCH($C$10,POISSON(ROW($A$1:$A$101)-1,E3,TRUE),1)"? I dont see any values in those cells, guess I don't quite understand the overall formula for Poisson you are using. Should I account for values or other when I take this onto my excel worksheet? If I need to account for more than 101 spares assume there is also additional mod I should make.
Current approach has been to use this formula "=POISSON(AA3,Y3,TRUE)" where I toggle / change values for "AA3" until I get 100%. Once I have this I know I have the recommended spares for my company.
Thanks you, this looks to be a great way to solve for Poisson "X" value. Really appreciate the interaction since an approach to this has been something I have been looking to solve for a few days.
/Sammy
Hi Sammy,
I am afraid that I am a big user of array formulas. Do not be confused by the fact that there is nothing in the cells A1 to A101 -- there isn't supposed to be any. The ROW function generates an array that contains 1:101. I then subtract 1 from each element to get an array that contains 0 to 100. I need to ask a basic question. Do you understand that array formulas must be entered using the key sequence "Control Shift Enter"? You will get an NA error if you don't do that.
Why don't you send me your worksheet and I will try to fix it.
Mathscinotes
Mathscinotes,
Ok, so seems I am a bit dense today. I do use arrays occasionally but normally use on a particular cell then tae into array formula. In the case of the ne you have created only works as an array. Went back and created single cell as array formula for a given Poisson mean and now looks like at least right now I’m good.
Thanks you are a life saver, like I said this problem has been eating at me for awhile since my approach was so manual. Toggling for umpteen rows of data to solve for "X" in Possion calc is very tedious indeed.
I can send my workbook if you but to what email address, you have mine?
Cheers,
Sammy
I have sent you a private note. If your worksheet is working -- great. Otherwise, you can still send it to me.
Mathscinotes
Great post, thank you very much!
Hello,
I've read your post and I'm really interested about your analysis but one thing that I can NOT understand is how to compute the number of spares for a duration of 4 years (for example) ...
I've assumed that your computations are done for a one-year duration. Is it correct ?
phdenis
I've assumed to take into account the repair time and a 4-years duration that I've to replace the Tr by (Tduration - Trepair times) in your formula. Is it correct ?
Correct. The key is to understand that the expression N·λ·TR stands for the expected number of failures in a replacement window. If your window is 4 years, then that becomes TR. In the spreadsheet, I called the time variable R.
Mathscinotes
Thanks for your answer.
Is it possible to share your mathcad template ?
B/c I've tried to do it and I've some problems to define a vector in Mathcad. (I'm a Mathcad newbees)
I have added the Mathcad worksheet to the end of the blog post. I hope it works for you.
mathscinotes
Thanks for the file, unfortunately this extension is not recognized by Mathcad 1.0.
According to my Mathcad version, the only extension which is supported is *.mcdx
I'm trying to install the Mathcad 2.0 prime version.
I only use Mathcad 15. Mathcad Prime looks intriguing, but my company has not made the switch yet.
mathscinotes
I have added a Mathcad Prime 2.0 version to the blog as well. I have not switched to Prime yet, but I do have a copy on my system.
I enjoyed your work, but would like to ask a few questions via another communication source such as simple email.
I tried your email, but it bounced. Was your server down?
Mathscinotes
Dear Mathscinotes,
first of all, thank you for showing your great attitude by sharing your thoughts and findings. I am currently trying to implement an equation very similar to yours:
http://src.alionscience.com/pdf/POIS_APP.pdf
- See page 2, section "sparing Analysis"
First Question: what's the difference to your approach?
Second Question: Could you maybe help me to get both versions running in a sample logistic breakdown?
Thanks!
I will try to help -- I am in-between flights right now and I may need to get back to you early next week.
Mathscinotes
Hello,
I'm wondering which is the formulae used to compute the real MTBF if you have only a number of failure, a time as it's done in the failure rate tab (http://src.alionscience.com/cgi-src/formdraw.pl?Change2=2)
I was thinking about blogging on estimating component failure rate based on experimental data -- I went through this exercise recently. Unfortunately, I have not had time. However, the procedure is well documented. My favorite reference is from Analog Devices. Take a look here.
Mathscinotes
Hello Mathscinotes,
Searching for a spare parts formula in order to solve the stock requirement, I found your post, which was shown and seriously considered, during an important company meeting. I think we`re ok with the 95% formula.
Thank you!
You certainly can change the 95% threshold. If you need help, just ask.
Mathscinotes
Hello,
Thanks for such a great post chock full of details. For myself, I'm trying to do an estimation on individual spares rather than an entire assembly and so I have to adjust the formula for the Poisson distribution accordingly. Therefore, I would like to ask - how did you get the formula for the Poisson distribution? I am unable to figure out, specifically, how does Replacement Time come into play into the formula. Hopefully you would be able to enlighten me. Thank you.
Regards,
Tom
I am not sure that I completely understand your question, but we certainly can begin a dialog. Let's breakdown the Poisson distribution formula.
where
I think the confusion comes in the computation of ζ, which is the probability that a widget fails in a given time. For a Poisson distribution, we assume that the probability a widget will fail in the next hour is the same probability that they will fail during an hour ten years from now. Now is when we usually talk about the bathtub curve. The Poisson distribution works when a part is in its "constant failure rate region", which is also known as the "random failure rate" region.
Let's call the constant failure rate μ, which will have units like "failures per hour" or "failures per month". The probability that something will fail in 10 hours is 10 times that μ or
. This is the number you plug into the Poisson formula.
So the longer your replacement time, the more likely you are to have a failure. I do not know if this was what you are asking, but it is a start on our dialog.
Thanks for the amazingly quick reply (I'm ashamed that I didn't reply just as quickly!)
I think you may be right as to where my confusion lies.
The most straightforward scenario is, say, the "failure rate per month", μ, of a certain Part X, is equal to 5, and the "time taken for replacement", T, is 1 month. This means that ζ will be equal to 5 x 1 = 5. To compute how many spares should be kept in stock, it would be akin to checking Pr(X=k) >= 0.95 (for a confidence level of 0.95) and determining the value of k.
Conversely, if the time taken for replacement for another Part Y, is, say, 4 months, and μ remains the same as Part X, ζ becomes 5 x 4 = 20 instead. But additionally, ζ can no longer be defined as "failure rate per month" and has to be changed to "failure rate per 4 months". This is where I get confused. On face value, it is clear that, given that both have the same failure rates per month and they differ only in terms of T, Part X is a better spare part to use compared to Part Y because it can be replaced more quickly. (of course, assuming that both have equivalent functions). I don't seem to be able to figure out how I'm able to show this via calculation (i.e. a numeric value that shows Part X's "superiority") using the Poisson formula. Perhaps you may be able to help. Thanks once again!
Regards,
Tom
I guess I am still confused by your question. If Part X and Part Y each have the same failure rate, then the only difference in the problem is that the replacement intervals are different. This just changes the number of spares you require. No part is superior to the other because they are identical from a reliability standpoint.
Remember that λ is not a rate -- it is the mean number of failures during the replacement interval. If you have more failures on average during the replacement interval, you will need more spares. In general, you set the number spares so that you are sure to have spares when you need them during the replacement interval.
Generally I do spare calculations because I have a set of parts that I need to buy every quarter. I normally do not choose a part based on the number of spares required, although I could imagine that situation occurring.
I realise that my definition of "Replacement Time" differs quite significantly from yours, and perhaps that is where the misunderstanding arises from. For myself, the "Replacement Time" I'm referring to is not the interval at which the company replaces the spares, but rather, comprising two components: Delivery Time, and Active Repair Time (if applicable). The idea is that even with fixed intervals at which the company orders the spares, there is still a lead time that needs to be considered since it takes time for the spares to be delivered. Additionally, some spares may be such that it needs to be sent back to the OEM to be repaired before being sent back to the company, which again, means that there needs to be spares in stock for use during this time period.
I'm not sure if, given that our definitions differ, a Poisson distribution is still usable in context. But I believe your answers have been definitely helpful in understanding other facets of spare replacement optimization, something which I've only started to read up recently. Thanks!
Regards,
Tom
Hello mathscinotes,
So I've plugged in your formula from the nonames spreadsheet into my own worksheet, changed all the relevant cell references and am still getting a #N/A error. I read previously on your blog that this may be because "array formulas must be entered using the key sequence 'Control Shift Enter'". Perhaps that is my problem but I don't know what that means.
Thank you kindly,
Stephen
I will send you a private email.
I just sent it.
Thanks!
Great Post ! It helped me along with my own spares analysis.
Hi Mathscinotes,
Could you please send me the excel sheet with formula for the calculation of the pare parts?
Appreciated if you can share your knowledge and expertise...Thanks a lot in advance. Cheers...
Good day, I need to do a spares prediction for 5 years and then in 5 year increments, eg. for 5yrs, 10yrs up to 25 yrs. Can you please send me an Excel spreadsheet with the formulas to do this, I am on my knees. Thanks in advance, Sampie
Hi
Just download the excel file and tune it with your qty and duration tr.
Good day,
Thanks for the reply but I can't get the formulas to work.
When you say the formulas do not work -- what do you mean? What failure does it give you?
mathscinotes
Hi., sorry to bother. I need to estimate my spares for 10 years period. Do i have to set the tr to 3650 (365 * 10)? thanks.
Hi there,
I am currently calculating my spares for the next 5 years..
Ready with my MTBF(49176) .. however it seems that after inserting 5 years worth of days, I gotten a shock from the number.
BTW my formulae is (((Operating time - DownTime)* no of equipment) / Frequency)
Please advice
I assume you are using the Excel version of the worksheet, which cannot handle a spares number greater than 100 units. This number reflects the range of product spares that I need to keep. Your MTBF of 49176 hours and replacement interval of 5 years are each about 10x greater than I normally deal with. However, we can make the spreadsheet work, you will just need to make a modification. I will show you what I did.
First, let's show you an example computation. Since you did not tell me the number of units you are dealing with, lets pick 1000 units. The following illustration shows that we can use the Gaussian approximation for this calculation. Notice how you need almost as many spares as you have operational units. This is because your replacement interval is nearly the same as your MTBF.
To make the spreadsheet work, let's look at the tab labeled "ArrayFormulaBased". We need to modify an array formula in F3 from this
=MATCH(_CL,POISSON.DIST(ROW($A$1:$A$101)-1,E3,TRUE),1)
to this
=MATCH(_CL,POISSON.DIST(ROW($A$1:$A$1001)-1,E3,TRUE),1)
All this change does is increase the maximum number of spares. Remember that for array formulas, you need to
You should get a result that looks like shown below.
I will take a look at updating my spreadsheet so that it handles a wider arrange of spares. I originally wrote the worksheet to deal with a quick calculation and I did not worry about making general-purpose.
Hi,
Any chance of writing one on repairable spares?
can I assume that Tr becomes the repair time and will the model still works?
Thanks.
Allen
Hi,
With repairable, shall we consider using Markov process... this is getting complex...
Is there an Excel way to do this?
Thanks.
Allen
Thank you for a very useful explanation, and especially the Excel example!
Hi there,
I am a little bit confuse with Tr- the replenishment time. Is it the lead time of spare parts or a interval which you decide to replacement?
I view it as the interval of time between buying more spares. I want to buy enough spares to ensure I rarely run out of spares. Let's give an example of how I use it. My customers want to minimize their inventory costs, but also want to ensure that they always have spare parts when they need them. They usually replenish their inventory every quarter. This calculation allows the customers to know how many parts they need in stock at the beginning of every quarter.
question: in spareexampleinexcel2, the Poisson mean is "fixed" to have its mean be equal to one specific example row's Factor - so how are the calculated values for the other example rows valid?
hello, i was given a table at work that contains the values of MTBF, MTTR, number of essential components (NEC) , number of redundant components(NRC) , availability of components in percentage (AC) , Availability of the Combined component in percentage(ACC) and the replenishment time is 1 month... so i figured i'll use the equation in Figure 2: "Illustration of Normal Approximation to the Poisson Distribution" but i dont know what the "n" is? it says its quantity but the quantity of what exactly? its the first time i do this so can you give me a detailed explanation and im sorry if my question is stupid
There is no such thing as a stupid question. To specifically answer your question, n is the number of operating units. To make this clearer, I have changed Figure 2 to explicitly show the n and mtbf vectors. I had assumed that folks would use the same n and mtbf vectors from Figure 1. However, I have had this question from a few folks and I will make it clearer in Figure 2.
Hopefully, this will help. If it doesn't, try sending me another question.
mathscinotes
hello mathscinotes, thanks alot for ur reply, but i dont know what does number of operating units exactly mean, the table i have is listing "number of essential components" is it the same? i mean can i use them in the n field?if not how can i compute the n exactly, thanks alot
Hi,
I'm happy to have come across this post. I have used the excel and it works with the exception of a few instances in which the match doesn't seem to work. I notice that in these cases the poisson formula returns a value greater than the confidence level. What does this actually mean? Any ideas on how to resolve this?
Thank you kindly.
It's me again, I just thought I would provide an example. Qty = 3, MTBF = 1E+07, Poisson formula returns 0.99935221, confidence level = 95%, Exact Calc = #N/A, Approx = 1
If I set the confidence level to 99.94% the Exact Calc = 1
Hi Racheal,
Sorry you had issues with the spreadsheet. It was something I cooked up quickly to solve a problem I had at work years ago. The NA makes sense when you look at how I solve the number of spares – however, it is a bug.
The algorithm I use is very simple. I generate all the probabilities for needing from 0 to 100 spares. I used an approximate match to find the largest probability value that is less than or equal to 95% (the confidence limit). This approach assumes that a probability of 95% exists in the table. Because your MTBF is 1141 years and you only have 3 units, it is unlikely that you will have even one failure. This means that even the probability of no failures (the largest probability in the table) is greater than 95%. So match could not find the largest probability less than 95% because nothing was less than 95%.
I will fix the spreadsheet later this week. You should interpret the result as meaning that you have a better than 95% chance of needing no spares during the 90 day replacement interval.
If you increase the number of devices from 3 to 10000, you will see that it will generate reasonable numbers for spares. I normally deal with quantities of 100,000 or greater and the spreadsheet was intended for those cases. I will extend it to your case.
Mark Biegert
Thank you for the very prompt response. I can action the problem in my spreadsheet with the info you have provided. But I might check back for the update out of interest - no rush though. Thanks again.
Racheal
Dear Mathscinotes,
I was very happy to find your approach for spare parts calculations here.
One question about your calculation:
(My assumption: The duration of a spare part depends on its MTBF and how many hours I am using it per year or day. The less hours, the less spare parts.)
Since I couldn't find the factor "operating hours/cycle": Are you assuming in this case that spare parts are used 24h per day? Where would I consider a usage of less than 100%?
Thank you very much for your answer in advance,
Thomas
Hi there,
In the factor & approximation Column, where you have the constant 24, I presume this is the duration in months for the system being under 'support'.
I have taken the confidence level to be the perceived reliability, although wasn't sure as this would be related to the MTBF?
Lastly... I took the replenishment factor (in days) to be the time taken to replace stock item once used.
Thanks,
Paul
Hello,
I've used your nonames.xlsx file some years ago in order to compute the number of spares required for the maintenance of a park of elements for a duration.
After 2 years, the number of equipement to maintain has increased (+2 items on a park of 20); so I've taken my excel sheets and update the duration in order to compute the number of items required for 2014 up to 2018.
I've shared my spares computations with one of my colleagues and we don't have the same point of view about the spares computations. My colleague disagrees with one of my parameter the duration due to the fact that the 2014 period mid 2016 period is over. Thus, for my colleague, I've only to consider the park from 2016 up to the end of 2018 and says that electronic board is memory-less. One the other side, my point of view is to say that I've to compute the spare computation from 2014 up to 2018 due to the fact that the electronic board contains capacitor and capacitor aged with the time.
Hoping my explanations are clear.
By advance thanks for your help.
Ph. D
So, could you please tell me whose is right and explain me if possible my misunderstanding of the problem ?
Hi mathscinotes,
May I enquire what will be the method to calculate the minimum number of spares required for repairable items with a rectification time given?
Regards
Shannon
Good afternoon,
Great article here. Something I have been working on for 8 months and it is killing me!
Like Pablo I am curious about the constant "24".
My other question is related to array and set at "100". What do you mean by setting the amount of spares between 0-100? How are you determining what that number should be?
I am also interested in the repairable formulas.
Regards,
Eric
Hi Eric,
I will try to get back to your question this evening. I am a bit tied up now.
mathscinotes
I have been re-energized to work on this issue. I did figure out what the "24" is related to in the factor formula; it converts the factor into hours from days to match the MTBF. Now my concern is on the array or "number of spares". I am not understanding the concept of setting the array up and what it is really doing to aid in the formula. This overall concept is something I am really trying to understand.My concern is when I put one of my companies parts data into the no-names formula, the result I get back is way out of the norm.
I appreciate you taking time to assist with my understanding.
Regards,
Eric
I guess one other questions (may be a dumb one) as I re-read the entire blog again, is the quantity the total number of this item in an end item or the total number of end items being supported?
Hi, I would like to ask one question that what to do if our N*(1/MTBF)*T is less than 10? How can we use the formula in our excel for that? I understand we can not use figure 2 formula ?
I would like to share my excel sheet with you for review? If that's possible.
Thanks in advance
Superb post!!
Made my concept about "Poisson distribution being used for spare part inventory" clear in no time.
Thanks for efforts taken by you for providing excel version.
God bless yo!!
madhav
Hi Mathsci, thought process implemented in excel is really great and useful ! Congrats. I how ever have following improvements to suggest:
1. a lot of times during presales stage, client do not provide failure history data specifically a combination of parts/components thereof when integrated have a solution that is working at site. This part can be made part of system level spares. This is essential since MTBF is required lot of times at system level
2. there is logistic component which various between sites drastically since spares arrived at based on excel sheet can be stocked in central warehouse. Now, from warehouse to actual site is a gap area that can be fixed seperately
3. component level MTBF reduces drastically due to maintenance procedures and practices used by maintenance engineers at site during failure resolution times
4. instead of confidence level, one may like to use actual lead time committed by OEM/supplier. How do we provide input for confidence level ?
5. when parts are rare to get but refurbished are available however failure rates are actually gathered after having actual failures at site and rectified after few trials! in this case, failures in million hours does not serve the purpose. HEnce if failures rate @ each quarter is available or estimated then how do we use this excel?
My sincere request to look at above and share your views would help.
Excellent suggestions! I am doing some rework on a number of pages and I will put the "Spare Parts Math" post on the list.
Thanks for the recommendations.
mathscinotes
I would like the formula in excel using Poisson distribution that calculates what my operational availability is based on a confidence level and defined MTBF, number of spares (which I may vary to increase/decrease availability), hours of operation, and quantity of item.
This is a good post. Some clarifications please.
To mathscinotes:
Does this analysis assume that the device is operational continuously such as a traffic light? If we do replenishments quarterly, is the number of spares 4 times what these calculations predict or are they based on 1 year? How would this be modified if the device was operational for 1000 hours per year? Thank you
I stumbled upon your blog after being asked to do some spares calculations for a project I'm working on. I just wanted to say thanks for this post and for including the sample Excel spreadsheets. Having never done a sparing analysis, this information was invaluable and saved me a ton of time. I'm hoping that you'll do a follow up to this post with the repairable parts calculations added in!
Btw, I've thoroughly enjoyed reading your other posts and have added your website to my list of Feedly blog feeds. Thanks again!
Thank you for the nice comment. If you see anything I can do to improve my work or if there is a related topic you would like to see written about, please send me a note.
mark
This is very good post and very useful.
Can I get clarity if I can use your formula to calculate the spare quantity with only having below information.
Total quantity in production :250
MTBF :1,240,020 hours
Replacement time :5 days
Operation Support for - 2 Years
Availability Requirement : 99.99%
Many Thanks in Advance
Thomas
mathscinotes,
Amazing post, one of the most clearly written I've ever seen. Thank you for sharing. As for the utilization, one thing is to read something, another is to use it... will try the tools provided and see if I get meaningful results. Will post again after trying it.
Hi Mark, finally got the information to ask the question.
Company XYZ has been selling and repairing Components C1, C2, C3... C70 for panel P for the last 10 years.
Owners may have P1, P2... Pm panels at their location.
XYZ kept good records of every Component ever sold or repaired.
Owners do not overstock and do not send good parts to repair, so: number of component sales + repairs represent more or less number of failures for each component.
XYZ can replace a failed component within 24 hours or repair it within 240 hours. Experience shows that when a component fails and is replaced with a spare, the spare has a service life of at approximately 36 months, XYZ offers a 24 month warranty on sales and repairs.
With the sales and repair information gathered during the 10 years for each component C1, C2, C3... C7o, XYZ plotted the histogram of the sorted Frequency of failure (F1 > F2 > F3 >...>F70) for each of the C1, C2, C3... C70 components.with X = C1, C2, C3.... C70 and Y = F1> F2 > F3>... >F70
The Histogram of the frequencies as a function of each component, shows a typical exponential distribution where the frequency of failure F1 for C1 is the highest, F2 for C2 is the 2nd highest etc...
Considering that the component C failures within a panel P, and between different panels P are independent. I.e. a failure of component C1 in panel P1 is independent of any other component failure in P1, and any failure in P1 is independent of a failure in P2, etc.
1. The owner wants to know what is the probability of NOT having a certain component when it fails, if the first n components with the highest failure rates are on site.
E.g. For n = 5, if owner has in stock components C1, C2, C3, C4 and C5 which have the F1>F2>F3>F4>F5 failure rates, what is the probability of needing component C6?
2. Depending on the number of panels P, how many spares does the owner need to have in stock to ensure a 99% probability of having the required spare on site when it fails?
3. Can XYZ say that µ = (F1+F2... +F70) failures/70 components *10 years is the average number of component failures per panel per year?
Thank you in advance for your insight.
Best.
a.
mathscinotes,
As others have said, great post. I recently stumbled across this site when trying to perform spares analysis, and have found it very useful. Thank you.
I have one additional question and I am trying to understand how to tweak your model to answer it. I need to determine the quantity of a lifetime buy for an end-of-life (EOL) component. I have some number of units in the field, a known MTBF, and a desired time for the units to be in service. Parts are not repairable. Replenishment interval = infinity? How many spares should I buy?
I appreciate any help you could provide.
Very best,
Mike
Just come across this article and find it very useful. However I have a question, if spare parts are non-repairable, how to treat the replenishment time ?
Your solution assumes that one knows the true MTBF, but more likely one knows only the operating hours and number of failures. You can calculate an MTBF confidence interval based on that, but what MTBF do you use for the inverse Poisson calculation?
Thank you so much for this blog post; it's been very informative!
In spareexampleinexcel2, DataTableBased tab, cell F2 ... why is that cell there, and why is 0.95 hard coded in, instead of using variable _CL?
Likewise, may I suggest using NORM.INV(_CL,0,1) instead of 1.645 in the Approximate Spare Calculation? That way, the approximation will match the user's intended CL.
The unfortunate, but perhaps unavoidable drawback is that while N*lambda*Tr must >10 to use the normal approximation, it must also be <20 or <100 depending on CL, before the two calculations appear to diverge. I quickly tested with 60 and 95% CL
On DataTableBased tab again, I struggle to understand the {=TABLE(,E3)} formula. When I CTRL-SHIFT-ENTER on your other array formulas (in your file or otherwise), they still work, but not this one. Maybe this has to do with the fact that I can't find any info on the TABLE function.
That's it for now. Thanks again.
Pingback: Reliability, MTBF, Sparing – natewesterveltblog
what a nice work to recommend safety stock.
can you guys share latest data sheet to me.
What are the units of measurement for the calculations used( In Excel) ?
What is the unit for MTBF, Hrs or Days?and why did we multiply by 24 ?
Hi, its so good to look this Page. I got some idea to analyses the failure and to plan the spare. please share some sample excel spread sheet.
By Vasanth
Wow. Nice post and even more impressive is your continuous updating in response to questions. To say I am impressed is an understatement.
This is almost perfect for what I am seeking. I have a case where assemblies are repairable and I have Mean-Time-To-Repair (MTTR) from many years of failure/repair history.
How might I alter the model if one assumption was modified as follows:
from: "The products fail and cannot be repaired"
to : "The products fail; however, y% are repaired in x days and returned to the sapres pool"?
(100-y)% are not repairable and are either scrapped or sent for overhaul.
Thank you so much for your time and expertise.
/SamCal/
According to your example, suppose I have only one part deployed, n=1, keeping all other data unchanged, the exact POISSON calculation comes out "#N/A". Could you please tell me how to fix this, thanks.
Thanks so much MathSciNotes... from a fellow Electrical and Computer Nerd (hehe)!!
I too have an EOL (end of life) part which I am interested in calculating the number of spares to use for a set period of time (i.e. until we plan to do a HW upgrade).
I would think that the number of spares needed should take the date at which the HW will be upgraded into consideration.
In the event that a part needed to be replaced and I am out of spares I used the replenishment time (Tr) as the amount of time it would take me to provide the customer with a replacement (in this case a new server due to a failed sub-component). I believe that this is still accurate but it's been a while since I've been in statistics class ?
For those of you asking about the 2nd to last row with a "#N/A" error in it, the equation has an error in it. To fix it just drag the 3 cells above it down and it will correct the equation.
Hello,
Thanks for the useful information listed herein.
I have a case where MTTR has been calculated for an igniter using the normal distribution with a value of 1339.6 HRS based on historical scrapped data.
Igniter Quantity Per Assembly is 06 and total assemblies available is 60. I have extracted the Actual Accumulated Time for all installed igniters, out of the 360 installed, 117 exceeded the MTTF calculated.
If I am going to use the excel sheet provided to calculate the NO of Spares with R = 365, what should be the N value ? Should I include all the 360 igniters or the 117 exceeding the MTTF value?