WIXLIB

An introduction to spreadsheet risk management1. Why are spreadsheets so prevalent today?Technology is developing rapidly, as are users’ expectations aboutwhat it should deliver – and when. This impatience poses challengesfor IT departments. When the IT department cannot meet users’expectations, they are more likely to explore alternative options.A spreadsheet is a powerful tool that in many cases is a viablealternative to lengthy software development cycles for userswho require results immediately or need to keep ahead of thecompetition. As a result, spreadsheets are everywhere. Theyenable users to quickly perform analysis that otherwise wouldbe difficult or time-consuming.3. Why do spreadsheets present a risk?Spreadsheets can provide a broad spectrum of solutions to the user.The following table contains some typical examples of spreadsheetuses and how they can go wrong:UseWhat can go wrongBillingA major telecom organisation invested millions in corebilling systems to support their key revenue earningstream: billing customers for calls made. For certaincorporate customers, however, the billing rules, whichwere often complex, changed from year to year.The billing team concluded that for these corporatecustomers, it was too difficult for IT to change the systemson a yearly basis. Therefore, flexible spreadsheets weredesigned that would download data from the core systemsand calculate the invoices.The ability of the user to develop and configure powerful solutionsin a spreadsheet environment without appropriate training orawareness is introducing a high degree of spreadsheet-relatedrisk into the corporate environment. This level of risk will growwith the increasing use and complexity of spreadsheets.The key reasons behind the growing use of spreadsheets include: They are flexible and easy to use. Immediate results are generated, with potentially very shortdevelopment periods. It is easy to become reasonably proficient in the use of aspreadsheet (though it is less straightforward to becomereasonably proficient in their design and development). They can be configured to the personal requirements of the user. They are readily accessible by nearly all users, as they are usuallya standard corporate desktop application. Spreadsheets can support the download and analysis of datafrom core systems. Over time, users have become more advanced in their useof spreadsheets. Spreadsheet software itself has become increasingly powerfulover the years, opening up greater functionality to users.2. What is spreadsheet risk management?A fundamental problem with spreadsheets is that untrainedusers tend to place undue trust in the integrity of the analysisthat is prepared in them. As users become more IT-literate,the number of spreadsheets in use is increasing, and theyare becoming significantly more sophisticated.Many companies rely on spreadsheets as a key applicationthat supports operational and financial reporting processes.The purposes of such spreadsheets are widespread, fromperforming complex modelling to make trading decisions, toaccounting reconciliations, to calculating employee bonuses.Spreadsheet risk management helps ensure that the risk presentedby spreadsheets is understood and appropriately mitigated. The billing rules were too complex for spreadsheet ownersto constantly check for possible user errors. As a result,errors were soon identified.While lost revenue was recovered from the relevantcorporate customers, the reputational impact on thetelecom organisation is difficult to quantify. Had a detailedreview of the spreadsheets not been performed, therevenue leakage would have remained undetected.ReportingAn accounting consolidation package provided areporting function that could not be configured tosupport the changing reporting requirements ofthe finance department.Spreadsheets were built that took the financial reportinginformation from appropriately controlled EnterpriseResource Planning and consolidation system software,manipulated the data and provided reporting tosenior management.Controls around the systems were regularly reviewedand assessed as operating effectively. The spreadsheetwas never in scope for the reviews as it was owned withinfinance by the individuals responsible for reporting.When the spreadsheets were reviewed in detail, asignificant error was identified in the calculation ofyear-end accruals – a result of an error within a numberof the calculations performed outside of the system inthe spreadsheet.Significant investment had occurred to ensure thatsystems were appropriately configured and controlled.This investment was entirely undermined by the creationof spreadsheets to produce reports that should have beenconfigured in the core IT systems.

