09-01 Contents: EuroCACS, SSQC, Euro news,Spreadsheet self-checking
ISSN 1649-2374 This issue online at http://www.sysmod.com/praxis/prax0901.htm [Previous] [Index]
|Systems Modelling Ltd.: Managing reality in Information Systems - strategies for success|
IN THIS ISSUE
|1) IT Risk
EuroCACS 15-18 March Frankfurt
Software & Systems Quality Conference 3-4 March Dublin
Slovakia Enters Euro Zone
European art and culture archives online
Unfair commercial practices: advice to consumers
Tips for cleaning up sloppy spreadsheets
|14 links in
newsletter and Archives
Subscribe and Unsubscribe information
Happy New Year! 2008 certainly ended with bangs and
whimpers; let's hope we make it through 2009 OK!
EuroCACS, presented by ISACA, provides the most comprehensive training for IT audit, security and governance professionals in Europe.
Ray Butler and I present a one day pre-conference workshop on Spreadsheet Auditing
Spreadsheet models are widely used to make vital business decisions and perform processes, but are known to be error-prone and high-risk applications. Despite the risks, they are often not tested. Testing can be tricky and time consuming for the auditor or reviewer, and if contracted out to specialist service companies in the field, expensive.
This one-day workshop:
The participant will learn more about:
Venue: Croke Park Conference Centre, Dublin
Date: March 3rd & 4th 2009
http://www.sqs-conferences.com/ire or phone (+353) (0) 1 657 2506
The keynote presenter is Sir Richard Noble, the man who broke the land speed record, achieving 633mph in Thrust 2 - on a budget a fraction of that of his competitors.
Slovakia is a high-income economy with one of the fastest growth rates in the EU and OECD. It joined the European Union in 2004 and joined the Eurozone on the 1st of January, 2009 at the rate of 30.1260 korunas to the euro.
Europeana.eu is a collaboration between universities, research institutes and content providers. A research prototype of a semantic search engine contains data of the Rijksmuseum Amsterdam, the Musée du Louvre in Paris, and the Rijksbureau voor Kunsthistorische Documentatie (Netherlands Institute for Art History). in The Hague.
http://www.isitfair.eu/ Consumer information on unfair commercial practices
National consumer associations in your country are usually aware of the rogue traders active in your area/country. They can advise you on domestic problems.
2. The European Consumer Centres Network (ECC-Net)
This network of consumer advice centres helps consumers specifically with cross-border disputes. It was set up in January 2005 by the European Commission in cooperation with the national authorities. The centres provide information and give advice and assistance to consumers with their complaints and the resolution of disputes.
CFO Magazine had a popular series of articles in 2008 on reader's peeves and solutions to spreadsheet sloppiness:
http://www.cfo.com/article.cfm/11288290 Spreadsheet "Worst Practices"
http://www.cfo.com/article.cfm/11525407 Sloppy Spreadsheets: Readers Speak Out
http://www.cfo.com/article.cfm/11950766 Sloppier Spreadsheets: How Bad Can They Get?
Many of the complaints about spreadsheets missing cross-foot checks or not being easily readable. So this month I'll list some tips on building self-checking into a spreadsheet in order to provide more confidence to the reader that your spreadsheet is robust.
In Accounting 101 you learn that a balance sheet must balance. (Usually by defining owner's equity as a balance line equal to total assets less total liabilities.) Chemical engineers prove their calculations by a mass balance that shows how all the material inputs end up as outputs. To prove that your spreadsheets have some data integrity, you need to look for any opportunity to verify that a total of inputs is the same as the total of outputs.
For example, if you have a total budget to allocate, the sum of the allocated amounts must equal the original total.
When forecasting projects, there is often no known input amount to compare with an output. In such cases we look at the absolute value of changes or the proportional value of changes to see if they correspond. One might compare last year's increase with this year's to see if they are as expected. Unfortunately, expectation is a dodgy measure that works in stable conditions but not in the unstable real world - see 'Expectations' below.
We've all seen this kind of total calculation:
and know there is a high likelihood of a pointing error - that one of those references is pointing to the wrong cell; or that there is some reference overlooked in creating that chain. It arises when there are detail lines, intermediate totals calculated by =SUM() and to get a total of these intermediate figures, each needs to be individually selected. Here are some ways to get around this:
4a) Assuming that every detail figure is also included in the =SUM() intermediate formulas, simply calculate =SUM(B2:B67)/2.
4b) Replace all the =SUM( formulas with =SUBTOTAL(9,... Then replace the multiple-plus formula with =SUBTOTAL(9,B2:B67). The SUBTOTAL function ignores the results of other SUBTOTAL functions in its range.
If the data is an export from an accounting system or database, or is a simple table of numbers, you can get Excel to automatically put in the subtotal formulas, so you don't have to. Ensure that every column has an identifying header; and that every row has an entry in a column that indicates what group this row belongs to, and the table is sorted by that column. Then choose the Data Subtotals command, specify the grouping column, and check all the data columns that you want subtotalled; and Excel puts them in for you. When you insert or delete rows in the table, simply re-do the Subtotals command.4c) There may be other intermediate calculations that are only required for display and not for further calculation. They could be SUM or cumulative calculations that give a running accumulation from left to right. A way to exclude their result is to use the text functions =FIXED() and =DOLLAR() which produce text results that are excluded from SUM totals. Be aware that Excel will still treat them as numbers if they are referred to individually in a formula.
How do you make sure the totals still refer to the correct cells if rows are inserted or deleted? Take for example a formula in B67 =SUBTOTAL(9,B51:B66)
If you insert a row at row 51 the formula now reads =SUBTOTAL(9,B52:B67)
If you insert a row at row 67 the formula still reads =SUBTOTAL(9,B51:B66)
Either way, the row you inserted is excluded from the calculation. On the Tools > Options > Edit tab there is a setting "Extend data table formats and functions" that may automagically adjust the formulas for you, but I would not rely on it.
To avoid the risk of missing an inserted row or column, always make sure every range to be summed begins and ends at a blank cell. If the first row consists only of a heading in the first column, that is readable enough. If not, you can enter in the first column the prompt "(Insert further rows below this line)" and in the last row "(Insert further rows above this line)". You could also fill the cells with ten underline characters, or make the interior colour black or blue and make the row a smaller height.
Another way to protect against insertion at the bottom of the column is: in B67 enter =SUBTOTAL(9,B51:OFFSET(B67,-1,0))
That looks like a circular reference to B67 in B67 but in fact the OFFSET function is interpreted as a reference first so in B67, OFFSET(B67,-1,0) is interpreted as B66.
http://www.mvps.org/dmcritchie/excel/offset.htm points out an alternative using INDEX to always refer to the row above:
However, both of those are more difficult for general Excel users to understand, and neither protects against insertion at the top of the range.
An obvious flaw in relying on expectations is getting the answer you expect instead of the correct answer. Another is getting an answer that is materially different but not obviously so. It is unavoidable that we tend to look at the new in terms of the familiar, and when that raises questions it's useful. But when it does not raise questions that does not mean the answer is correct - merely that that particular test gave us no information.
Remember the Dilbert cartoon where he says to a person offering a spreadsheet checking "I don't think accuracy matters if no one can tell what it's for", and that person repors back to the puzzled boss "And Dilbert found no inaccuracies!" Some people are better than others at finding errors, just as good developers can be many times more productive than bad ones.
http://sysmod.buy.ie/catalog/product_info.php?products_id=188 Our offer - free shipping to EU .
Simply send your comments to FEEDBACK (at) SYSMOD (dot) COM
Thank you! Patrick O'Beirne, Editor
This photo is a souvenir of the Christmas season TV station ident for RTE 1. I'm on the left :-)
Copyright (c) Systems Modelling Limited,
. Reproduction allowed provided this copyright notice is included.
We appreciate any feedback or suggestions for improvement. If you have received this newsletter from anybody else, we urge you to sign up for your personal copy by sending a blank email to EuroIS-subscribe (at) yahoogroups (dot) com
For those who would like to do more than receive the monthly newsletter, the EuroIS list makes it easy for you to discuss issues raised, to share experiences with the rest of the group, and to contribute files to a common user community pool independent of the sysmod.com web site. I moderate posts to the EuroIS list, to screen out inappropriate material.
Patrick O'Beirne, Editor
ABOUT THIS NEWSLETTER
"Praxis" means model or example, from the Greek verb "to do". The name is chosen to reflect our focus on practical solutions to IS problems, avoiding hype. If you like acronyms, think of it as "Patrick's reports and analysis across Information Systems".
Please tell a friend about this newsletter.
We especially appreciate a link to www.sysmod.com from your web site!
To read previous issues of this newsletter please visit our web site at http://www.sysmod.com/praxis.htm
This newsletter is prepared in good faith and the information has been taken from observation and other sources believed to be reliable. Systems Modelling Ltd. (SML) does not represent expressly or by implication the accuracy, truthfulness or reliability of any information provided. It is a condition of use that users accept that SML has no liability for any errors, inaccuracies or omissions. The information is not intended to constitute legal or professional advice. You should consult a professional at Systems Modelling Ltd. directly for advice that is specifically tailored to your particular circumstances.
We guarantee not to sell, trade or give your e-mail address to anyone.
To subscribe to this Newsletter send an email to
EuroIS-subscribe (at) yahoogroups (dot) com
To unsubscribe from this Newsletter send an email to
EuroIS-unsubscribe (at) yahoogroups (dot) com
EuroIS is the distribution list server of the PraxIS newsletter. It also offers a moderated discussion list for readers and a free shared storage area for user-contributed files. The archives of this group are on YahooGroups website http://finance.groups.yahoo.com/group/EuroIS/