Many of the engineers working in engineering consulting organizations know that a lot of in-house engineering calculation templates are utilized for equipment sizing, rating and other engineering applications.Many commercial software are also utilized in such organizations involving lease-and-license agreements with suppliers and developers of such software.
But what many engineers are not aware that if your organization is ISO 9001:2008 (Quality Management Systems-Requirements) compliant or is desirous to acquire an ISO 9001 certification then part of the compliance to ISO 9001 requires that the in-house developed software is validated. What does the term “validated” mean? Today’s blog entry talks about validation of in-house software.
Please note that commercial software, for example simulation software such as ASPEN PLUS, ASPEN HYSYS, HTRI, PIPESIM do not require validation by the end-user. The validation for such software is the responsibility of the developers and licensors of such software and not the end-user. Many a times the technical term “Firmware” is used for such software which means it is a permanent software programmed into a read-only memory and cannot be altered or modified by anybody except the developers of the software.
The best definition I could find for validation is as follows:
“To establish the soundness, accuracy, or legitimacy of”
In context to this blog entry it means to establish the soundness, accuracy or legitimacy of the in-house engineering calculation templates. The question then arises that how to validate these calculation templates and let me assure you that it is a very simple task. Having said that, a task is a task and needs effort and time to accomplish.
What needs to be done? Since I personally have been involved in such validation procedures, I can provide a very simple explanation on how to proceed for validating in-house software.
MS-Excel is one of the simplest and most effective calculation software tool for in-house engineering calculations and most organizations subscribe to using Excel as a tool of choice for developing engineering calculations. Many of the engineers who subscribe to “Cheresources” for knowledge sharing and guidance are well versed with calculations done in excel spreadsheets.
A good calculation template will have a logical progression in steps for calculations, wherein the input data will be defined sequentially and the outputs will be generated in terms of numerical values for the desired parameter based on certain equations and formulas. However, the standard norm for excel calculation templates is to protect the output cells which contains formulas and only allow the input cells to remain unprotected. Essentially, the excel worksheet or workbook needs to be password protected to prevent inadvertent changes in the equations and formulas leading to erroneous results. When an excel worksheet is protected the formulas do not remain visible and the user does not know the formula used unless it is separately defined in the worksheet using for example Microsoft Equation 3.0. I personally follow the practice of defining the equations to ensure complete unambiguity. Even if the formula is visible, the source or reference of all the formulas is rarely mentioned in order to make the spreadsheet less complicated and cumbersome.
The spreadsheet or workbook calculation template as prepared above by any engineer in his organization and used to generate information for clients and 3rd parties is not validated. This has to be validated if ISO 9001 compliance has to be met. Let us proceed on how to validate this calculation template in a manner where it not only is ISO 9001 compliant but makes any new engineer very comfortable to use it without having doubts about its correctness and legitimacy.
In earlier days all engineering calculations were done on paper (engineering record sheets) using a blue or black pen. These engineering record sheets had a “Prepared”, “Checked” and “Approved” block on every sheet where the initials and / or signature of the preparer, checker and approver were to be printed by pen. To validate your in-house excel worksheet you need to do the same.
Use a live calculation from an existing or archived project. Write down all the inputs required for the calculations, the formulas used and their source and /or reference in the engineering record sheets. For complex arithmetic or algebraic calculations break it down into two or more steps (which is generally not required in excel spreadsheets). Attach hard paper copies of the source and / or references to the calculation record sheets as annexures. Highlight the formulas, equations or sentences / paragraphs in the annexures for sake of clarity and ease.
Once the person who has prepared the paper document is through with his task, this paper document will pass to the “Checker” for checking. After due diligent checking, corrections, if any, will be done by the engineer who has prepared the document. If corrections have been done, it will undergo a 2nd check by the checker. Once the checker clears the document, it will move to the approving authority. A diligent approving authority will do a careful scrutiny of the paper document, and on finding any errors or misses, will discuss with the preparer and the checker. If required, other specialist engineer(s) may be involved in the discussion to resolve any differences of opinion. Once a consensus is reached on the correctness of the document including an exact match of the calculated outputs with those in the excel workbook, the validation paper document becomes final, and the preparer, checker and approving authority will print their initials / signatures on the paper document. This paper document will have to be filed diligently by the department quality representative with complete traceability. Traceability means that it is a numbered (alpha-numeric or numeric) document as per the numbering system adopted in the department quality work procedure or work instructions belonging to the organization ISO 9001 QMS.
This completes the validation procedure.
In addition, a supplementary MS-Word document with may be prepared which provides step-wise English language instructions on data entry in the excel calculation template. This would be specifically useful when calculations are long and complex and require elucidation in simple English. This word document could also be attached as an annexure to the main calculation hand printed paper sheets.
What happens if I don’t validate? Very simply, any ISO 9001 auditor who audits the department quality management systems, has it in his rights, to give a “Non-Conformance Report” (NCR) for a not validated in-house software used for business purposes (projects), and demand a “Corrective Action Request” (CAR) report from the Department or Company Management Representative (MR). It means, that the CAR gets closed only when the validation document is prepared, is traceable, and easily retrievable.
Too many NCRs and CARs put a question mark on the effectiveness of the quality management systems of the organization and ultimately reflect badly on the organization.
In today’s blog entry I have tried to explain briefly how you can validate your in-house engineering calculation templates. I hope many of you will ask questions and initiate a debate on such an important activity.