UseWhat can go wrongUseWhat can go wrongPricingA commodities trading firm priced and managedexposure on its options trading book through a complexspreadsheet that included a coded Monte Carlo algorithm.DataqualityMany organisations use spreadsheets as a simple toolfor capturing data on large projects. A common exampleof this has been the capturing of data on risk and controlfor Sarbanes-Oxley projects. Spreadsheets are also oftenused to track remediation and closure of gaps.The spreadsheet was produced by a trader with advancedspreadsheet knowledge. The trader also operatedadditional manual controls that provided assurance thatthe spreadsheet was accurately calculating price andexposure levels.When the trader moved to another organisation, thespreadsheet was inherited by a new options trader whowas not an advanced user of spreadsheets. This tradermade some assumptions about the spreadsheet’soperation. Over time, errors were introduced into formulasand exposure levels were tracked inaccurately. Optionswere incorrectly traded and month-end profit and lossanalysis showed a significant loss on the options book.The error was tracked back to inaccuracies within thespreadsheet. The options trader had no knowledge ofthe errors.BudgetingA consulting firm employed basic spreadsheets to priceand budget client engagements. The spreadsheetsprovided analysis that allowed the engagement managersto calculate the hours and level of the team on theengagement. The objective was to ensure that the firmachieved a certain margin on each engagement. Thespreadsheets, while relatively simple, had little or nocontrol over the content. Formulas could be changedand pricing tables updated.Businesses are often left with large numbers ofspreadsheets that must be maintained over time.Organisations that have adopted this approach oftenwant to extract information from the templates anduse it – for example, to prepare weekly/monthlyprogress reports.Many organisations that have adopted this approach havefound that the production of management information isextremely time-consuming. Furthermore, when the datais consolidated into monthly reports, inconsistenciesare often identified. These are typically a combination oftiming issues and errors.Another common problem is that there often are multipleusers of the spreadsheets. This results in significantversion-control issues as the wrong versions are pickedup and used or two users attempt to make changessimultaneously, potentially undoing each other’s changes.Though the direct consequences of these data qualityissues were not significant, the cost of manually producingmanagement information and resolving the quality issueswas substantial.When errors were accidentally introduced into anengagement budgeting spreadsheet, they did notresult in significant financial impact for that particularengagement. However, the error was significantlycompounded when the spreadsheet was shared amongall the engagement managers and the model was usedto price other engagements.In addition to these examples, a simple Internet search forspreadsheet errors reveals numerous examples, includingbudgeting errors, financial statement errors, pricing errors,and fraud or bad decision-making as a result of poor information.The financial impact can be significant (many millions of pounds)and the damage to a company’s reputation can be even worse.Eventually, it was discovered that major engagementshad been priced inappropriately and the firm wouldnot achieve its target margin. The lost money was notrecoverable from the clients, as fees were part ofalready-signed contracts.Some frequently quoted examples include:“ A cut-and-paste error cost TransAlta 24 million when it underbidan electricity-supply contract.”Source: The Register“ Falsely-linked spreadsheets permitted fraud totalling 700 millionat Allied Irish Bank/Allfirst.”Source: EuSpRIG“ Kodak’s SEC 10-K filing reported a material weakness in its internalcontrols surrounding the preparation and review of spreadsheetsthat include new or changed formulas.”Source: Compliance Week

As spreadsheet users have become more proficient, theirspreadsheets have become more complex. Spreadsheets werenever designed to be enterprise-level applications. However,the growing use of complex and user-defined functions, lengthymacros and links to other spreadsheets and systems has ledto the development of highly complicated applications.4. Is the level of risk increasing?Yes. Spreadsheets are becoming more complex and users arefinding increasingly novel applications for them. User training andawareness is still limited, however. As spreadsheets become morecomplex, they are more prone to error. As users are perceived tobecome more IT-literate, more spreadsheets are being used tosupport critical business processes. A combination of these twofactors is significantly increasing the overall risk profile for manyorganisations. The perceived level of risk is also rising due togrowing awareness and understanding of the risk that uncontrolledspreadsheets pose, as well as increased regulatory andaudit scrutiny.5. What about other desktop tools available to users?While this document uses the term ‘spreadsheet’, the issues andapproaches outlined could just as easily apply to other desktoptools available to end users. These tools include database software(e.g. Microsoft Access), reporting tools (e.g. Crystal Reports) orany other ‘power’ tool that can be configured by the end user anddepended upon to support operational processes.End-user-developed databases can be even more risky thanspreadsheets, as in many cases the data manipulation is lesstransparent to the end user. Reporting tools often allow usersto develop customised reports which, if the query is configuredincorrectly, can result in users inadvertently restricting the datathey report.However, the key difference between spreadsheets and otherdesktop tools is that spreadsheets are by far the most commonlyused, and have by far the broadest end range of users.The technology solutions referenced later in this guide to supportthe management of spreadsheets differ from those available forother desktop tools. In certain cases, the solutions have somefunctionality that can be applied across multiple desktop tools,but this is generally the exception. 6. Why has spreadsheet risk management suddenlybecome important?Spreadsheet risk always has been important. However, as discussedin answers to previous questions, there are indications it isbecoming more significant.The UK’s H. M. Customs & Excise, in its ‘Methodology for the Auditof Spreadsheet Models’ (2001), said that “the complexity andfunctionality of spreadsheets has reached levels of sophisticationthat few could have imagined even five years ago. The consequentthreat posed to businesses by such powerful ‘end-user’applications, mainly in the hands of untrained users, is immense”.This observation has continued to hold true in the years sinceits publication.It is also fair to say that recent regulatory compliance initiativeshave forced organisations to consider the spreadsheet risk to whichthey are exposed. In particular, guidance produced in support ofthe Sarbanes-Oxley Act has advised organisations to specificallyconsider spreadsheet risk. Regulatory bodies and external auditfirms have detected the increasing exposure to spreadsheet riskand are taking action to ensure it is addressed.7. Do technology solutions exist that can assist with managingspreadsheet risk?Yes. The section ‘Technology enabling effective spreadsheetrisk management’ provides more detail about the types ofsolutions available.

