Developing a complex equipment availability model


New Member
Jul 19, 2004
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?

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back