09.09.10

By Mel Glass, Director of Business Development EASA

Spreadsheets may not come with a business health warning on them as tobacco has done for some time, but as Mel Glass, EASA's director of business development explains, there is an argument that they should....

How do you define business efficiency in financial terms other than the bottom line in your periodical budget performance reports?

That’s a simple one from a private business perspective — it all comes down to your business’ or business division’s profitability, contribution or whatever your accounts people call the money that remains after deducting fixed and marginal costs from the revenues that your business generates.

If your company is a listed one, then one indicator of the efficiency of the firm is its share price and, as we all know, share prices can dip — and soar — for all sorts of reasons, and sometimes for reasons that have no substance at all. Or due to a misunderstanding of what is happening inside the company.

Which is exactly what happened to the C&C Group — producers of the popular Magners cider — in the summer of 2009 when a misunderstanding over the firm’s revenues sent the group’s share price tumbling by a whacking 15 per cent www.bit.ly.

The misunderstanding was caused by senior management initially toasting what they originally thought was a 3.0 per cent rise in revenues in the four months to June, but they ended up drowning their sorrows when it transpired that the group's revenues had actually dipped 5.0 per cent during the period.

In the UK, the sales figures were even worse, as the company tapped a 12.0 per cent slump in cider revenues, rather than a modest 1.0 per cent as was initially announced.

The problem was blamed on data being "incorrectly transferred from an accounting system used for internal guidance to a spreadsheet used to produce the trading statement."

A subsequent investigation revealed that there was nothing wrong with the group's accounting systems - a simple human error meant that the company's spreadsheet gave the wrong figures and, quite simply, a more complex calculation of 2 plus 2 gave the wrong answer.

What's ironic about this situation is that Stephen Glancy, C&C Group's group finance director and the firm's CEO - who has a solid track record with Scottish and Newcastle amongst other firms - said that the group's profit outlook or sales volumes figures had not changed. It was all down to spreadsheet error.



But why did the spreadsheet result foul up?

The reason is a simple but devastating one. Spreadsheets are, by definition, a changing business canvas. One member of staff can create the basic spreadsheet, complete with cell definitions and matrices, allowing other staff to routinely enter the data into the appropriate spreadsheet cells, and so generate - through a logical and repeatable calculation process - a finished spreadsheet.

But if someone else exports the data file - complete with the spreadsheet structure - perhaps to work on his/her laptop at home, but then presses the wrong button(s) whilst at home?

Although the data in the spreadsheet might not change, the underlying structure of the cells and their relations with each other might, causing the totals of the various cells to be miscalculated.

This type of calculation error is called spreadsheet version drift and is a serious, as well as a recurring problem in many businesses, as when, for example, an employee returns to the office after a work-at-home weekend, and relays the revised - but fatally modified - spreadsheet to his colleagues via email, the error effectively becomes set in stone.

No one - from the lowliest office clerk to the managing director - thinks to look back at the previous version of the spreadsheet, and, quite frankly, why should they? The calculations have been carried out by a competent member of staff who made a relatively simple error.

And this is exactly what happened at the C&C Group.

As I mentioned, the problem is a recurring one. A year before the C&C Group spreadsheet-triggered share slide, the Financial Services Authority tore into Credit Suisse's business credibility to the tune of 5.6 million pounds, following a financial write-down fiasco triggered by - you guessed - spreadsheet error.

Before the writedown, Credit Suisse had announced a $2 billion 10-year bond issue, which was due to close on 19 February 2008. Following the announcement, the financial aspects of the bond issue were sent flying and the settlement was postponed.

Delving into the FSA report on this massive fiasco (www.bit.ly) reveals that $1.67 billion of the multi-billion dollar variance was attributable to the UK operations of Credit Suisse.

"The positions which were the subject of the write down, mis-markings and pricing errors were proprietary positions," said the report.

"Credit Suisse announced that, following its internal review, it had concluded that the write down had been caused by pricing errors that were, in part, the result of intentional misconduct by a small number of traders and that the controls put in place to prevent or detect this activity were not effective. Credit Suisse also identified a series of remedial actions," the report added.

In theory, standard business governance safeguards - which roll constantly as an international bank centre staff go about their business duties - should generate a `red flag' when a seriously erroneous trading position takes place.

But what if the system of business monitoring is unsound? Then the `red flags’ are not generated in response to the correct situation and, whilst the FSA report in to the Credit Suisse fiasco is couched in financial jargon - the conclusion is inescapable: it all comes down to spreadsheet miscalculations.

C&C Group and Credit Suisse were lucky. Lucky because their market valuation was sufficiently large not to cause a financial hole below the water line, effectively putting them out of business.

Small and Medium Enterprises

But let's translate these devastating spreadsheet error scenarios into the business sector and assume an MD of a 200-employee firm takes a significant loan from his/her bank on the basis of healthy sales growth - even in the current recession.

Six months into the five-year loan, the financial director reports the next accounting period figures do not stack up, and actually show a 15 per cent reduction in sales. Going back over the previous period's figures reveals that sales were not up 1.0 per cent, but actually down 12.0 per cent.

Arghh!

Later on, mainly because of having to pay off the loan — which was designed to help the firm increase capacity and modernise — the company starts to hit financial problems and lays off around half its workforce.

Despite this massive cutback, the company is still liable for a further four years of financial agony, committed to paying the loan facility down with its bank.

Half way through the remaining four years of the loan, the company hits the buffers and goes into liquidation.