Executive ownership and governance8. Who is accountable for effective spreadsheet risk management?Senior management (‘the executive’) including, but not limited tothe board, is ultimately accountable, on behalf of the organisation,for the effective management of all risk, including spreadsheet risk.This executive accountability is usually to the shareholders (whereapplicable) and the regulatory bodies governing the industry andenvironment in which the organisation operates.The executive must understand: What is the risk? Where does the risk exist? How significant is the risk? Who is currently dealing with the risk? When will this risk be managed to an acceptable level?Given the ever-increasing dependency on spreadsheets, as wellas the external focus on them, the executive is increasingly awarethat spreadsheet risk is an area of exposure that should be activelymanaged. This potentially time-consuming task should leveragemany of the risk management processes already in operation,including current compliance efforts.9. What do the major legislative acts have to sayabout spreadsheets?The major legislative acts in existence today, namely SarbanesOxley, Companies Act, Turnbull, Basel and MiFID, do not focusspecifically on spreadsheet risk. However, effective managementof spreadsheet risk is required to satisfy the requirements of eachof these regulations.Legislation tends to provide more generic statements such as,“An effective system of internal control ” (Turnbull). This ensuresa broad sweep of requirements that will cover as many scenariosas possible within a diverse commercial environment. Therefore,organisations and the monitoring bodies (e.g. external audit firms,regulatory authorities) are required to interpret the legislationand determine how its requirements should be applied toeach organisation.What has become clear over the last five years is that the regulatorybodies and audit firms are becoming increasingly aware ofthe potential exposure to spreadsheet risk that can exist in anorganisation. In fact, this issue became so significant during theSarbanes-Oxley compliance peak between 2004 and 2006 that themajor audit firms released various papers and guidance to ensureorganisations were aware that spreadsheet risk management was anarea they would be focusing on specifically. In many organisations,they found that managing spreadsheet risk was an issue for whichno one in the organisation was taking accountability.Spreadsheet risk management is therefore a requirement forall organisations that are subject to these regulations. The onlyscenario in which this would not apply is when an organisation hasno significant business processes supported by spreadsheets.In fact, the only way an organisation without an effectivespreadsheet risk management strategy can be confident it isnot exposed to significant risk is to prevent users from havingaccess to the application. This is clearly not a practical solutionfor most organisations.10. How can the executive define and communicate theirspreadsheet risk management requirements?Typically this is achieved by creating a spreadsheet riskmanagement policy that states what the executive expectsfrom the organisation. Then, the organisation will need todefine how it implements the policy in a spreadsheet riskmanagement operating model. This operating model shouldset out accountability, roles and responsibilities, processes,controls and minimum control standards.When defining such requirements, the executive should takeinto account processes in place to ensure compliance withany existing policies. If there is not an effective complianceprocess in place, it is likely the spreadsheet policy will becomeanother ineffective piece of paper on the pile of existing policies.Further guidance on implementing an effective governance,risk and compliance programme can be found in Protiviti’sEnterprise Risk Management FAQ Guide.If clear and regular assurance is provided to the executive on otherpolicies, the executive can be more assured that introducing aspreadsheet risk management policy will be an effective vehiclefor ensuring the organisation can begin to effectively managespreadsheet risk.11. Who should operate spreadsheet risk management processes?Because the IT department provides the infrastructure and softwarecritical to the operation of the spreadsheets, it is obviouslyresponsible for ensuring that this aspect of the technology iseffectively controlled. However, the IT department cannot beheld solely responsible for operating risk management processesaround individual spreadsheets.Spreadsheets are designed, implemented, updated, tested(sometimes) and made operational by the owners and users ofthose spreadsheets. This is why spreadsheets are so prevalent,and this should not change. However, spreadsheet ownersshould be responsible for operating effective spreadsheet riskmanagement processes.The executive should define, on behalf of the business, whatconstitutes effective spreadsheet management processes.The executive also should ensure appropriate monitoring isput in place to ensure compliance with these processes.

