Spreadsheet Design, Verification And Validation, Use And .

7m ago
78 Views
0 Downloads
1.36 MB
25 Pages
Transcription

DFS/ORALaboratory Information BulletinNo. 4349Software VerificationPage 1 of 25Spreadsheet Design, Verification and Validation, Use andStorage of Single-User Workbook Files in the US FDALaboratoriesPart IIbyDennis Cantellops, San Juan DistrictIntroductionTwo Laboratory Information Bulletins (LIBs)* covering the design, testing, verification andvalidation of spreadsheets have been prepared. Part I covers the general principles of spreadsheetapplication to be employed by several users (multi-users). This document (Part II) covers thespreadsheets to be used by single-users. It includes the use and storage of single-user spreadsheetfiles, the design aspects of spreadsheets and the verification and validation documentation forreporting in a regulatory environment. The discussion pertains to applications of the MicrosoftExcel spreadsheet program.Due to the variability and complexity of sample analyses in FDA laboratories, analyticalspreadsheet applications are often modified or created in actual time, as an analyst performs theanalytical procedure.This paper relates to a single-user spreadsheet which is intended to be used by a single analyst,its creator, at one specific point in time for one time use depending of the type (single-usertemplate or module). The single user spreadsheet can take various forms, but in general it is asingle workbook file which is relatively simple because it is based on uncomplicated formulaconstruction and does not contain macros, color-coding, cells protection or instructionworksheets. These features are not needed when the spreadsheet is intended for personal use,since the developer knows how to identify the raw data, cell locations of formulas, data-entrycells and cell addresses.The single user spreadsheet usually follows one of two basic options:A) A single-user workbook file can be created from a pre-developed single user template,which can contain approved and protected FDA forms (e.g. forms 431, 431a, andnutrition sample transfer form) and worksheet examples to aid the analyst in undertakingthe sample analysis with the spreadsheet. The single user template is installed in a sharednetwork location for use by the analysts. After the single user template is opened, it issaved as a single-user workbook file in a designated folder on a personal drive for* Note: The Laboratory Information Bulletin is a tool for the rapid dissemination of laboratory methods (orinformation) which appear to work. It does not report completed scientific work. The user must assure himself / herselfby appropriate validation procedures that LIB methods and techniques are reliable and accurate for the intended use.Reference to any commercial materials, equipment, or process does not in any way constitute approval, endorsement,or recommendation by the US Food and Drug Administration.

DFS/ORALaboratory Information BulletinNo. 4349Software VerificationPage 2 of 25personal use. The individual analyst can perform data entries and modifications to handle rawand secondary data. This single-user workbook file will increase in file size because theanalyst will add the additional data including formula construction and additional formattingto complete the sample analyses.B) Another way to implement the single-user workbook file is to develop it from an emptyworkbook i.e. the analyst opens an empty workbook with unpopulated worksheets (oropens a previously used version from the local drive for use in the development of thenecessary worksheets). For this article we shall refer to this process as using a single usermodule. During the creation and data population (data-entry) stages of the single-userworkbook files, the analyst uses the same formatting practices as if it was a hand-writtenworksheet. The creator should perform in-process verification of any formulas by manualcalculations during the developmental stage of the workbook file. These verificationsshould be documented and the evidence of correct operation should be attached with thefuture workbook results, either in paper form or as a separate worksheet if the verificationis performed electronically using one of the referenced auditing tools. After completion,but prior to data use or approval, a second analyst verifies the calculations and dataintegrity of the printed sample worksheet using the visible data and the formulasdisplayed by the auditing tools. Therefore, the degree of the verification depends on themodule complexity.In either of the two options mentioned, whether the single user workbook is created from a singleuser template or a single user module, the workbook file must not be transferred between theanalysts. This is explained on section under “Managing Single-User Workbook Files”, page 14.In addition, a copy of the completed, verified and printed sample worksheet shall be maintainedfor documentation in the laboratory for future worksheet audits against the saved single userworkbook file.The Power Pak Utility-PUP v5.0 (1) mentioned in Part I (2), or the ABB SpreadsheetSpecification and Reporting Tool (3), can be used to audit the single-user workbook filetemplates during the testing and verification stages. It is important that effective and appropriateprinciples and procedures be applied to all stages of producing analytical spreadsheets. At thedesign stage, such principles include design standards, clarity of formulas, documentation, anduser-friendliness. Furthermore, when the basic principles of software engineering techniques areapplied to the construction of spreadsheet models, many errors are reduced or eliminated.Notes:1.2.Excel documents are called workbook files. Each workbook is made up of individual spreadsheets calledworksheets and sheets containing charts called chart sheets.In summary “spreadsheet”is only really used as a generic term whereas “single-user template”, is the readonly template, “single-user module”is the empty file ready for development and “single-user workbook”,is a saved analyst file (it can be created from either the template or a module).

Laboratory Information BulletinDFS/ORANo. 4349Software VerificationPage 3 of 25Below there is a table describing the differences between the Single-User Template1, Single-UserModules2, Single-User Workbooks3, and Multi-User application.Table 1 – Differences between analytical spreadsheets (Single-User Template1, Single-UserModules2, Single-User Workbooks3, and Multi-User)Spreadsheet AttributesSingle 1UserTemplateSingle 2UserModuleSingle 3UserWorkbookMultiuser3Created from empty spreadsheetDesigned for use by one analyst33Can be opened by more than one analyst3333Can be populated by more than one analyst3May contain complex formulas3May contain macros33May contain color coded data-entry333Should protect cells, whenever possible333May contain instruction worksheets333May contain protected forms (e.g., 431 and 431a)333During population, formulas checked by calculator3Second analyst verifies raw data entry3Second analyst verifies calculations integrity3Electronic workbook file is saved3Contains unprotected worksheets333May insert worksheets333Needs validation documentation3333Needs in-process verification of formulas during thepopulation stage3Should verify data-entry33Notes:1) The Single User Template1 and the Single User Module2 only exist prior to use. Once they are opened andsaved, they become a Single User Workbook3. The differences between single and multi-user spreadsheetsare summarized in Table 1 above.2) For single-user workbook files standard formulas (such as “Round”and statistical functions) need to beverified for accuracy by the creator and by the reviewer (second check analyst). For multi-user workbookfile applications the standard formulas (such as “Round”and statistical functions) need to be verified foraccuracy by the developer during the testing and validation of the application. After the validation thereviewer only needs to verify data-entry entered by the end user.

