Spreadsheet Validation for Your Lab!

GOOD VALIDATION PRACTICE (cGVP) LABORATORY, METHOD AND ANALYTICAL VALIDATION
1

Spreadsheet Validation

Avoid or Validate

Spreadsheets are deployed across the pharmaceutical industry. Spreadsheets are intuitive, flexible, and transportable making them the medium of choice to perform tasks such as:

  • routine analytical or formulation calculations
  • instrument and equipment asset management -
    logs, calibrations and check events
  • unique ID generation for documents such as SOPs,
    methods, investigations…
  • training records
  • tabular and graphical reporting

The very usefulness of spreadsheets can produce a compliance nightmare when un-validated spreadsheets are utilized to generate regulated reports or make scientific decisions.

Growing companies often struggle with two difficult options:

  1. Remove spreadsheets from the compliance sphere. This is more difficult than it sounds since, the use of spreadsheets must be replaced by some validated process. The replacement process can be a manual operating procedure which is often cumbersome and less efficient. It is also possible to replace spreadsheets with targeted IT solutions. The IT solutions however are never as flexible as a spreadsheet and must be validated (no small task).

  2. Validate all spreadsheets which may be used for regulated reports or decisions. Companies often find upwards of 200 spreadsheets which need to be validated and maintained. A daunting endeavor.

Option 1 above requires a significant budget to purchase one or more IT solutions (often LIMS). In addition, the company processes must be modified to fit the purchased solution, and employees must be trained to operate in the new environment. A truism regarding IT solutions like LIMS is worth repeating: “IT solutions are a lot like concrete, they can be configured in many shapes but once deployed are almost impossible to change. The point is that unlike spreadsheets which can be updated as your company grows and changes, IT solutions are often antiquated even before they are deployed.

Option 2 requires validating a large suite of spreadsheets. In addition, the spreadsheets must be maintained in a validated state and must be capable of rapid updates to support change within your company. Attacking this problem one spreadsheet at a time is cost and time prohibitive. To be successful, a company wide spreadsheet validation solution is required.

Solution Overview

The diagram below illustrates a typical deployment for compliant spreadsheets. Templates are maintained in a controlled state. To run the spreadsheet process, a copy of the template is made which we will refer to as the “Document”.

Templates are the basic building blocks of a managed spreadsheet system. Templates are managed within a controlled, secure environment which provides lifecycle/status control and supports spreadsheet versioning. The diagram indicates that templates progress through a defined lifecycle. Templates are available for use in either “Active” or “Verified” status and are placed in “Inactive” status when superseded or when the work process is no longer required.

Template verification is an optional step where template calculations and automation are documented and tested to produce a “Verified” template. By electing to verify a template, a company avoids lengthy review of individual documents. The decision on whether to verify a template is mainly determined by the frequency of template use and spreadsheet complexity. In a compliant environment, verification of spreadsheet content is not optional and must be done at either up front on the template or at the review stage of each document created from the template.

Documents are copies of templates created to execute a process such as to test a group of samples. Like templates, documents are managed within a controlled, secure environment which provides lifecycle/status control. In addition, changes to documents must be recorded in an audit trail and author/reviewer/approver signatures must be applied.

##Selecting a Solution

There are many potential solutions which can be harnessed to provide the controlled environments required for templates and documents. Three general categories are provided below:

  1. From Scratch - combination of operating procedures and access controlled file shares

  2. Tool Kit - file share software i.e.: SharePoint, Dropbox…

  3. Vendor Solution - targeted spreadsheet compliance solution

The “Vendor Solution” is highly recommended unless your company has a wealth of experience in this area and the time/ energy to invest. The problem with in-house development, even with an advanced tool kit such as SharePoint is the flexibility inherent in spreadsheets. The principles of file security, control, and 21 CFR Part 11 are well known and straight forward to implement, but taming and bringing into compliance the wide-ranging functionality of spreadsheets takes experience. The goal is to bring your spreadsheets into compliance, not to start an open-ended research project.

Validation

Your spreadsheet validation should be separated into two components. The first validation component is the environment in which Templates and Documents are secured and maintained. The second component is individual validation packages for each spreadsheet. It is important to separate these components rather than execute a single validation exercise because new spreadsheets and revisions to existing spreadsheets must be expected as your business grows. Handling spreadsheets as modular add-ins will make maintenance of your solution easier.

Validating the Spreadsheet Environment

The required industry standard validation documentation will be the same regardless of the solution you picked for your spreadsheet environment. If you picked the Vendor Solution, your task is simplified by the documentation set provided by the vendor and denoted by an asterisk.

  • Validation Master Plan
  • User Requirements
  • High Level Business Requirements
  • System Requirements Specification *
  • High Level Solution Requirements
  • Design Qualification
  • Comparison of the User Requirements with the System Requirements
  • Functional Requirements Specification *
  • How the solution implements the system requirements
  • Design Specification *
  • Technical system definition
  • Installation Qualification *
  • Documentation of successful installation
  • Operational Qualification *
  • Report summarizing the testing executed to prove the solution functionality works as expected
  • Risk Assessment
  • Consider how the solution fits into the business environment and potential risks which may arise
  • Performance Qualification
  • Report summarizing testing performed which proves the solution operates as expected in the target environment
  • Summary Report and Release Statement
  • Report summarizing the validation process and a statement of release for production use

Validating Individual Spreadsheets

Spreadsheet validation requirements are defined by GAMP 5 “A Risk-Based Approach to Compliant GxP Computerized Systems”. GAMP categorizes spreadsheets into 5 categories depending on impact and complexity. The documentation required for each category increases as the impact and/or complexity increases.

To avoid the complexity of managing different documentation sets across your portfolio, it is possible to assign the highest category of GAMP risk across all templates. Categorizing individual templates can avoid full documentations sets for simpler templates BUT since simple templates are easy to document the savings are not large and compensated by a single well-defined process.

GMP Category 5 documentation includes:

  • Validation Plan
  • A single plan should be created to cover all spreadsheets both new and revised. The plan should also define the template lifecycle.
  • User/Functional/Design Requirements
  • Combined document delineating high level requirements, functionality employed to implement the requirements and design considerations. The document should include calculations, alert criteria, number of decimals displayed, automation deployed, data validation options, and significant format.
  • Traceability
  • Between requirements and testing
  • Test Plan
  • Defined testing and predetermined acceptance criteria
  • Test Report
  • Documentation of testing performed
  • Validation Report
  • Summarize the validation exercise. Include a summarization of previous template versions and testing performed on each version.

Spreadsheet Revisions

The template revision process should be defined in the “Validation Plan” including a risk based strategy for regression testing. A typical documentation set is defined below:

  • Required Changes and Risk Assessment
  • Define the required changes and assess against criteria defined in the “Validation Plan”
  • Test Plan
  • Defined testing and predetermined acceptance criteria
  • Test Report
  • Documentation of testing performed
  • Traceability
  • Between requirements and testing
  • Validation Report
  • Summarize the validation exercise. Include a summarization of previous template versions and testing performed on each version.

If you’re interested in more information regarding Spreadsheet Validation, such as Spreadsheet Design for Laboratory Use, visit us here!

1 Like