how to use excel for Risk Based Inspection?
We will figure out inspection plan using data in the previous article. First we will find optimum inspection date base on fixed interval and RBI interval using simple excel spreadsheet. Use excel function “ + ; MAX”.
- next inspection – fixed = Last inspection date + fixed inspection interval ( 5 years)
- next inspection – RBI = Last inspection date + RBI inspection interval
- next inspection date = Maximum (optimum) value between next inspection – fixed and next – inspection RBI. We extend inspection interval using Risk Based Inspection study.
Second, we will find inspection cost using excel function: Short / IF and PIVOT Table.
- Mechanical engineer, please be creative! Group equipment type using unique equipment number. Vessel equipment number always begin with “V” letter and Heat Exchanger equipment number always begin with “HE” letter. Do you see it?
- For small quantity of equipment and consist only 2 equipment type, it much easier we use “short” function. But when we have thousand equipment data with hundred equipment type, it will not work to use “short” function. We need more smart excel function like “IF”. At this stage, I put all Heat Exchanger equipment on top rows.
- Now we can add one column at right side to add inspection cost data base on equipment type.
Next step is figure out inspection cost spending based on year & month using “pivot table”. We will compare cost spending between normal fixed interval approaches and using optimum / RBI approach.
- We need to add column to represent “year” and “month”
- Use “pivot table”
Last step is to create summary of inspection cost spending. Based on fixed interval approach, company will spend $ 2,500 in year 2022 and additional $ 2,500 on 2027 or total inspection cost is $ 5,000 by year 2027.
But if we use Risk Based Inspection approach, company will spend $ 0 on 2022 and spend $ 2,500 on year 2024 – 2027. As per 2027 the inspection cost will be reduced up to 50%!
summary of inspection cost spending using fixed interval
summary of inspection cost spending using Risk Based Inspection
Below we can see quantity of vessel to be inspected at each oil field using fixed interval compare to optimum inspection considering RBI Study.
Considering Risk Based Inspection study and use simple excel calculation, we can create cost saving for inspection cost. If You can propose this 50% lower inspection cost to your supervisor, you will get credit to your career.
Bear in mind, this example is only for 20 equipment. In actual, Oil and Gas Company may have 2000 or 3000 equipment, therefore the cost saving will be much higher.