SREMS: System Reliability Using Monte Carlo Simulation with VBA and Excel

Abstract

This paper shows how it is possible to apply Visual Basic for Applications (VBA) and Excel to study complex systems reliability via Monte Carlo simulation. A series of VBA subroutines are added-up to form a program called SREMS, which runs under Excel spreadsheet. SREMS can be applied to estimate reliability in a huge range of different complex systems. It is assumed that both the structure of the system and the theoretical distribution of failure times for each component are known. The RND function and the inverse transformation method are then used to generate pseudo-random numbers from these theoretical distributions, therefore simulating failure times for each component. Next, the logical structure of the system is used to determine system failure times. Apart from giving estimates for the system reliability together with associated confidence intervals, SREMS is also able to calculate descriptive statistics about the system failure times. In this article, the reliability of two complex systems are estimated: the first one combines blocks in series, parallel, stand-by, and k-out-of-n redundancies; the second one is a system that cannot be decomposed in such basic structures, and must be analyzed using the concept of “minimal paths.” In both cases, the quality of the estimation is discussed: for the first system, real reliability values are compared with estimated ones. For the second system, estimated values obtained with a professional simulator are compared with those obtained with SREMS. Both tests point out the goodness of this VBA simulator.

Keywords:

• System reliability
• Monte Carlo simulation
• Visual basic
• Excel

Acknowledgments