Spreadsheet Check and Control - Softcover

O'Beirne, Patrick R

Softcover

ISBN 10: 190540400X ISBN 13: 9781905404001

Publisher: Systems Publishing, 2005

Synopsis

This spreadsheet book is quite different from every other book on Microsoft Excel. If spreadsheet users had a driving licence, this would be their seat belt, air bag, navigation aid, repair kit, hazard indicators, and the rules of the road.

This new book describes how to produce well-crafted spreadsheets that are easy to understand, maintain, audit, and operate. It shows how to ensure data quality and accuracy and protect against formula and operational errors.

Each of the 47 techniques is explained with examples and has a self-test practical question with answers at the back of the book. The book¡¦s support web site provides sample spreadsheets for these tests.

The material gives spreadsheet users more confidence in their work, and saves time chasing and fixing easily preventable errors. Those whose job is to check the work of others, whether they are software testers or auditors, will learn formula auditing techniques and be able to reveal hidden data and formulas. This book should be adopted by organizations concerned to reduce compliance costs by being able to demonstrate management of material risks and increase controls on spreadsheet based financial reporting.

About this title

About the Author

Patrick is Managing Director of Systems Modelling Limited since 1981. His current focus is spreadsheet systems modelling and auditing services as well as general IT consultancy. He lectures on Quantitative Methods in the Irish Management Institute. He is current chair of the European Spreadsheet Risks Interest Group (EuSpRIG) and of SoftTest Ireland, and a Fellow of the Irish Computer Society. He advised the ECDL Foundation on the content of a syllabus for a new certification in good spreadsheet practices. His latest book is "Spreadsheet Check and Control" (Systems Publishing, 2005, ISBN 190540400X)

From the Back Cover

Learn how to:
• Increase efficiency by avoiding rework
• Discover powerful formula auditing techniques
• Foil attempts to conceal data and formulas from you
• Reduce worry about costly and embarrassing mistakes
• Create spreadsheets faster by avoiding wasted time from lack of specification
• Present results with more confidence knowing that you have checked for errors
Benefits to your organisation
• Ensure data quality and accuracy
• Protect against formula and operational errors
• Be able to demonstrate management of material risks
• Increase controls over spreadsheet based financial reporting
• Reduce compliance costs for businesses in regulated sectors

"Excellent ¡K it should be required reading for all young trainee accountants" Ciaran Walsh, senior finance specialist, Irish Management Institute.

Get the assurance of better quality spreadsheets!
Now you can discover how to avoid the most expensive pitfalls that await the unwary. And you can learn how to produce well-crafted spreadsheets that are easy to maintain and give to others to use. This is courseware for a European Computer Driving Licence (ECDL) Foundation certification programme on Spreadsheet Check + Control. Its valuable checklists help you assure the consistency of work both for yourself and those who depend on you to get it right.
Get a confidential look into spreadsheet issues!
In-company consulting provides you with a confidential examination of your most important spreadsheets. Let Systems Modelling put you on the fast track to understanding how and where good spreadsheets have gone bad, and help draw up a training plan to bring other spreadsheet creators and users up to speed.

From the Inside Flap

'Save red faces all round by buying, absorbing and passing-on this book, especially if you personally develop spreadsheets or if your organization is subject to Sarbanes Oxley and related regulations. Avoiding even a trivial spreadsheet mistake may well pay for the book. Avoiding a large one may save your career.' Dr. Gary Hinson, independent consultant in information security and computer auditing, editor of security awareness website NoticeBored.com.

'An essential guide for serious spreadsheet users. This book goes a long way to help spreadsheet users adopt methods that will reduce errors and thereby improve the quality of the information vital to the success of all organisations.' P M Cleary, University of Wales Institute Cardiff, Wales

Excerpt. © Reprinted by permission. All rights reserved.

