PraxIS Jan. 2009

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]

_______________________________________________________

Welcome to PraxIS

Happy New Year!  2008 certainly ended with bangs and whimpers; let's hope we make it through 2009 OK!

Patrick O'Beirne

_______________________________________________________ _______________________________________________________

1)  IT Risk & Quality

EuroCACS 15-18 March Frankfurt

http://www.isaca.org/eurocacs

EuroCACS, presented by ISACA, provides the most comprehensive training for IT audit, security and governance professionals in Europe.

WS2—Spreadsheet Auditing (one day) 14 March 2009

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:

• Offers a comprehensive approach to auditing spreadsheets.
• Uses real-world examples and hands-on learning to demonstrate the benefits of such an approach.

The participant will learn more about:

• Where to start and the most efficient techniques to use
• How to cut down a system of spreadsheets to a manageable audit task
• The symptoms that indicate potential or actual problems
• How a company can create an inventory of its critical spreadsheets, assess them for risk and prioritize scarce resources
• Little-known secrets of Excel's auditing features
• The causes of spreadsheet errors
• The risk environment and processes around spreadsheet use
• Techniques for inspecting a spreadsheet for errors
• Techniques to reduce the incidence of errors in spreadsheets
• What to look for in software tools

Software & Systems Quality Conference

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.

____________________________________________________________
____________________________________________________________   

2) Europe news

Slovakia Enters Euro Zone

http://en.wikipedia.org/wiki/Slovak_euro_coins 

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.

http://www.spectator.sk/articles/view/34005/10/sramko_conversion_to_euro_has_been_smooth.html

European art and culture archives online

http://www.Europeana.eu

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.

Unfair commercial practices: advice to consumers

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.

http://ec.europa.eu/consumers/empowerment/cons_networks_en.htm#national

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.

http://ec.europa.eu/consumers/redress_cons/webcenters_en.htm

____________________________________________________________
____________________________________________________________

3) Spreadsheets

Cleaning up sloppy spreadsheets

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.

1) Cross foot

2) Balance

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.

3) Proportion

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.

4) Multiple plus ungood

We've all seen this kind of total calculation:

=B11+B17+B27+B37+B48+B67

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.

5) Room for expansion

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:

=SUBTOTAL(9,B51:INDEX(B:B,ROW()-1))

However, both of those are more difficult for general Excel users to understand, and neither protects against insertion at the top of the range.

6) Expectations

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.

Spreadsheet Check and Control: 47 best practices to detect and prevent errors

http://sysmod.buy.ie/catalog/product_info.php?products_id=188  Our offer - free shipping to EU .

_______________________________________________________
_______________________________________________________

FEEDBACK

Simply send your comments to FEEDBACK (at) SYSMOD (dot) COM

Thank you! Patrick O'Beirne, Editor

_______________________________________________________ _______________________________________________________

4) Off Topic

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, http://www.sysmod.com . 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!
______________________________________________________
ARCHIVES
To read previous issues of this newsletter please visit our web site at http://www.sysmod.com/praxis.htm

DISCLAIMER
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.
_______________________________________________________
PRIVACY POLICY:
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/
_______________________________________________________