Related Pages: [ Agile Spreadsheet Development ] [ Spreadsheet resources ] [ Inventory your Spreadsheets ] [ Free spreadsheet software ]

Patrick O'Beirne provides training for best practice and accelerated productivity, and expert consultancy in spreadsheet modelling, model audit and testing. Contact us by phone (+353 5394 22294) or email to request a private in-company course or consultation. He is the author of a new book on best practices in spreadsheet use entitled Spreadsheet Check and Control.
This page is specifically about spreadsheet quality, and financial and other modelling.
See our spreadsheet software page for more general Microsoft Excel websites, forums, techniques, tips, tricks, spreadsheet add-ins, clones, templates, support for Excel, VBA code. See our main spreadsheet page for a list of recommended Spreadsheet and Financial Modelling books with buying links to Amazon. We provide SCANXLS to create an inventory of spreadsheets.
Categories on this page:
News, events, conferences Design quality, good practice to minimise mistakes Auditing software tools and add-ins Mail lists and discussion groups Academic Research into spreadsheet errors Testing methodologies Is Excel safe for Statistical use? Spreadsheet Risks Interest Group EuSpRIG Directories, portals, publications, accountancy articles Business modelling (aka financial modeling) generally

News, events, conferences

www.eusprig.org  July 6/7,2006: European Spreadsheet Risks Interest Group Seventh Annual Conference, Cambridge, 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.mailbarrow.com/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!

Other sites on good practices ( in no particular order )

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.

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/scanxls.htm SCANXLS is our utility to 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.

http://tinyurl.com/6e4m6  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.

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.spreadsheetauditing.com EXChecker - not an Excel addin but a standalone tool with a cataloguing front-end for the necessary inventory & risk analysis phase. A key feature in litigious cases is that because ExChecker is an external application that does not use Excel but accesses the file structures readonly, the auditor cannot change the data.

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 Lastname

Google 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.charting

http://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 Spreadsheets

www.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, UK

I 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 Languages

http://oregonstate.edu/research/TechTran/technos/os99-04.html
Methodology for Testing Spreadsheets

www.cs.orst.edu/~grother/abstracts/icse02b.html
Automated Test Case Generation for Spreadsheets

www.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 website

www.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 2003

http://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 Statistics

www.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 statistics

www.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=833618 

www.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 > Spreadsheets

www.pcworld.com/heres_how/category/0,1696,Spreadsheet,00.html  
PC World's Index to spreadsheet tips

http://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 Excel

www.dmoz.org/Computers/Software/Spreadsheets/Excel/ 
The Open Database project: Add-Ins, Data Formats, Data Recovery, Help

www.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 spreadsheets

www.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 2001

www.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.cfodirect.com/cfopublic.nsf/vContent/THUG-63CNN5?Open
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 404 process.

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)

 

Revision history:
05-May-2005: Tuck SERP added.
27-Jan-2005: more tidying of moved sites
23-May-2004: Software page split off as this page was getting too big.
24-Jan-2004: Book list, other links
12-Jan-2004: Validation, Visualisation, XLanalyst.
12-Dec-2003: Statistics functions cautions, XL2003 Rand bug
01-Dec-2003: Linux, AuditExcel (S.A.), Software Agents (UWIC), Chan Hock Chuan, KPMG
04-Nov-2003: SPRIG moved, "Subversive Spreadsheet" two papers
19-Aug-2003: more quality links, Eusprig 2003
27-Jul-2003: More design & research links
25-Sep-2002: OzGrid added.
23-Jul-2002: EuSpRIG 2002
9-Jul-2001: EuSpRIG 2001 symposium report.
11-Aug-2001: Excel-L mail list added, mathtools.net, other personal Excel pages.

Back to Systems Modelling Ltd. Spreadsheet Page