Spreadsheet Validation

Data Integrity App   Spreadsheet reformatting Design, development and reformatting of the spreadsheets should be performed with a view to facilitating validation. If spreadsheet reformatting is to be performed repeatedly in house, it is preferable to have predefined design guidelines User Acceptance Prototyping, Design/Build Live Operation, URS, FS, Single Spreadsheet Specification Document, Test Installation, OQ/PQ, Single Spreadsheet Qualification Document, IQ & Functional Testing Amended V model for Excel Spreadsheet validation and standards. When the work is contracted out then the design standards should be agreed in advance, typical guidelines to be followed include: • Workbooks should have an ‘intuitive’ lay-out with discrete/manageable sections, for example separate areas for data input and results • Where possible lookups should be used to “pull” data from other locations and worksheets rather than having repeated data input areas. • Macro use should be kep...

Data Integrity App   Live use of spreadsheet Prior to live operation, a number of procedures need to be in place, checks are incorporated in the spreadsheet qualification for the presence of a specification, backup/restore procedure and a spreadsheet operation SOP. Additionally the following procedures (which may be generic departmental SOPS) may need to be in place to ensure compliant operation: • System administration including system access/authorization • Error logging / resolution • Routine testing / Periodic Reviews / Re-qualification. • Change Control / Configuration Management. • Disaster Recovery / Business Continuity • Records Retention • Decommissioning / Data Migration procedures “Trust but Verify “  Ronald Reagan   Across the internet, there are millions of resources are available which provide information about Everything.   If you found all content under one roof then it will save your time, effort & you will more concentrated on ...

  Data Integrity App   Spreadsheet Validation Lifecycle The simplified software development lifecycle (SDLC) for validating spreadsheets. The principal features of this simplified SDLC, and key considerations for a cost-effective validation, are summarized below: User Acceptance The ‘final’ spreadsheet design is developed in conjunction with the end-user(s) via a ‘prototyping’ methodology. Excel is an ideal tool for rapid application development and prototyping. In many instances the user will already have developed a “working” version and may even be in a position to submit the final design without modification. This prototyping process allows flexibility, is amenable to change/improvement and ensures communication between the developer and the end user. When a final design has been agreed by the user, the spreadsheet must be locked down and further development stopped. Strong project management is required at this step to avoid the danger of ‘scope-creep’, the ...

Data Integrity App   Spreadsheet Inventory Review Inventory should be reviewed with the goal of minimizing the workload and simplifying the inventory. Often the spreadsheet inventory will include ‘populated’ spreadsheets (i.e. “.XLS” files) containing data and it is strongly recommended that these are NOT subjected to validation in their native format. In order to facilitate validation, it is preferable to install controlled and ‘empty’ spreadsheet templates (i.e. “.XLT” files); hence some reformatting / data cleaning will be necessary prior to validation. This reformatting step is an opportunity to review and rationalize the spreadsheets that are going to be subject to validation. An inventory review should be performed with the following questions in mind: • Are there any new requirements for existing spreadsheets? • Can any spreadsheets be combined to produce a single template? • Can spreadsheets be ‘genericised’ to allow future flexibility? The combination/gen...

Data Integrity App     Resources for spreadsheet Validation Many spreadsheet projects fail due to a lack of communication between the spreadsheet users and the people responsible for validation of the spreadsheets. It cannot be over emphasized how important it is to get full and complete buy in from the spreadsheet users. The key reasons for this are detailed below. • Often the spreadsheet users were the developers of the spreadsheet, and they have both intimate knowledge, and a sense of ownership of their creation. • The users know where and how the spreadsheets can be improved, and exactly where the deficiencies lie between the spreadsheet and the business process being undertaken. • The users are the people who defined the User Requirements, and an error when defining the requirements cascades throughout the lifecycle to result in a spreadsheet that may not be fit for purpose. The user/owners invariably have the highest burden to bear during a spreadsheet vali...

Data Integrity App   Project/Validation Preparation Project planning is essential for the successful execution of any validation exercise. This is particularly true when embarking on spreadsheet validation. It is likely that a large number of spreadsheets will be involved and therefore efficiency and repeatability are critical factors in the validation process; the key to the efficiency and minimizing the validation costs is to keep the process simple. This is achieved by introducing streamlined processes throughout the validation lifecycle, and the use of a generic yet adaptable set of documentation. It is essential that the validation process is documented and commonly this may require a formal Validation Plan to satisfy local procedures. Alternatively it may be possible to document your process in other project documents such as Standard Operating Procedures (SOP) or the Spreadsheet Specification. However, one of the biggest strengths of a Validation Plan (and s...

Data Integrity App Tips & Tricks for spreadsheet Validation   A good road map and some tips and tricks can help you on your way through the rough landscape of spreadsheet validation. With all your experience, regulations, and guidelines in mind, develop a spreadsheet that includes all necessary requirements. Think of the following aspects: Use of the spreadsheet must be restricted to the intended user. Place the template on a (server) location with access limited to selected users. Protect the spreadsheet with a password that allows users to open the spreadsheet as read-only. You want to protect the spreadsheet against any undesired changes, so lock all cells within the spreadsheet (except the cells in which the user needs to give input). This way, the user cannot change cells with formulas, constants, labels etc. Give the input cells a color to make the spreadsheet more clear and easier to understand fo...