10-07 Contents: UK XL Dev Con, Eusprig 2010 summary
ISSN 1649-2374 This issue online at http://www.sysmod.com/praxis/prax1007.htm [Previous] [Index]
This issue covers July and August which is a vacation month. I
wonder would it be time to move from a 1990s style newsletter to a
Wordpress blog?
Patrick O'Beirne
____________________________________________________________
Morten Siersted’s keynote address described the danger of “trust me” models, that people do not review because they cannot. F1F9 spreadsheets are built by an industrialised process by a team of 25 developers in India, to the FAST modelling standard that creates highly navigable and therefore understandable models.
Dean Buckner of the FSA described the effects of spreadsheet jockeys creating islands of automation in a sea of data. If he sees any spreadsheet operations that match up related data (similar to a database Join), he knows that is where to look for problems.
Tom Grossmann presented the first side-by-side comparison of FAST, Operis and BPM’s SSRB modelling standard. He had 45 minutes but could have done with three times as much! He stresses the basic quality principle “Find the best way to do something and do it always”. Rather than a naïve expectation that there is one best way, he reminded us of the US Army saying that “there’s not a right way or a wrong way, there’s the Army way.”
Thomas Lemon showed how Protiviti use the ClusterSeven technology for compliance monitoring. Users register their spreadsheets into the compliance system. He stressed the importance of a clear executive mandate. He recommended that auditors should not only add controls but add value; and provide instructions on how to check.
Seb Dewhurst described EASA’s authoring tool for creating a web based interface for multi-user access to a spreadsheet running on a server. To prepare the spreadsheet, interactive elements such as VBA pop-ups need to be removed.
Patrick O’Beirne described ways to “refactor” spreadsheets – ie to improve their understandability and maintainability without changing their functionality. He covered formulas, reference structure, data normalisation, and VBA refactoring.
Simon Murphy finds that the more rigid the IT approval process, the more people will work around it with spreadsheets. He sees a growing market for migration of VBA applications to C#. A rule of thumb for filtering out the trivial spreadsheets is to look for those which are still in use after a year. He recommends deskside expert support rather than classroom training.
David Colver: “Names are natural”. Symbolic names were adopted since the earliest days of computing to make code easier to write and read. He argues that the same principle applies to the use of range names in Excel. Operis models can have thousands of names. To check names you have to verify that the range referred to is the correct one for the meaning of the model. Then, as for any other formula, you check that the formula itself is correct. The Operis OAK tool can de-apply range names, a facility missing in Excel. They do that to remove the names from their models for clients who specifically request it.
Darren Miller of Sumwise showed how a web client can be used to specify a model using symbolic names for row and column items which can then be used for calculations without the need to copy master formulas into ranges. The syntax used is similar to the Table syntax in Excel 2007/2010. He is looking for beta testers to give them initial feedback.
Ralph Baxter spoke of the role of continuous monitoring to provide a quick health check on every spreadsheet that is saved to a server.
Angus Dunn asserted that a spreadsheet generator such as RingTailXL would be a safer way to specify and build spreadsheets than at present by hand. Along with Angela Collins, he proposed a set of standards categorised as Require, Desire, Avoid, and Ban.
Ray Panko posed a test for recommendations: “is the prescription safe and effective?” Suggest we should have same ratio of 40% of ALC is in Test, like MS. SW testing covers 2 weeks training in MS. Humans find 56% of defects, software tools 0.5%. We’re teaching new dogs old tricks.
Grenville Croll presented a retrospective analysis of a 2001 spreadsheet that modelled Real Options. He concluded that the models underestimated the stochastic nature of the market and the buyers overpaid for the options.
Mary Pat Campbell requires users to leave a spreadsheet in a fit state for others; or for yourself one year later. Consider the plight of new management taking over, having to justify the previous teams’ models to the auditors. She recommended training in Excel, systems thinking, problem recognition and solving.
Dermot Balson’s paper also required that spreadsheets should be created with others in mind, nit just the original creator. “Make it easy to check and maintain, and safe to use.”
Ruth McKeever won the ISACA student prize with a paper that provided evidence that the use of range names hindered the debugging performance of novices. This is because any feature that reduces error visibility hinders the perception of those who are not aware of the design tradeoffs in such techniques.
Ben Rittweger presented a survey of spreadsheet risk management practices in organisations in Ireland. 59% claimed to have controls on access and validation.
Françoise Tort provided a set of principles to design a curriculum for teaching spreadsheets. A study had shown that students did not master basic spreadsheet concepts but rather acquired habits and routines. She recommended exploring unusual situations, implementation options, and quality.
Andrea Kohlhase discussed the use of semantic technology for assessing spreadsheets. Their institute DFKI created SACHS, a help system for their financial control system in Excel. It provides explanatory text, colour coding, and dependency graphs.
Simon Thorne presented a proposed MSc module in EUC risk management at UWIC with ten topics including spreadsheet errors and risk management.
Ruth McKeever's paper shows that people not trained in testing do not test well. If range names hinder novices, the problem is that they are novices. They have not yet passed the Pons Asinorum of range name use. They may know what they are, but they have not yet acquired awareness of the risks and tradeoffs in using them. People should not be asked to test or maintain a model above their skill level. One software requirement is maintainability, and the spec can say that it should be maintainable by people who don’t know range names. An analogy with DIY car maintenance is that it is not possible with modern cars; your choice is to stick to old cars or have the job done by a trained mechanic.
My paper on spreadsheet refactoring is available at
http://www.eusprig.org/2010/Spreadsheet%20Refactoring,%20O'Beirne.pdf
and the workbook (which I presented instead of Powerpoint slides) to illustrate some of the principles is at
http://www.eusprig.org/2010/Refactoring%20examples%20O'Beirne%20(enable%20macros).xlsb
If your browser downloads that as a .zip file, just change the
extension to .xlsb
And as the name hints, enable macros!
There was quite an amount of discussion and it was finally decided that Eusprig should set up a web page giving the pros and cons of the many proposals made to build better spreadsheets. Ray Panko would like any proposal to be backed by, or to stimulate, research to demonstrate that the prescription is safe and effective.
____________________________________________________________
____________________________________________________________
Simply send your comments to FEEDBACK (at) SYSMOD (dot) COM
Thank you! Patrick O'Beirne, Editor
_______________________________________________________
_______________________________________________________
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/
_______________________________________________________