I am updating an existing Reliability and Availability Model (commonly referred to as RAM) in EXCEL that is used to estimate the percentage of time a facility is operational. For instance a plant with 97% availability is operational 97% of the time, and down due to some kind of failure for 3% of the time. I have done similar in the past using proprietary software but never something of this complexity in EXCEL, and my current employer has no software for this purpose as it is not a core function of our work.
The RAM uses failure data from a number of sources and basic probability to combine everything together. For instance if three components are connected in series then the availability of that stream is the product of the their availabilities. If they are in parallel you calculate accordingly, and ultimately the whole plant is made up of a bunch of subsystems that you can combine together mathematically. All easy enough, it just takes time and care and the best data you can get hold of (this is the main challenge of the project).
For the most part this is pretty straightforward, but to get meaningful results some more complexity needs to be added. My predecessor did this but I'm not entirely happy that his methods are all that representative of the actual situation. I haven't been able to come up with anything better though. I'm not confident it can be done without @RISK or some other funky add-on that I don't have (or even with them), but hope to be proven wrong
Essentially my problem lies in the fact that RAM analysis is, when done properly, performed using monte carlo simulation over a large number of discrete time steps. Apart from the simulation of faults, this also lets you vary things over time more effectively, such as failure rates and available spare capacity within the plant. The latter is my main issue, I need to accurately account for the change in spare production capacity over time for certain components. Without that I don't have a good representative model. As it stands now the model estimates some capacity factors, which essentially say something like this
typically this system has 120% overcapacity, so we'll multiply the availability by 1.2 and hey presto..
This doesn't work. Just because a subsystem has capacity to produce more than its required amount, doesn't mean if a component fails production isn't lost, or that it is possible to make up for lost production afterwards. I also can't account for the time taken for spare capacity in other units to be activated when one goes down - in some cases this could be weeks or months as I am talking about subsea oil production equipment. To do it properly (and i worry that if I don't this just becomes a model which will be tweaked with factors to give the answers we want) really requires something more involved than just a bunch of probabilities multiplied together to give an overall picture.
The question is, is there capability to run a monte carlo type simulation for a large number of interelated components in EXCEL? Basically it would need to evaluate against probability of failure for say 100 components combined in different subsystems, and determine how that changes plant capacity for the duration it is out (failure rates, time to repair and mobilisation times to be looked up from tables). At the time of a failure it will need to calculate what spare capacity is available to cover the loss, and if there is a substantial time lag to account for it. The average capacity over time as a percentage of target is the plant availability.
I have looked at the help on monte carlo simulations and done a search on all related threads in this forum, nothing comes close to what I need. Rereading what I have just written I think it is a lost cause, but worth asking just in case! Has anyone attempted something of this nature or do I need to live with what I've got or convince my employer to cough up for some applicable software?
The RAM uses failure data from a number of sources and basic probability to combine everything together. For instance if three components are connected in series then the availability of that stream is the product of the their availabilities. If they are in parallel you calculate accordingly, and ultimately the whole plant is made up of a bunch of subsystems that you can combine together mathematically. All easy enough, it just takes time and care and the best data you can get hold of (this is the main challenge of the project).
For the most part this is pretty straightforward, but to get meaningful results some more complexity needs to be added. My predecessor did this but I'm not entirely happy that his methods are all that representative of the actual situation. I haven't been able to come up with anything better though. I'm not confident it can be done without @RISK or some other funky add-on that I don't have (or even with them), but hope to be proven wrong
Essentially my problem lies in the fact that RAM analysis is, when done properly, performed using monte carlo simulation over a large number of discrete time steps. Apart from the simulation of faults, this also lets you vary things over time more effectively, such as failure rates and available spare capacity within the plant. The latter is my main issue, I need to accurately account for the change in spare production capacity over time for certain components. Without that I don't have a good representative model. As it stands now the model estimates some capacity factors, which essentially say something like this
typically this system has 120% overcapacity, so we'll multiply the availability by 1.2 and hey presto..
This doesn't work. Just because a subsystem has capacity to produce more than its required amount, doesn't mean if a component fails production isn't lost, or that it is possible to make up for lost production afterwards. I also can't account for the time taken for spare capacity in other units to be activated when one goes down - in some cases this could be weeks or months as I am talking about subsea oil production equipment. To do it properly (and i worry that if I don't this just becomes a model which will be tweaked with factors to give the answers we want) really requires something more involved than just a bunch of probabilities multiplied together to give an overall picture.
The question is, is there capability to run a monte carlo type simulation for a large number of interelated components in EXCEL? Basically it would need to evaluate against probability of failure for say 100 components combined in different subsystems, and determine how that changes plant capacity for the duration it is out (failure rates, time to repair and mobilisation times to be looked up from tables). At the time of a failure it will need to calculate what spare capacity is available to cover the loss, and if there is a substantial time lag to account for it. The average capacity over time as a percentage of target is the plant availability.
I have looked at the help on monte carlo simulations and done a search on all related threads in this forum, nothing comes close to what I need. Rereading what I have just written I think it is a lost cause, but worth asking just in case! Has anyone attempted something of this nature or do I need to live with what I've got or convince my employer to cough up for some applicable software?
