Spreadsheet Design and Validation for the Multi-Ur Application for the Chemistry Laboratory
Part I
by
Dennis Cantellops, Evelyn Bonnin and Anne Reid
Southeastern Regional Laboratory储存区
Introduction
Two LIB’s covering design and validation of analytical spreadsheets have been prepared. This document covers the general principles of spreadsheet application to be employed by veral urs (multi-urs), Part I. Another document to be submitted, Part II concentrates on spreadsheets to be ud by only one ur (single-ur). The design aspects of spreadsheets and the verification and validation documentation for reporting in a regulatory environment are discusd. The discussion pertains to applications of the Microsoft Excel spreadsheet program.
For multi-urs, special care is required becau the spreadsheet must be “bulletproof” to minimize the
possibility of misu. The application of the spreadsheet must be clearly defined, which means providing additional formatting and instructional elements. The degree of extra effort depends on the experience level of the expected urs, but always, when multiple urs are involved, the spreadsheet requires stringent validation and its documentation. Documentation for the multi-ur spreadsheet provides evidence that formulas, macros, data integrity, accuracy, reliability, and operational procedures (such as instructions for ur) will perform as expected.
The ufulness of software (such as Power Pak Utility-PUP v5.0) that audits the workbook file and generates validation documentation will be discusd. In the production of both, the analyst (single-ur) and urs spreadsheets effective and appropriate principles and procedures must be applied to all stages of the analytical workbook files. At the design stage, such principles include design standards, clarity of formulas, documentation, and ur-friendliness.
Furthermore, when the basic principles of software engineering techniques are applied to the construction of spreadsheet models, many errors are reduced or eliminated.
Obrved Spreadsheet Application Problems
The following spreadsheet problems were discovered during an FDA inspection of a QC analytical laboratory in a pharmaceutical firm. The QC laboratory failed to follow current good manufacturing practice (cGMP) (1) and good laboratory practice (GLP) (2) regulations. Also, basic principles of multi-ur spreadsheet design, validation and documentation were not apparent. Spreadsheet files were not maintained and many were deleted. The SOPs for the spreadsheets were abnt and validation and documentation procedures were not in u. The spreadsheet problems can also be prent in FDA laboratories. Specific problems included the following:
•Nearly all of the spreadsheets in the laboratory showed rounding-off errors and none ud the Excel rounding function, when needed to avoid rounding-off discrepancies
between the original and check calculations by the cond analyst.
•Formulas in the analytical procedures did not follow the Excel equations.
•Conversion factors were not expresd in the analytical procedures.
•The formulas ud by the check analyst for manual calculations were not the same as tho ud in
the Excel spreadsheets.
•The pre-determined specifications or limits were not shown in the spreadsheets.
•Spreadsheets were not clearly documented. For example, the product declaration was not indicated, replaced by only a number in a cell.
• Units were expresd as numbers without descriptive labels such as mg/mL or mg/g.
•Sample weights were described as sample volume and areas as ratios.
•Spreadsheets did not contain provisions for curity and integrity of data.
•Spreadsheet applications were not protected from changes, meaning analysts could freely change labels and formulas.
•Regression analysis was calculated with the y and x axis inverted in the Excel formula, which generated erroneous slope and intercept results.
The analytical spreadsheet mistakes can be eliminated if the analyst (developer) ensures that each element in the spreadsheet application is uniquely and unambiguously defined.
As a minimum, a simple spreadsheet validation documentation should include the following items (3):
• A description of what the program does and proper instructions.
口干舌燥原因•U of color-coding for data-entry.
• A description of the mathematical formulas ud .
•An explanation of the relationship of the formulas in the analytical method to the Excel equations.
•Listings for any macro programs.
•Acceptance criteria, including product declarations and specifications or USP limits.
•Test sheets with anticipated and actual results, signed and reviewed, that have been verified by manual calculations.
•Security and password maintenance.
•Operating system ud, date of installation and version number.
•Documentation of the spreadsheet program, including any rvice packs or add-ins.
Some rvice packs such as the PUP v5.0 or the additions of macros may not be
reflected in the software program version designation but should be documented all
the same.
Good Laboratory Practice (GLP) Regulations
The u of spreadsheets to judge the quality of a product (4) is controlled by cGMP’s, GLP, regulations (1) (2) and ISO 17025 accreditation (5). The following are some of the requirements established by GLP regulations:
•Configuration Management. Quality assurance for managing software address all documentation associated with the system and is applied during all operational phas
of software u, including the development and maintenance phas. It includes
identification of changes, implementation of changes as reported, and revalidation of
权益法核算
formulas and workbook files at specified times or when the developer introduces a
new version of the spreadsheet. If modifications, up-grade or changes to the software
have been implemented, perform regression analysis to determine what portions of
the software that had not been changed might have been adverly affected by
changes elwhere in the software. In addition, conduct regression testing on tho
portions to determine if any new errors or problems were introduced.
•Written Standard Operating Procedures (SOPs). SOPs are defined at 21 CFR
58.81 (a) (2), as “A testing facility shall have standard operating procedures in
writing tting forth non-clinical laboratory study methods that management is
satisfied are adequate to insure the quality and integrity of the data generated in the
cour of a study. All deviations in a study from standard operating procedures shall
be authorized by the study director and shall be documented in the raw data.
Significant changes in established standard operating procedures shall be properly
authorized in writing by management.”
•Completeness of Data. Validated analytical applications need a performance-monitoring program to establish data integrity and completeness. Compliance Policy
Guide (CPG) 7132a.07 (21 CFR 211.68) defines the program for input/output (I/O)
checking. The I/O test involves inputting a t of data packages that have an
accurately known solution and checking this known solution against the result given
by the software application. In addition, all workbook files should be prerved,
controlled and maintained for future audits against the reviewed printed sample
reports. A backup-and-restore system should be in place to ensure data completeness
and curity. A backup-and-restore SOP provides detailed instructions as to how to recover the spreadsheet files after a system failure, designate the backup location, and define the schedule-and-restore methodology. The SOP should identify the person responsible for retaining backup records, including schedules or logs, the type and storage location of the media ud, system failures and recovery logs.
•Raw Data. Raw data is defined at 21 CFR 58.3 (k) (2), as “any laboratory worksheets, records, memoranda, notes, or exact copies thereof, that are the result of original obrvation and activities of a non-clinical laboratory study and are necessary for the reconstruction and evaluation of the report of that study.” In addition to hand written obrvations in ink on paper, “raw data may include photographs, microfilm, or microfiche copies, computer printouts, magnetic media, including dictated obrvations, and recorded data from automated instruments”. In the chemistry laboratory, the raw data from notebooks or paper worksheets can be entered manually into the computer spreadsheet program or can be directly collected (from balances, GC, LC, etc.) into the spreadsheet program, by means of an interface or a Laboratory Information Management System (LIMS). Under tho circumstances, the spreadsheet becomes raw data. A worksheet generated by a spreadsheet program can be designed to be ud as a fill-in-the-blanks worksheet for raw data. After the raw data
is entered manually into the paper worksheet (fill-in-the-blanks worksheet), the worksheet becomes original data, which is the result of original obrvations. The raw data worksheet can not be remade if errors are found. Errors cannot be erad or overwritten; a line must be drawn through an incorrect entry and the correct figure or word written nearby and initiated. Also, significant data must not be discarded without explanation. Discarded analytical data must be crosd out, initiated, and the reason for discarding the data explained. An example of the raw data worksheet will be prented in Part II of this LIB.
•Secondary Data. The condary data worksheet is ud for the calculations and results, which utilize the data from the raw data worksheet (standard and sample weights and dilutions, sample dilution factor, etc.) previously developed and printed, which included the original obrvations. It can be remade if errors are found. To verify the condary data spreadsheet (calculation and results sheet), data in the spreadsheet are checked against the printed and ud raw data sheet (fill-in-the-blanks Excel Worksheet in which the blanks had been manually written). The results of the verification must be documented, and the check analyst must sign for approval. A laboratory notebook in which raw data is entered manually also becomes original data and copies can be attached to the spreadsheet printout. Figures 2 and 3 are examples of the condary data worksheets.
The workbook files containing the raw data (fill-in-the-blank form) and condary data worksheets should be prerved, controlled, and maintained for future audits of擦干眼泪
the electronic file. In addition a copy of the completed sample printout should include the name and “saved” date for the workbook file, and the software name, version, and upgrade designations.
•Security. 21 CFR Part 11; Electronic Records; Electronic Signatures (6) “Sec. 11.10 Controls for clod systems. Urs who u clod systems to create, modify, maintain, or transmit electronic records shall employ procedures and controls designed to ensure the authenticity, integrity, and, when appropriate, the confidentiality of electronic records. Such measures ensure that the signer cannot readily repudiate the signed record as not genuine. Such procedures and controls shall include the following: (a) Validation of systems to ensure accuracy, reliability, consistent intended performance, and the ability to discern invalid or altered records.
(b) The ability to generate accurate and complete copies of records in both human之字形
readable and electronic form suitable for inspection, review, and copying by the agency. Individuals should contact the agency if there are any questions regarding the ability of the agency to perform such review and copying of the electronic records.”
Other part 11 requirements may be implemented as well (6).
For example, the access control to the computer and the Excel workbook files and audit trails (Section 21 CFR 11.10(e)) should be implemented as follows:
(a) For computer access control, the Agency established curity in accordance with
Federal mandates (Computer Security Act of 1987, Computer Fraud Abu Act of 1986, and others, e http: //v/oirm/itcurity/uful.htm).
学习理论知识
The Agency is required to provide “adequate curity” to manage potential risk to its automated information. The Southeast Region Information Technology Services (SRITS) manages the computer curity program. The Information System Security Officer (ISSO) rves as the Agency focal point to direct and overe the curity program. The ISSO will develop policies, procedures, and guidance establishing, implementing, maintaining and overeing requirements for the Agency’s information technology (IT) curity program. For example, the agency and the SRITS have established access controls at various levels. The ORA's wide area network (WAN) is a clod system protected by two internal firewalls. This clod system is contained within another clod system that consists of the HHS WAN (the departmental level). The ORA's WAN has a cured gat
eway to the Internet through the various firewalls. At the ORA's WAN level, all computers (laptops as well as desktops) have been migrated to Microsoft's Windows 2000 Professional operating system (OS). This new OS has improved curity features. The most important ones are:
9Authenticated Logon - Urs now must enter a urname / password combination in order to access the files and resources in any computer. If a ur does not have
an account in the ORA network domain, then the ur would not be able to access any files or computer resources.
9File Level Security - All hard disks will be using NTFS (New Technology File System from Microsoft) as the file system (not the common FAT file system).
This provides additional curity features for file access over previous file
systems such as FAT (File Allocation Table) or FAT32. Files stored in local hard drives can be cured by using access lists. Administrators can designate who can access a specific file and what level of access the ur can have (read, write,
change, delete).
9Audit Trails - NTFS and Windows 2000 provides features that allow the system to log the u of a file or folder.
9File Encryption - Urs can encrypt the data files to protect them from un-authorized access. The highest encryption algorithm is being implemented (128-bit).
In addition, data files stored in network drives are also protected from unauthorized access. File rvers u NTFS as the file system and data is backup every night.
(b) For access control to Excel workbook files applications, the developer should implement adequate curity. For example, Excel has veral levels of protection that can be applied to a workbook file. The highest level of protection is t on the file level. A password can be t in order to open, access and save a workbook. After the workbook file is open, curity options can be t inside the application file. There are three levels of curity: workbook, worksheet, and object. Protecting the workbook prevents the ur from inrting new worksheets. Protecting the worksheet prevents the ur from changing the contents of a worksheet, such as formulas. Protecting the object prevents the ur from deleting, moving or resizing objects.
In addition, spreadsheet applications should be protected with the safeguards identified in 21 CFR P
art 11. The include validation with its documentation; curity, and audit trails (Section 21 CFR 11.10(e)). The curity, audit trails, and true electronic signatures can be implemented by software and rvices such as what is available from the company Wimmer Systems (). For example, Wimmer Systems can take our verified Excel application spreadsheet,add the compliance package, and when finished,install the application and provide training.乌衣巷古诗
Also, during an in-hou audit, the laboratory must provide the validation documentation and copies of the applications in both hard copy and electronic form as per 21 CFR 11.10 (a) and (b), if the copies are requested by the auditor. Therefore, Part 11 requires that laboratories be able to give the auditor electronic copies and hard
暴什么天物
