News, events, conferences
http://www.eusprig.org European Spreadsheet Risks Interest Group Eleventh Annual Conference 15-16 July 2010 in Greenwich, London.
Spreadsheet design
The following sites deal with best practice in the development of robust spreadsheets that can be safely maintained. They show how to control spreadsheet quality to create bug-free models that are easy to use. It is remarkable how many are based in Australia and New Zealand!
The Top Three Recommendations
Spreadsheet Check and Control: 47 key practices to detect and prevent errors. Patrick O'Beirne, 200 pages, Sep 2005, Systems Publishing.
www.eusprig.org European Spreadsheet Risks Interest Group downloads include Spreadsheet Modelling Best Practice Guide (1.4MB pdf) 108-page document (PDF) on spreadsheet style by Nick Read and Jonathan Batson and How do you know your spreadsheet is right? (666K pdf) more than fifty principles, techniques and practice of spreadsheet style by Philip L. Bewig.
www.sysmod.com/spreadsheetsafe.htm Spreadsheet Safe™ is a training and certification programme designed to help spreadsheet end-users and organisations assure and maintain good spreadsheet design, usage and control.
Other references to good practices
www.fast-modeling.net FAST: flexible, accurate, structured, and transparent financial modelling; site with wiki
www.bpmglobal.com/bpm_standards.html Best Practice Spreadsheet Modelling Standards 2005 from BPM Global. 77 page PDF, 2.8MB
www.treasury.govt.nz/dice/reports/rev-spreadsheets.pdf 18 page PDF file - Review of Spreadsheets - November 1997 - New Zealand Treasury. This document aims to highlight the main risk areas, and provide best practice guidelines in order for the user to make the correct trade-off decision.
www.accessanalytic.com.au/services_excel_prevent.php "52 Easy Ways to Prevent Spreadsheet Problems" by Jeff Robson, an Australian consultant. Put one tip for each week of the year in your end-user support newsletter!
www3.uakron.edu/cba/cretisa/2005/pwc_spreadsheets.pdf The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley Act. PricewaterhouseCoopers, July 2004. This PwC White Paper discusses the evaluation of the control environment and specific control activities that should be considered by management in evaluating the use of significant spreadsheets as part of their SOX 404 process.
http://www.mang.canterbury.ac.nz/people/jfraffen/sscom/index.htm
John
Raffensperger's free ReferenceBrowser add-in and a set of links
with ratings of content and style that is a good portal
to tutorials, advanced tips and tricks, downloads and
add-ins, education and research. (spreadsheetstyle.com is now closed)
www.j-walk.com/ss/ John Walkenbach and Associates. Books, tips, help, and the spreadsheet power utility pack. His links page provides a comprehensive list with recommendations on particularly useful sites.
www.xl-logic.com/pages/design_notes.html Elements of Good Spreadsheet Design by Aaron T. Blood who also has a downloadable 48K .DOC Spreadsheet Design Notes
www.excelbestpractices.com/excel Excel Best Practices Resource Center. Author Loren Abdulezer's support site for the book Excel Best Practices for Business which covers building and managing large & complex spreadsheet models, uncertainty and fuzzy data, "Smart Data", validation and auditing, and XML based spreadsheet portals in Excel 2003.
www.cima.org.uk/ The Chartered Institute of Management Accountants has an article on "Budget management software: ten rules for success"
www.epri.com/eprisoftware/processguide/ssdev.html Spreadsheet Development standards
www.frontsys.com/readable.htm Frontline Systems Hints for Building Readable, Manageable Models.
www.bized.ac.uk/educators/he/spreadsheet/section_2.htm Making spreadsheets usable. Teaching notes on usability testing for students, accessibility, and how a well designed spreadsheet looks.
www.enumera.co.uk/spreaderr.htm Enumera's check list of spreadsheet errors
http://tigger.uic.edu/~hagstrom/excel/sprdocument.ppt Jane Hagstrom, How to document a spreadsheet (Powerpoint: University of Illinois-Chicago)
www.luca.com/cpajournal/old/07917358.htm Standardizing spreadsheet designs Reprint from CPA Journal Online, October 1989
www.ozgrid.com Dave Hawley's large site with spreadsheet design tips, financial and chart add-ins, business templates, and discussion forums for support on VBA and Excel.
www.willowsolutions.com have a downloadable 141K document Willow Solutions Financial Model Workbook Standards Standards and procedures including financial modeling with real-time data.
www.auditexcel.co.za/vital.html Miricle solutions in South Africa has a page of vital tips on the auditing functions already in Excel. Other pages describe the spreadsheet issue, research results, and war stories.
www.crystalball.com/models/useful.html model library downloads including "Auditable Excel Spreadsheet Tips" from George Laking.
www.comp.nus.edu.sg/~chanhc/spreadsheet/ "Easy Steps to Design & Check Your Excel Spreadsheets", by Chan Hock Chuan, Department of Information Systems, National University of Singapore. Published by Federal Publications, 2001, ISBN 983-58-0630-6.
www.kpmg.co.uk/services/ras/irm/iro/nov/modelling.cfm KPMG Financial & Data Modelling Information Risk article discusses the rise in spreadsheet modelling and the risk factors.
www.auditnet.org/spreadsheets.htm AuditNet: spreadsheets for auditors and accountants includes advice on "Preparing, Documenting, and Referencing Spreadsheets " and Excel Spreadsheets shared by auditors.
www.rwc.uc.edu/cheatham/gen_class_notes.htm Margaret Cheatham's course notes include "Developing Models" and "Audits and Controls".
http://faculty.fuqua.duke.edu/~pecklund/ExcelReview/2001_Documents/ExcelDesign&AuditTips20001.pdf Excel Design & Audit Tips. Paula Ecklund, Fuqua School of Business, Duke, provides tips on how to create a well-designed worksheet and also discusses some auditing techniques.
www.excelsig.org/Tips/0002%20Spreadsheet%20Design.htm Rasey's Design Philosophy
www.xl-edge.com/ExcelSpreadsheetTipsIntro.htm Tips on Excel and VBA for process engineers includes sample workbooks for download. Excel VBA Tip #3 has advice on the user interface and structure of spreadsheets for process modelling, analysis and prediction.
www.elvis.ac.nz/brain?SpreadsheetAuditing Wiki collating quotes on auditing techniques
www.sysmod.com/y2ksprds.htm the FAQ (Frequently Asked Questions - and answers!) on Year 2000 issues in Microsoft Excel and Lotus 123
The Devil's guide to spreadsheet creation is my article in April 2004 PraxIS.
www.exceluser.com/tools/agile1.htm Excel User web site by Charley Kyd has published an update on my article on applying "agile methods" (aka XP, Extreme Programming) to this most agile of end user development tools! "Many programmers use Agile Software Development, a collection of techniques that reduce errors and speed software development. Here's how Excel users can use similar techniques to gain similar benefits."
Especially for accountants, the AICPA Journal of Accountancy has regular articles:
www.aicpa.org/pubs/jofa/feb2004/simkin.htm Ferret Out Spreadsheet Errors by Mark Simkin (using the Excel auditing tools)
www.aicpa.org/pubs/jofa/dec2003/weisel.htm Boost Profits With Excel by James Weisel. Solver calculates the most cost-effective and profitable product mix.
www.aicpa.org/pubs/jofa/nov2003/bewig.htm In Excel, Cell Names Spell Speed, Safety by Philip Bewig. (How to use Range Names)
www.aicpa.org/pubs/jofa/aug2003/rose.htm Turn Excel Into a Financial Sleuth by Anna Rose and Jacob Rose. An easy-to-use digital analysis tool can red-flag irregularities. (Using Benford's law)
www.aicpa.org/pubs/jofa/aug2002/callahan.htm "Block That Spreadsheet Error" by Theo Callahan. "In an instant a perfectly good spreadsheet disintegrates right before your eyes, leaving a wasteland of #VALUE! error cells."
www.aicpa.org/pubs/jofa/oct1999/hormann.html "Getting the OOPS! Out of Spreadsheets" by Franz Hörmann: How to use built-in audit tools to minimize mistakes.
Spreadsheet auditing and inspection tools
These are diagnostic tools to analyse the accuracy and integrity of a spreadsheet, to verify that it is correctly built, and to validate that it meets requirements. See below for a link to a 2001 review comparing spreadsheet auditing software tools.
http://www.sysmod.com/xltest.htm XLTest is our spreadsheet auditing add-in to check the structural integrity of a spreadsheet and produce both detailed documentation and colour maps highlighting anomalies. It can catalogue your inventory of spreadsheets on a local or network drive, showing file properties and statistics on error incidence. It can also compare workbooks to check if one is a correct copy of another or not.
Prodiance Enterprise Spreadsheet Manager for remediation and control
ClusterSeven Change control and United Platform for Complete Lifecycle Management
Lyquidity ComplyXL for change tracking
RedRover software audit tool for Excel
www.auditware.com SpACE from the UK Customs and Excise Audit unit is a risk assessment and audit methodology for spreadsheet models and a software product to support spreadsheet auditing now distributed by Auditware.
www.operis.com/oak.htm Operis Analysis Kit for spreadsheet audit.
www.XLanalyst.co.uk XL Analyst from Codematic. (more...)
www.spreadsheetinnovations.com The Spreadsheet Professional auditing tool also at www.exceleratedconsulting.com.au/software.htm
www.excelauditor.com From the author of EXchecker, BGB Solutions offer not an Excel addin but a standalone tool with a cataloguing front-end for the necessary inventory and risk analysis phase.
www.SpreadsheetDetective.com Southern Cross Software's Spreadsheet Detective auditing program, plus a long set of links to other sites.
www.themodelanswer.com The Rainbow Analyst free tool for Excel 2000 auditing
www.sheetware.com XDrill drill-down utility
www.codetracer.com Code Tracer, a spreadsheet visualiser and analyser
www.kellogg.nwu.edu/faculty/myerson/ftp/addins.htm Formlist is a free simple auditing tool that adds procedures for displaying the formulas of any selected range.
http://groups.yahoo.com/group/eusprig/files/TrafficLights.xla (requires membership of EuSpRIG group) Phil Bewig's Traffic Lights: Guide cell-by-cell inspection of a spreadsheet by colouring precedents and dependents.
http://groups.yahoo.com/group/eusprig/files/CellMaps.xla (requires membership of EuSpRIG group) Phil Bewig's Cell Maps: Display structure of a spreadsheet using colored backgrounds for various cell types.
Other utilities and tools
www.appspro.com Rob Bovey's Excel Utilities add-in provides 25 routines for Excel development that simplify maintenance. (more...)
www.brandywine-software.com xlNavigator generates a flow chart representation of the Spreadsheet Model Dependencies.
www.mastertool.co.uk Master Tool is an add-in that can handle tedious tasks that normally require repeated Excel menu selections. (more...)
www.robbo.com.au/ Navigator Utilities helps you to easily navigate through Sheets, Links, and Named Ranges (more...)
www.decisionmodels.com/downloads.htm Name Manager utility to manager Local, Global, Relative and Absolute names; list, filter, edit, delete, hide, unhide, localise, globalise.
http://www.torry.net/vcl/reports/reportdesigners/agexcelreport.zip Contains BIFF Editor - a utility to show the binary record structure of an Excel .XLS file.
www.synkronizer.com Excel tool for comparing Excel spreadsheets and Excel databases, reporting differences, and synchronizing (merging) versions.
www.addix.com Spreadsheet Selector Object Library provides the ability to extract data and formulae from Excel spreadsheets without the need to open the spreadsheets using Excel.
www.informationactive.com ActiveData advanced data manipulation and analysis within the Excel environment, designed for use by auditors and accountants. My review is in June 2004 PraxIS.
www.xl-logic.com/pages/explode.html Explode by Aaron Blood is a free tool that traces cross-sheet precedent/dependents
www.johnmote.com add-ins: Formula Mapper, Formula Indenter, Password Remover, and PowerEdit
Spreadsheet mail lists
The Excel-L Excel Developers' List is a high volume email discussion forum for people who are doing, or want to do, sophisticated things with Microsoft Excel.
To subscribe, send an email to LISTSERV(at)PEACH(dot)EASE(dot)LSOFT(dot)COM and put the following in the body of mail (no subject line needed):
SUBSCRIBE EXCEL-L Firstname Lastname
Where Firstname and Lastname are your first and last names.EXCEL-G is a list where the general or casual user of EXCEL can get help for more everyday questions. To subscribe, send an email to the same listserv address above, but in the body put:
SUBSCRIBE EXCEL-G Firstname LastnameGoogle archives the following newsgroups:
http://groups.google.com/groups?group=comp.apps.spreadsheets
http://www.faqs.org/faqs/spreadsheets/faq/ The comp.apps.spreadsheets FAQ
http://groups.google.com/groups?group=microsoft.public.excel.misc
http://groups.google.com/groups?group=microsoft.public.excel.programming
http://groups.google.com/groups?group=microsoft.public.excel.worksheet.functions
http://groups.google.com/groups?group=microsoft.public.excel.chartinghttp://groups.yahoo.com/group/ExcelStyle/ Members: 8 as of July 2002, Founded: Dec 14, 2001
For Excel Users and Spreadsheet Application Developers who would like to share ideas (and files) on spreadsheet design and style.http://groups.yahoo.com/group/QuantitativeFinance/ Members: 63 as of July 2002, Founded: Jun 23, 2000
This discussion group addresses quantitative methodologies and their software implementation in
day-to-day trading, financing, valuation, and risk management situations. This is a forum where
both practical issues such as market conventions or spreadsheet problems can be discussed.http://groups.yahoo.com/group/sjiexcelintro/ Members: 24 as of July 2002, Founded: Apr 7, 2002
Introduction to Microsoft's Excel 2000 spreadsheet software.
Spreadsheet Research
Academic research on spreadsheet errors, students' cognitive difficulties, common mistakes and defects.
www.actuaries.asn.au/PublicSite/pdf/fsfpaper2004-jasminandlee.pdf Financial Modelling of Project Financing Transactions by: Robert J Lawrence MSc Jasmine Lee FIAA MCom. Presented to the Institute of Actuaries of Australia Financial Services Forum 26-27 August 2004. Appendix Statistics on Model Error Rates reports "They are based on the thirty most financially significant projects that Mercer Finance & Risk Consulting reviewed during the financial year ending 30 June 2004. For the financial models related to these thirty projects the average number of unique formulae per model was 2,182 and the average number of issues raised during the initial review of these models was 151 (or, 6.9% of the number of unique formulae). The average number of versions required in order to produce a model that could be “signed-off” was 6."
www.panko.com/SSR/HOME.HTM Ray Panko’s Spreadsheet Research page is the primary resource about error rates in spreadsheets both from lab research and field studies. His paper at Eusprig 2000 was "What we know, what we think". His list of "Reports of Spreadsheet Errors in Practice" covers 1984-1995. Most web sites on this issue cite his paper (This used to be at the University of Hawaii web site http://panko.cba.hawaii.edu/ssr )
The University of Wales Institute Cardiff (UWIC) research group headed by Pat Cleary proposes to develop software agents for the reduction of risk in end-user spreadsheet use.
http://www.ifi.uni-klu.ac.at/Publications/result?writtenby=member&imgnr=mcler&order=year
Papers by Markus Clermont while at the Departments of Informatics, University of Klagenfurt:
A Scalable Approach to Spreadsheet Visualization (2003)
Analyzing Large Spreadsheet Programs (2003)
Auditing Large Spreadsheet Programs (2003)
A Spreadsheet Auditing Tool Evaluated in an Industrial Context (2002)
Finding High Level Structures in Spreadsheets (2002)
Detecting Errors in Spreadsheets (2000)
http://www.j-paine.org/ Jocelyn Paine, University of Bristol, articles on Spreadsheet Algebra and Spreadsheet structure discovery with logic programming. The Prolog language is used to look for high-level patterns in spreadsheets and automate some transformations to make them easier to read.Kamalasen Rajalingham Ph.D in Computing & Information Technology at the University of Greenwich (London). His research web page publishes a number of documents such as
http://panko.cba.hawaii.edu/ssr/HICSS33/HICSS33-Rajal-SoftEngr.pdf Quality Control in Spreadsheetswww.arcs.ac.at/dissdb/rn036237 Ayalew Yirsaw Spreadsheet testing using interval analysis
www.chacocanyon.com/essays/spreadsheets.shtml Reviews and Inspections of Spreadsheets by Rick Brenner.
www.chacocanyon.com/essays/spreadsheetchecklist.shtml A Checklist for Use in Reviewing or Inspecting Spreadsheets by Rick Brenner.www.mang.canterbury.ac.nz/people/jfraffen/ John F.Raffensperger at the University of Canterbury (NZ) has a long article on The New Guidelines for Writing Spreadsheets based on the importance of readability.
http://eecs.oregonstate.edu/EUSES/ End Users Shaping Effective Software - a 12-person, multi-university research consortium directed by Oregon State University computer science professor Margaret Burnett and researchers from Pennsylvania State University, Carnegie Mellon, Drexel, University of Nebraska, and Cambridge University in the UK. By combining computer science research with expertise in psychology and education, the group hopes to find more effective solutions than if only the technological aspects were considered.
http://eecs.oregonstate.edu/~grother/ Dr Gregg Rothermel articles include
www.cs.orst.edu/~grother/papers/icsm01c.pdf Incorporating Incremental Validation and Impact Analysis into Spreadsheet Maintenance: An Empirical Study, and
www.cs.orst.edu/~grother/vptestdebug.html Visual Programming Testing and Debugging.http://mba.tuck.dartmouth.edu/spreadsheet/index.html The Spreadsheet Engineering Research Project (SERP) by researchers at the Tuck School of Business at Dartmouth. The purpose of this three-year project, funded by a grant from the National Institute of Standards and Technology (NIST), is to improve the design and use of spreadsheets by individuals and organizations. The research focuses on identifying best practices in spreadsheet development (design, testing, documenting); procedures for implementing, modifying, sharing, and archiving spreadsheets; and organizational policies relating to standards, training and quality control.
http://csgrad.cs.vt.edu/~mvass/pse/empstudy.pdf Empirical Study of Error Detection Support.
http://cob.jmu.edu/kruckse/research/KRUCK-SHEETZ_SpreadsheetAccTheory.pdf Spreadsheet Accuracy Theory S. E. Kruck Computer Information Systems, James Madison University
www.cl.cam.ac.uk/~afb21/CognitiveDimensions/CDtutorial.pdf Cognitive Dimensions of Information Artefacts: a tutorial Thomas Green and Alan Blackwell Version 1.2 October 1998 The tutorial is intended for Human-Computer Interaction (HCI) practitioners and consultants, especially those actively involved in design of novel user interfaces.
A review and research agenda for spreadsheet based systems in end-user computing P.B. Cragg, Canterbury, NZ and M. King, Loughborough, UK 1992.
www.mcs.vuw.ac.nz/~db/RIMS.shtml?comp489 Daniel Ballinger's page at the School of Mathematical and Computing Sciences of Victoria University of Wellington that cites many references to spreadsheet research with ratings of level of interest. His projects uses low-level access program visualisation techniques to improve end-user understanding.
www.SpreadsheetAnalytics.com is Tom Grossman's Spreadsheet Analytics page at the University of San Francisco which includes his links on Spreadsheet Engineering. "...experienced spreadsheet users are but amateur spreadsheet programmers."
http://cbae.nmsu.edu/~jkreie/EUC/abstr_dissert.shtml Jennifer Kreie "After the training treatment subjects were more likely to incorporate proper design features, such as documentation, in their applications. The other quality measures--completeness, accuracy, and end-user satisfaction--were not significantly related to the training."
www.gre.ac.uk/~cd02/EUSPRIG/2001/Nixon_2001.htm David Nixon's prize-winning 2001 thesis at Greenwich University compares spreadsheet audit software tools including Excel's auditing toolbar by applying them to a specially created test spreadsheet seeded with errors (bebugged).
www.palgrave-journals.com/cgi-taf/DynaPage.taf?file=/ jors/journal/v55/n1/full/2601654a.html&filetype=pdf (194K PDF)
Validation of decision-aiding spreadsheets: the influence of contingency factors Journal of the Operational Research Society January 2004, Volume 55, Issue 1 CW Olphert and JM Wilson* Loughborough University, England, UKI have been unable to track down online sources of early studies like these:
D. Freeman, (1996) How to make spreadsheets error-proof. J. Accountancy 181 (5) , pp. 75-77 "In an examination into blue-chip companies using large spreadsheets (more than 150 rows), Coopers & Lybrand discovered that more than 90% of the models contained at least one calculation error. In 21 out of the 23 models reviewed, results were inaccurate by more than 5%. In fact, some models were so badly written that they were impossible to test, and they had to be completely rewritten, resulting in delayed projects."
Spreadsheet Testing, validation, and verification
www.xlsior.com XLSior is an add-in produced by Louise Pryor for running test cases on spreadsheets, and adds auto-documenting features too. My review is in May 2004 PraxIS.
http://tinyurl.com/6e4m6 UK Customs & Excise spreadsheet testing method and tool "SPACE"
http://panko.cba.hawaii.edu/ssr/HICSS33/HICSS33-Butler-Evader.pdf Ray Butler (HMCE) article "Is this spreadsheet a tax evader?"
http://www.informationweek.com/529/29uwfw.htm Applications Development: Finding Spreadsheet Errors by Ray Panko, May 29, 1995
http://sprig.section.informs.org Tom Grossman's SPRIG (Spreadsheet productivity Research Interest Group, previously SRIG, Spreadsheet Research Interest Group))
http://sprig.section.informs.org/sprigfiles/The_Subsersive%20Spreadsheet_by_RayButler_HMCE.pdf Presentation to INFORMS 2001 by Ray Butler (in UK Govt Customs & Excise dept. at the time) on the application of the COBIT framework to reducing spreadsheet risk.
http://www.eusprig.org/eusprig.pdf "Stop the Subversive Spreadsheet" by David Chadwick of Greenwich University describes EUSPRIG's work.
http://www.isaca-london.org/events20022003.htm 23 January 2003 Spreadsheet Fraud Presentation materials by Ray Butler
www.isaca-london.org/presentations/spreadsheetJan03/ISACA%20London%20Spreadsheet%20handout.zip The Subversive Spreadsheet (301KB)
www.isaca-london.org/presentations/spreadsheetJan03/Applying%20the%20CobiT%20framework%20to%20Spreadsheets.zip Applying the Cobit Framework (41KB) (also at http://isaca-london.org/datawatchpdf/Datawatch 50.pdf Datawatch Dec 2001 468K PDF) Back to Basics - A Control Framework for Spreadsheet Developments)
www.isaca-london.org/presentations/spreadsheetJan03/eusprig_risk.zip EUSPRIG (99KB)
www.isaca-london.org/presentations/spreadsheetJan03/roulette_article.zip Roulette Article (130KB)
http://citeseer.nj.nec.com/reichwein99slicing.html
Slicing Spreadsheets: An Integrated Methodology for Spreadsheet Testing and Debugging (1999)http://citeseer.nj.nec.com/context/961383/145146
G. Rothermel, L. Li, C. DuPuis, and M. Burnett. What you see is what you test: A methodology for testing form-based visual programs. In Proceedings of the International Conference on Software Engineering, pages 198-207, April 1998.http://kmi.open.ac.uk/people/paulm/summer99/spreadsheet.html
Testing and Debugging in Spreadsheet-Like Visual Programming Languageshttp://oregonstate.edu/research/TechTran/technos/os99-04.html
Methodology for Testing Spreadsheetswww.cs.orst.edu/~grother/abstracts/icse02b.html
Automated Test Case Generation for Spreadsheetswww.npl.co.uk/ssfm/download/ssfm1_index.html
Testing Spreadsheets and Other Packages Used in Metrology. See also
www.npl.co.uk/ssfm/ssfm1/validate/testing/www.pharmtech.com/pharmtech/data/articlestandard/pharmtech/022003/42756/article.pdf
Validation of Electronic Spreadsheets for complying with 21 CFR Part 11 by Tuan T. Phan based on the good automated manufacturing practice (GAMP) methodology. Jan. 2003 Also on Pharmaceutical Training Institute websitewww.pti-international.com/index.cfm/Link=86
www.biopharm-mag.com/biopharm/article/articleDetail.jsp?id=90851
Using Macros and Spreadsheets in a regulated environment - article from LabCompliance.com published in Pharmaceutical Training Institute and Biopharm Magazine.www.labmetrology.com/quality/quality_spreadsheet%20validation.htm
Labmetrology 2002 Spreadsheets Validation SOP (Standard Operating Practice). "Unfortunately, spreadsheets have a fundamental weakness contradicting their secure use and therefore compliance with FDA regulations 21 CFR Part 11."www.spreadsheetvalidation.com ABB, agents for Wimmer Systems' DACS software for Excel compliance in the pharma industry.
www.nuclearmedicine.org.uk articles by Phil Cosgriff on Medical Software Quality Assurance
"Spreadsheets: the 'Cinderella' of medical software" (2.6MB PDF) and Basic guidelines for writing spreadsheets (1.6 MB PDF) Structure, version control, access security, error trapping, prove it works!www.northviewlabs.com/Spreadsheet_Validation.htm
A Practical Approach to Spreadsheet Validations in the cGxP Environment by Hung Che (June 2002). cGxP = Current Good x Practice where x is any of C=Clinical (cGCP), L=Laboratory (cGLP), M=Manufacturing (cGMP)www.et.byu.edu/~jww8/excel.html Excel in Engineering page by John Wittwer
We could do with more links to articles in the application areas of engineering, medicine, and manufacturing - contact us if you know of any.
Is Excel safe for Statistical use?
www.daheiser.info/excel/frontpage.html 'Microsoft Excel 2000 And 2003 Faults, Problems, Workarounds And Fixes'. David Heiser's review of the inherent problems with Excel's computing and display "machinery". 18 sections covering tests and evaluations of both the Excel 2000 and 2003 versions on graphics, univariate statistics, ANOVA, covariance, linear and non-linear regression, the statistical distributions, significance tests, random number generators and add-in packages. 26 notes covering more extended discussions on complaints and faults, suggested improvements in Excel, guides to the use of Excel and on other issues.
http://support.microsoft.com/default.aspx?kbid=833855 Excel 2003 hotfix package: February 29, 2004 refers to two issues:
834521 The FORECAST, STEYX, SLOPE and INTERCEPT functions cause different results in Excel 2003 than in earlier versions of Excel
834520 The RAND function returns negative numbers in Excel 2003http://support.microsoft.com/default.aspx?kbid=828888 Description of improvements in the statistical functions in Excel 2003 and in Excel 2004 for Mac. Inadequacies in Excel statistical functions were reported in papers by Knusel and McCullough and Wilson. This article warns users of Excel 2002 and earlier to avoid using results in the last three rows of the output table for LINEST(known_y's, known_x's, FALSE, TRUE). Users of all versions should not use the Analysis ToolPak's tool for t-Test: Paired Two Sample for Means if there are missing data. These two cases return incorrect results regardless of data values.
www.rdg.ac.uk/ssc The Statistical Services Centre (SSC) of Reading University has Resources for Excel Users:
www.personal.rdg.ac.uk/~snsstern/resources/excel.html Guide to Excel for Statistics (pdf, 151KB)
www.rdg.ac.uk/ssc/publications/guides/topxfs.html Using Excel for Statistics - Tips and Warnings (also as PDF)
www.rdg.ac.uk/ssc/publications/guides/topsde.html Disciplined Use of Spreadsheets for Data Entry "Their very flexibility means they can result in poor data entry and management. They should thus be used with great care. Users should apply the same rigour and discipline that is obligatory with more structured data entry software. "
www.rdg.ac.uk/ssc/publications/guides/toptgs.html Informative Presentation of Tables, Graphs and Statisticswww.practicalstats.com/Pages/excelstats.html
1. Many statistical methods are not available in Excel.
2. Several Excel procedures are misleading.
3. Distributions are not computed with precision.
4. Routines for handling missing data were incorrect.
5. Regression routines are incorrect for multicollinear data.
6. Ranks of tied data are computed incorrectly.
7. Many of Excel's charts violate standards of good graphics.http://hesweb1.med.virginia.edu/biostat/teaching/clinicians/excel.hazards.txt The Association of Statistics Specialists Using Microsoft Excel (ASSUME) described known errors in Excel's statistical facilities
A post to the newsgroups sci.math.num-analysis, sci.stat.math Date: 2002-09-22 answered a request for studies of the accuracy of the algorithms used by VBA functions and by Excel functions. Also see sci.stat.consult Date: 2002-12-13 for more links. Here are some selected PDF documents:
http://pages.stern.nyu.edu/~jsimonof/classes/1305/pdf/excelreg.pdf Jeffry Simonoff's collection of stat errors.
www.stat.uiowa.edu/~jcryer/JSMTalk2001.pdf Jonathan Cryer's 2001 JSM Talk Problems using Microsoft Excel for statisticswww.burns-stat.com/pages/Tutor/spreadsheet_addiction.html Patrick Burns lists a number of Excel features that are prone to causing user errors. He has a large list of links about Excel limitations for statistical use.
http://groups.google.com/groups?threadm=%23QzBp%23ZvDHA.1996%40TK2MSFTNGP12.phx.gbl
Excel 2003 RAND() function bug. The random number function in Excel is relied upon by Monte Carlo simulation modellers (huge financial risk models) and office syndicate lottery number pickers. It is specified to create only positive numbers 0-1; in Excel 2003 it occasionally produces negative numbers. The patch was issued in Jan 2004 at http://support.microsoft.com/default.aspx?kbid=833618www.mis.coventry.ac.uk/~nhunt/publicat.html Neville Hunt's publications includes a link to:
www.mis.coventry.ac.uk/~nhunt/pottel.pdf (122K PDF) Statistical flaws in Excel Hans Pottel Innogenetics NV, which cites inter alia:
www-unix.oit.umass.edu/~evagold/excel.html Using Excel for Statistical Data Analysis: Successes and Cautions, EvaGoldwater;
www.stat.uni-muenchen.de/~knuesel/elv/excelacc.pdf Knüsel L. On the Accuracy of Statistical Distributions in Microsoft Excel 97;
www.agresearch.co.nz/Science/Statistics/exceluse1.htm Cox Neil, Use of Excel for Statistical Analysis.www.mis.coventry.ac.uk/research/discus/discus_home.html the DISCUS project at Coventry University - Discovering Important Statistical Concepts Using Spreadsheets - produced low-budget spreadsheet-based teaching materials for elementary statistics courses. Units have been produced on Descriptive statistics, Probability, Binomial distributions, Poisson distributions, Continuous distributions, Sampling, Regression and Hypothesis testing. Each unit consists of a Microsoft Excel 97 workbook of spreadsheets with accompanying work-cards of student-centred investigations. The complete set of DISCUS materials is available from Coventry University free of charge at www.mis.coventry.ac.uk/research/discus/discus.exe (A DOS .exe file; about 330k)
Spreadsheet Risk Interest Group
The European Spreadsheet Risks Interest Group (EuSpRIG) had their inaugural meeting at Greenwich, England, on July 18 & 19 2000. A brief report on their Dublin 2003 conference is in the August 2003 PraxIS newsletter. The fifth annual conference was in Klagenfurt, Austria in July 2004. Their 2005 conference on "Managing Spreadsheets in the light of Sarbanes-Oxley", will be on July 7/8, 2005, University of Greenwich, London, England.
Their discussion group has been set up under yahoogroups.com: http://groups.yahoo.com/group/eusprig/
The Eusprig web site has a large and growing collection of spreadsheet disasters / frauds / errors / mistakes / slips / glitches / fubars / ... at www.eusprig.org/stories.htm
Directories, portals, publications, accountancy articles, education
http://www.accountingweb.co.uk/cgi-bin/item.cgi?id=141045 AccountingWeb UK has a large ExcelZone Compendium of tips on setup, charts, pivot tables, queries, functions, macros, troubleshooting, and applications.
www.sie.bond.edu.au/ Spreadsheets in Education (eJSiE) is an electronic journal devoted to the publication of quality refereed articles concerned with studies of the role that spreadsheets can play in education.
www.ijee.dit.ie/callfp/spreadsheets.html The International Journal of Engineering Education will publish a special issue on Applications of Spreadsheets in Engineering Education.
www.mathtools.net is a technical computing portal for all scientific and engineering needs. The portal is free and contains over 20,000 useful links to technical computing programmers, covering Excel, Fortran, Java, MATLAB and others.
http://vertex42.com/ExcelLinks/index.php The Excel Nexus Portal allows user ratings and comments on individual links.
www.zdnet.com/downloads/business/spreadsheets.html
ZDNet > Downloads > Business > Spreadsheetswww.pcworld.com/heres_how/category/0,1696,Spreadsheet,00.html
PC World's Index to spreadsheet tipshttp://www.pcmag.com/search_results/0%2C2426%2C%2C00.asp?qry=spreadsheet PC Magazine - Spreadsheet articles
www.zdnet.com/pccomp/features/1001tips/123.html PC Computing's 1,001 Tips: Lotus 1-2-3
www.zdnet.com/pccomp/features/1001tips/excel.html PC Computing's 1,001 Tips: Microsoft Excelwww.dmoz.org/Computers/Software/Spreadsheets/Excel/
The Open Database project: Add-Ins, Data Formats, Data Recovery, Helpwww.icai.ie/itfactsheets/5a57ca1.htm Institute of Chartered Accountants in Ireland Factsheet Series on IT - Factsheet 1 Spreadsheet Modelling (not found June 2003)
http://accounting.pro2net.com/x11503.xml The Spreadsheet Challenge
Nov. 22, 1999 (Pro2Net Accounting)www.stanford.edu/~wfsharpe/mia/mat/mia_mat4.htm
William F Sharpe: Matrix Operations in Excel for Macro-Investment Analysis.http://members.attcanada.ca/~johnjaz/excel.htm (offline May 2004)
FOCARB: Foreign Content and Re-Balancing of retirement investment portfolios
John Jaz (08/26/1999 )http://archive.baarns.com/Excel/free/excelexp.asp Baarns Publishing developed a series of On-Line Experiences detailing the benefits and outlining the steps of many Excel features.
www.langreiter.com/misc/spread-paradigm.html The early history of spreadsheetswww.medienkultur.org/sm5/medienderoekonomie/index.html German language web site by Caspar Clemens Mierau on the term Tables or "Tabellen". A spreadsheet is a "Rechenblatt in einem Tabellenkalkulationsprogramm"
The History of Mathematical Tables - From Sumer to Spreadsheets Edited by Martin Campbell-Kelly, Department of Computer Science, University of Warwick, and others. ISBN 0-19-850841-7 Pub. October 2003 372 pages. Click here for links to buy the book at Amazon in the USA, UK, France, Germany, Japan, or Canada.
The oldest known mathematical table was found in the ancient Sumerian city of Shuruppag in southern Iraq. This book contains a series of articles summarising the history of mathematical tables from earliest times until the late twentieth century. Martin Campbell-Kelly presented a talk "The rise and rise of the spreadsheet" to the Eusprig 2002 conference in Cardiff
Business modelling (US: financial modeling)
www.pearsoned.com.au/elearning/hovey/inv_dec/home.html Spreadsheet Modeling for Investment Decisions, online ebook by Martin Hovey of the University of South Queensland. "Spreadsheet Modelling for Investment Decisions should be treated as a supplement to finance textbooks... a valuable aid to developing workable models quickly and accurately."
www.pearsoned.com.au/elearning/hovey/corp_fin/main.html Spreadsheet Modeling in Corporate Finance, online ebook by Martin Hovey of USQ.edu.au. "The book is written as a supplement to your corporate finance textbook. "
www.wilmott.com - Paul Wilmott's Quantitative Finance site has forums discussing financial engineering techniques, simulation, and more.
www.atebit.co.uk Atebion, a simultaneous equation modelling solver
http://analycorp.com Sam Savage's Analycorp site for XLSIM. His article "The flaw of averages" states that: "Plans based on average assumptions will be wrong on average." His academic site is www.stanford.edu/~savage/software.htm Excel Add-ins and templates for performing Monte Carlo and Queue Simulation.
www.cfoeurope.com/200107g.html
New life for old spreadsheets July/August 2001www.cfo.com/printable/article.cfm/3014451 Spreadsheet Hell by Don Durfee, CFO IT June 15, 2004
"CFOs are interested in the many new technologies being pitched to them, but are they really trapped in spreadsheet hell? While only 33 percent of respondents with revenue under $100 million say that 'spreadsheet hell' is a fair description of what goes on in their departments, that figure jumps to 59 percent for larger companies."www.financialmodelling.net Stephen Aldridge's site includes best practice guides and an article on Financial modelling which was published in the June 2003 edition of CIMA'a Financial Management magazine.
www.financial-models.com Financial Modelling UK. Alastair Day is the author of the books Mastering Financial Modelling and Mastering Risk Modelling
http://mis.ucd.ie/mssi/ Management Science Society of Ireland (previously ORMSSI). The MSSI promotes, in the Irish business environment, a scientific approach to problem-solving in support of management decision-making. It is concerned with innovation, whether technological or organisational, in response to an increasingly turbulent environment.www.cognos.com/roughroad "Spreadsheet Planning: Rough Road Ahead" is a white paper from Cognos that discusses the three types of error that typically occur in a spreadsheet-based planning model which can cost lost productivity and cast doubt on the integrity of plans.
www.bfmag.com/magazine/archives/article.html?articleID=13985 July 2003, Business Finance: The Case Against Budgeting by Eric Krell "Given the rules within Sarbanes-Oxley and the heightened pressure to make earnings forecast, there's a huge risk factor in relying that heavily on spreadsheets."
www.spreadsheetsox.com Derek Wimmer's site related to the use of spreadsheets in the Sarbanes-Oxley (SOX) realm.
www.enterprisenetworksandservers.com/opinionw/art.php/113 Butler Group, 14 May 2004: "Most organizations are facing a mammoth data quality problem. Clamping down spreadsheet usage is a logical place to start."www.solutionmatrix.com/newsletter41.html Cost/Benefit Newsletter June 2004 'We just lost our negotiating room - the pitfalls of Excel.' Gives two Excel formulas for estimating salary and overhead costs in Year 2 of a multi-year analysis period - see if you can spot the error.
www.lumina.com/dlana/papers/Whats%20wrong%20with%20spreadsheets.pdf 'Ten things I hate about spreadsheets and how Analytica fixes them'.
www.derivativesstrategy.com/magazine/archive/1997/0697fea1.asp Controlling Model Risk By Margaret Elliott ($83 million after-tax write-off story and more) "model risk covers at least three distinct areas: the choice, testing and safekeeping of the mathematics and computer code that form a model; the choice of inputs and calibration of the model; and the management issues associated with these activities."
www.planningobjects.com Planning Objects from Brixx.com, an object-oriented modelling system for larger multi-dimensional problems with an explicit focus on time dimension management. (not found Aug 2002)
www.ulb.ac.be/euro/euro_welcome.html The Association of European Operational Research Societies (not found Aug 2002)
Reciprocal links
OLAS Training Patrick O'Beirne provides Spreadsheet Safe training through OLAS.
Excel Courses online contains over 50 Excel Primers and step by step detailed instructions on hundreds of common Excel tasks as well as information on their online Excel course.
Revision history:
Reciprocal links added
Back to Systems Modelling Ltd. Home Page
provided subject to the terms and conditions below.