Introduction Are your spreadsheets important? This book is for you! It describes good practices that all spreadsheet users should follow. Use this book to learn how to avoid the most common errors and to make future development easier. If you are a software tester or a manager of end-user-computing, it gives you techniques for checking spreadsheets for accuracy and soundness. If you are an auditor looking for evidence of fraud, such as deliberately concealed data or functionality, you will also benefit from knowing the many ways in which data can be hidden or calculation methods subverted. To derive the fullest benefit from this book, you should already have spreadsheet skills corresponding to the European Computer Driving Licence (ECDL) Module 4 (M4) or Advanced Module 4 (AM4). The focus of this book is on spreadsheet con-struction using formulas. All the examples are in Microsoft Excel. They have been tested in Excel versions 97, 2000, 2002(XP), and 2003. We mention a few differences between Excel 97 and Excel 2002/2003. Why is this book necessary? This book was created in response to expressions of concerns by managers about risk to the business from a pervasive dependence on spreadsheets. Barry Boehm, in his article 'Software Defect Reduction Top 10 List' warned: 'The ranks of sorcerer¡¦s apprentice user-programmers will swell rapidly, giving many who have little training or expertise in how to avoid or detect high-risk defects tremendous power to create high-risk defects'. He proposed that to help reduce the likelihood of user errors, developers should provide the equivalent of seat belts and air bags in cars, along with safe-driving aids and rules of the road. This fits in nicely with the 'Driving Licence' analogy! Regulators are examining spreadsheet controls The UK HM Revenue and Customs department audit the tax returns for Value-Added Tax (VAT) submitted to them in spreadsheet form. The largest VAT error found in a spreadsheet in 2001 was around £1M sterling (about £á1.5M, or $1.9M). Recovering that amount gave them a very good payback on their investment in developing spreadsheet auditing software and skills. The US Food and Drug Administration (FDA) have rigorous specifications for the integrity of electronic records. These include spreadsheets used as laboratory log books. The US Sarbanes-Oxley Act requires auditors to comment on internal controls over financial reporting. As consolidation and reporting is frequently done by spread-sheets, management are spending more time on review and testing.Their annual conference attracts speakers from all over the world to present on tools and techniques for the detection, correction, and prevention of spreadsheet errors. Researchers from ISACA (Northern UK Chapter), University of Wales Institute Cardiff and the University of Greenwich met in 1999 to discuss the ever-increasing problem of business risk associated with spreadsheet errors. As a result of this meeting they founded Eusprig. This group aims to increase awareness of spreadsheet risk among academia and industry. It also promotes research into the extent and nature of the problem, methods of preventing and detecting of errors and methods of limiting damage. This has brought together researchers and professionals in the areas of business, software engineering and auditing to seek effective solutions. It now includes interested parties from HM Revenue and Customs, PricewaterhouseCoop-ers, KPMG, the British Computer Society (BCS), the Dutch Computer Society (NGI) and the Netherlands Bank, as well as many other companies and individuals. The ECDL Foundation The European Computer Driving Licence (ECDL) foundation in Dublin, Ireland, develops the ECDL syllabus for the certification of users in computer competence. They found demand from companies for certification, beyond technical knowledge, to include a level of self-awareness and responsibility for data integrity and calcula-tion correctness. Accordingly, in 2003 they met with experts in the European Spread-sheet Risk Interest Group (Eusprig) to capture current best practices in design quality and the avoidance of errors. They presented a paper at the fifth Eusprig conference in Klagenfurt, Austria, in 2004. In it, they outlined their goals for a future syllabus: [This] Module requires the candidate to appreciate their responsibility for good spreadsheet design and use given the scope and significance of spreadsheets use in the contemporary workplace. The candidate shall appreciate the need for good spreadsheets specification, be aware of some of the key security considerations, and appreciate the benefits of clearly organised, well presented and easy-to-use spreadsheets. The candidate shall be able to construct sound spreadsheets, find and correct common errors, and test for input and output accuracy. It is anticipated that the candidate will already have mastered the skills and achieved the knowledge detailed in ECDL Module 4, Spreadsheets, or ECDL Module AM4, Spreadsheets, Advanced-Level.

The structure of this book Each section is organised as follows: The key practice item appears in a heavy box. The item is followed by some definitions of any special terms that are used. This is followed by a description of the essential skills needed, and examples. Stories with web links in a shaded box give reported cases of expensive mistakes that have been made by not following good practice. ° Each section ends with a 'Check your knowledge' self-test marked with a writing hand symbol in the left margin. The answers are in the back of the book.You may be asked for a username and password taken from a page of this book. Typographical conventions Normal text appears in a normal typeface. Worksheet names and special terms are shown in italics. Spreadsheet file names are shown in boldface. Code samples and formulas appear in monospace text. Microsoft Excel Menu commands and dialog text appear in sans serif text. The > symbol means you continue to a submenu or dialog tab, eg File > Open. The underlined letters indicate where shortcut keys may be used. Excel function names are in uppercase, eg ROUND, SUM. We sometimes advise you to search the Help file contents for further detail. We provide a key phrase to type into the Answer Wizard or the Ask a Question box:

Edition details

• Publisher: Systems Publishing
• Publication date: 2005
• Language: English
• ISBN 10: 190540400X
• ISBN 13: 9781905404001
• Binding: Paperback
• Number of pages: 204