Spreadsheet calculations are popular in all kinds of businesses. Macro programs are used to add functionality to standard programs and to automated processes. Results generated by Macro and Spreadsheet (M&S) calculations are frequently used to make business decisions. M&S are also frequently used in regulated environments, for example batch releases in pharmaceutical manufacturing can be based on M&S calculations.
Spreadsheet programs were not designed for the regulated environment and without preventive actions the risk that they will generate errors is relatively high. Therefore Spreadsheet applications are amongst the most important targets for risk assessment and control.
Typical problems with spreadsheet applications are :
- Anybody can access the programs : Spreadsheet software is installed on most personal computers and servers. A lot of the time they are accessible by all users. Users can use such programs without any documentation or control.
- When Spreadsheets are loaded onto PCs without individual user passwords to access the PC, all users can change the Macro or Spreadsheet.
- Anybody can develop Spreadsheet calculations and other tasks. Although Spreadsheet calculations are considered to be software, developing Spreadsheet calculations does not require any software developing skills. Formulae can be added to a Spreadsheet using simple menu functions.
- Anybody can change calculations and Formulae and other functions of Spreadsheets are easy to change. A lot of the time such changes are not documented and no testing is done after the change.
- Development and use not validated : There is little awareness that Spreadsheet calculations should be validated. This is especially true for ‘ad hoc’ calculations where somebody defines a formula and performs a calculation for a specific one-time task.
- Developers/Users don’t see a need for documentation. Frequently users of Spreadsheet calculations are also developers. Sometimes the developer is the only user. Requirements and codes are obvious to the user, so the question is why to document the obvious.
- Many different environments, PC, Operating Systems, Versions of Excel. Spreadsheets are developed on a specific computer configuration with a specific operating system and Spreadsheet version number. They are distributed and used on computers with other configurations and different Spreadsheet versions.
- Different versions are used located in different locations of a computer system. This can be a local PC, a server, or in different folders within a PC.
- Typically do not comply with regulations: Currently Spreadsheet programs do not comply with all regulations. The most difficult regulation to comply with is FDA’s 21 CFR Part 11.
High-Level Risk Assessment :
For high-level risk assessment of Macros and Spreadsheet applications first of all applications are categorized as high, medium or low risk. The result of this exercise is used to decide if and to what extent Part 11 requirements will be implemented or if it is worth developing and implementing a detailed risk management plan for a specific application. Proposals for such assessments are made by operations and approved by the risk management team.
The resulting risk level information is used for considerations such as:
- How extensively do we test the Spreadsheet application or Macro? For example, high risk applications will be tested under normal and stress conditions, and over the full data
- How strictly should access to the application be controlled? High risk applications should be stored and only accessible through password protected servers.
- What requirements of Part 11 should be implemented in the computer systems? For example, for high risk applications the Excel trackability function should be activated. However this is still not in full compliance with Part 11. Therefore this should be documented as a deviation from the Part 11 requirement and a plan should be developed to solve the problem.
- What needs to be part of the report? For high risk applications the operator name, date/time and the location where the file was loaded from should be printed in the report.
Factors contributing to high risk applications are:
- Used in regulated applications
- Used in production environments
- Used in production quality control environment. Results are used as a criterion to release batches.
- Probability of detecting and correcting errors is low or zero.
- Product quality problems may permanently impact people’s health.
- Extensive use of VBA scripts.
Factors contributing to low risk systems are:
- Not used in regulated applications
- Used in early product development stage
- Product quality problems may not have any impact on people’s health.
- Probability of detecting and correcting errors is high
- Use of only simple standard routine functions.
The basic questions to be answered for this assessment are:
- What is the impact of the Macro and Spreadsheet on a company’s business and product quality and how big is the problem if data generated by the application are wrong or lost?
- What is the likelihood that the application generates wrong data or looses data?
Detailed Risk Management:
Detailed risk management should cover all lifecycle phases. For commercial systems risk factors should be identified for:
- Setting system requirement specifications
- Definition of design specifications
- Development of codes
- Code review and testing
- Applications testing
- Installation
- Testing
- On-going use
- Changes
- Retirement
A sample Checklists help to identify risks and activities to control the risk is below.
Risk mitigation and on-going control should follow the recommendations made in the article under section Risk Mitigation and On-going monitoring review and control :
How to create Risk Management Master Plan For FDA Regulated Companies
Below Forms can be used in Evaluation, assessment & Mitigation:
Related Articles :
How to do Risk Management of Computer Systems Used for FDA Compliance
How to Do Risk Assessment of IT Networks for FDA compliance
Risk management of existing(Legacy) systems for FDA Compliance