The bank cashes in on its insurance and the company joins the ranks of the failed. 200 employees are laid off - not because the firm’s management made seriously bad decisions or the market turned on them, but because a junior member of staff hit the wrong set of keys when transferring a spreadsheet file between computers.

And the solution is...

But what if, when the master spreadsheet is placed behind a controlling wall that permits easy spreadsheet file viewing and modification, but prohibits the underlying structure from being modified?

This is what EASA Software's technology does. Put simply it creates a Web interface using what programmers call a codeless — and IT users call easy-to-use - application that effectively places the master spreadsheet in amber, allowing users to view - and within carefully defined limits and by specific users - the data to be modified on a granularly controlled basis.

Our observations at EASA over the years when developing our spreadsheet integrity defence strategy show that errors creep into spreadsheets structures because they tend to be accessed in Microsoft Excel on the desktop.

Whilst this methodology is highly flexible, the freedom it allows means that users can accidentally - or intentionally - modify the structure of the spreadsheet, creating a new version over which there are no business controls.

Developed originally to solve spreadsheet problems in the nuclear industry, where the consequences of spreadsheet error could be unthinkable, EASA's approach centres on taking a business governance approach to the issue and creating a master spreadsheet that sits on a central server, with members of staff gaining access to the spreadsheet functionality and data via the Web.

Using this approach ensures that the user never has the spreadsheet on his/her desktop and prevents them from being able to modify the underlying master spreadsheet.

This does not stop, in any way, the owner of the spreadsheet from making approved changes, and then publishing the new version to all users - because, in fact, there is only one version of the spreadsheet.

Put simply, EASA's platform gives business management total control over the spreadsheet calculation and data manipulation process.

Is it possible to develop your own internal controlled spreadsheet environment to ensure that your business does not suffer as a result of spreadsheet errors?

The truth is yes, but the administrative overhead with developing — and maintaining — such a system can be significant, as business managers may discover that, as well as soaking up time like blotting paper, a customised and in-house developed control system can also create business paralysis and a reduction in business confidence in the information the spreadsheet generates.

Business paralysis is a significant risk that arises from a customised spreadsheet control system as management refrain from taking the next step in their business costing analyses because the complexity of changing the spreadsheet is just too daunting.

Assuming you have gone down the route of developing your own in-house control system, rather than deploying an EASA Software platform, then it can pay to use a business modelling tool for forecasting purposes, but the downside of this strategy is that it adds to the direct, as well as the indirect, costs of the spreadsheet control system.

Perhaps the most insidious result of developing an in-house spreadsheet control system, however, is information confidence.

As we’ve mentioned before, spreadsheets can contain a lot of errors, in part because users need to copy and paste information and formulas in multiple places. One misplaced formula can result in bad information, and the errors just get replicated over time.

There are both direct and indirect business costs associated with those inaccuracies, and whilst few people like to admit that their spreadsheets have problems, the reality is that most of them do.

How confident are you in your spreadsheets? On a scale of one to 10, with 10 being totally confident, if you are at less than seven, then you should not be using the spreadsheet to make important business decisions. It’s time to look at the EASA Software solution to defend the integrity of spreadsheet systems.

The 1-2-3 strategy - planning is everything

So how easy is it to implement the EASA spreadsheet management platform for a typical business?
The EASA approach is already used by many major companies such as Zurich, General Electric, Procter & Gamble, Hewlett-Packard to deploy business-critical spreadsheets. But unusually for such powerful software it is easily affordable and easy to implement for SME’s (Small and Medium Enterprises) too.

Although the process of replacing `tinkerable' spreadsheets requires careful planning for a seamless switchover at all management levels, breaking down the process into a series of steps means that it can be entirely logical and - rather than a big red switch being pulled over a weekend, an evolutionary approach can be taken.

This is less stressful and, of course, a lot easier for all the business parties concerned, and allows staff to get on with their normal job functions without impediment.

The nett result will be both tangible and intangible advantages. On the tangible side, improved - and provable — business governance is a big plus, with management able to prove to their internal and external audit people that best practice procedures are being followed.

Less tangibly, business management can have vastly improved confidence in the figures that their staff come up with, and this can often translate into enhanced investor confidence, since the reports they receive are effectively better qualified.

Our observations amongst clients — and, of course, potential clients - are that there is a noticeable increase in the understanding of spreadsheet version drift and the allied problems that spreadsheet errors can engender.

There has, we have found, been a massive recognition that there is an operational risk involved with spreadsheets, largely as a result of the increased importance that the audit and governance function has in most businesses.

As a result, we are currently working and talking with a number of private sector businesses in all types of industry, as well as government departments and their agencies in the public sector.

It’s worth noting that the EASA software solution is scalable both downwards and upwards, meaning that the spreadsheet defence technology can be deployed on an SME, as well as a corporate basis — this is flexible feature that you do not often see on a professional application like EASA’s and means that, as companies evolve and divisions merge or spin off, the platform can adapt to changing business circumstances.

For many of the businesses we see, they are aware of the spreadsheet drift problem, but not of the scale of the issue in their business, meaning that it is a top down issue that is difficult to quantify.

The reality, we find, is that the problem is usually larger and more complex than most business managers are aware of and, as a result, the initial risk analysis aspect of the planning process involves knowledge gathering and expansion stages.

Once this has taken place, the scale of the potential problem - and its potential to seriously damage or even sink a business - becomes clear and the remediation planning process can begin.

In many ways, the EASA spreadsheet management platform is analogous to the automatic braking and traction control systems that one finds in modern cars - you are barely aware that it is protecting you, as its background drive `management' is almost invisible.

But it's still there, protecting you constantly.