The Hindu Business Line : Make spreadsheets yield their secrets

Spreadsheet users looking for `good practices', stop right here. As also auditors "looking for evidence of fraud, such as deliberately concealed data or functionality". Also, read up on Planguage.

THE European Computer Driving Licence Foundation Ltd (ECDL-F) www.ecdl.com is `the global governing body of the world's leading end-user computer skills certification programme'.

"With over five million participants, the European Computer Driving Licence (ECDL) is the world's largest vendor-neutral end-user computer skills certification and is internationally recognised as the global benchmark in this area.

ECDL is currently available in 138 countries and has been translated into 32 languages around the world, making it the world's leading certification programme," is information from the site that most of us may not be aware of.

You'd also learn that the European Spreadsheet Risks Interest Group (www.eusprig.net) helped to frame the ECDL syllabus.

For those taking up the advanced module of the certification, here is Patrick O'Beirne's Spreadsheet Check and Control, from Systems Publishing (www.SystemsPublishing.com) .

The book is also for spreadsheet users who look for `good practices', says the introduction, offering tips on how to avoid the most common errors and assuring assistance to auditors "looking for evidence of fraud, such as deliberately concealed data or functionality".

The author cites Barry Boehm's prediction: "The ranks of sorcerer's apprentice user-programmes 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."

An example of such a defect was the one million pound-VAT error that the UK Customs Department found in 2001. "A very good payback on their investment in developing spreadsheet auditing software and skills," notes O'Beirne. The US FDA (Food and Drug Administration) has rigorous specifications for the integrity of electronic records including `spreadsheets used as laboratory log books,' he points out.

One of the first points that the author highlights is about the utility of `spreadsheet File Properties'. Fill in the file's data, so that it is easier to search for files, he writes.

"Excel can prompt you to fill the file properties each time you save." On documentation, again, he advises putting in a separate `Instructions' worksheet, with all "user instructions and explanatory notes on calculations and dependencies on external data sources".

Describe assumptions and limitations, even if they seem too obvious; also, "state what is intentionally omitted from the scope of a spreadsheet project". Bear in mind that "complex calculation methods, processing sequences, and shortcut tricks, obvious when building the spreadsheet" may fade from memory over time.

"Use font effects, such as size, bold, italic, colour, borders and shading, moderation," counsels O'Beirne, citing John Raffensperger's rule, "Format for description, not decoration." Instead of merging cells and then having problems in copy and paste operations, try `centre across selection' option. "When spreadsheets are to be exchanged internationally," a date format such as 12-October-2005 is better than 12/10/05.

O'Beirne calls long complex formulas `write-only' because nobody else can read them! It would be right, instead, to break up the formula into intermediate ones, though in additional rows. When baffled by a formula, use the `step through' facility available in `Tools > Formula Auditing > Evaluate Formula'. Beware `Precision as displayed', because truncation of data can lead to problems such as the $36-million underestimate that the book cites from Ray Panko's article in Business Week two decades ago. Similarly, parentheses in the wrong place meant a fall in projected gains from $200 million to $25 million, as in another case quoted in the book.

Armed with this book, you can check your accountant's work for controls when cells are one too many!

On how to talk design ideas

TOM Gilb's Competitive Engineering, from Elsevier (http://books.elsevier.com) is `a handbook for systems engineering, requirements engineering, and software engineering using Planguage.'

But what's Planguage? It is plan plus language, explains Gilb.

"Planguage consists of a new industrial system engineering language for communicating systems engineering and management specifications, and a set of methods providing advice on best practices," he adds.

At the end of the introduction is `a friendly warning' from the author: "This book is intentionally written in a very condensed style. Don't get discouraged if you have to slow down to understand it, or if you have to reread parts. It is `useful ideas per hour' which count, not `pages turned per hour'."

Practical unit of measure, that is; and you can grab more on `how to quantify' in a chapter on `scales of measure'.

There, Gilb exhorts you to learn the art of developing your own tailored scales of measure for the performance and resource attributes, which are important to your organisation or system.

Maintain `a library of meters', he advises, because meters provide `practical methods for actually measuring the numeric scale values'.

Gilb lays down 10 principles for scale definition. For instance, quantification is mandatory because "if you cannot put numbers on your critical system variables, then you cannot expect to communicate about them, or to control them". Apply the Cartesian principle, and put complexity at bay, guides Gilb, because high-performance attributes need decomposition into sub-attributes. Another principle cautions against the choosing of the easy scale; go for "scales of measure that give you the most direct control over the critical stakeholder requirements", Gilb suggests.

Projects exist primarily to deliver stakeholder performance requirements, declares Gilb in a chapter on resources, budgets and `costs of solutions'. It may seem matter-of-fact, but the author emphasises that projects must control costs, lest they exceed the sponsors' capability for providing resources. "Ultimately, every system requirement can be viewed in terms of resources," according to Gilb. "When making decisions about system changes, a stakeholder is merely exercising choice over where resources are to be expended - by choice (now) or by default (later)."

Narrow cost control won't help; exercise `value for money' control instead. For example, "there is no point in constraining head count on a project only to experience that the consequence is project delays to market, which threaten profitability". Perfection can be elusive and make costs dangerously rising infinitely, beware. "So, as we become more ambitious regarding performance, we must become much more exact at specifying the performance levels."

The best approach to control costs for complex systems is `Design to cost', along with Evo or Evolutionary Project Management, says Gilb. Design to cost means you intentionally select designs that fit within your committed cost budgets, he explains. Using Evo method, deliver to your customer or market "a succession of improvements in the system's functionality and performance levels." Thus, deliver first `the highest priority improvements'; learn from `frequent feedback from the partial deliveries'; and make `necessary adjustments to cost budgets'.

A book that merits a thorough reading before you launch on your next project. Catch up also with `Rich Requirement Specs: The use of Planguage to clarify requirements,' on www.gilb.com.

Tailpiece

"I started growing a French beard when... "

"When you realised your pate growing balder?"

"No, when I began feeling that the computer was trying to act smart!"

Books2Byte@TheHindu.co.in

Article E-Mail :: Comment :: Syndication :: Printer Friendly Page