Validating Excel-based Spreadsheets Why wouldn’t you validate Excuses, excuses Excuses, excuses Why would you validate

Validating Excel-based Spreadsheets Why wouldn’t you validate Excuses, excuses Excuses, excuses Why would you validate www.phwiki.com

Validating Excel-based Spreadsheets Why wouldn’t you validate Excuses, excuses Excuses, excuses Why would you validate

Di Rende, Susan, Features Editor has reference to this Academic Journal, PHwiki organized this Journal Validating Excel-based Spreadsheets Robert Ladyman File-Away Limited Why wouldn’t you validate We don’t validate Excel Spreadsheets because Excuses, excuses We only per as long as m one-off calculations Not true if it’s not “untitled” A well-known Monitoring Authority 100% QC check Clarification: must be validated if used as long as pivotal data h in addition to ling

PJ's College of Cosmetology- Muncie IN www.phwiki.com

This Particular University is Related to this Particular Journal

Excuses, excuses We don’t per as long as m any calculations, we just store data “We don’t per as long as m calculations, so we’ll store it in something specialized as long as calculations” You need to state what you do, not what you don’t do (you don’t store your data in Paintbrush). Excel is an active agent (more later) Why would you validate GxP in addition to its relatives Data Protection Laws in addition to who wants to keep doing 100% QC What type of ‘system’ is a spreadsheet Level 1 systems Off-the-shelf “Engines”: O/S, Oracle, Word, Excel itself (you don’t tend to validate them) Level 5 systems Bespoke Strictly validated Spreadsheets are programs+data in addition to level 5, NOT LEVEL 1 Applies to all spreadsheets, not just Excel-based ones

How do you validate Use the classic, st in addition to ard methods Our old friends URS – User Requirements Specification Version Control Testing Documentation (including the validation report) URS For the desired system: what you want not what you have Field-values (how many, + or -) Calculations (st in addition to ards) If you end up with Excel, make sure you know what version you might use

URS – Audit trails If your URS states that you need Audit Trails, Excel spreadsheets are probably the wrong tool Sarbanes-Oxley has resulted in more plug-in systems (e.g. Wimmer, RSME) of course, audit trails aren’t needed if you don’t store data Version control MD5 sums External to the file, best in addition to simplest tool What Why There is no such thing as ‘bit rot’ How Testing Test data Valid in addition to boundary checks Predict the results (plan) Evaluate the test results (validation report)

Documentation Outputs Test results Programming notes – how does it work internally (you’d do that as long as a program) Don’t as long as get the user manual in addition to SOP Existing spreadsheets Call it “version 1” Write a URS Create in addition to record an MD5 be as long as e any changes Test against the URS in addition to any other claims What can ( in addition to can’t) be done Excel Controls Set passwords to control:- Access (to open the spreadsheet) To modify (otherwise there is read-only access) To protect / un-protect (to program it) Message displayed if changes attempted BUT – the Microsoft Excel Help states (in “About worksheet in addition to workbook protection”) “[Excel passwords are] not intended to be mechanisms as long as securing data or protecting [data]“ 7 minutes

Ah but “You CANNOT lock it down” “You CAN lock it down” What else can ( in addition to can’t) you do Excel Controls Set cell types (integer, date, number) Date as long as mat can be yyyy-mm-dd Error message as long as wrong type when entered BUT this can be overridden by pasting in addition to by the delete key AND watch out as long as those ‘active’ conversions (remember 12-3, 12/4) Excel is an active agent The test value was NOT prefixed with ‘=‘. Shaded are the correct output – only 6 out of 15 are correct

Built-in Tools Use the built-in Excel tools Tools – Formula Auditing Precedents Dependants Trace Error An example spreadsheet Designed as long as Excel 2003 Calculates the sum in addition to mean as long as each pair of values ( as long as a sample of some sort) Banner as long as “Valid” in addition to “Invalid” H in addition to out has ‘all’ the parts (you might not print that) Not just as long as show Example Spreadsheet URS (the highlights) Must indicate missing sample ID in a row Must indicate missing value 1 in addition to value 2 data Date must be no later than current date Must not store data Must display study number

How can we match the URS Have an area as long as meta-data Have an area as long as data entry Have an area as long as validity checks Protect all other areas so that they cannot be modified Use entry-field types Don’t take MY word as long as it EuSpRIG – European Spreadsheet Risks Interest Group “Research has repeatedly shown that an alarming proportion of corporate spreadsheet models are not tested to the extent necessary to support Directors’ fiduciary, reporting in addition to compliance obligations. Uncontrolled in addition to untested spreadsheet models there as long as e pose significant business risks. These risks include: Lost revenue & profits Mis-pricing in addition to poor decision making due to prevalent but undetected errors Fraud due to malicious tampering Difficulties in demonstrating fiduciary in addition to regulatory compliance” Don’t take MY word as long as it Ray Panko, University of Hawaii. Lawrence in addition to Lee examined 30 project financing spreadsheets: all 30 had errors. Error rate: 100%

Di Rende, Susan LA Splash Magazine Features Editor www.phwiki.com

Resources Ray Panko:http://panko.shidler.hawaii.edu/ More research to frighten you: http://arxiv.org/find/all/1/all:+spreadsheet/0/1/0/all/0/1 in addition to more figures: http://www.isaca.org/Template.cfmSection=Home&CONTENTID=35903&TEMPLATE=/ContentManagement/ContentDisplay.cfm The example spreadsheet: http://www.file-away.co.uk/spreadsheets.html MD5 software (check with your administrator) http://www.pc-tools.net/win32/md5sums/

Di Rende, Susan Features Editor

Di Rende, Susan is from United States and they belong to LA Splash Magazine and they are from  West Hollywood, United States got related to this Particular Journal. and Di Rende, Susan deal with the subjects like Domestic Lifestyle (Regional); Society

Journal Ratings by PJ’s College of Cosmetology- Muncie

This Particular Journal got reviewed and rated by PJ’s College of Cosmetology- Muncie and short form of this particular Institution is IN and gave this Journal an Excellent Rating.