DFS/ORALaboratory Information BulletinNo. 4349Software VerificationPage 4 of 25Analytical Spreadsheets and Hand-Written Worksheets; a ComparisonWhen only hand-written paper worksheets (e.g. FDA forms 431, 431a and calculation of resultsworksheets) are used, a hand electronic calculator is used to calculate sample results. MicrosoftExcel single user workbook files can be created in a similar manner, as a simple reportingmethod, which includes an integrated calculator. For example, when using Excel to implement asimple calculation, the formula is entered into a cell, which will display the formula result. Whenusing the paper or electronic process, the creator should perform in-process verification offormulas using an electronic calculator (hand electronic calculator or Windows Accessorycalculator).Single-user workbook templates should be kept very simple. For example, they may involve theFDA form 431 and its general continuation form 431a for describing the sample. The templatemay also include other worksheets to aid the analyst in the population of raw data forms (fill-inthe-blank form) and development of secondary data forms (calculations and results) includingthe option to insert worksheets to create personal formatting capabilities. The equation editor(Microsoft Equation v3.0) can be used to describe the formulas in the analytical procedure. Wecan visualize the spreadsheet worksheet as an integrated electronic calculator in the backgroundof the worksheet.In normal use, calculations are performed in the background of the spreadsheet, due to formulasinserted in the cells and the Excel formulas will reference other cells in the workbook to displaythe correct result. To aid the user (and the verifier) in the visualization and traceability of suchformulas standard, Excel tools can give visual presentation (draws lines to the respective relativeactive cells) of the relationships between the cells that provide values to the formulas or the cellsthat depend on the formulas. Similar commands in the Auditing toolbar can locate the cells thatprovide data to the formula in the active cell and find the cells that depend on the value in theactive cell.It is important to mention that both systems (paper hand-written worksheets and analyticalspreadsheets) are developed in a similar manner according to the FDA Laboratory Manual ofQuality Policies for ORA Regulatory Laboratories, the Good Laboratory Practices Manual inconjunction with any FDA worksheet-training module. The analytical spreadsheet constructionmimics the paper hand-written worksheet. Labels (sample number, method reference, descriptionof formulas) and formulas are located in comparable places to where they are situated on thepaper hand-written worksheets. Therefore, when viewed on screen or printed the analyticalspreadsheet should look similar to the paper hand-written worksheet.Types of Single-User Workbook FilesThere are several ways to implement single-user workbooks:a. Exclusive use Single-user Template. The analyst opens a read-only template file(usually saved as an XLT file), which may include protected forms (e.g. FDA 431 andFDA 431a continuation sheet) and other forms verified by the creator of the single-

DFS/ORALaboratory Information BulletinNo. 4349Software VerificationPage 5 of 25user template and approved by the management for sample documentation. This readonly template file should be installed in a shared or network drive. Also, this templateis likely to contain unprotected forms to aid the analyst in the population and/ordevelopment of the analytical worksheets. Analysts can create raw data worksheets(fill-in-the-blank forms) and secondary data worksheets (calculation and results) for aparticular analysis such as a USP drug assays or for a vitamin determination. Thissingle-user template can vary in complexity depending on the number of testsrequired for completion of sample analysis. Once in use, this template would be savedwith a unique name and would become a single-user workbook.b. Portion of work (single-user module). The analyst creates a module for a specific typeof analysis. Creation may begin from a blank workbook, or from a workbook alreadypopulated with data from another source. Examples of single-user modules includeworkbook files with data on content uniformity for a drug or the calculations requiredfor various assays. Also, a single-user module can consist of one or a combination ofthe following:1) Raw data and secondary data worksheets including supporting documentation(system suitability results, instrument parameters, HPLC/GC chromatograms).2) Secondary data only combined with other forms (for example Microsoft Word orExcel fill-in-the-blanks forms). Once in use, this module would be saved with aunique name and would become a single-user workbook.c. Fill-in-the-blank form (with no formulas). In this case, the analyst creates a workbookfor a specific analysis: a fill-in-the-blank form with no formulas being used within theworkbook. Usually these forms are used to enter hand-written raw data such asstandard and sample weights, sample dilution factors and their aliquots and dilutions.The creator may use the workbook to simply create table headings and formatting.d. Fill-in-the-blank form with formulas. In this case, the analyst creates a workbook fora specific analysis: a fill-in-the-blank form with formulas being used in the populationof tables or associated worksheets. Usually these forms with formulas utilize the rawdata (standard and sample weights, sample dilution factors and their aliquots anddilutions) which were hand-written on the fill-in-the-blank form with no formulas andthen this form with formulas automatically calculates the results using standard Excelfunctions such as SUM, AVERAGE, STDEV etc.An example familiar to FDA staff of an “Exclusive use Single-user Template”is the spreadsheetwhich includes forms FDA 431 and 431a, along with other forms used to aid the analyst to createor modify raw data and secondary data wo