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!
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.
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
Paper discusses the evaluation of the control environment and
control activities that should be considered by management in
evaluating the use of significant spreadsheets as part of their SOX 404
www.j-walk.com/ss/ John Walkenbach and Associates.
Books, tips, help, and the spreadsheet power utility pack.
page provides a comprehensive list with recommendations
on particularly useful sites.
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.
The Chartered Institute of Management Accountants has an article on
software: ten rules for success"
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.
"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.
AuditNet: spreadsheets for auditors and accountants includes advice on
"Preparing, Documenting, and Referencing Spreadsheets " and Excel
Spreadsheets shared by auditors.
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:
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.
Spreadsheet Manager for remediation and control
control and United Platform for Complete Lifecycle Management
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.
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
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.
Academic research on spreadsheet errors, students'
cognitive difficulties, common mistakes and defects.
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
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.
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://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://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.
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.
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
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."
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).
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
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.
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
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.
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.
The Statistical Services Centre (SSC) of Reading University has
Resources for Excel Users:
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.
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
www.mis.coventry.ac.uk/research/discus/discus.exe (A DOS .exe
file; about 330k)
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.
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.
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
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
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
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."
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.
Management Science Society of Ireland (previously ORMSSI). The MSSI
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
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
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
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
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)
The Association of European Operational Research Societies (not found
Your email address is treated as confidential and never disclosed
without your explicit permission. OPT-OUT: The phone and fax numbers and
email addresses at sysmod.com provided on this web site are provided
solely for one-to-one communications to Systems Modelling Ltd. We
forbid any harvesting of email addresses from this site, or the
inclusion of any sysmod.com address in any mail list without our
explicit permission. SPAM: Unsolicited bulk email to
sysmod.com will be reported to SpamCop.