It is important that organisations do not let responsibility forspreadsheet risk management fall between the gaps. The businessside often considers spreadsheets to be IT’s responsibility andremoves them from the scope of any risk management work. Thesame goes for IT professionals, who often consider spreadsheetsto be owned by the business side. Clearly, if nobody is takingresponsibility for spreadsheet risk management, the executivehas a problem.The organisation can resolve this confusion by defining clear rolesand responsibilities within the spreadsheet risk managementoperating environment.The IT department may be able to provide solutions to assist witheffective spreadsheet risk management. In this scenario, the ITdepartment would become accountable for the effective operationof these solutions; therefore, the responsibility for effective riskmanagement may be shared between the IT department and thespreadsheet owners.In practice, co-operation between business and IT iscritical to the operation of an effective spreadsheet riskmanagement environment.12. Why should we report on spreadsheet risk to seniormanagement and the executive?Creating a reporting process that demonstrates an effectivespreadsheet risk management process is critical for thefollowing reasons: It allows operational management and the executive tounderstand the key risks to the organisation, the significanceof those risks and the work in progress to manage those risks. Better transparency of spreadsheet risk management drivesbetter behaviour among operational personnel. Demonstration of effective risk management processes iscritical for satisfying legislative requirements.Failing to implement a discrete process for reporting on theeffectiveness of the spreadsheet risk management environmentis a missed opportunity. Ensuring there is transparency overthe effectiveness of the whole operational risk managementenvironment is a goal any organisation should look to achieve.Many organisations already have some form of operational riskmanagement reporting process in place. In these cases, thecritical step is the integration of the spreadsheet risk managementprocesses into the current assessment and reporting approach.13. What should the risk responsibilities of a spreadsheetowner cover?The spreadsheet owner should be responsible for the identificationand assessment of operational risks that exist in the spreadsheetsthey own.In fulfilling these responsibilities, the spreadsheet owner should beprovided with guidance on what is expected and given access to thetools necessary to ensure their assessment of risks and controls isconsistent with the rest of the organisation.The spreadsheet owner should be responsible for the identificationand operation of appropriate controls that mitigate the risk to anacceptable level. They also should be responsible for acceptingspreadsheet risk within defined limits of authority. Limitations onthe amount of risk they can accept should be agreed upon withsenior management or the executive.14. What should be the role of the IT department?It has been emphasised that the spreadsheet ownersare responsible for controlling the risks associated withtheir spreadsheets.However, there is an assumption that the IT infrastructure reliedupon by the spreadsheet owners is available and secure. This isthe responsibility of the IT department. A lack of control over thisinfrastructure typically has an impact on the availability or securityof spreadsheets (as well as a pervasive impact across othertechnology within the organisation).When assessing the risks associated with a spreadsheet, thespreadsheet owner might choose to rely on the controls operated bythe IT department. For example, a spreadsheet may be needed everyday to process key transactions. The availability of the spreadsheetis therefore critical, and the spreadsheet owner will wish to establishthat the spreadsheet will be available and can be recovered inthe event of any problems. The owner will have to establish theeffectiveness of these controls through interaction with theIT department.Another example involves access to the spreadsheet. Thespreadsheet owner may determine that the spreadsheet shouldbe restricted to certain individuals. Therefore, IT may need toset up a storage location that has restricted access and ensurethese restrictions are maintained unless further access has beenauthorised by the spreadsheet owner.In both of the above examples, IT implements the required controls.However, these controls have been defined by the spreadsheetowner, who must assess the adequacy of these controls againstthe risks he is seeking to address.

15. What should be the role of operational risk departments?Operational risk departments exist within many organisations.Typically, mature operational risk management frameworks alreadyhave been implemented and processes around these frameworksare well established and operating effectivel

An introduction to spreadsheet risk management 2 1. Why are spreadsheets so prevalent today? 2 2. What is spreadsheet risk management? 2 3. Why do spreadsheets present a risk? 2 4. Is the level of risk increasing? 4 5. What about other desktop tools available to users? 4 6. Why has spreadsheet risk management suddenly become important? 4 7.